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

王朝other·作者佚名 2008-05-19

宽屏版字体: 小|中|大|超大

* 可以使用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 ~

SQLEXECUTE 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: 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 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 ;