[Oracle] dbms_metadata.get_ddl usage Summary

Dbms_metadata.get_ddl () is used to obtain the object DDL. Its usage is as follows.
Note: In sqlplus, the following parameters must be set to better display DDL:
Copy codeThe Code is as follows:
Set line 200
Set pagesize 0
Sets long 99999
Set feedback off
Set echo off

1) DDL for obtaining tables, indexes, views, stored procedures, and functions
Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
Set pagesize 0
Sets 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 ('processed', u. object_name, u. owner,) from dba_objects u where u. object_type = 'processed ';
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:
Copy codeThe Code is as follows:
Select dbms_metadata.get_ddl ('tablespace', 'tbs _ name') from dual;

Obtain the DDL of all tablespaces:
Copy codeThe Code is as follows:
SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name)
FROM DBA_TABLESPACES TS;

3) obtain the user's DDL
Obtain the DDL of a single user:
Copy codeThe Code is as follows:
Select dbms_metadata.get_ddl ('user', 'epay _ user') from dual;

Get the DDL of all users:
Copy codeThe Code is as follows:
SELECT DBMS_METADATA.GET_DDL ('user', U. username)
FROM DBA_USERS U;