基本信息
场景：测试库到生产库，需要迁移表结构，表数量较多。
源库版本：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手工迁移
支持对象中，不含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
dbms_metadata.get_ddl暂时不支持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文件图标。确认执行成功，如果有错误，需要全部错误都得到处理。
