本文内容来自 YashanDB 官网，原文内容请见 https://www.yashandb.com/newsinfo/7080870.html?templateId=1718516
基本信息
场景：测试库到生产库，需要迁移表结构，表数量较多。
源库版本：YashanDB 企业版 22.2.4.1
目标库版本：YashanDB 企业版 22.2.13.100
查询源库中对象情况
SQL> select object_type,status,count(*) from user_objects group by object_type,status;
OBJECT_TYPE STATUS COUNT(*)
--------------------- --------- ---------------------
TABLE VALID 115
INDEX VALID 206
SEQUENCE VALID 8
复制代码
确定迁移方案
一般情况采用 exp 元数据导出，然后 imp 导入，此处由于 22.2.4.1 版本兼容问题，无法采用。
考虑用 dbms_metadata.get_dll 手工迁移，手册如下：
https://doc.yashandb.com/yashandb/22.2/zh/%E5%BC%80%E5%8F%91%E6%89%8B%E5%86%8C/PLSQL%E5%8F%82%E8%80%83%E6%89%8B%E5%86%8C/%E5%86%85%E7%BD%AE%E9%AB%98%E7%BA%A7%E5%8C%85/DBMS_METADATA.html
支持对象中，不含 sequence 和 index，需要手工迁移。
要检索的对象的类型，不能为NULL，可输入的值有：
VIEW
FUNCTION
TRIGGER
PROCEDURE
PACKAGE
TYPE
TABLE
复制代码
迁移过程
sequence
SELECT 'CREATE SEQUENCE ' || 'NEW_SCHEMA' || '."' || sequence_name ||'"'|| CHR(10) ||
' START WITH ' || to_char(last_number + 1000000) || CHR(10) ||
' MINVALUE ' || to_char(min_value) || CHR(10) ||
CASE WHEN max_value <> to_number('999999999999999999999999999') THEN
' MAXVALUE ' || to_char(max_value)
ELSE
' NOMAXVALUE'
END || CHR(10) ||
' INCREMENT BY ' || to_char(increment_by) || CHR(10) ||
CASE WHEN cycle_flag = 'Y' THEN ' CYCLE' || CHR(10) ELSE '' END ||
CASE WHEN order_flag = 'Y' THEN ' ORDER' || CHR(10) ELSE '' END ||
CASE WHEN cache_size > 1 THEN ' CACHE ' || to_char(cache_size) || CHR(10) ELSE '' END ||
';' || CHR(10) || CHR(10) AS create_statement
FROM dba_sequences
where SEQUENCE_OWNER =UPPER('OLD_SCHEMA')
;
输出
CREATE SEQUENCE NEW_SCHEMA."ABC_SEQ"
START WITH 1000021
MINVALUE 1
MAXVALUE 9999999999999
INCREMENT BY 1
CACHE 20
;
复制代码
Table
SELECT dbms_metadata.get_ddl('table',table_name,'NEW_SCHEMA')||';' FROM user_tables;
输出样例：
替换 "OLD_SCHEMA" 为 "NEW_SCHEMA"，也需要替换tablespace，确保生产库tablespace存在。
CREATE TABLE "NEW_SCHEMA"."ABC_TOTAL"
("ID" BIGINT DEFAULT "NEW_SCHEMA"."ABC_SEQ".NEXTVAL NOT NULL ENABLE,
"B_ID" BIGINT DEFAULT NULL,
"I_ID" BIGINT DEFAULT NULL,
"C_NAME" VARCHAR(256) DEFAULT NULL,
"C_TOTAL" INTEGER DEFAULT NULL,
"IS_CHECK" TINYINT DEFAULT NULL,
"A_TASK" TINYINT DEFAULT NULL,
"C_BY" VARCHAR(255) DEFAULT NULL,
"C_TIME" TIMESTAMP(6) DEFAULT NULL,
"U_BY" VARCHAR(255) DEFAULT NULL,
"U_TIME" TIMESTAMP(6) DEFAULT NULL,
PRIMARY KEY ("ID")
USING INDEX
PCTFREE 8 INITRANS 2 MAXTRANS 255
TABLESPACE "NEW_SCHEMA" ENABLE
) PCTFREE 8 INITRANS 2 MAXTRANS 255
LOGGING
TABLESPACE "NEW_SCHEMA"
ORGANIZATION HEAP;
复制代码
Table Comment
SELECT 'COMMENT ON TABLE NEW_SCHEMA.'||table_name||' IS '||''''||comments||''';' FROM dba_tab_comments WHERE owner='OLD_SCHEMA' AND comments IS NOT NULL ORDER BY table_name;
示例代码：
COMMENT ON TABLE NEW_SCHEMA.ABC_TOTALIS '表-注释';
复制代码
Table Column Comment
SELECT 'COMMENT ON COLUMN NEW_SCHEMA.'||table_name||'.'|| column_name ||' IS '|| ''''||comments||''';' FROM USER_col_comments WHERE comments IS NOT NULL ORDER BY table_name,COLUMN_name;
COMMENT ON COLUMN NEW_SCHEMA.ABC_TOTAL.C_TIME IS '检查时间';
COMMENT ON COLUMN NEW_SCHEMA.ABC_TOTAL.C_ID IS '作业id';
COMMENT ON COLUMN NEW_SCHEMA.ABC_TOTAL.ID IS '自增id';
复制代码
Index
SELECT 'CREATE INDEX ' || 'NEW_SCHEMA' || '.' || index_name || ' ON ' || 'NEW_SCHEMA' || '.' || table_name || ' (' ||
LISTAGG(column_name ||
CASE WHEN ASC_OR_DESC = 'ASC' THEN ' ASC' ELSE ' DESC' END, ', ') WITHIN GROUP (ORDER BY column_position) ||
');' AS create_index_ddl
FROM (
SELECT i.owner,
i.index_name,
i.table_owner,
i.table_name,
ic.column_name,
ic.column_position,
CASE WHEN ic.descend IS NULL THEN 'ASC' ELSE ic.descend END AS ASC_OR_DESC
FROM dba_indexes i
JOIN dba_ind_columns ic ON i.owner = ic.index_owner AND i.index_name = ic.index_name
WHERE i.owner = 'OLD_SCHEMA' -- 替换为你的用户名
)
GROUP BY owner, index_name, table_owner, table_name
ORDER BY table_owner, table_name , index_name;
示例代码：
CREATE INDEX NEW_SCHEMA."SYS_C_369" ON NEW_SCHEMA."ABC_TOTAL" (ID ASC);
复制代码
执行迁移代码
采用 yasql 命令行，执行完毕后检查 log 文件，确认执行成功，如果有错误，需要全部错误都得到处理。
yasql NEW_SCHEMA/Passwd@192.168.58.58:1688/sys -f -e create_table.sql > create_table.sql.log
复制代码
采用 dbeaver
将代码复制到 sql 窗口，点击执行 sql 文件图标。确认执行成功，如果有错误，需要全部错误都得到处理。
评论