[Oracle] dbms_metadata.get_ddl usage Summary

[Oracle] dbms_metadata.get_ddl usage summary dbms_metadata.get_ddl () is used to obtain the object's DDL. Its usage is as follows. Note: In sqlplus, the following parameters must be set to better display DDL:

[sql] set line 200  set pagesize 0  set long 99999  set feedback off  set echo off  

 

1) DDL for obtaining tables, indexes, views, stored procedures, and functions

[sql] select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;    select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;    select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;    select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;    select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;  

 

The following script is used to obtain the DDL statements of all tables, indexes, views, stored procedures, and functions in a schema.

[sql] set pagesize 0  set long 90000  set feedback off  set echo off  spool schema_ddl.sql  SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,u.owner) FROM DBA_TABLES u;  SELECT DBMS_METADATA.GET_DDL('VIEW',u.view_name,u.owner) FROM DBA_VIEWS u;  SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.owner) FROM DBA_INDEXES u;  select dbms_metadata.get_ddl('PROCEDURE',u.object_name, u.owner,) from dba_objects u where u.object_type = 'PROCEDURE';  select dbms_metadata.get_ddl('FUNCTION',u.object_name, u.owner,) from dba_objects u where u.object_type = 'FUNCTION';  spool off;  

 

2) obtain the DDL of the tablespace.

Obtain the DDL of a single TABLESPACE: [SQL] select dbms_metadata.get_ddl ('tablespace', 'tbs _ name') from dual; obtain the DDL of all tablespaces: [SQL] SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name) FROM DBA_TABLESPACES TS;

 

3) obtain the user's DDL

Get the DDL of a single USER: [SQL] select dbms_metadata.get_ddl ('user', 'epay _ user') from dual; get the DDL of all users: [SQL] SELECT DBMS_METADATA.GET_DDL ('user', U. username) FROM DBA_USERS U;