Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 导出一ORACLE用户下的所有对象的DDL脚本
发新帖
Bill

0

积分

0

好友

9

主题

[SQL调优] 导出一ORACLE用户下的所有对象的DDL脚本

1#
发表于 2012-12-19 14:50:42 | 查看: 13694| 回复: 17
本帖最后由 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不太会用！
大家看看有什么办法给指点一下吧！
2#
发表于 2012-12-19 17:21:37
个人建议，仅供参考

1 . 将DBA在OS 下 imp/expdp 导入与导出 *.dmp 文件
2 . OS下 strings *.bmp >> table.txt
3 . 发给开发

回复 只看该作者 道具 举报

3#
发表于 2012-12-19 17:26:36
一个方法是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;

回复 只看该作者 道具 举报

4#
发表于 2012-12-20 15:07:34
FOR EXAMPLE
  1. [oracle@vrh8 ~]$ exp maclean/maclean file=maclean_ddl rows=no owner=maclean

  3. Export: Release 10.2.0.5.0 - Production on Thu Dec 20 02:04:45 2012

  5. Copyright (c) 1982, 2007, Oracle.  All rights reserved.


  8. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
  9. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  10. Export done in US7ASCII character set and UTF8 NCHAR character set
  11. server uses AL32UTF8 character set (possible charset conversion)
  12. Note: table data (rows) will not be exported

  14. About to export specified users ...
  15. . exporting pre-schema procedural objects and actions
  16. . exporting foreign function library names for user MACLEAN
  17. . exporting PUBLIC type synonyms
  18. . exporting private type synonyms
  19. . exporting object type definitions for user MACLEAN
  20. About to export MACLEAN's objects ...
  21. . exporting database links
  22. . exporting sequence numbers
  23. . exporting cluster definitions
  24. . about to export MACLEAN's tables via Conventional Path ...
  25. . . exporting table                             BB
  26. EXP-00091: Exporting questionable statistics.
  27. EXP-00091: Exporting questionable statistics.
  28. . . exporting table                        MACLEAN
  29. EXP-00091: Exporting questionable statistics.
  30. . . exporting table                    MACLEAN_LOB
  31. EXP-00091: Exporting questionable statistics.
  32. . . exporting table                             TV
  33. EXP-00091: Exporting questionable statistics.
  34. . . exporting table                         VALIT2
  35. EXP-00091: Exporting questionable statistics.
  36. . exporting synonyms
  37. . exporting views
  38. . exporting stored procedures
  39. . exporting operators
  40. . exporting referential integrity constraints
  41. . exporting triggers
  42. . exporting indextypes
  43. . exporting bitmap, functional and extensible indexes
  44. . exporting posttables actions
  45. . exporting materialized views
  46. . exporting snapshot logs
  47. . exporting job queues
  48. . exporting refresh groups and children
  49. . exporting dimensions
  50. . exporting post-schema procedural objects and actions
  51. . exporting statistics
  52. Export terminated successfully with warnings.



  56. [oracle@vrh8 ~]$ imp maclean/maclean file=maclean_ddl.dmp show=y full=y

  58. Import: Release 10.2.0.5.0 - Production on Thu Dec 20 02:07:06 2012

  60. Copyright (c) 1982, 2007, Oracle.  All rights reserved.


  63. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
  64. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  66. Export file created by EXPORT:V10.02.01 via conventional path
  67. import done in US7ASCII character set and UTF8 NCHAR character set
  68. import server uses AL32UTF8 character set (possible charset conversion)
  69. . importing MACLEAN's objects into MACLEAN
  70. "BEGIN  "
  71. "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
  72. "CURRENT_SCHEMA'), export_db_name=>'G10R25', inst_scn=>'31433226');"
  73. "COMMIT; END;"
  74. "CREATE TABLE "BB" ("OWNER" VARCHAR2(30), "OBJECT_NAME" VARCHAR2(128), "SUBO"
  75. "BJECT_NAME" VARCHAR2(30), "OBJECT_ID" NUMBER, "DATA_OBJECT_ID" NUMBER, "OBJ"
  76. "ECT_TYPE" VARCHAR2(19), "CREATED" DATE, "LAST_DDL_TIME" DATE, "TIMESTAMP" V"
  77. "ARCHAR2(19), "STATUS" VARCHAR2(7), "TEMPORARY" VARCHAR2(1), "GENERATED" VAR"
  78. "CHAR2(1), "SECONDARY" VARCHAR2(1))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRA"
  79. "NS 255 STORAGE(INITIAL 6291456 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 B"
  80. "UFFER_POOL DEFAULT) TABLESPACE "TTSA" LOGGING NOCOMPRESS"
  81. "CREATE INDEX "IND_OBJD1" ON "BB" ("OBJECT_ID" )  PCTFREE 10 INITRANS 2 MAXT"
  82. "RANS 255 STORAGE(INITIAL 1048576 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1"
  83. " BUFFER_POOL DEFAULT) TABLESPACE "TTSA" LOGGING"
  84. "CREATE TABLE "MACLEAN" ("T1" NUMBER(*,0), "T2" CHAR(20), "T3" CHAR(20), "T4"
  85. "" CHAR(20), "T5" CHAR(20), "T6" DATE)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAX"
  86. "TRANS 255 STORAGE(INITIAL 17825792 NEXT 1048576 FREELISTS 1 FREELIST GROUPS"
  87. " 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
  88. "CREATE TABLE "MACLEAN_LOB" ("T1" VARCHAR2(200) NOT NULL ENABLE, "T2" CLOB, "
  89. ""T3" CLOB)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 3"
  90. "145728 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"
  91. "ESPACE "USERS" LOGGING NOCOMPRESS LOB ("T2") STORE AS  (TABLESPACE "USERS" "
  92. "ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 50 CACHE  STORAGE(INITIAL 6553"
  93. "6 NEXT 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) LOB ("T3"
  94. "") STORE AS  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSI"
  95. "ON 50 CACHE  STORAGE(INITIAL 65536 NEXT 1048576 FREELISTS 1 FREELIST GROUPS"
  96. " 1 BUFFER_POOL DEFAULT))"
  97. "CREATE TABLE "TV" ("RN" NUMBER, "RP" VARCHAR2(600))  PCTFREE 10 PCTUSED 40 "
  98. "INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 450887680 NEXT 1048576 FREELISTS 1 "
  99. "FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRES"
  100. "S"
  101. "CREATE TABLE "VALIT2" ("T1" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 "
  102. "MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 FREELISTS 1 FREELIST GROUPS"
  103. " 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS"
  104. "ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = PLSQL"
  105. "CREATE procedure insert_data(s int) as"
  106. "    begin"
  107. "      for i in 1..s  loop"
  108. "        insert into MACLEAN values(i,'A','B','C','D',sysdate);"
  109. "        commit;"
  110. "        end loop;"
  111. "        end;"
  112. "ALTER SESSION SET "_LOAD_WITHOUT_COMPILE" = NONE"
  113. "ALTER PROCEDURE "INSERT_DATA" COMPILE REUSE SETTINGS TIMESTAMP '2012-10-25:"
  114. "12:00:02'"

复制代码

回复 只看该作者 道具 举报

5#
发表于 2012-12-20 15:07:49
简单来说就是

exp  + rows=no

imp + show=y

回复 只看该作者 道具 举报

6#
发表于 2012-12-20 15:12:22
imp的show=y是不是就是显示导入内容，如LZ所说的，如果要把导出的数据变成insert脚本；
2楼的 strings *.bmp >> table.txt是不是一个快速的方法呢？

回复 只看该作者 道具 举报

7#
发表于 2012-12-20 16:14:09
用了多种方法也不行啊，所生成的脚本要复制到PL/SQL DEVELOPER的窗口里执行。。。依赖关系不能错，也不能报错。。。

回复 只看该作者 道具 举报

8#
发表于 2012-12-20 16:21:47
Liu Maclean(刘相兵 发表于 2012-12-20 15:07
简单来说就是

exp  + rows=no

这种应该是显示出IMP导入时的操作内容吧，把SHOW=Y出来的内容复制到maclean连接到数据库的SQL DEVELOPER窗口里执行是不行的。会报错。

回复 只看该作者 道具 举报

9#
发表于 2012-12-20 18:58:07
imp SHOW     just list file contents (N)

show 是只列出 dmp文件的内容，实际不导入， 即exp rows=no => 只导出定义　＋ imp show=y 只列出dmp文件中的DDL

回复 只看该作者 道具 举报

10#
发表于 2012-12-21 11:34:56
Liu Maclean(刘相兵 发表于 2012-12-20 18:58
imp SHOW     just list file contents (N)

show 是只列出 dmp文件的内容，实际不导入， 即exp rows=no => ...

理解Mac的意思，谢谢，但这种方法实现不了。

回复 只看该作者 道具 举报

11#
发表于 2012-12-24 16:27:57
学习了。

回复 只看该作者 道具 举报

12#
发表于 2012-12-25 09:35:50
Bill 发表于 2012-12-21 11:34
理解Mac的意思，谢谢，但这种方法实现不了。

不理解你为啥说实现不了

对于你的需求， 定时跑一个crontab  exp+ rows=no导出定义 用时间字段命名该dump文件

具体需要该ddl定义时 用imp show=y 取出来

写脚本实现这个 2-3句的事情吧？

回复 只看该作者 道具 举报

13#
发表于 2012-12-25 12:01:48
imp show=y
imp 都是先单独创建表,最后再创建并启用外键关系的
例如
  1. SQL> create table t0(x number(4) primary key);

  3. 表已创建。

  5. SQL>  create table t1 (x number(4) primary key);

  7. 表已创建。

  9. SQL>  create table t2  (x number(4) primary key);

  11. 表已创建。

  13. SQL> alter table t1 add foreign key (x) references t0(x);

  15. 表已更改。

  17. SQL>  alter table t2 add foreign key (x) references t1(x);

  19. 表已更改。

  21. SQL> alter table t0 add foreign key (x) references t2(x);

  23. 表已更改。

  25. SQL> host
  26. [oracle@zhdbtest ~]$ exp csq3/csq3 file=csq3.dmp rows=n

  28. Export: Release 11.2.0.3.0 - Production on 星期二 12月 25 11:55:01 2012

  30. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


  33. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  34. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  35. 已导出 AL32UTF8 字符集和 AL16UTF16 NCHAR 字符集
  36. 注: 将不导出表数据 (行)
  37. . 正在导出 pre-schema 过程对象和操作
  38. . 正在导出用户 CSQ3 的外部函数库名
  39. . 导出 PUBLIC 类型同义词
  40. . 正在导出专用类型同义词
  41. . 正在导出用户 CSQ3 的对象类型定义
  42. 即将导出 CSQ3 的对象...
  43. . 正在导出数据库链接
  44. . 正在导出序号
  45. . 正在导出簇定义
  46. . 即将导出 CSQ3 的表通过常规路径...
  47. . . 正在导出表                              T0
  48. . . 正在导出表                              T1
  49. . . 正在导出表                              T2
  50. . 正在导出同义词
  51. . 正在导出视图
  52. . 正在导出存储过程
  53. . 正在导出运算符
  54. . 正在导出引用完整性约束条件
  55. . 正在导出触发器
  56. . 正在导出索引类型
  57. . 正在导出位图, 功能性索引和可扩展索引
  58. . 正在导出后期表活动
  59. . 正在导出实体化视图
  60. . 正在导出快照日志
  61. . 正在导出作业队列
  62. . 正在导出刷新组和子组
  63. . 正在导出维
  64. . 正在导出 post-schema 过程对象和操作
  65. . 正在导出统计信息
  66. 成功终止导出, 没有出现警告。
  67. [oracle@zhdbtest ~]$ imp csq3/csq3 file=csq3.dmp  show=y

  69. Import: Release 11.2.0.3.0 - Production on 星期二 12月 25 11:55:27 2012

  71. Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


  74. 连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  75. With the Partitioning, OLAP, Data Mining and Real Application Testing options

  77. 经由常规路径由 EXPORT:V11.02.00 创建的导出文件
  78. 已经完成 AL32UTF8 字符集和 AL16UTF16 NCHAR 字符集中的导入
  79. . 正在将 CSQ3 的对象导入到 CSQ3
  80. "BEGIN  "
  81. "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','"
  82. "CURRENT_SCHEMA'), export_db_name=>'ZHDB', inst_scn=>'120647537');"
  83. "COMMIT; END;"
  84. "CREATE TABLE "T0" ("X" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
  85. "RANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
  86. "ALTER TABLE "T0" ADD  PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 M"
  87. "AXTRANS 255 TABLESPACE "USERS" LOGGING ENABLE"
  88. "CREATE TABLE "T1" ("X" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
  89. "RANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
  90. "ALTER TABLE "T1" ADD  PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 M"
  91. "AXTRANS 255 TABLESPACE "USERS" LOGGING ENABLE"
  92. "CREATE TABLE "T2" ("X" NUMBER(4, 0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
  93. "RANS 255 TABLESPACE "USERS" LOGGING NOCOMPRESS"
  94. "ALTER TABLE "T2" ADD  PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 M"
  95. "AXTRANS 255 TABLESPACE "USERS" LOGGING ENABLE"
  96. [color=Red]"ALTER TABLE "T1" ADD FOREIGN KEY ("X") REFERENCES "T0" ("X") ENABLE"
  97. "ALTER TABLE "T2" ADD FOREIGN KEY ("X") REFERENCES "T1" ("X") ENABLE"
  98. "ALTER TABLE "T0" ADD FOREIGN KEY ("X") REFERENCES "T2" ("X") ENABLE"[/color]
  99. 成功终止导入, 没有出现警告。

复制代码

回复 只看该作者 道具 举报

14#
发表于 2012-12-26 15:46:06
这个问题 ，可以参见这里 http://blog.csdn.net/tianlesoftware/article/details/6798428

回复 只看该作者 道具 举报

15#
发表于 2012-12-27 22:48:20
imp的indexfile参数可以实现你想要的。

回复 只看该作者 道具 举报

16#
发表于 2012-12-28 15:11:56
cshiqin 发表于 2012-12-25 12:01
imp show=y
imp 都是先单独创建表,最后再创建并启用外键关系的
例如

你用show出 来的脚本再执行一下，不会报错？

回复 只看该作者 道具 举报

17#
发表于 2012-12-28 15:24:30
Liu Maclean(刘相兵 发表于 2012-12-25 09:35
不理解你为啥说实现不了

对于你的需求， 定时跑一个crontab  exp+ rows=no导出定义 用时间字段命名该dum ...

却实是可以导出来哦，不过把show出来的脚本复制到SQL DEVELOPER里执行就不行了

回复 只看该作者 道具 举报

18#
发表于 2014-7-18 21:09:07
不错的帖子~

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-21 01:47 , Processed in 0.054760 second(s), 20 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569