DDL Query

DDL Query

GENERAL USAGE
-------------
SET LONG 99999999
SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;

Before droping user:(super Script---> collecting all grants of user)
--------------------------------------------------------------------
set long 100000

select dbms_metadata.get_ddl('USER','&username') from dual;
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT','&username') from dual;
select DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','&username') FROM dual;
select dbms_metadata.get_granted_ddl( 'ROLE_GRANT','&username') from dual;

SET LONG 99999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;

select  dbms_metadata.get_ddl ('TABLE', '<TABLE_NAME>', '<SCHEMA_NAME>') from dual;

select  dbms_metadata.get_ddl ('TABLE', '<SCHEMA_NAME>') from dual;
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;

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

To get the create scripts of all  tables of  a particular schema
----------------------------------------------------------------
SQL>spool tables.sql
SQL>select 'select dbms_metadata.get_ddl(''TABLE'', '''||TABLE_NAME||''',''<schema>'') from dual;' FROM DBA_TABLES
/

http://tech.padipa.net/generating-create-scripts-through-dbms_metadata-package

BACKUP DATABASE LINK IN ORACLE

Database links backup
---------------------

All DBlinks backup:
---------------------
set pages 0
set long 90000
set trimspool on
SELECT u.OWNER,DBMS_METADATA.GET_DDL('DB_LINK',u.DB_LINK,u.OWNER) FROM dba_db_links u;

For a specific DBlink backup:
---------------------------------
set pages 0
set long 90000
set trimspool on
SELECT DBMS_METADATA.GET_DDL('DB_LINK','&DB_LINK_NAME','&USER_NAME') FROM dba_db_links;

PACKAGE BACKUP in ORACLE

PACKAGE BACKUP
--------------
set heading off
set echo off
set flush off
set pagesize 50000
set linesize 32767
set long 99999999
spool PACKAGE_NAME.pks
select dbms_metadata.get_ddl('PACKAGE','<PACKAGE_NAME>','<OWNER_NAME>') from dual ;
spool off;

OR

set linesize 500
set pagesize 100000
set head off
set verify off
spool PACKAGE_NAME.pks
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_NAME>';
spool off

OR

select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_NAME' and type='PACKAGE' order by line;

PACKAGE BODY BACKUP
-------------------
select dbms_metadata.get_ddl('PACKAGE_BODY','<PACKAGE_BODY_NAME>','<OWNER_NAME>') from dual ;

OR

set pagesize 0
set linesize 800
set trimspool on
set heading off
set feedback off
spool PACKAGE_BODY_NAME.pkb
select text from dba_source where owner='<OWNER_NAME>'and name='<PACKAGE_BODY_NAME>';
spool off

OR

select text from dba_source where owner='<OWNER_NAME>' and name='PACKAGE_BODY_NAME' and type='PACKAGE BODY' order by line;

DROP PACKAGE
------------
set pagesize 0
set linesize 800
col object_name for a30
col object_type for a25
col owner for a25
spool package_drop.sql
select owner,object_name,object_type from dba_objects where object_name='<object_name>';
drop package <owner>.<package_name>;
spool Off;