exp /imp 导入导出表结构

今天在ml论坛上看到一位同学问如果导入导出数据的表结构，ml给出了一个方案，觉得很好用就记录下来了

imp SHOW     just list file contents (N)
show 是只列出 dmp文件的内容，实际不导入， 即exp rows=no => 只导出定义　＋ imp show=y 只列出dmp文件中的DDL 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114

[oracle@vrh8 ~]$ 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.          [oracle@vrh8 ~]$ 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'"

 

通过 为知笔记 发布