How Does oracle view DDL statements when creating database objects such as tables?

You can use the package dbms_metadata.get_ddl to view a table or table space DDL. The get_ddl function detailed parameter GET_DDL function in the dbms_metadata Package returns the DDL statement for the original data of the created object. The detailed parameters are as follows -- object_type --- the object type of the DDL statement to return the original data -- n

You can use the package dbms_metadata.get_ddl to view a table or table space DDL. The get_ddl function detailed parameter GET_DDL function in the dbms_metadata Package returns the DDL statement for the original data of the created object. The detailed parameters are as follows -- object_type --- the object type of the DDL statement to return the original data -- n

You can use the package dbms_metadata.get_ddl to view a table or table space DDL.

Detailed parameters of the get_ddl function in the dbms_metadata Package
The GET_DDL function returns the DDL statement of the original data of the created object. The detailed parameters are as follows:
-- Object_type --- object type of the DDL statement to return the original data
-- Name --- Object name
-- Schema --- the Schema of the object. The default value is the Schema of the current user.
-- Version --- version of the original data of the object
-- Model --- the default data type is ORACLE.
-- Transform.-XSL-T transform. to be applied.
-- RETURNS: the original data of the object is returned as CLOB by default.

Get_ddl function definition in the dbms_metadata Package
FUNCTION get_ddl (object_type IN VARCHAR2,
Name IN VARCHAR2,
Schema IN VARCHAR2 default null,
Version IN VARCHAR2 DEFAULT 'compuble ',
Model IN VARCHAR2 DEFAULT 'oracle ',
Transform. IN VARCHAR2 DEFAULT 'ddl ') return clob;

NOTE: If sqlplus is used, the following formatting is required, and you need to set long. Otherwise, the complete SQL statement cannot be displayed.

Set linesize 180
Sets pages 999
Sets long 90000

View the SQL statement used to create a user table

View the SQL statement of the current user table
Select dbms_metadata.get_ddl ('table', 'ployees') from dual;
View other SQL statements using tables or Indexes

SELECT DBMS_METADATA.GET_DDL ('table', 'dept', 'Scott ') from dual;

View the SQL statement used to create a user Index
View the index of the expected table
SQL> select INDEX_NAME, INDEX_TYPE, TABLE_NAME from user_indexes WHERE table_name = 'emp ';
View the current user index SQL
Select dbms_metadata.get_ddl ('index', 'pk _ DEPT ') from dual;

View the SQL statements indexed by other users
Select dbms_metadata.get_ddl ('index', 'pk _ DEPT ', 'Scott') from dual;
View the SQL statement used to create a primary key
View the constraints of the required table
SQL> select owner, table_name, constraint_name, constraint_type from user_constraints where table_name = 'emp ';
View the SQL statement used to create a primary key
SELECT DBMS_METADATA.GET_DDL ('straint', 'emp_pk') from dual;
View the SQL statement used to create a foreign key
SQL> SELECT DBMS_METADATA.GET_DDL ('ref _ CONSTRAINT ', 'emp_fk_dept') FROM DUAL;
VIEW the statement for creating a VIEW
View the current user view SQL
SQL> SELECT dbms_metadata.get_ddl ('view', 'My _ TABLES ')

View SQL statements in other user views
SQL> SELECT dbms_metadata.get_ddl ('view', 'My _ TABLES ', 'Scott') FROM DUAL;
You can also view the SQL statement used to create a view.
SQL> select text from user_views where view_name = upper ('& view_name ');
Some tips for using DBMS_METADATA.GET_DDL
1. Obtain the ddl statements of all tables, indexes, stored procedures, and functions of a user.
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. Obtain the ddl statements for all tablespaces.
SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name)
FROM DBA_TABLESPACES TS;
3. Obtain the ddl statements of all created users.
SELECT DBMS_METADATA.GET_DDL ('user', U. username)
FROM DBA_USERS U;
4. Remove redundant parameters such as storage.
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM, 'store', 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

Solution: run $ ORACLE_HOME/rdbms/admin/catmeta. SQL