DBMS_METADATA without STORAGE parameter

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

 

EXECDBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,’SQLTERMINATOR’,TRUE);
SET HEAD OFF

SET LINES 1000

SET LONG 1000

SET PAGES 100

COLUMN SYNTAX FORMAT A121

select dbms_metadata.get_ddl(OBJECT_TYPE,OBJECT_NAME,OWNER)||'/' "SYNTAX" FROM DBA_OBJECTS

WHERE OBJECT_TYPE='TABLE'

AND OWNER='DOTSOFT';

 

FOR FOREIGN KEYS

 

select dbms_metadata.get_dependent_ddl( 'REF_CONSTRAINT',TABLE_NAME) SYNTAX

FROM DBA_CONSTRAINTS WHERE CONSTRAINT_TYPE='R' AND OWNER='DOTSOFT'

AND STATUS='DISABLED';

 

 

Execute the following to get the create script of existing database objects

 

SQL>set lines 120
SQL>set pages 99999
SQL>set long 1000000
SQL> SELECT DBMS_METADATA.GET_DDL('<object_type','<object_name>','<object owner>') FROM DUAL;

For example

 

 

 

Following example to get table structure of EMP OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT') FROM DUAL;

 

 

 

Following example to get index structure of PK_EMP OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','PK_EMP','SCOTT') FROM DUAL;

 

 

 

Following example to get Package Specification  EMP_PKG OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE','EMP_PKG','SCOTT') FROM DUAL;

 

 

 

Following example to get Package Body   EMP_PKG OF SCOTT SCHEMA

SQL> SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','EMP_PKG','SCOTT') FROM DUAL;

 

 

 

Also we could get ddl for tablespace

 

SQL>  SELECT DBMS_METADATA.GET_DDL('TABLESPACE','USERS') FROM dual;

 

 

 

To get the defination of the foreign key constraints.

 

SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('REF_CONSTRAINT','<table_name>','<schema>') from dual;

 

 

 

To get the System privileges grants for a schema.

 

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

 

 

 

To get the Role grant for a schema,

 

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

 

 

 

To get the object grants for a schema

 

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

 

 

 

Following query 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
/

 

 

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

 

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','FALSE');

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE','FALSE');

EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES','FALSE');

 

Then run the DBMS_METADATA.GET_DDL

 

 

Using DBMS_METADATA to get GRANTS ON A TABLE

 

SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','ACTIVITY_TARIFF') FROM dual;

To make the storage clause to default

 exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'DEFAULT');