Dbms_metadata.get_ddl usage Summary

Dbms_metadata.get_ddl usage Summary

In daily work, some people often use tools to view the definition of objects. In fact, it is annoying to view objects in this way. At work, some colleagues asked me how to get the object definition. Today I will give you a summary, hoping to help you!

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

The GET_DDL function returns the DDL statement for the original data of the created object. parameter description

1. object_type --- object type of the DDL statement to return the original data
2. name --- Object name
3. schema-the Schema of the object. The default value is the Schema of the current user.
4. version --- version of the object's original data
5. model --- the default type of the original data is Oracle.
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.

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:

1. If sqlplus is used, you need to format the following items, especially long. Otherwise, the complete SQL statement cannot be displayed.
2. Use uppercase letters for parameters. Otherwise, the parameters cannot be found.

Set linesize 180
Sets pages 999
Sets long 90000

1. view the definition of the database table:

Select dbms_metadata.get_ddl ('table', 'tablename', 'username') from dual;

2. view the index SQL

Select dbms_metadata.get_ddl ('index', 'indexname', 'username') from dual;

3. view the SQL statement used to create a primary key

SELECT DBMS_METADATA.GET_DDL ('constraint', 'constraintname', 'username') from dual;

4. view the SQL statement used to create a foreign key

SELECT DBMS_METADATA.GET_DDL ('ref _ CONSTRAINT ', 'ref _ CONSTRAINTNAME', 'username') from dual;

5. view the SQL statement used to create a view

SELECT DBMS_METADATA.GET_DDL ('view', 'viewname', 'username') from dual;

6. view your SQL

SELECT DBMS_METADATA.GET_DDL ('user', 'username') from dual;

7. view the role's SQL

SELECT DBMS_METADATA.GET_DDL ('role', 'rolename') from dual;

8. view the tablespace SQL

SELECT DBMS_METADATA.GET_DDL ('tablespace', 'tablespacename') from dual;

9. Obtain the materialized view SQL

Select dbms_metadata.get_ddl ('materialized view', 'mvname') from dual;

10. Obtain the remote connection definition SQL

SELECT dbms_metadata.get_ddl ('db _ link', 'dblinkname', 'username') stmt FROM dual

11. Obtain the user's trigger SQL

Select DBMS_METADATA.GET_DDL ('trigger', 'triggername', 'username) from dual;

12. obtain user Sequences

Select DBMS_METADATA.GET_DDL ('sequence ', 'sequencename') from DUAL;

13. Obtain the functions of a user.

Select DBMS_METADATA.GET_DDL ('function', 'functionname', 'username') from DUAL

14. Get the definition of a package

Select DBMS_METADATA.GET_DDL ('package', 'packagename', 'username') from dual

15. Obtain the Stored Procedure

Select DBMS_METADATA.GET_DDL ('processure', 'processurename', 'username') from dual

16. Get the package Definition

Select DBMS_METADATA.GET_DDL ('package body', 'packagebodyname', 'username') from dual

17. Get definitions of remote database objects

SELECT DBMS_LOB.SUBSTR @ dblinkname (DBMS_METADATA.GET_DDL @ dblinkname ('table', 'tablename', 'username') from dual @ dblinkname

18. Get definitions of multiple objects

SELECT DBMS_METADATA.GET_DDL (O. OBJECT_TYPE, O. object_name, O. OWNER)
FROM DBA_OBJECTS O
Where O. OBJECT_TYPE IN ('table', 'index', 'processed', 'function') and ONWER = 'onwername ';

This statement can be changed to get a lot of statements.

19. Common Errors
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