oracle旧库向新库添加表和相同表表结构比较

场景:
(新库)用户1: FUELMIS_ZWNEW 账户:FUELMIS_ZWNEW 密码:FUELMIS_ZWNEW
(旧库)用户2: FUELMIS_ZWOLD 账户:FUELMIS_ZWOLD 密码:FUELMIS_ZWOLD
sql在 system/sys用户操作

--需求一:旧库比新库多的表名和相同的表
--不同的表
select  OLD, NEW from (select a.table_name OLD,b.table_name NEW from (select table_name from all_all_tables where owner = 'FUELMIS_ZWOLD') a left join (select table_name from all_all_tables where owner = 'FUELMIS_ZWNEW') b on a.table_name = b.table_name) c where c.new is null
--相同的表
select  OLD, NEW from (select a.table_name OLD,b.table_name NEW from (select table_name from all_all_tables where owner = 'FUELMIS_ZWOLD') a left join (select table_name from all_all_tables where owner = 'FUELMIS_ZWNEW') b on a.table_name = b.table_name) c where c.new is not null  

新建表(存储相同表和不同表)--后续相同表结构比较做准备:
相同表:
t_usersametable
字段:samename
不同表:
t_userdifferenttable
字段:differentname

不同表--直接导出,在插入新库(一次性)
PLSQL导出表结构和表数据
https://blog.csdn.net/u014644574/article/details/103750761

--需求2:
--相同的表表结构进行比较
select oldcolumn_name,newcolumn_name from (select old.column_name oldcolumn_name,new.column_name newcolumn_name from (SELECT atc.COLUMN_NAME COLUMN_NAME FROM  ALL_TAB_COLUMNS atc  LEFT JOIN ALL_COL_COMMENTS acc ON (atc.TABLE_NAME = acc.TABLE_NAME AND atc.OWNER = acc.OWNER AND atc.COLUMN_NAME = acc.COLUMN_NAME)  WHERE1 = 1AND atc.  TABLE_NAME = '"+ T_TRUCKTAG + "' AND atc.OWNER = 'FUELMIS_ZWOLD') old left join(SELECT atc.COLUMN_NAME COLUMN_NAME FROM ALL_TAB_COLUMNS atc LEFT JOIN ALL_COL_COMMENTS acc ON (atc.TABLE_NAME = acc.TABLE_NAME AND atc.OWNER = acc.OWNER AND atc.COLUMN_NAME = acc.COLUMN_NAME)  WHERE 1 = 1   AND atc.  TABLE_NAME = '"+ T_TRUCKTAG + "'     AND atc.OWNER = 'FUELMIS_ZWNEW') new on old.column_name = new.column_name) c where c.newcolumn_name is null;

Oracle新版旧版数据库
链接： https://pan.baidu.com/s/1btyfn4LxfxDoD0-vJUtBFg
提取码：3b3f