Oracle exp imp 参数show的使用案例

验证imp show参数，通过实验知道，show=y表示展示imp导入的时候，会执行的相关语句(不包括insert语句)，而不会真正的在数据库中执行

1.exp导出数据

[oracle@node1 ~]$ exp "'/ as sysdba'" owner=scott file=/tmp/scott.dmp log=/tmp/s.logExport: Release 11.2.0.4.0 - Production on Wed Aug 30 19:05:55 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in US7ASCII character set and AL16UTF16 NCHAR character setserver uses AL32UTF8 character set (possible charset conversion)About to export specified users .... exporting pre-schema procedural objects and actions. exporting foreign function library names for user SCOTT . exporting PUBLIC type synonyms. exporting private type synonyms. exporting object type definitions for user SCOTT About to export SCOTT's objects .... exporting database links. exporting sequence numbers. exporting cluster definitions. about to export SCOTT's tables via Conventional Path .... . exporting table                          BONUS          0 rows exported. . exporting table                           DEPT          4 rows exported. . exporting table                            EMP         14 rows exported. . exporting table                       SALGRADE          5 rows exported. . exporting table                           TEST         39 rows exported. . exporting table                          TEST2         14 rows exported. 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 statisticsExport terminated successfully without warnings.

2.创建新用户

[oracle@node1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 18 16:39:17 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> create user scoot identified by xff; User created.SQL> grant dba to scoot;Grant succeeded.SQL> exit

3.imp+show=y导入

[oracle@node1 ~]$ imp "'/ as sysdba'" fromuser=scott touser=scoot \>file=/tmp/scott.dmp log=/tmp/scott.log show=yImport: Release 11.2.0.4.0 - Production on Wed Aug 30 19:11:49 2017Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport done in US7ASCII character set and AL16UTF16 NCHAR character setimport server uses AL32UTF8 character set (possible charset conversion). importing SCOTT's objects into SCOOT "BEGIN  " "sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','" "CURRENT_SCHEMA'), export_db_name=>'PROD', inst_scn=>'1102684');" "COMMIT; END;" "ALTER SESSION SET CURRENT_SCHEMA= "SCOOT"" "CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER" ", "COMM" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE " ""USERS" LOGGING NOCOMPRESS". . skipping table "BONUS"                             "ALTER SESSION SET CURRENT_SCHEMA= "SCOOT"" "CREATE TABLE "DEPT" ("DEPTNO" NUMBER(2, 0), "DNAME" VARCHAR2(14), "LOC" VAR" "CHAR2(13))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 6" "5536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DE" "FAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS". . skipping table "DEPT"                              "CREATE UNIQUE INDEX "PK_DEPT" ON "DEPT" ("DEPTNO" )  PCTFREE 10 INITRANS 2 " "MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FR" "EELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING" "ALTER SESSION SET CURRENT_SCHEMA= "SCOOT"" "ALTER TABLE "DEPT" ADD  CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING I" "NDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 " "MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE " ""USERS" 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". . skipping table "EMP"                               "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 SESSION SET CURRENT_SCHEMA= "SCOOT"" "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 " "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER)  P" "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 104" "8576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLES" "PACE "USERS" LOGGING NOCOMPRESS". . skipping table "SALGRADE"                          "ALTER SESSION SET CURRENT_SCHEMA= "SCOOT"" "CREATE TABLE "TEST" ("ID" NUMBER, "NAME" VARCHAR2(10))  PCTFREE 10 PCTUSED " "40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 " "FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGI" "NG NOCOMPRESS". . skipping table "TEST"                              "ALTER SESSION SET CURRENT_SCHEMA= "SCOOT"" "CREATE TABLE "TEST2" ("ID" NUMBER, "NAME" VARCHAR2(10))  PCTFREE 10 PCTUSED" " 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1" " FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGG" "ING NOCOMPRESS". . skipping table "TEST2"                             "ALTER SESSION SET CURRENT_SCHEMA= "SCOOT"" "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE" "ALTER TABLE "EMP" ENABLE CONSTRAINT "FK_DEPTNO""Import terminated successfully without warnings.

4.查询imp结果

[oracle@node1 ~]$ sqlplus scoot/xffSQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 18 16:45:10 2012Copyright (c) 1982, 2011, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP, Data Mining,Oracle Database Vault and Real Application Testing optionsSQL> select * from tab;no rows selected

通过实验知道，show=y表示展示imp导入的时候，会执行的相关语句(不包括insert语句)，而不会真正的在数据库中执行

原文唯一网址：http://www.oracleplus.net/arch/921.html