Oracle uses DBMS_METADATA.GET_DDL to view DDL statements

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 required tables
 SQL> Select owner, TABLE_NAME, constraint_name, Constraint_type from user_constraints where table_name= ' EMP ';
  View the SQL
 select dbms_metadata that created the primary key. GET_DDL (' CONSTRAINT ', ' emp_pk ') from dual; 
View the SQL
 SQL> SELECT dbms_metadata that created the foreign key. GET_DDL (' Ref_constraint ', ' emp_fk_dept ') from dual; 
View statement to create view  
View SQL for 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);

Common errors

Sql> Select Dbms_metadata.get_ddl (' TABLE ', ' PC ', ' SCOTT ') from dual;
ERROR:
Ora-19206:invalid value for query or REF CURSOR parameter
Ora-06512:at "SYS. Dbms_xmlgen ", line 83
Ora-06512:at "SYS. Dbms_metadata ", line 345
Ora-06512:at "SYS. Dbms_metadata ", line 410
Ora-06512:at "SYS. Dbms_metadata ", line 449
Ora-06512:at "SYS. Dbms_metadata ", line 615
Ora-06512:at "SYS. Dbms_metadata ", line 1221
Ora-06512:at Line 1

No rows selected

Workaround: Run the $ORACLE _home/rdbms/admin/catmeta.sql