导出一ORACLE用户下的所有对象的DDL脚本
本帖最后由 Bill 于 2012-12-19 14:52 编辑有个问题想请教下大家，是这样的：
公司的开发人员用一个oracle用户连接到数据库（比如SCOTT用户），领导要求这样做：
开发人员随时要对表结构进行更改（他们自己改，DBA不干预），所以要时不时地把某一时间的表结构及数据导出，在需要时好把表结构及数据回到某个点，要求我写两个脚本，现实如下功能：
一个是创建（创建前先DROP掉所有表及序列等）SCOTT用户下的所有对象的脚本，还要考虑表之间的依赖关系，但不插入数据；另一个是数据脚本,INSERT插入数据的脚本。
这样做是因为他们要把脚本复制到PL/SQL DEVELOPER 或 SQL DEVELOPER 里面执行，不用DBA在OS下用imp/expdp来实现。
我的想法是用power designer的反向工程来实现，但power designer不太会用！
大家看看有什么办法给指点一下吧！ 个人建议，仅供参考
1 . 将DBA在OS 下 imp/expdp 导入与导出 *.dmp 文件
2 . OS下 strings *.bmp >> table.txt
3 . 发给开发 一个方法是exp 加rows=n
另一个方法是获取一个SCHEMA下的所有建表的语法，以scott为例：
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema.sql
connect scott/tiger;
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name) FROM USER_TABLES u;
spool off; FOR EXAMPLE$ exp maclean/maclean file=maclean_ddl rows=no owner=maclean
Export: Release 10.2.0.5.0 - Production on Thu Dec 20 02:04:45 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
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 MACLEAN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MACLEAN
About to export MACLEAN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MACLEAN's tables via Conventional Path ...
. . exporting table BB
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. . exporting table MACLEAN
EXP-00091: Exporting questionable statistics.
. . exporting table MACLEAN_LOB
EXP-00091: Exporting questionable statistics.
. . exporting table TV
EXP-00091: Exporting questionable statistics.
. . exporting table VALIT2
EXP-00091: Exporting questionable statistics.
. 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 with warnings.
$ imp maclean/maclean file=maclean_ddl.dmp show=y full=y
Import: Release 10.2.0.5.0 - Production on Thu Dec 20 02:07:06 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing MACLEAN's objects into MACLEAN
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'G10R25', inst_scn=>'31433226');"
"COMMIT; END;"
"CREATE TABLE "BB" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBO"
"BJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJ"
"ECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" V"
"ARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VAR"
"CHAR2(1), "SECONDARY" VARCHAR2(1)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
"NS 255 STORAGE(INITIAL 6291456 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 B"
"UFFER_POOL DEFAULT) TABLESPACE "TTSA" LOGGING NOCOMPRESS"
"CREATE INDEX "IND_OBJD1" ON "BB" ("OBJECT_ID" ) PCTFREE 10 INITRANS 2 MAXT"
"RANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1"
" BUFFER_POOL DEFAULT) TABLESPACE "TTSA" LOGGING"
"CREATE TABLE "MACLEAN" ("T1" NUMBER(*,0), "T2" CHAR(20), "T3" CHAR(20), "T4"
"" CHAR(20), "T5" CHAR(20), "T6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAX"
"TRANS 255 STORAGE(INITIAL 17825792 NEXT 1048576 FREELISTS 1 FREELIST GROUPS"
" 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
"CREATE TABLE "MACLEAN_LOB" ("T1" VARCHAR2(200) NOT NULL ENABLE, "T2" CLOB, "
""T3" CLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 3"
"145728 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"
"ESPACE "USERS" LOGGING NOCOMPRESS LOB ("T2") STORE AS (TABLESPACE "USERS" "
"ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 50 CACHE STORAGE(INITIAL 6553"
"6 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LOB ("T3"
"") STORE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSI"
"ON 50 CACHE STORAGE(INITIAL 65536 NEXT 1048576 FREELISTS 1 FREELIST GROUPS"
" 1 BUFFER_POOL DEFAULT))"
"CREATE TABLE "TV" ("RN" NUMBER, "RP" VARCHAR2(600)) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 450887680 NEXT 1048576 FREELISTS 1 "
"FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRES"
"S"
"CREATE TABLE "VALIT2" ("T1" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 "
"MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 FREELISTS 1 FREELIST GROUPS"
" 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = PLSQL"
"CREATE procedure insert_data(s int) as"
" begin"
" for i in 1..s loop"
" insert into MACLEAN values(i,'A','B','C','D',sysdate);"
" commit;"
" end loop;"
" end;"
"ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = NONE"
"ALTER PROCEDURE "INSERT_DATA" COMPILE REUSE SETTINGS TIMESTAMP '2012-10-25:"
"12:00:02'"
简单来说就是
exp + rows=no
imp + show=y imp的show=y是不是就是显示导入内容，如LZ所说的，如果要把导出的数据变成insert脚本；
2楼的 strings *.bmp >> table.txt是不是一个快速的方法呢？ 用了多种方法也不行啊，所生成的脚本要复制到PL/SQL DEVELOPER的窗口里执行。。。依赖关系不能错，也不能报错。。。 Liu Maclean(刘相兵 发表于 2012-12-20 15:07 static/image/common/back.gif
简单来说就是
exp + rows=no
这种应该是显示出IMP导入时的操作内容吧，把SHOW=Y出来的内容复制到maclean连接到数据库的SQL DEVELOPER窗口里执行是不行的。会报错。 imp SHOW just list file contents (N)
show 是只列出 dmp文件的内容，实际不导入， 即exp rows=no => 只导出定义 ＋ imp show=y 只列出dmp文件中的DDL Liu Maclean(刘相兵 发表于 2012-12-20 18:58 static/image/common/back.gif
imp SHOW just list file contents (N)
show 是只列出 dmp文件的内容，实际不导入， 即exp rows=no => ...
理解Mac的意思，谢谢，但这种方法实现不了。 学习了。 Bill 发表于 2012-12-21 11:34 static/image/common/back.gif
理解Mac的意思，谢谢，但这种方法实现不了。
不理解你为啥说实现不了
对于你的需求， 定时跑一个crontab exp+ rows=no导出定义 用时间字段命名该dump文件
具体需要该ddl定义时 用imp show=y 取出来
写脚本实现这个 2-3句的事情吧？ imp show=y
imp 都是先单独创建表,最后再创建并启用外键关系的
例如SQL> create table t0(x number(4) primary key);
表已创建。
SQL> create table t1 (x number(4) primary key);
表已创建。
SQL> create table t2 (x number(4) primary key);
表已创建。
SQL> alter table t1 add foreign key (x) references t0(x);
表已更改。
SQL> alter table t2 add foreign key (x) references t1(x);
表已更改。
SQL> alter table t0 add foreign key (x) references t2(x);
表已更改。
SQL> host
$ exp csq3/csq3 file=csq3.dmp rows=n
Export: Release 11.2.0.3.0 - Production on 星期二 12月 25 11:55:01 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 AL32UTF8 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表数据 (行)
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 CSQ3 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 CSQ3 的对象类型定义
即将导出 CSQ3 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 CSQ3 的表通过常规路径...
. . 正在导出表 T0
. . 正在导出表 T1
. . 正在导出表 T2
. 正在导出同义词
. 正在导出视图
. 正在导出存储过程
. 正在导出运算符
. 正在导出引用完整性约束条件
. 正在导出触发器
. 正在导出索引类型
. 正在导出位图, 功能性索引和可扩展索引
. 正在导出后期表活动
. 正在导出实体化视图
. 正在导出快照日志
. 正在导出作业队列
. 正在导出刷新组和子组
. 正在导出维
. 正在导出 post-schema 过程对象和操作
. 正在导出统计信息
成功终止导出, 没有出现警告。
$ imp csq3/csq3 file=csq3.dmp show=y
Import: Release 11.2.0.3.0 - Production on 星期二 12月 25 11:55:27 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 AL32UTF8 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 CSQ3 的对象导入到 CSQ3
"BEGIN "
"sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
"CURRENT_SCHEMA'), export_db_name=>'ZHDB', inst_scn=>'120647537');"
"COMMIT; END;"
"CREATE TABLE "T0" ("X" NUMBER(4, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
"RANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
"ALTER TABLE "T0" ADD PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 M"
"AXTRANS 255 TABLESPACE "USERS" LOGGING ENABLE"
"CREATE TABLE "T1" ("X" NUMBER(4, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
"RANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
"ALTER TABLE "T1" ADD PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 M"
"AXTRANS 255 TABLESPACE "USERS" LOGGING ENABLE"
"CREATE TABLE "T2" ("X" NUMBER(4, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
"RANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
"ALTER TABLE "T2" ADD PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 M"
"AXTRANS 255 TABLESPACE "USERS" LOGGING ENABLE"
"ALTER TABLE "T1" ADD FOREIGN KEY ("X") REFERENCES "T0" ("X") ENABLE"
"ALTER TABLE "T2" ADD FOREIGN KEY ("X") REFERENCES "T1" ("X") ENABLE"
"ALTER TABLE "T0" ADD FOREIGN KEY ("X") REFERENCES "T2" ("X") ENABLE"
成功终止导入, 没有出现警告。
这个问题 ，可以参见这里 http://blog.csdn.net/tianlesoftware/article/details/6798428 imp的indexfile参数可以实现你想要的。 cshiqin 发表于 2012-12-25 12:01 static/image/common/back.gif
imp show=y
imp 都是先单独创建表,最后再创建并启用外键关系的
例如
你用show出 来的脚本再执行一下，不会报错？ Liu Maclean(刘相兵 发表于 2012-12-25 09:35 static/image/common/back.gif
不理解你为啥说实现不了
对于你的需求， 定时跑一个crontab exp+ rows=no导出定义 用时间字段命名该dum ...
却实是可以导出来哦，不过把show出来的脚本复制到SQL DEVELOPER里执行就不行了 不错的帖子~
页:
[1]