获取用户、用户权限的DDL语句

[code language="sql"]set feedback off pages 0 long 90000 serveroutput onaccept USERNAME prompt "Enter username :"

[/code]

获取对象、相关对象的DDL

特定表的DDL：

[code language="sql"]SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DDL(TABLE,EMP’,’SCOTT’) FROM DUAL;

[/code]

 

获取表相关的授权：

[code language="sql"]SET LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_DEPENDENT_DDL(‘OBJECT_GRANT’,’EMP’,’SCOTT’) FROM DUAL;

[/code]

 

获取指定schema（这里用scott举例）的所有表DDL：

[code language="sql"]-- ### Schema all object DDL
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md IS

-- Define local variables.
h NUMBER; -- handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB; -- metadata is returned in a CLOB
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');

-- Use filters to specify the schema.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','HTBIZSALE_HTBIZ_NEW_PDB');

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

--add semicolon at the end of the statement
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SQLTERMINATOR',true);

-- Fetch the objects.
LOOP
doc := DBMS_METADATA.FETCH_CLOB(h);

-- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
EXIT WHEN doc IS NULL;

-- Store the metadata in the table.
INSERT INTO my_metadata(md) VALUES (doc);
COMMIT;
END LOOP;

-- Release resources.
DBMS_METADATA.CLOSE(h);
END;
/

[/code]