匯出資料結(jié)構DDL的程序和方法
2024-07-21 02:33:20
供稿：網(wǎng)友
* 可以使用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匯出。
假如不想產(chǎn)生STORAGE CLAUSE，可以DISABLED ~
SQL>EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.session_TRANSFORM,'STORAGE',false);
* 假如要匯出大量的資料庫DDL結(jié)構，可利用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刪除后就完成啦~可以儲存一份留著以后備用。
