汇出资料结构DDL的程序和方法

　　* 可以使用DBMS_METADATA PACKAGE抓出DDL

DESC dbms_metadata，我们使用get_ddl function

FUNCTION GET_DDL RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OBJECT_TYPE VARCHAR2 IN
NAME VARCHAR2 IN
SCHEMA VARCHAR2 IN DEFAULT
VERSION VARCHAR2 IN DEFAULT
MODEL VARCHAR2 IN DEFAULT
TRANSFORM VARCHAR2 IN DEFAULT

　　所以只要输入OBJECT_TYPE,NAME 就可以

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','T2','SYS') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T2','SYS')
----------------------------------------------------------------

CREATE TABLE "SYS"."T2"
( "A" NUMBER,
"B" NUMBER
) PCTFREE 10 PCTUSE

　　配合spool就可以把需要的表格DDL汇出。

　　如果不想产生STORAGE CLAUSE，可以DISABLED ~

SQL>EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

　　* 如果要汇出大量的资料库DDL结构，可利用EXPORT/IMPORT

1) $ exp userid=test/test rows=n file=test.dmp

Connected to: 9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
JServer Release 9.2.0.5.0 - Production
Export done in ZHT16MSWIN950 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table DEPT_TEST
. . exporting table LAB1
. . exporting table T1
. . exporting table T2
. . exporting table TEST
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

2) $ imp userid=test/test full=y file=test.dmp indexfile=test.sql

Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHT16MSWIN950 character set and UTF8 NCHAR character set
Import terminated successfully without warnings.

3) $ cat test.sql

REM CREATE TABLE "TEST"."DEPT_TEST" ("DEPTNO" NUMBER(2, 0), "DNAME"
REM VARCHAR2(14), "LOC" VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM CREATE TABLE "TEST"."LAB1" ("NAME" VARCHAR2(10), "ADDR" VARCHAR2(20),
REM "ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM "USERS" LOGGING NOCOMPRESS ;
REM CREATE TABLE "TEST"."T1" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS
REM 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM CREATE TABLE "TEST"."T2" ("ID" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS
REM 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "USERS" LOGGING NOCOMPRESS ;
REM CREATE TABLE "TEST"."TEST" ("DEPTNO" NUMBER(2, 0), "DNAME"
REM VARCHAR2(14), "LOC" VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
REM TABLESPACE "USERS" LOGGING NOCOMPRESS ;

　　把讨厌的REM删除后就完成啦~可以储存一份留着以后备用。

【责编:Amy】

--------------------next---------------------