The following is a detailed analysis of the use of DBMS_METADATA.GET_DDL in Oracle, the need for friends under the reference
DBMS_METADATA.GET_DDL () is used to get the DDL for the object, as follows.
Note: In Sqlplus, to better demonstrate the DDL, you need to set the following parameters:
Copy Code code as follows:
Set Line 200
Set pagesize 0
Set Long 99999
Set Feedback off
set echo off
1 Get the DDL of Table, index, view, stored procedure, function
Copy Code code as follows:
Select Dbms_metadata.get_ddl (' TABLE ', ' table_name ', ' Table_owner ') from dual;
Select Dbms_metadata.get_ddl (' INDEX ', ' index_name ', ' Index_owner ') from dual;
Select Dbms_metadata.get_ddl (' VIEW ', ' view_name ', ' View_owner ') from dual;
Select Dbms_metadata.get_ddl (' PROCEDURE ', ' procedure_name ', ' Procedure_owner ') from dual;
Select Dbms_metadata.get_ddl (' FUNCTION ', ' function_name ', ' Function_owner ') from dual;
The following script is used to obtain the DDL of all tables, indexes, views, stored procedures, functions under a schema
Copy Code code as follows:
Set pagesize 0
Set Long 90000
Set Feedback off
set echo off
Spool Schema_ddl.sql
SELECT Dbms_metadata. GET_DDL (' TABLE ', u.table_name,u.owner) from Dba_tables u;
SELECT Dbms_metadata. GET_DDL (' VIEW ', u.view_name,u.owner) from Dba_views u;
SELECT Dbms_metadata. GET_DDL (' INDEX ', u.index_name,u.owner) from Dba_indexes u;
Select Dbms_metadata.get_ddl (' PROCEDURE ', U.object_name, U.owner,) from dba_objects u where U.object_type = ' procedu RE ';
Select Dbms_metadata.get_ddl (' function ', U.object_name, U.owner,) from dba_objects u where U.object_type = ' function ';
spool off;
2 Obtain the DDL of the table space
to obtain DDL for a single table space:
Copy Code code as follows:
Select Dbms_metadata.get_ddl (' tablespace ', ' Tbs_name ') from dual;
To obtain DDL for all tablespace:
Copy Code code as follows:
SELECT Dbms_metadata. GET_DDL (' tablespace ', ts.tablespace_name)
from Dba_tablespaces TS;
3 Obtain the user's DDL
to obtain a single user's DDL:
Copy Code code as follows:
Select Dbms_metadata.get_ddl (' USER ', ' Epay_user ') from dual;
To obtain the DDL for all users:
Copy Code code as follows:
SELECT Dbms_metadata. GET_DDL (' USER ', u.username)
From Dba_users U;
