imp和impdp使用ignore=y的差异?

2023-12-20 13:08:56

关于大神[20151125]IMPDP参数TABLE_EXISTS_ACTION_ITPUB博客

中提到到

这里补充说明下imp使用ignore=y相当于于impdp中的table_exists_action的skip和append.

大神之前说imp的时候漏了append的情况。

也就是说如果表不存在,则会创建出表同时导入数据。

如果表已存在,则会追加记录(有主键则会提示冲突插入失败)。

对于impdp,导入的过程会明确显示ignore=y和n时候转换的对应,默认是n,即对应skip

详细测试如下:

[oracle@lnkf dmp]$ exp jyc/jyc file=j1.dmp log=j1.log owner=jyc buffer=102400

Export: Release 11.2.0.4.0 - Production on Wed Dec 20 09:12:52 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user JYC 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user JYC 
About to export JYC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export JYC's tables via Conventional Path ...
. . exporting table                             J1          1 rows exported
. . exporting table                             J2          1 rows exported
. . exporting table                              T          1 rows exported
. . exporting table                             T1          1 rows exported
. . exporting table                             T2          1 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 statistics
Export terminated successfully without warnings.
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:13:24 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00031: Must specify FULL=Y or provide FROMUSER/TOUSER or TABLES arguments
IMP-00000: Import terminated unsuccessfully
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:13:33 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "J1" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "J2" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR"
 "ANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIS"
 "T GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T1" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T2" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE FORCE VIEW "JYC"."V_T"                             ("ID") AS "
 "select "ID" from t"
Import terminated successfully with warnings.
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc ignore=y

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:13:55 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                           "J1"          1 rows imported
. . importing table                           "J2"          1 rows imported
. . importing table                            "T"          1 rows imported
. . importing table                           "T1"          1 rows imported
. . importing table                           "T2"          1 rows imported
Import terminated successfully without warnings.
[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:14:15 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> select count(*) from j1;

  COUNT(*)
----------
         2

SQL> select count(*) from j2;

  COUNT(*)
----------
         2

SQL> select count(*) from t;

  COUNT(*)
----------
         2

SQL> select count(*) from t1;

  COUNT(*)
----------
         2

SQL> select count(*) from t2;

  COUNT(*)
----------
         2

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc ignore=n

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:14:58 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "J1" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "J2" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR"
 "ANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIS"
 "T GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T1" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T2" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE FORCE VIEW "JYC"."V_T"                             ("ID") AS "
 "select "ID" from t"
Import terminated successfully with warnings.
[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:15:04 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> truncate table j1;

Table truncated.

SQL> truncate table j2;

Table truncated.

SQL> truncate table t;

Table truncated.

SQL> truncate table t1;

Table truncated.

SQL> truncate table t2;

Table truncated.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc ignore=n

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:15:36 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "J1" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "J2" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR"
 "ANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELIS"
 "T GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T1" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T2" ("ID" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXT"
 "RANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT)                    LOGGING NOCOMPRESS"
IMP-00015: following statement failed because the object already exists:
 "CREATE FORCE VIEW "JYC"."V_T"                             ("ID") AS "
 "select "ID" from t"
Import terminated successfully with warnings.
[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:15:38 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> select count(*) from j1;

  COUNT(*)
----------
         0

SQL> select count(*) from t;

  COUNT(*)
----------
         0

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc ignore=y

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:15:50 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                           "J1"          1 rows imported
. . importing table                           "J2"          1 rows imported
. . importing table                            "T"          1 rows imported
. . importing table                           "T1"          1 rows imported
. . importing table                           "T2"          1 rows imported
Import terminated successfully without warnings.
[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:15:55 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> select count(*) from j1;

  COUNT(*)
----------
         1

SQL> select count(*) from j2;

  COUNT(*)
----------
         1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc ignore=y INDEXFILE=j1.sql

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:20:03 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping table "J1"                               

. . skipping table "J2"                               

. . skipping table "T"                                

. . skipping table "T1"                               

. . skipping table "T2"                               

Import terminated successfully without warnings.
[oracle@lnkf dmp]$ more j1.sql

REM  CREATE TABLE "JYC"."J1" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."J2" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."T" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."T1" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."T2" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
[oracle@lnkf dmp]$ imp jyc/jyc file=j1.dmp log=j1.log buffer=102400 fromuser=jyc touser=jyc ignore=n INDEXFILE=j2.sql

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:20:29 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . skipping table "J1"                               

. . skipping table "J2"                               

. . skipping table "T"                                

. . skipping table "T1"                               

. . skipping table "T2"                               

Import terminated successfully without warnings.
[oracle@lnkf dmp]$ more j2.sql

REM  CREATE TABLE "JYC"."J1" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."J2" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."T" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."T1" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
REM  CREATE TABLE "JYC"."T2" ("ID" NUMBER(*,0)) PCTFREE 10 PCTUSED 40 
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 
REM  1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE 
REM  "USERS" LOGGING NOCOMPRESS ;
REM  ... 1 rows
[oracle@lnkf dmp]$ expdp jyc/jyc directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc

Export: Release 11.2.0.4.0 - Production on Wed Dec 20 09:23:38 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "JYC"."SYS_EXPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "JYC"."J1"                                  5.015 KB       1 rows
. . exported "JYC"."J2"                                  5.015 KB       1 rows
. . exported "JYC"."T"                                   5.015 KB       1 rows
. . exported "JYC"."T1"                                  5.015 KB       1 rows
. . exported "JYC"."T2"                                  5.015 KB       1 rows
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
  /oracle/dmp/jyc1.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Dec 20 09:23:53 2023 elapsed 0 00:00:14

[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:24:51 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> !vi trucate.sql
truncate table j1;
truncate table j2;
truncate table t;
truncate table t1;
truncate table t2;
select count(*) from t1;
~
~
"trucate.sql" [New] 6L, 119C written

SQL> @truncate.sql
SP2-0310: unable to open file "truncate.sql"
SQL> !pwd
/oracle/dmp

SQL> !ls *.sql
a1.sql  a2.sql  a3.sql  a.sql  j1.sql  j2.sql  jyc.sql  jyc-v1.sql  jyc-v2.sql  jyc-v4.sql  jyc-v.sql  trucate.sql

SQL> @/oracle/dmp/trucate.sql

Table truncated.


Table truncated.


Table truncated.


Table truncated.


Table truncated.


  COUNT(*)
----------
         0

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ impdp jyc/jyc directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:26:45 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "JYC"."J1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."J2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"JYC"."V_T" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 7 error(s) at Wed Dec 20 09:26:49 2023 elapsed 0 00:00:03

[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:27:00 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> select count(*) from j1;

  COUNT(*)
----------
         0

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ impdp jyc/jyc directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc ignore=y

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:27:22 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc table_exists_action=append 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "JYC"."J1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "JYC"."J2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "JYC"."T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "JYC"."T1" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Table "JYC"."T2" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JYC"."J1"                                  5.015 KB       1 rows
. . imported "JYC"."J2"                                  5.015 KB       1 rows
. . imported "JYC"."T"                                   5.015 KB       1 rows
. . imported "JYC"."T1"                                  5.015 KB       1 rows
. . imported "JYC"."T2"                                  5.015 KB       1 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"JYC"."V_T" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Wed Dec 20 09:27:25 2023 elapsed 0 00:00:03

[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:27:33 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> select count(*) from j1;

  COUNT(*)
----------
         1

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ vi drop.sql
drop table j1;
drop table j2;
drop table t;
drop table t1;
drop table t2;
~

~
"drop.sql" [New] 5L, 74C written                                                                                                                
[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:28:46 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> @drop.sql

Table dropped.


Table dropped.


Table dropped.


Table dropped.


Table dropped.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ impdp jyc/jyc directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc ignore=y

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:28:55 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=TRUE" Location: Command Line, Replaced with: "table_exists_action=append"
Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc table_exists_action=append 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JYC"."J1"                                  5.015 KB       1 rows
. . imported "JYC"."J2"                                  5.015 KB       1 rows
. . imported "JYC"."T"                                   5.015 KB       1 rows
. . imported "JYC"."T1"                                  5.015 KB       1 rows
. . imported "JYC"."T2"                                  5.015 KB       1 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"JYC"."V_T" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 2 error(s) at Wed Dec 20 09:28:58 2023 elapsed 0 00:00:01

[oracle@lnkf dmp]$ sqlplus jyc/jyc

SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 20 09:29:03 2023

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> select count(*) from j1;

  COUNT(*)
----------
         1

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
J1
J2
T
T1
T2

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnkf dmp]$ impdp jyc/jyc directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc ignore=n

Import: Release 11.2.0.4.0 - Production on Wed Dec 20 09:35:15 2023

Copyright (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 Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "ignore=FALSE" Location: Command Line, ignored.
Master table "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01":  jyc/******** directory=oradmp dumpfile=jyc1.dmp logfile=t.log schemas=jyc 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39151: Table "JYC"."J1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."J2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
ORA-39151: Table "JYC"."T2" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"JYC"."V_T" already exists
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 7 error(s) at Wed Dec 20 09:35:17 2023 elapsed 0 00:00:01

[oracle@lnkf dmp]$ 

文章来源:https://blog.csdn.net/jycjyc/article/details/135100644
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。