How do I export definition statements for stored procedures, functions, packages, and triggers? How do I export table and index creation statements?
How do I export definition statements for stored procedures, functions, packages, and triggers in Oracle? How to export the structure of the table? How do I export an index creation statement?
Someone in the QQ group asked: how to export a stored procedure under a user?
Mai Miao answer: there are many ways, you can use DBMS_METADATA.GET_DDL package.
Use the following script to export the stored procedure code under a user to the / tmp/a.sql file:
SET PAGESIZE 0
SET TRIMSPOOL ON
SET LINESIZE 10000
SET LONG 90000
SET FEEDBACK OFF
SET FEED OFF
SET ECHO OFF
Spool / tmp/a.sql
SELECT DBMS_METADATA.GET_DDL ('PROCEDURE', U.OBJECT_NAME) | | CHR (10) | |' /'
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PROCEDURE'
Spool OFF
After opening the file, you can simply deal with it.
Generally speaking, there are two ways to get it, first, using the GET_DDL function in the system package DBMS_METADATA package, and second, using exp or expdp to obtain.
Let's take a look at the first way, how to use the GET_DDL function in the system package DBMS_METADATA package to get the definition statement of the object. The following are the input and output parameters of the function:
SQL > DESC DBMS_METADATA.GET_DDL
PARAMETER TYPE MODE DEFAULT?
(RESULT) CLOB
OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN Y
VERSION VARCHAR2 IN Y
MODEL VARCHAR2 IN Y
TRANSFORM VARCHAR2 IN Y
The detailed parameters are as follows:
L NAME object name
Version of the original data of the VERSION object
L TRANSFORM defaults to DDL
L View the create table SQL statement:
SELECT DBMS_METADATA.GET_DDL ('TABLE','DEPT','SCOTT') FROM DUAL
SELECT DBMS_METADATA.GET_DDL ('TABLE',U.TABLE_NAME) FROM USER_TABLES U
N View the SQL statement that creates the primary key:
SELECT DBMS_METADATA.GET_DDL ('CONSTRAINT','EMP_PK') FROM DUAL
N View the SQL statement that creates the view (VIEW):
SELECT DBMS_METADATA.GET_DDL ('VIEW',' MY_TABLES','SCOTT') FROM DUAL
SELECT DBMS_METADATA.GET_DDL ('VIEW', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'VIEW'
SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME=UPPER ('& VIEW_NAME')
N View the SQL statement that creates the trigger (TRIGGER):
SELECT DBMS_METADATA.GET_DDL ('TRIGGER', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'TRIGGER'
N View the SQL statement that creates the package (PACKAGE):
SELECT DBMS_METADATA.GET_DDL ('PACKAGE', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'PACKAGE'
N View the SQL statement that creates the synonym (SYNONYM):
SELECT DBMS_METADATA.GET_DDL ('SYNONYM', U.OBJECT_NAME)
FROM USER_OBJECTS U
WHERE OBJECT_TYPE = 'SYNONYM'
N View the SQL statement that creates the role (ROLE):
SELECT DBMS_METADATA.GET_DDL ('ROLE', U.ROLE) FROM DBA_ROLES U
N get the creation statement of a SCHEDULER JOB:
SELECT DBMS_METADATA.GET_DDL ('PROCOBJ', D.JOB_NAME, D.OWNER)
FROM DBA_SCHEDULER_JOBS D
WHERE D.JOB_TYPE = 'STORED_PROCEDURE'
AND D.STATE = 'SCHEDULED'
AND D.SCHEDULE_NAME IS NULL
N