Several common methods for Oracle to acquire DDL

Oracle
Several common methods for Oracle to acquire DDL



Author: Liu Yingbo

Time: 2004-2-26

Mail:liuyingbo@126.com, please correct me.



Reprint please indicate the source and the author



There are roughly three ways to do this:

One, can through the toad, Plsql develop and other Third-party tools to export DLL operations, the advantage of this approach is simple and convenient operation, but need to install, the following a brief introduction to use these two tools to obtain the operation of the DLL statement.



1. The first is the Toad tool, which can be downloaded to the www.quest.com website

A brief description of the procedure is as follows:

A. Start toad, connect to target database

B. Click Dba->generate Schema Script, as shown in Figure 1:



Figure 1

C. The operating interface of Figure 2 appears:



Figure 2

Through the Operation interface (Figure 2) already very clear, select the corresponding schema, of course, you can choose the corresponding conditions through several other tab pages, click the Execute button, OK. The details of the operation will not say!

D. When the export operation is finished, an interface like Figure 3 appears



Figure 3

We can save the exported DDL script (save to File) or copy to the Clipboard (Clipboard).

2. Let's say another tool plsql develop how to get to a DDL statement

Action steps are as follows:

A. Start Plsql develop, connect to the target database

B. Click Tools->export Tables, as shown in Figure 4



Figure 4

C. Then the following interface appears, as shown in Figure 5



Figure 5

D. In the interface of Figure 5, select the appropriate content, and then click the Export button, OK, the interface will appear as shown in Figure 6, in fact, these tools are also called Oracle's EXP Tool



Figure 6

If you do not specify a file directory, the file appears in the Plsql develop software installation directory.

OK, here's the second method.

II. Directly through the Exp/imp tool

The most original and useful import Export tool Oracle provides, we can basically divide into three ways to implement export DDL.

A. Specify indexfile parameters through imp, but this is not a good way to have rem before each line.

The syntax is generally as follows:

Exp userid= ... tables=emp rows=n file=emp.dmp
Imp userid= ... file=emp.dmp indexfile=emp.sql

B. Specify show=y by IMP, and specify log parameters, the format is not very cool, in the format is very beautiful or through the tool to export more beautiful

The syntax is generally as follows:

Exp userid= ... tables=emp rows=n file= emp.dmp
Imp userid= ... file= emp.dmp show=y log=emp.sql

C. The use of Unix under the Strings command, the syntax is generally as follows, this method is more brutal:

Exp userid= ... tables=tab1 rows=n file=tab1.dmp
Strings Emp.dmp >emp.sql
There are DLL statements in the Emp.sql.

The third way is to take advantage of the new features of Oracle9i, that is, the Dbms_metadata package, to get the DLL statement, this is the focus of this article

Third, the DLL statement is obtained through the 9i dbms_metadata package

The syntax that is basically used is as follows:

A. Getting a single table-and-indexed syntax

Set heading off;
Set echo off;
Set pages 999;
Set long 90000;

Spool Dept.sql
Select Dbms_metadata.get_ddl (' TABLE ', ' DEPT ', ' SCOTT ') from dual;
Select Dbms_metadata.get_ddl (' INDEX ', ' dept_idx ', ' SCOTT ') from dual;
Spool off;



B. Get all the table and indexed syntax under a schema, take Scott for example:

Set pagesize 0

Set Long 90000

Set Feedback off

Set echo off
Spool Scott_schema.sql
Connect Scott/tiger;
SELECT Dbms_metadata. GET_DDL (' TABLE ', u.table_name)
From User_tables u;
SELECT Dbms_metadata. GET_DDL (' INDEX ', u.index_name)
From User_indexes u;
Spool off;



C. Getting the syntax of a schema to build all stored procedures

Connect Brucelau/brucelau;

Spool Procedures.sql


Select
Dbms_metadata. GET_DDL (' PROCEDURE ', u.object_name)

From
User_objects u

where
object_type = ' PROCEDURE ';


Spool off;



Other:

DBMS_METADATA.GET_DDL (' TABLE ', ' TAB1 ', ' USER1 ')
In three parameters, the first specifies the type of object to export the DDL definition (in this case, the table type), the second is the object name (in this case, the table name), and the third is the user name of the object.



Reference:

ZDNet "Easy access to Oracle Architecture description Syntax"

Official Documents of Oracle





Thank Shangym (Landscape Sky Snow Bridge)