Posts
从导出文件取出建表建索引的DDL语句
9i提供了DBMS_METADATA程序包取出DDL语句, 如
set pages 50000 line 130 long 4000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl('TABLE','T2') from dual;
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'DEFAULT');
SQL> set pages 50000 line 130 long 4000
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
PL/SQL procedure successfully completed.
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
PL/SQL procedure successfully completed.
SQL> select dbms_metadata.get_ddl('TABLE','T2') from dual;
DBMS_METADATA.GET_DDL('TABLE','T2')
--------------------------------------------------------------------------------
CREATE TABLE "A"."T2"
( "A" NUMBER(*,0),
"B" VARCHAR2(10)
) ;
SQL> exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'DEFAULT');
PL/SQL procedure successfully completed.
SQL>
10g的data dump import也可以做到,如
expdp a/a content=metadata_only directory=data_pump_dir dumpfile=a.dmp nologfile=y impdp a/a sqlfile=a.sql remap_tablespace=ts_test:ts_xxx transform=storage:n transform=segment_attributes:n:table include=table/table,table/index/index directory=data_pump_dir dumpfile=a.dmp nologfile=y cat /home/oracle/app/oracle/admin/test/dpdump/a.sql
[oracle@DEV-RPT-2 ~]$ expdp a/a content=metadata_only directory=data_pump_dir dumpfile=a.dmp nologfile=y
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 April, 2009 16:16:03
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "A"."SYS_EXPORT_SCHEMA_01": a/******** content=metadata_only directory=data_pump_dir dumpfile=a.dmp nologfile=y
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/JAVA_SOURCE/JAVA_SOURCE
Processing object type SCHEMA_EXPORT/JAVA_CLASS/JAVA_CLASS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "A"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for A.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/app/oracle/admin/test/dpdump/a.dmp
Job "A"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:16:06
[oracle@DEV-RPT-2 ~]$ impdp a/a sqlfile=a.sql remap_tablespace=ts_test:ts_xxx transform=storage:n transform=segment_attributes:n:table include=table/table,table/index/index directory=data_pump_dir dumpfile=a.dmp nologfile=y
Import: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 April, 2009 16:17:00
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
Master table "A"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "A"."SYS_SQL_FILE_FULL_01": a/******** sqlfile=a.sql remap_tablespace=ts_test:ts_xxx transform=storage:n transform=segment_attributes:n:table include=table/table,table/index/index directory=data_pump_dir dumpfile=a.dmp nologfile=y
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Job "A"."SYS_SQL_FILE_FULL_01" successfully completed at 16:17:04
[oracle@DEV-RPT-2 ~]$ cat /home/oracle/app/oracle/admin/test/dpdump/a.sql
-- CONNECT A
-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "A"."MYROW"
( "A" NUMBER
) ;
CREATE TABLE "A"."T"
( "A" NUMBER
) ;
CREATE TABLE "A"."T1"
( "A" NUMBER(*,0),
"B" VARCHAR2(10)
) ;
CREATE TABLE "A"."A"
( "A" NUMBER(*,0)
) ;
CREATE TABLE "A"."T2"
( "A" NUMBER(*,0),
"B" VARCHAR2(10)
) ;
CREATE TABLE "A"."B"
( "A" NUMBER(*,0),
"B" CHAR(1)
) ;
CREATE TABLE "A"."C"
( "A" NUMBER(*,0),
"B" CHAR(1),
"C" CLOB
) ;
-- new object type path is: SCHEMA_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "A"."IND_T1_A" ON "A"."T1" ("A")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TS_XXX" PARALLEL 1 ;
ALTER INDEX "A"."IND_T1_A" NOPARALLEL;
CREATE UNIQUE INDEX "A"."IND__AAAAABBBBBCCCCCDDDDDEEEEE" ON "A"."T1" ("A", "B")
PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TS_XXX" PARALLEL 1 ;
ALTER INDEX "A"."IND__AAAAABBBBBCCCCCDDDDDEEEEE" NOPARALLEL;
[oracle@DEV-RPT-2 ~]$
8i,可以从导出文件中获取
grep -a "CREATE TABLE" a.dmp
[oracle@DEV-RPT-2 ~]$ grep -a "CREATE TABLE" a.dmp
CREATE TABLE "A" ("A" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_TEST" LOGGING NOCOMPRESS
CREATE TABLE "B" ("A" NUMBER(*,0), "B" CHAR(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_TEST" LOGGING NOCOMPRESS
CREATE TABLE "C" ("A" NUMBER(*,0), "B" CHAR(1), "C" CLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_TEST" LOGGING NOCOMPRESS LOB ("C") STORE AS (TABLESPACE "TS_TEST" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
CREATE TABLE "MYROW" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_TEST" LOGGING NOCOMPRESS
CREATE TABLE "T" ("A" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_TEST" LOGGING NOCOMPRESS
CREATE TABLE "T1" ("A" NUMBER(*,0), "B" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_TEST" LOGGING NOCOMPRESS
CREATE TABLE "T2" ("A" NUMBER(*,0), "B" VARCHAR2(10)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS_XXXXXYYYYYZZZZZAAAAABBBBB" LOGGING NOCOMPRESS
[oracle@DEV-RPT-2 ~]$
更靠谱的方法是,在imp命令加上indexfile选项, 生成一个文件, 里面包括有建表和建索引等语句, 然后转换一下它的格式. 过程如下:
导出文件:
exp a/a rows=n compress=n file=a.dmp
[oracle@DEV-RPT-2 ~]$ exp a/a rows=n compress=n file=a.dmp Export: Release 10.2.0.4.0 - Production on Thu Apr 23 15:12:47 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set Note: table data (rows) will not be exported . exporting pre-schema procedural objects and actions . exporting foreign function library names for user A . exporting PUBLIC type synonyms . exporting private type synonyms . exporting object type definitions for user A About to export A's objects ... . exporting database links . exporting sequence numbers . exporting cluster definitions . about to export A's tables via Conventional Path ... . . exporting table A . . exporting table B . . exporting table C . . exporting table MYROW . . exporting table T . . exporting table T1 . . exporting table T2 . 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. [oracle@DEV-RPT-2 ~]$
生成语句:
imp a/a file=a.dmp indexfile=a.sql
[oracle@DEV-RPT-2 ~]$ imp a/a file=a.dmp indexfile=a.sql Import: Release 10.2.0.4.0 - Production on Thu Apr 23 15:13:20 2009 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set Import terminated successfully without warnings. [oracle@DEV-RPT-2 ~]$
用sed命令格式化:
cat a.sql |\
sed 's/^REM // #删除开头的注释字符串
#/^CONNECT /s/^/REM / #删除CONNECT语句
/^CONNECT /d #删除CONNECT语句
/^$/d #删除空行
' |\
sed '
/^CREATE\|^ALTER/ { #如果是CREATE或ALTER语句
:x
/;$/!{ #如果行尾不是分号
N #读取下一行,保存起来
b x
}
#直到行尾是分号
#s/\n//g #删除换行符, 多行合并成一行
s/PCTFREE .*/tablespace xxxx;/ #删除存储参数等,并改变表空间
#s/PCTFREE .* \(TABLESPACE [\n]*"[^ ]*"\) .*/\1;/ #或者是,表空间不变
}
'
[oracle@DEV-RPT-2 ~]$ cat a.sql |\
> sed 's/^REM // #删除开头的注释字符串
> #/^CONNECT /s/^/REM / #删除CONNECT语句
> /^CONNECT /d #删除CONNECT语句
> /^$/d #删除空行
> ' |\
> sed '
> /^CREATE\|^ALTER/ { #如果是CREATE或ALTER语句
> :x
> /;$/!{ #如果行尾不是分号
> N #读取下一行,保存起来
> b x
> }
> #直到行尾是分号
> #s/\n//g #删除换行符, 多行合并成一行
> s/PCTFREE .*/tablespace xxxx;/ #删除存储参数等,并改变表空间
> #s/PCTFREE .* \(TABLESPACE [\n]*"[^ ]*"\) .*/\1;/ #或者是,表空间不变
> }
> '
CREATE TABLE "A"."A" ("A" NUMBER(*,0)) tablespace xxxx;
CREATE TABLE "A"."B" ("A" NUMBER(*,0), "B" CHAR(1)) tablespace xxxx;
ALTER TABLE "A"."B" ADD PRIMARY KEY ("A", "B") USING INDEX tablespace xxxx;
CREATE TABLE "A"."C" ("A" NUMBER(*,0), "B" CHAR(1), "C" CLOB) tablespace xxxx;
CREATE TABLE "A"."MYROW" ("A" NUMBER) tablespace xxxx;
CREATE TABLE "A"."T" ("A" NUMBER) tablespace xxxx;
CREATE TABLE "A"."T1" ("A" NUMBER(*,0), "B" VARCHAR2(10)) tablespace xxxx;
CREATE UNIQUE INDEX "A"."IND_T1_A" ON "T1" ("A" ) tablespace xxxx;
CREATE UNIQUE INDEX "A"."IND__AAAAABBBBBCCCCCDDDDDEEEEE" ON "T1" ("A" ,
"B" ) tablespace xxxx;
CREATE TABLE "A"."T2" ("A" NUMBER(*,0), "B" VARCHAR2(10)) tablespace xxxx;
[oracle@DEV-RPT-2 ~]$
也可以用Java或awk程序完成转换, 参见
Pretoria is a tool for manipulating Oracle indexfiles
beautify_indexfile
-fin-
No comments:
Post a Comment