DDL -Metadata commands and example

DDL -Metadata commands and example

SELECT 'CREATE PUBLIC SYNONYM ' || synonym_name || ' FOR '|| table_owner || '.' || table_name || ';' FROM dba_synonyms WHERE TABLE_OWNER='<owner_name>';

 SELECT text FROM user_source WHERE name = <PACKAGE_name>;

SELECT DBMS_METADATA.GET_DDL('PACKAGE','<PACKAGE_name>','<schema_name>') FROM DUAL;

SELECT dbms_metadata.get_ddl('FUNCTION','<FUNCTION_name>','<schema_name>') FROM dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL( 'OBJECT_GRANT','<schema_name>') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','<schema_name>') from dual;

SELECT dbms_metadata.get_ddl('PROCEDURE','<proc_name>','schema') FROM dual;

PROCEDURE

select dbms_metadata.get_ddl('PROCEDURE', '<proc_name>') from dual;

SET pages 9999 LONG 1000000 LONGCHUNKSIZE 200000 

PAGESIZE 0 LINESIZE 10000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

SQL> 

SQL> set line 32767

SQL> 

SQL> 

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','PKG_IMPAIR','owner') FROM DUAL;

SQL> select DBMS_Metadata.Get_DDL('TRIGGER',t.trigger_name,t.owner) From DBA_Triggers t Where TRIGGER_NAME ='<>';

db start up time

SET LINES 200

SET PAGES 999

COLUMN INSTANCE_NAME FOR A20

SELECT INSTANCE_NAME,TO_CHAR(STARTUP_TIME, 'HH24:MI DD-MON-YY') "STARTUP TIME"

FROM V$INSTANCE;

SELECT dbms_metadata.get_ddl('ROLE','YOUR_ROLE_NAME') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','YOUR_ROLE_NAME') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','YOUR_ROLE_NAME') from dual;

SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT','YOUR_ROLE_NAME') from dual;