从Oracle到8a的表注释迁移

1.

采用orato8a工具，导出oracle端注释信息（注：需要根据oracle端名称更换owner名字）

从oracle端取注释信息

--owner做好对应,where条件自行划定导出范围

./orato8a --user='system/DHHZDHHZ@TEST' \

--query="select owner, table_name, column_name, comments from all_col_comments where owner = 'ORATO8A' and table_name = 'T2' and comments is not null" \

--file='/home/oracle/test.txt' \

--field=";" \

--format=3

2.

8a端

修改系统参数

set global _gbase_query_path=on;

set global gcluster_insertselect_use_values_optimize=1;

之后重新登录数据库

创建临时表

create table test.temp1

(

table_schema varchar(100),

table_name   varchar(100),

column_name  varchar(100),

comments     varchar(1000)

);

create table test.temp2

(

table_schema varchar(64),

table_name   varchar(64),

column_name  varchar(64),

column_type  longtext

);

insert into test.temp2

select table_schema,table_name,column_name,column_type

  from information_schema.columns;

load data infile 'sftp://gbase:gbase@192.168.203.7/home/gbase/test.txt'  INTO TABLE test.temp1 data_format 3 FIELDS  terminated by ';';

拼sql

--登录数据库，将结果集导出

rmt:select 'alter table '||a.table_schema||'.'||a.table_name||' modify column '\

||a.column_name||' '||a.column_type||' comment ''' || b.comments || ''';'\

from test.temp2 a, test.temp1 b\

where lower(a.table_schema) = lower(b.table_schema)\

and lower(a.table_name) = lower(b.table_name)\

and lower(a.column_name) = lower(b.column_name)\

into outfile '/home/gbase/temp.sql';

--执行

gccli -f -vvv < temp.sql > temp.log