Get DDL

GET_DEPENDENT_DDL(object_type, base_object_name, base_object_schema, version, model, transform, object_count)

GET_GRANTED_DDL(object_type, grantee, version, model, transform, object_count)


----------------------------------------------------------------------------------------------------

select DBMS_METADATA.GET_DDL('TABLE','ACCOUNTS')||'/' from dual;

----------GET_DEPENDENT_DDL
select DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','ACCOUNTS') aa from dual;

select DBMS_METADATA.GET_DEPENDENT_DDL('TRIGGER','ACCOUNTS') aa from dual;

select DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT','ACCOUNTS') aa from dual;

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('CONSTRAINT','ACCOUNTS') from dual;

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','ACCOUNTS') from dual;


--------------------------------
select DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema>') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','<schema>') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','<schema>') from dual;

select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','KSH') aa from dual;

-----------------------------------------------------------------------------------------------------------------------------
SET LONG 1000000

select dbms_metadata.get_ddl( 'USER', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'LDBO' ) from dual
UNION ALL
select dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', 'LDBO' ) from dual;


-----------------------------------------------------------------------------------------------------------------------------
CREATE TABLE my_ddl (owner VARCHAR2(30),
                     table_name VARCHAR2(30),
                     ddl   CLOB);
INSERT INTO my_ddl (owner, table_name, ddl)
SELECT owner, table_name,
DBMS_METADATA.GET_DDL('TABLE', table_name, owner) ddl
 FROM DBA_TABLES WHERE OWNER = 'LDBO';