DBMS_METADATE.GET_DDL, dbmsmetadata

DBMS_METADATE.GET_DDL, dbmsmetadata

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 'ddls'

) Return clob;

NOTE: If sqlplus is used, you need to format it as follows. In particular, you need to set long. Otherwise, the complete SQL statement cannot be displayed.

Set linesize 120
Sets pages 1000
Sets long 10000

Bytes ---------------------------------------------------------------------------------------

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', 'emp') from dual;

View the SQL statements of other user tables:

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

View the SQL statement used to create a user Index

View the indexes of the expected table:

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:

Select owner, TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE

FROM user_constraints WHERE TABLE_NAME = 'emp ';

View the SQL statement for creating a primary key:

SELECT DBMS_METADATA.GET_DDL ('straint', 'emp_pk') from dual;

View the SQL statement used to create a foreign key:

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:

SELECT DBMS_METADATA.GET_DDL ('view', 'My _ TABLES ') from dual;

View SQL statements in other user views:

SELECT DBMS_METADATA.GET_DDL ('view', 'My _ TABLES ', 'Scott') from dual;

You can also view the SQL statement used to create a view:

Select text from user_views WHERE view_name = 'view _ name ';

---------------------------------------------------------------------------

Some tips for using DBMS_METADATA.GET_DDL

Obtain the ddl statements for 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 ');

Obtain the ddl statements for all tablespaces:

SELECT DBMS_METADATA.GET_DDL ('tablespace', TS. tablespace_name)
FROM DBA_TABLESPACES TS;

Obtain the ddl statements for all created users:

SELECT DBMS_METADATA.GET_DDL ('user', U. username)
FROM DBA_USERS U;

---------------------------------------------------------------------------

Use the DBMS_METADATA.GET_DDL package to obtain the ddl script of the database object

Syntax for getting a single table, view, and index:

Set pagesize 1000

Set linesize 120

Set newpage 0

Set echo off/* display commands in the file, only display execution results */

/* Set echo on shows each command and execution result in the file */

Sets LONG 10000

SPOOL spool/dept. SQL

Select dbms_metadata.get_ddl ('table', 'table _ name', 'Scott ') from dual;
Select dbms_metadata.get_ddl ('view', 'view _ name', 'Scott ') from dual;
Select dbms_metadata.get_ddl ('index', 'index _ name', 'Scott ') from dual;

Spool off;

Obtains the syntax for creating tables, views, and indexes in a SCHEMA. scott is used as an example:

Set pagesize 1000

Set linesize 120

Set newpage 0

SET ECHO OFF

Sets LONG 10000

SPOOL spool/schema. SQL

Connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL ('table', u. table_name) FROM USER_TABLES u;
SELECT DBMS_METADATA.GET_DDL ('view', u. VIEW_name) FROM USER_VIEWS u;
SELECT DBMS_METADATA.GET_DDL ('index', u. index_name) FROM USER_INDEXES u;

Spool off;

Obtain the syntax for creating all stored procedures in a SCHEMA:

Set pagesize 1000

Set linesize 120

Set newpage 0

SET ECHO OFF

Sets LONG 10000

SPOOL spool/procedure. SQL

SELECT DBMS_METADATA.GET_DDL ('processed', u. object_name)

FROM user_objects u WHERE object_type = 'processed ';

Spool off;

Obtain the syntax for creating all functions in a SCHEMA:

Set pagesize 1000

Set linesize 120

Set newpage 0

SET ECHO OFF

Sets LONG 10000

SPOOL spool/function. SQL

SELECT DBMS_METADATA.GET_DDL ('function', u. object_name)

FROM user_objects u WHERE object_type = 'function ';

Spool off;

---------------------------------------------------------------------------

How to execute DBMS_METADATA.GET_DDL to report the ORA-39212

Log in as sys and run DBMS_METADATA.get_ddl to get the metadata of a tablespace. An error is reported.
Select DBMS_METADATA.get_ddl ('tablespace', 'pda') from dual;
ERROR:
ORA-39212: installation error: XSL stylesheets not loaded correctly
ORA-06512: at "SYS. DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS. DBMS_METADATA_INT", line 7428
ORA-06512: at "SYS. DBMS_METADATA_INT", line 7477
ORA-06512: at "SYS. DBMS_METADATA_INT", line 9495
ORA-06512: at "SYS. DBMS_METADATA", line 1920
ORA-06512: at "SYS. DBMS_METADATA", line 2793

.........

Solution:
Log on to the system as sysdba and run the following statement:

SQL> exec dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

Execution successful again:

SQL> selectDBMS_METADATA.get_ddl ('tablespace', 'pda') from dual;