Oracle Gets Several Common Methods of DDL
Author: Liuying Bo
Time: 2004-2-26
Mail: liuyingbo@126.com, please correct
Reprint, please indicate the source and the author
Generally, three methods:
One; can derive DLL operations through third-party tools such as TOAD, PLSQL Development, and the advantage of this method is simple and convenient, but need to install, and simply introduce the operation of obtaining the DLL statement with these two tools.
1. First is the Toad tool, you can download it on www.quest.com website
The brief introduction to the operation steps is as follows:
a. Start Toad, Connect the Target Database
b. Click DBA-> Generate Schema Script, as shown in Figure 1:
figure 1
c. The operation interface of Figure 2 appears:
figure 2
Through the operation interface (as shown in Figure 2), it is already clear, select the corresponding schema, of course, the corresponding condition can be selected by other TAB pages, click the EXECUTE button, OK. Don't say a detailed operation!
d. After the export operation is over, the interface as shown in Figure 3 will appear.
image 3
We can save the exported DDL scripts or Copy to Clipboard.
2. Let's talk about how the other tool PLSQL DevelopP is going to DDL statement.
The steps are as follows:
a. Start PLSQL DevelopP, Connect the Target Database
b. Click Tools-> Export Tables, as shown in Figure 4
Figure 4
c. Then appear below, as shown in Figure 5
Figure 5
d. On the interface as shown in Figure 5, select the corresponding content, then click the Export button, OK, the interface will appear as shown in Figure 6, in fact, these tools are also calling Oracle's EXP tools.
Figure 6
If you don't specify a file directory, the file will appear in the installation directory of the PLSQL Develop software.
Ok, the second method is described below.
Second; directly through the Exp / IMP tool
Oracle provides the most primitive and most practical import export tool, we can generally divide three ways to implement export DDL.
a. Specify the indexfile parameters via IMP, but this method is uncomfortable that there will be REM before every line.
The syntax is generally as follows:
Exp userid = ... tables = Emp Rows = n file = Emp.dmpimp userid = ... file = Emp.dmp indexfile = Emp.sql
b. Specify Show = Y by IMP, and specify log parameters, it is not very cool in the format. It is very beautiful in format or more beautiful through tools.
The syntax is generally as follows:
Exp userid = ... TABLES = Emp Rows = n file = Emp.dmpimp userid = ... file = Emp.dmp show = y log = Emp.sql
c. With the Strings command under UNIX, the syntax is generally the following, this method is more barbaric:
Exp userid = ... tables = Tab1 rows = n file = Tab1.dmpstrings Emp.dmp> Emp.sqlemp.sql has a DLL statement
The third method is to use the new features of Oracle9i, which is the DBMS_METADATA package to get the DLL statement. This is also the focus of this article; through the 9i DBMS_METADATA package DLL statement
Basically, the syntax is as follows:
a. Get a single mergement table and the syntax of the index
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 scheduled tables and indexed syntax under Schema, which is SCOTT as an 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. Get the syntax of all stored procedures for SCHEMA
CONNECT Brucelau / Brucelau;
Spool procedures.sql
SELECT
DBMS_METADATA.GET_DDL ('procedure', u.object_name)
From
User_Objects U
WHERE
Object_type = 'procadu';
Spool OFF;
another:
DBMS_METADATA.GET_DDL ('Table', 'Tab1', 'USER1') three parameters, the first specified object type defined by the export DDL (in this case is the table type), the second is the object name (this example That is the table name), the third is the username where the object is located.
reference:
ZDNet China "easy to get the Oracle structure description syntax"
Oracle's official documentation
Thank shangym (Mountain Water Snow Bridge)
