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
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！