[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"