[20220610]對比表結構的不同.txt

[20220610]對比表結構的不同.txt

--//重複測試:

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 '), '/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"