[20220610]对比表结构的不同.txt

[20220610]对比表结构的不同.txt --//https://connor-mcdonald.com/2022/06/08/comparing-two-database-objects-for-differences/,重复测试: 1.环境: SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: --//create table emp as select * from scott.emp; create table emp2 as select * from scott.emp; --//But now I'll make some changes to EMP2 so that it is now slightly different to EMP. alter table emp2 pctfree 20; alter table emp2 add newcol number; 3.测试: SCOTT@book> select dbms_metadata_diff.compare_alter('TABLE','EMP','EMP2') from dual; DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','EMP','EMP2') -------------------------------------------------------- ALTER TABLE "SCOTT"."EMP" ADD ("NEWCOL" NUMBER)   ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "PK_EMP"   ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "FK_DEPTNO"   ALTER TABLE "SCOTT"."EMP" PCTFREE 20   ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP2" --//输出实际上clob类型的信息. --//dbms_metadata_diff.compare_alter还支持schema以及dblink. SCOTT@book> @ desc_proc sys dbms_metadata_diff compare_alter INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER PACKAGE_NAME       OBJECT_NAME   SEQUENCE ARGUMENT_NAME  DATA_TYPE IN_OUT    DEFAULTED ----- ------------------ ------------- -------- -------------- --------- --------- ---------- SYS   DBMS_METADATA_DIFF COMPARE_ALTER        1                CLOB      OUT       N                                               2 OBJECT_TYPE    VARCHAR2  IN        N                                               3 NAME1          VARCHAR2  IN        N                                               4 NAME2          VARCHAR2  IN        N                                               5 SCHEMA1        VARCHAR2  IN        Y                                               6 SCHEMA2        VARCHAR2  IN        Y                                               7 NETWORK_LINK1  VARCHAR2  IN        Y                                               8 NETWORK_LINK2  VARCHAR2  IN        Y 8 rows selected. 4.作者还提供一个生成结尾分号的输出,利用dbms_metadata_diff.compare_alter_xml: with t as ( select dbms_metadata_diff.compare_alter_xml('TABLE','EMP','EMP2') xml from dual ) select xt.txt||';' from t,      xmltable(xmlnamespaces(default 'http://xmlns.oracle.com/ku'), '/ALTER_XML/ALTER_LIST/ALTER_LIST_ITEM/SQL_LIST/SQL_LIST_ITEM'      passing xmltype(t.xml)      columns        txt     varchar2(255)  path 'TEXT'      ) xt; XT.TXT||';' ------------------------------------------------------- ALTER TABLE "SCOTT"."EMP" ADD ("NEWCOL" NUMBER); ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "PK_EMP"; ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "FK_DEPTNO"; ALTER TABLE "SCOTT"."EMP" PCTFREE 20; ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP2";      --//真心话,XML格式不熟悉,捣腾这些有点烦. This can be a little annoying because you might want to omit some of the commands, augment them in some way, or run them one at a time, and this would then mean digging around within the CLOB with INSTR and SUBSTR functions to locate each statement. 这可能有点烦人，因为您可能想要省略一些命令，以某种方式增强它们，或者一次运行一个命令，这将意味着在CLOB中挖掘instr和 SUBSTR函数来定位每个语句。 --//自己尝试看看,简单一点直接使用replace: with t as (select dbms_metadata_diff.compare_alter('TABLE','EMP','EMP2' ) c400 from dual) select replace(c400,chr(10),';'||chr(10)) from t; REPLACE(C400,CHR(10),';'||CHR(10)) -------------------------------------------------------- ALTER TABLE "SCOTT"."EMP" ADD ("NEWCOL" NUMBER);   ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "PK_EMP";   ALTER TABLE "SCOTT"."EMP" DROP CONSTRAINT "FK_DEPTNO";   ALTER TABLE "SCOTT"."EMP" PCTFREE 20;   ALTER TABLE "SCOTT"."EMP" RENAME TO "EMP2"