汇出资料结构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: 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 ;
　　
　　把讨厌的REM删除后就完成啦~可以储存一份留着以后备用。

查看本文来源