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

In daily work, people often use tools to check the definition of objects, in fact, sometimes it is very annoying to check it this way. In my work, some colleagues asked me how to get the object definition. Today, I would like to make a summary for you. I hope it will be helpful to you!

The package to get the object definition is: dbms_metadata, where the get_ddl function is the function to get the object

The GET_DDL function returns the DDL statement of the original data of the created object, with the parameter description

1. Object_type-the object type of the DDL statement that needs to return the original data

2. Name-object name

3. Schema-the Schema in which the object resides, which defaults to the Schema of the current user

4. Version-the version of the original data of the object

5. Model-the type of original data is ORACLE by default

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

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

Among them, we often use the first three items.

The 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 'COMPATIBLE'

Model IN VARCHAR2 DEFAULT 'ORACLE'

Transform. IN VARCHAR2 DEFAULT 'DDL') RETURN CLOB

Note:

1. If using sqlplus requires the following formatting, especially if you need to set long, otherwise the complete SQL cannot be displayed.

2. Parameters should be in uppercase, otherwise they will not be found.

Set linesize 180

Set pages 999

Set long 90000

1. View the definition and writing of the database table:

Select dbms_metadata.get_ddl ('TABLE','TABLENAME','USERNAME') from dual

2. View the SQL of the index

Select dbms_metadata.get_ddl ('INDEX','INDEXNAME','USERNAME') from dual

3. View the SQL that created the primary key.

SELECT DBMS_METADATA.GET_DDL ('CONSTRAINT','CONSTRAINTNAME','USERNAME') FROM DUAL

4. View the SQL that created the foreign key

SELECT DBMS_METADATA.GET_DDL ('REF_CONSTRAINT','REF_CONSTRAINTNAME','USERNAME') FROM DUAL

5. View the SQL where the view is created

SELECT DBMS_METADATA.GET_DDL ('VIEW','VIEWNAME','USERNAME') FROM DUAL

6. View the user's SQL

SELECT DBMS_METADATA.GET_DDL ('USER','USERNAME') FROM DUAL

7. View the SQL of the role

SELECT DBMS_METADATA.GET_DDL ('ROLE','ROLENAME') FROM DUAL

8. View the SQL of the tablespace

SELECT DBMS_METADATA.GET_DDL ('TABLESPACE','TABLESPACENAME') FROM DUAL

9. Get the materialized view SQL

Select dbms_metadata.get_ddl ('MATERIALIZED VIEW','MVNAME') FROM DUAL

10. Get the remote connection definition SQL

SELECT dbms_metadata.get_ddl ('DB_LINK','DBLINKNAME','USERNAME') stmt FROM dual

11. Get the trigger SQL under the user

Select DBMS_METADATA.GET_DDL ('TRIGGER','TRIGGERNAME','USERNAME) FROM DUAL

12. Get the sequence under the user

Select DBMS_METADATA.GET_DDL ('SEQUENCE','SEQUENCENAME') from DUAL

13. Get the function under the user

Select DBMS_METADATA.GET_DDL ('FUNCTION','FUNCTIONNAME','USERNAME') from DUAL

14. Get the definition of the package

Select DBMS_METADATA.GET_DDL ('PACKAGE','PACKAGENAME','USERNAME') from dual

15. Get stored procedures

Select DBMS_METADATA.GET_DDL ('PROCEDURE','PROCEDURENAME','USERNAME') from dual

16. Get the packet definition

Select DBMS_METADATA.GET_DDL ('PACKAGEBODY', 'PACKAGEBODYNAME','USERNAME') from dual

17. Get the definition of the remote database object

SELECT DBMS_LOB.SUBSTR@dblinkname (DBMS_METADATA.GET_DDL@dblinkname ('TABLE',' TABLENAME', 'USERNAME')) FROM DUAL@dblinkname

18. Get the definition of multiple objects

SELECT DBMS_METADATA.GET_DDL (O.OBJECT_TYPE, O.objectwriter)

FROM DBA_OBJECTS O

Where O.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE','FUNCTION') and ONWER =' ONWERNAME'

This statement can be changed, and you can get a lot of statements.

19. Common mistakes

SQL > select dbms_metadata.get_ddl ('TABLE','TABLENAME','USERNAME') 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.