dbms_metadata&dbms_metadata_diff

（一）DBMS_METADATA
两种方式获取元信息
1. 使用get_dll
GET_xxx is used to fetch named objects, especially schema objects (tables, views).
GET_DEPENDENT_xxx is used to fetch dependent objects (audits, object grants).
GET_GRANTED_xxx is used to fetch granted objects (system grants, role grants).

set pagesize 0
set long 1000000
--以下设置当前session有效，使用default值来恢复
execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);    
--execute dbms_metadata.set_transform_param(dbms_metadata.session_transform,'DEFAULT'); 

--按不同格式转化 
select dbms_metadata.get_ddl(object_type=>'TABLE',name=>'T1',schema=>'SCOTT', version=>'11.2',model=>'ORACLE') from dual;
--select dbms_metadata.get_xml(object_type=>'TABLE',name=>'T1',schema=>'SCOTT', version=>'11.2',model=>'ORACLE') from dual;
--select dbms_metadata.get_sxml(object_type=>'TABLE',name=>'T1',schema=>'SCOTT', version=>'11.2',model=>'ORACLE') from dual;

select dbms_metadata.get_ddl('TABLE','T1','HR') from dual;            --表
select dbms_metadata.get_ddl('INDEX','IND_T1','SYS') from dual;        --索引
select dbms_metadata.get_ddl('CONSTRAINT','EMP_PK') from dual;        --约束
select dbms_metadata.get_ddl('VIEW', 'MY_TABLES')            --视图

--显示scott.t1表相关索引信息：
select dbms_metadata.get_dependent_ddl(object_type=>'INDEX',base_object_name=>'T1',base_object_schema=>'SCOTT', version=>'11.2',model=>'ORACLE') from dual;
--查看对象权限：
select dbms_metadata.get_dependent_ddl(object_type=>'OBJECT_GRANT',base_object_name=>'T1',base_object_schema=>'SCOTT', version=>'11.2',model=>'ORACLE') from dual;
--查看授予scott系统权限：
select dbms_metadata.get_granted_ddl(object_type=>'SYSTEM_GRANT',grantee=>'SCOTT', version=>'11.2',model=>'ORACLE') from dual;
--用户表，索引，存储过程，函数的ddl
select dbms_metadata.get_ddl(U.OBJECT_TYPE, u.object_name) from user_objects u where u.object_type in ('TABLE','INDEX','PROCEDURE','FUNCTION');        
--所有表空间的ddl语句
select dbms_metadata.get_ddl('TABLESPACE', TS.tablespace_name) from dba_tablespaces ts;    
--所有用户的ddl
select dbms_metadata.get_ddl('USER',U.username) from dba_users u;                    

2. 自定义方法：
1）显示单表语句：
SQL> CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
h NUMBER;
th NUMBER;
doc CLOB;
BEGIN
h := DBMS_METADATA.OPEN(object_type=>'TABLE',version=>'11.2',mode=>'ORACLE');
DBMS_METADATA.SET_FILTER(handle=>h, name=>'SCHEMA',value=>'SCOTT');
DBMS_METADATA.SET_FILTER(handle=>h,name=>'NAME',value=>'T1');
th := DBMS_METADATA.ADD_TRANSFORM(handle=>h, name=> 'DDL');
--下面过滤可以不设置
DBMS_METADATA.SET_TRANSFORM_PARAM(transform_handle=> th, name=> 'SEGMENT_ATTRIBUTES', value=> false);
doc := DBMS_METADATA.FETCH_CLOB(h);
DBMS_METADATA.CLOSE(h);
RETURN doc;
END;
/ 
SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT get_table_md FROM dual;

2）显示多表语句：
CREATE TABLE my_metadata (md clob);   ---创建结果表
CREATE OR REPLACE PROCEDURE get_tables_md IS
h       NUMBER;         -- handle returned by 'OPEN'
th      NUMBER;         -- handle returned by 'ADD_TRANSFORM'
doc     CLOB;           -- metadata is returned in a CLOB
BEGIN
h := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
LOOP
  doc := DBMS_METADATA.FETCH_CLOB(handle=>h);
  EXIT WHEN doc IS NULL;
  INSERT INTO my_metadata(md) VALUES (doc);
  COMMIT;
END LOOP;
DBMS_METADATA.CLOSE(h);
END;
/
SQL> EXECUTE get_tables_md;
SQL> SET LONG 9000000
SQL> SET PAGES 0
SQL> SELECT * FROM my_metadata;

3）导出集合，如某个schema所有对象
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md2 IS
h       NUMBER; 
th      NUMBER;
doc     CLOB; 
BEGIN
 h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');
 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');
--下面过滤可选，过滤了TABLE类型的名子为my_metadata的表，过滤了所有的procedure
--DBMS_METADATA.SET_FILTER(handle=>h,name=>'NAME_EXPR',value=>'!=''MY_METADATA''', object_type_path=> 'TABLE');
--DBMS_METADATA.SET_FILTER(h,'EXCLUDE_PATH_EXPR','=''PROCEDURE''');
 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false,'TABLE');
 LOOP
   doc := dbms_metadata.fetch_clob(h);
   EXIT WHEN doc IS NULL;
   INSERT INTO my_metadata(md) VALUES (doc);
   COMMIT;
 END LOOP;
 DBMS_METADATA.CLOSE(h);
END;
/
EXECUTE get_schema_md2;
SET LONG 9000000
SET PAGESIZE 0
SELECT * FROM my_metadata;

(二) DBMS_METADATA_DIFF

CREATE TABLE TAB1 ("EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "DEPTNO" NUMBER(2,0)) ;
CREATE TABLE TAB2( "EMPNO" NUMBER(4,0) PRIMARY KEY ENABLE, "ENAME" VARCHAR2(20), "MGR" NUMBER(4,0), "DEPTNO" NUMBER(2,0)) ;

--比较两个表并以SXML格式输出：
SELECT dbms_metadata_diff.compare_sxml(object_type=> 'TABLE', name1=>'TAB1', name2=>'TAB2',schema1=>'SCOTT',schema2=>'SCOTT') FROM dual;
--比较两个表并以ALTERXML格式输出，会显示ALTER TABLE语句，执行后可把表1转为表2
SELECT dbms_metadata_diff.compare_alter_xml(object_type=> 'TABLE', name1=>'TAB1', name2=>'TAB2',schema1=>'SCOTT',schema2=>'SCOTT') FROM dual;
--比较两个表并以ALTERDDL格式输出，会显示ALTER TABLE语句，执行后可把表1转为表2
SELECT dbms_metadata_diff.compare_alter(object_type=> 'TABLE', name1=>'TAB1', name2=>'TAB2',schema1=>'SCOTT',schema2=>'SCOTT') FROM dual;