Shulou(Shulou.com)06/02 Report--

When we want to view the DDL of a table or tablespace, 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 of the original data of the created object. The detailed parameters are as follows

-- object_type-object type of the DDL statement that needs to return the original data

-name-object name

-- schema-the Schema in which the object resides. Default is the Schema of the current user.

-- version-version of the original data of the object

-- model-the type of original data is Oracle by default

-- transform. -XSL-T transform. To be applied.

-- RETURNS: the original data of the object is returned as CLOB by default

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 that if using sqlplus requires the following formatting, especially if you need to set up long, otherwise the complete SQL cannot be displayed.

Set linesize 180

Set pages 999

Set long 90000

View the SQL that created the user table

View the SQL of the current user table

Select dbms_metadata.get_ddl ('TABLE','EMPLOYEES') from dual

View other SQL using tables or indexes

SELECT DBMS_METADATA.GET_DDL ('TABLE','DEPT','SCOTT') FROM DUAL

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 the SQL of the current user index

Select dbms_metadata.get_ddl ('INDEX','PK_DEPT') from dual

View the SQL of other user indexes

Select dbms_metadata.get_ddl ('INDEX','PK_DEPT','SCOTT') from dual

View the SQL that created the 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 that created the primary key

SELECT DBMS_METADATA.GET_DDL ('CONSTRAINT','EMP_PK') FROM DUAL

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 creates the VIEW

View the SQL of the current user view

SQL > SELECT dbms_metadata.get_ddl ('VIEW',' MY_TABLES')

View the SQL of other user views

SQL > SELECT dbms_metadata.get_ddl ('VIEW',' MY_TABLES','SCOTT') FROM DUAL

You can also view the SQL that created the view.

SQL > select text from user_views where view_name=upper ('& view_name')

Some skills of using DBMS_METADATA.GET_DDL

1. Get the ddl of all tables, indexes, stored procedures and functions under 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. Get the ddl statements for all tablespaces

SELECT DBMS_METADATA.GET_DDL ('TABLESPACE', TS.tablespace_name)

FROM DBA_TABLESPACES TS

3. Get the ddl of all the 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,'STORAGE',false)

Common mistakes

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.