Oracle uses dbms_metadata.get_ddl to obtain DDL statements

 

After 9i, Oracle can use the dbms_metadata.get_ddl package to obtain the DDL script of the database object. As follows (executed in sqlplus ):

1. Obtain the syntax for creating a single table, view, and index
Set pagesize 0
Sets long 90000
Set feedback off
Set echo off
Spool Dept. SQL
Select dbms_metadata.get_ddl ('table', 'tab _ name', 'Scott ') from dual;
Select dbms_metadata.get_ddl ('view', 'view _ name', 'Scott ') from dual;
Select dbms_metadata.get_ddl ('index', 'idx _ name', 'Scott ') from dual;
Spool off;

 

2. Obtain the syntax for creating tables, views, and indexes in a schema. Take Scott as an example:
Set pagesize 0
Sets long 90000
Set feedback off
Set echo off
Spool schema. SQL
Connect Scott/tiger;
Select dbms_metadata.get_ddl ('table', U. table_name) from user_tables U;
Select dbms_metadata.get_ddl ('view', U. view_name) from user_views U;
Select dbms_metadata.get_ddl ('index', U. index_name) from user_indexes U;
Spool off;

 

3. Obtain the syntax for creating all stored procedures in a schema
Set pagesize 0
Sets long 90000
Set feedback off
Set echo off
Spool procedures. SQL
Select dbms_metadata.get_ddl ('processed', U. object_name) from user_objects u where object_type = 'processed ';
Spool off;

4. Obtain the syntax for creating all functions in a schema.
Set pagesize 0
Sets long 90000
Set feedback off
Set echo off
Spool function. SQL
Select dbms_metadata.get_ddl ('function', U. object_name) from user_objects u where object_type = 'function ';
Spool off;