Viewing DDL statements with DBMS_METADATA.GET_DDL

When we want to see the DDL of a table or table space, we can use the DBMS_METADATA.GET_DDL package to view it.

Detailed parameters of the GET_DDL function in the Dbms_metadata package
The GET_DDL function returns the DDL statement that created the original data for the object, with the following detailed parameters
--Object_type---The object type of the DDL statement that needs to return the original data
--Name---object names
Schema---object, which defaults to the schema of the current user
Version---The original data of the object
--Model---The original data type default to Oracle
--Transform. -Xsl-t transform. To is applied.
--RETURNS: The original data of the object is returned by default with the CLOB type

Definition of GET_DDL function in Dbms_metadata package
FUNCTION Get_ddl (object_type in VARCHAR2,
Name in VARCHAR2,
Schema in VARCHAR2 DEFAULT NULL,
Version in VARCHAR2 DEFAULT ' COMPATIBLE ',
Model in VARCHAR2 DEFAULT ' ORACLE ',
Transform. In VARCHAR2 DEFAULT ' DDL ') RETURN CLOB;

Note If the following formatting is required for the use of sqlplus, it is particularly necessary to set the long, otherwise the full SQL cannot be displayed

Set Linesize 180
Set pages 999
Set Long 90000

View the SQL that created the user table

View SQL for the current user table
Select Dbms_metadata.get_ddl (' TABLE ', ' EMPLOYEES ') from dual;
View other SQL with tables or indexes

SELECT Dbms_metadata. GET_DDL (' TABLE ', ' DEPT ', ' SCOTT ') from DUAL;

To view the SQL that created the user index
View the index of the desired table
Sql> Select Index_name, Index_type, table_name from user_indexes WHERE table_name= ' EMP ';
View SQL for current user index
Select Dbms_metadata.get_ddl (' INDEX ', ' pk_dept ') from dual;

view SQL for other user indexes
Select Dbms_metadata.get_ddl (' INDEX ', ' pk_dept ', ' SCOTT ') from dual;
view the SQL that created the primary key
View constraints on the required table
sql> Select owner, TABLE_NAME, constraint_name, Constraint_type from user_constraints where table_name= ' EMP ';
View the SQL that created the primary key
SELECT Dbms_metadata. GET_DDL (' CONSTRAINT ', ' emp_pk ') from DUAL;
To view the SQL that created the foreign key
sql> SELECT dbms_metadata. GET_DDL (' Ref_constraint ', ' emp_fk_dept ') from DUAL;
View the statement that created the view
View SQL for the current user view
sql> SELECT dbms_metadata.get_ddl (' VIEW ', ' my_tables ')

view SQL for other user views
sql> SELECT dbms_metadata.get_ddl (' VIEW ', ' my_tables ', ' SCOTT ') from DUAL;
Viewing the SQL that creates the view can also
sql> Select text from user_views where View_name=upper (' &view_name ');
Dbms_metadata. Some tips for using GET_DDL
1, get a user under all tables, indexes, stored procedures, functions of the DDL
SELECT Dbms_metadata. GET_DDL (U.object_type, U.object_name)
From User_objects u
where U.object_type in (' TABLE ', ' INDEX ', ' PROCEDURE ', ' FUNCTION ');
2. Get DDL statements for all table spaces
SELECT Dbms_metadata. GET_DDL (' tablespace ', ts.tablespace_name)
From Dba_tablespaces TS;
3. Get DDL for all created users
SELECT Dbms_metadata. GET_DDL (' USER ', u.username)
From Dba_users U;
4, remove storage and other redundant parameters
EXECUTE Dbms_metadata. Set_transform_param (Dbms_metadata. Session_transform, ' STORAGE ', false);

Viewing DDL statements with DBMS_METADATA.GET_DDL