Oracle DBMS_METADATA 用法例子

rom：http://www.psoug.org/reference/dbms_metadata.html

General Information
Source	{ORACLE_HOME}/rdbms/admin/dbmsmeta.sql
First Available	9.0.1

几个常用过程或函数：

GET_DDL
Fetch DDL for objects	dbms_metadata.get_ddl( object_type IN VARCHAR2, name        IN VARCHAR2, schema      IN VARCHAR2 DEFAULT NULL, version     IN VARCHAR2 DEFAULT 'COMPATIBLE', model     IN VARCHAR2 DEFAULT 'ORACLE', transform   IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB;
	Table CREATE TABLE test PCTFREE 0 TABLESPACE uwdata AS SELECT table_name, tablespace_name FROM user_tables; SET LONG 10000 SELECT dbms_metadata.get_ddl('TABLE', 'TEST') FROM dual; View CREATE OR REPLACE VIEW my_tables AS select table_name, tablespace_name FROM user_tables; SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES') FROM dual; Function CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS BEGIN    RETURN user; END whoami; / SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI') FROM dual; Tablespace SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA') FROM dual;

GET_DEPENDENT_DDL
Fetch DDL for dependent objects (audits, object grants)	dbms_metadata.get_dependent_ddl( object_type        IN VARCHAR2, base_object_name   IN VARCHAR2, base_object_schema IN VARCHAR2 DEFAULT NULL, version            IN VARCHAR2 DEFAULT 'COMPATIBLE', model              IN VARCHAR2 DEFAULT 'ORACLE', transform          IN VARCHAR2 DEFAULT 'DDL', object_count       IN NUMBER   DEFAULT 10000) RETURN CLOB;
	GRANT select ON servers TO hr; GRANT select ON servers TO scott; set long 100000 SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','SERVERS') FROM dual;

GET_GRANTED_DDL
Fetch granted objects (system grants, role grants) DDL	dbms_metadata.get_granted_ddl( object_type IN VARCHAR2, grantee      IN VARCHAR2 DEFAULT NULL, version      IN VARCHAR2 DEFAULT 'COMPATIBLE', model        IN VARCHAR2 DEFAULT 'ORACLE', transform    IN VARCHAR2 DEFAULT 'DDL', object_count IN NUMBER   DEFAULT 10000) RETURN CLOB;
	SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'UWCLASS') FROM dual;

SET_TRANSFORM_PARAM
Specify parameters to the XSLT stylesheet identified by transform_handle.Use them to modify or customize the output of the transform Overload 1	dbms_metadata.set_transform_param( transform_handle IN NUMBER, name             IN VARCHAR2, value            IN VARCHAR2), object_type      IN VARCHAR2 DEFAULT NULL);
	set long 2000000 set pagesize 0 SELECT dbms_metadata.get_ddl('TABLE', 'T1') FROM dual; -- omit the storage clause exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'STORAGE', FALSE); SELECT dbms_metadata.get_ddl('TABLE', 'T1') FROM dual; exec dbms_metadata.set_transform_param( dbms_metadata.session_transform, 'DEFAULT');
Overload 2	dbms_metadata.set_transform_param( transform_handle IN NUMBER, name             IN VARCHAR2, value            IN BOOLEAN DEFAULT TRUE, object_type      IN VARCHAR2 DEFAULT NULL);
	TBD
Overload 3	dbms_metadata.set_transform_param( transform_handle IN NUMBER, name             IN VARCHAR2, value            IN NUMBER,), object_type      IN VARCHAR2 DEFAULT NULL);
	TBD

Demo (copied from http://www.orafaq.com/node/59)

CREATE TYPE tableddl_ty AS OBJECT ( table_name VARCHAR2(30), orig_schema VARCHAR2(30), orig_ddl    CLOB, comp_schema VARCHAR2(30), comp_ddl    CLOB); / CREATE TYPE tableddl_ty_tb AS TABLE OF tableddl_ty; / CREATE OR REPLACE FUNCTION tableddl_fc (input_values SYS_REFCURSOR) RETURN tableddl_ty_tb PIPELINED IS PRAGMA AUTONOMOUS_TRANSACTION; -- variables to be passed in by sys_refcursor */ table_name VARCHAR2(30); orig_schema VARCHAR2(30); comp_schema VARCHAR2(30); -- setup output record of TYPE tableddl_ty out_rec tableddl_ty := tableddl_ty(NULL,NULL,NULL,NULL,NULL); /* setup handles to be used for setup and fetching metadata information handles are used to keep track of the different objects (DDL) we will be referencing in the PL/SQL code */ hOpenOrig0 NUMBER; hOpenOrig   NUMBER; hOpenComp   NUMBER; hModifyOrig NUMBER; hTransDDL   NUMBER; dmsf        PLS_INTEGER; /* CLOBs to hold DDL Orig_ddl0 will hold the baseline DDL for the object to be compared Orig_ddl1 will also hold the baseline DDL for the object to be compared against but will also go through some translations before being compared against Comp_ddl2 Comp_ddl2 will contain the DDL to be compared against the baseline */ Orig_ddl0 CLOB; Orig_ddl1 CLOB; Comp_ddl2 CLOB; ret        NUMBER; BEGIN /* Strip off Attributes not concerned with in DDL. If you are concerned with      TABLESPACE, STORAGE, or SEGMENT information just comment out these few lines. */ dmsf := dbms_metadata.session_transform dbms_metadata.set_transform_param(dmsf, 'TABLESPACE', FALSE); dbms_metadata.set_transform_param(dmsf, 'STORAGE', FALSE); dbms_metadata.set_transform_param(dmsf, 'SEGMENT_ATTRIBUTES', FALSE); -- Loop through each of the rows passed in by the reference cursor LOOP     /* Fetch the input cursor into PL/SQL variables */     FETCH input_values INTO table_name, orig_schema, comp_schema;     EXIT WHEN input_values%NOTFOUND;     /* Here is the first use of our handles for pointing to the original table DDL        It names the object_type (TABLE), provides the name of the object (our PL/SQL        variable table_name), and states the schema it is from */     hOpenOrig0 := dbms_metadata.open('TABLE');     dbms_metadata.set_filter(hOpenOrig0,'NAME',table_name);     dbms_metadata.set_filter(hOpenOrig0,'SCHEMA',orig_schema);     /* Setup handle again for the original table DDL that will undergo transformation        We setup two handles for the original object DDL because we want to be able to        Manipulate one set for comparison but output the original DDL to the user */     hOpenOrig := dbms_metadata.open('TABLE');     dbms_metadata.set_filter(hOpenOrig,'NAME',table_name);     dbms_metadata.set_filter(hOpenOrig,'SCHEMA',orig_schema);     -- Setup handle for table to compare original against     hOpenComp := dbms_metadata.open('TABLE');     dbms_metadata.set_filter(hOpenComp,'NAME',table_name);     dbms_metadata.set_filter(hOpenComp,'SCHEMA',comp_schema);     /* Modify the transformation of "orig_schema" to take on ownership of "comp_schema"        If we didn't do this, when we compared the original to the comp objects there        would always be a difference because the schema_owner is in the DDL generated */     hModifyOrig := dbms_metadata.add_transform(hOpenOrig,'MODIFY');     dbms_metadata.set_remap_param(hModifyOrig,'REMAP_SCHEMA',orig_schema,comp_schema);     -- This states to created DDL instead of XML to be compared     hTransDDL := dbms_metadata.add_transform(hOpenOrig0,'DDL');     hTransDDL := dbms_metadata.add_transform(hOpenOrig ,'DDL');     hTransDDL := dbms_metadata.add_transform(hOpenComp ,'DDL');     -- Get the DDD and store into the CLOB PL/SQL variables     Orig_ddl0 := dbms_metadata.fetch_clob(hOpenOrig0);     Orig_ddl1 := dbms_metadata.fetch_clob(hOpenOrig);     /* Here we are providing for those instances where the baseline object does not        exist in the Comp_schema. */     BEGIN       Comp_ddl2 := dbms_metadata.fetch_clob(hOpenComp);     EXCEPTION       WHEN OTHERS THEN         comp_ddl2 := 'DOES NOT EXIST';     END;     -- Now simply compare the two DDL statements and output row if not equal     ret := dbms_lob.compare(Orig_ddl1, Comp_ddl2);     IF ret != 0 THEN       out_rec.table_name := table_name;       out_rec.orig_schema := orig_schema;       out_rec.orig_ddl := Orig_ddl0;       out_rec.comp_schema := comp_schema;       out_rec.comp_ddl := Comp_ddl2;       PIPE ROW(out_rec);     END IF;     -- Cleanup and release the handles     dbms_metadata.close(hOpenOrig0);     dbms_metadata.close(hOpenOrig);     dbms_metadata.close(hOpenComp); END LOOP; RETURN; END TABLEDDL_FC; / SELECT * FROM TABLE(tableddl_fc(CURSOR(SELECT table_name, owner, 'UWCLASS' FROM dba_tables where owner = 'ABC')));