Get metadata DDL details of Schemas objects in oracle

by

This page will help to everyone to Get metadata DDL details about any schema objects which include the (tables,packages,db_link)etc.

DBMS_METADATA

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

Object Types are:

  • TABLE
  • DB_LINK
  • TABLESPACE
  • INDEX
  • VIEW
  • MATERIALIZED_VIEW
  • MATERIALIZED_VIEW_LOG
  • DIRECTORY
  • TRIGGER
  • SYNONYM

There are other object types for which we can get the DDL metadata in oracle database , but here we have discussed most commonly used examples.

To check any object DDL in oracle database

SET LONG 99999999

SELECT DBMS_METADATA.GET_DDL('OBJECT_TYPE','OBJECT_NAME','OWNER') FROM DUAL;

Before dropping user we must be collect the DDL in live environment: (best Script —> To collecting all grants of user). Here you can see some examples which help you lot to gather details of user scripts and system grant as well as object grants.

We can store these details for backup purpose to create it again or used for any other object references.

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;

In the below example you can get the METADATA DDL of tablespaces, tables, package and procedure etc.

SET LONG 99999999

select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL; select dbms_metadata.get_ddl ('TABLE', ", ") from dual;

select dbms_metadata.get_ddl ('TABLE', ") from dual;

select dbms_metadata.get_ddl('PACKAGE',",") from dual ;

select dbms_metadata.get_ddl('PACKAGE_BODY',",") from dual ;

select dbms_metadata.get_ddl('PROCEDURE',",") from dual;

To get the create scripts of all tables of a particular schema in oracle database. We can create spool file or you can call it script which have .sql extension if you want to run it on sql prompt. 

SQL> spool tables_data.sql

SQL> select 'select dbms_metadata.get_ddl('TABLE',"’||TABLE_NAME||"’,"") from dual;' FROM DBA_TABLES

/

For taking backup of DBLINK metadata in oracle database you DBMS_METADATA.GET_DDL package where you can pass the DBlink and DBlink name and owner details.

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;

If you want to take metadata DDL of specific DBlink then you have to pass the exact name of DBlink and owner details or it can be PUBLIC.

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;

DDL of Package PACKAGE BACKUP:

set heading off

set echo off

set flush off

set pagesize 50000

set linesize 32767

set long 99999999

spool PACKAGE_NAME_data.pks

select dbms_metadata.get_ddl(‘PACKAGE’,'','') from dual ;

spool off;

OR

set linesize 500

set pagesize 100000

set head off

set verify off

spool PACKAGE_NAME_data.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','','') from dual ;

OR

set pagesize 0

set linesize 800

set trimspool on

set heading off

set feedback off

spool PACKAGE_BODY_NAME_info.pks

select text from dba_source where owner='OWNER_NAME'and name='OBJ_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 drop_package.sql

select owner,object_name,object_type from dba_objects where object_name='OBJ_NAME';

drop package .;

spool Off;

Click here to learn about Check User Password Expiration and changed time

To check DDL metadata of table you can also refer to ORACLE-BASE

Please leave a comment and subscribe to my YouTube Channel – Oracle World

Please share this Blog with your colleagues or friends. Your suggestions and feedback are very helpful for everyone who come to this site and learn it from oracleocpworld.com.
Please comment here for your any query related to above post. You can email me on : oracleocpworld@gmail.com.

Leave a Comment