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

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

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.