【YashanDB知识库】YashanDB到YashanDB手工元数据迁移

本文内容来自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

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文件图标。确认执行成功,如果有错误,需要全部错误都得到处理。

posted @   YashanDB  阅读(4)  评论(0编辑  收藏  举报
编辑推荐:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· C++代码改造为UTF-8编码问题的总结
· DeepSeek 解答了困扰我五年的技术问题
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· 易语言 —— 开山篇
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
点击右上角即可分享
微信分享提示