通常我们习惯通过PL/SQL Developer导出表结构。Tools->Export Tables ->SQL Inserts tab页
中在where clause 中输入where1=0 导出建表SQL。然而这种方法不能导出含有long类型的表。
如cust表中含有long类型字段，则会遇到下述错误。Table CUST contains one or more LONG columns.Cannot export in SQL format, use PL/SQL Developer format instead.
如果要导出用户下所有的表结构，可以用exp 加rows=n 来解决。如果想得到建表的SQL可以用
imp加show=y参数获得建表SQL.
D:/>exp scott/tiger rows=y file=ora12.dmp tables=cust,emp statistics=none
Export: Release 11.2.0.1.0 - Production on 星期二 4月 19 18:16:37 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集服务器使用 AL32UTF8 字符集 (可能的字符集转换)即将导出指定的表通过常规路径.... . 正在导出表 CUST导出了 2 行. . 正在导出表 EMP导出了 13 行成功终止导出, 没有出现警告。
加了show=y参数，只会显示dump文件的内容，而不会执行数据的导入。
D:/>imp report/report show=y rows=y file=ora12.dmp fromuser=scott touser=reportImport: Release 11.2.0.1.0 - Production on 星期二 4月 19 18:18:17 2011Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
经由常规路径由 EXPORT:V11.02.00 创建的导出文件警告: 这些对象由 SCOTT 导出, 而不是当前用户已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入导入服务器使用 AL32UTF8 字符集 (可能的字符集转换). 正在将 SCOTT 的对象导入到 REPORT "CREATE TABLE "CUST" ("CUST_ID" NUMBER(2, 0) NOT NULL ENABLE, "CUST_NAME" VA" "RCHAR2(15), "E_NAME" LONG) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 S" "TORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS " "1 BUFFER_POOL DEFAULT) TABLESPACE "TBS_REPORT" LOGGING NOCOMPRESS". . 正在跳过表 "CUST"
"CREATE INDEX "CUST_ID_PK" ON "CUST" ("CUST_ID" ) PCTFREE 10 INITRANS 2 MAX" "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL" "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TBS_REPORT" LOGGING" "ALTER TABLE "CUST" ADD CONSTRAINT "CUST_ID_PK" PRIMARY KEY ("CUST_ID") DEF" "ERRABLE INITIALLY DEFERRED USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 S" "TORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS " "1 BUFFER_POOL DEFAULT) TABLESPACE "TBS_REPORT" LOGGING ENABLE "
"CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCH" "AR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUM" "BER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRAN" "S 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST " "GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS". . 正在跳过表 "EMP"
"GRANT SELECT ON "EMP" TO "REPORT"" "CREATE UNIQUE INDEX "PK_EMP" ON "EMP" ("EMPNO" ) PCTFREE 10 INITRANS 2 MAX" "TRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL" "IST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "ALTER TABLE "EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDE" "X PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MIN" "EXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "US" "ERS" LOGGING ENABLE " "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE" "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""成功终止导入, 没有出现警告。
