当前位置：文章写作网 >日记 >日记 >oracle快速建表比较两个数据库表

oracle快速建表比较两个数据库表

2009-01-12 16:06 作者：tshfang 阅读量：12746 推荐44次 | 我要投稿

oracle中比较两个数据库的表是否一致。快速建表，并导入数据。

首先看下面的语句，该语句显示LC019999缺少的表（对比LC0019999）

SELECT *

FROM (SELECT *

FROM SYS.dba_tables

WHERE owner = 'LC019999') a

RIGHT JOIN

(SELECT *

FROM SYS.dba_tables

WHERE owner = 'LC0019999') b ON a.table_name = b.table_name

WHERE a.table_name IS NULL

执行语句，大概缺少了10多个表。那么如何快速的建表，并将数据引入呢？

我们来再写一个语句

SELECT 'create table lc019999.'||b.table_name ||' as select * from lc0019999.' || b.table_name || ';'

FROM (SELECT *

FROM SYS.dba_tables

WHERE owner = 'LC019999') a

RIGHT JOIN

(SELECT *

FROM SYS.dba_tables

WHERE owner = 'LC0019999') b ON a.table_name = b.table_name

WHERE a.table_name IS NULL

语句的执行结果如下：

create table lc019999.TESTTEST as select * from lc0019999.TESTTEST;

create table lc019999.TESTTEST1 as select * from lc0019999.TESTTEST1;

create table lc019999.CERTIFICATEBILL as select * from lc0019999.CERTIFICATEBILL;

create table lc019999.TT_PUCHASE_LBJYRKMUUR as select * from lc0019999.TT_PUCHASE_LBJYRKMUUR;

create table lc019999.TT_PUCHASE_OVRFBQOFFY as select * from lc0019999.TT_PUCHASE_OVRFBQOFFY;

create table lc019999.GSPAUDIT0812 as select * from lc0019999.GSPAUDIT0812;

create table lc019999.GSPAUDIT0901 as select * from lc0019999.GSPAUDIT0901;

create table lc019999.GSPAUDITCATEGORY as select * from lc0019999.GSPAUDITCATEGORY;

create table lc019999.GSPAUDITDATA0812 as select * from lc0019999.GSPAUDITDATA0812;

create table lc019999.GSPAUDITDATA0901 as select * from lc0019999.GSPAUDITDATA0901;

create table lc019999.GSPAUDITDATASETTING as select * from lc0019999.GSPAUDITDATASETTING;

create table lc019999.GSPAUDITEVENT as select * from lc0019999.GSPAUDITEVENT;

create table lc019999.GSPAUDITTYPE as select * from lc0019999.GSPAUDITTYPE;

create table lc019999.SALESINVOICEITEMS_DING as select * from lc0019999.SALESINVOICEITEMS_DING;

create table lc019999.SALESINVOICES_DING as select * from lc0019999.SALESINVOICES_DING;

create table lc019999.GSPUSER_XS as select * from lc0019999.GSPUSER_XS;

create table lc019999.GSPWINFORMACTIONASS as select * from lc0019999.GSPWINFORMACTIONASS;

create table lc019999.HELPSORTINFOS as select * from lc0019999.HELPSORTINFOS;

create table lc019999.GSPBIZOPSET as select * from lc0019999.GSPBIZOPSET;

create table lc019999.GSPBIZOPSETHC as select * from lc0019999.GSPBIZOPSETHC;

create table lc019999.GSPBIZOPSETOP as select * from lc0019999.GSPBIZOPSETOP;

结果是执行建表的语句。将执行结果另存为文本文件，然后执行此文件，就可以快速建表，并将数据引入。

其他人在看啥