Oracle IMPDP's Table_exists_action detailed

Oracle IMPDP's table_exists_action detailedCategory: [oracle]--[Backup and Recovery]2012-01-06 22:44 9105 People read Comments (0) favorite reports Tableactionoracleschemaobjectimport 1 table_exists_action parameter Description

When importing data using IMP, if the table already exists, drop the table before importing.

When using IMPDP to complete the database import, if the table already exists, there are four kinds of processing methods:

1) Skip: Default action

2) Replace: Drop table First, then create TABLE, and finally insert data

3) Append: Add data based on the original data

4) Truncate: Truncate first, then insert data

2 Preparation of the experiment

2.1 SYS users create directory objects and authorize

Sql> Create directory Dir_dump as '/home/oracle ';

Directory created

Sql> Grant Read, write on directory dir_dump to Tuser;

Grant succeeded

2.2 Exporting data

[Email protected] ~]$ EXPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser;

Export:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 20:44:22

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Starting "TUSER". " Sys_export_schema_01 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp Schemas=tuser

Estimate in progress using BLOCKS method ...

Processing Object Type Schema_export/table/table_data

Total estimation using BLOCKS method:128 KB

Processing Object Type Schema_export/pre_schema/procact_schema

Processing Object Type Schema_export/table/table

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/table/comment

. . Exported "TUSER". " TAB1 "5.25 KB 5 rows

. . Exported "TUSER". " TAB2 "5.296 KB rows

Master table "TUSER". " Sys_export_schema_01 "Successfully loaded/unloaded

******************************************************************************

Dump file set for TUSER. SYS_EXPORT_SCHEMA_01 is:

/home/oracle/expdp.dmp

Job "TUSER". " Sys_export_schema_01 "successfully completed at 20:47:29

2.3 Viewing data for two tables

Sql> select * from Tab1;

A B

--- ----

1 11

2 22

3 33

4 44

5 55

Sql> select * from TAB2;

A B

--- ----

1 AA

2 BB

3 cc

4 DD

5 EE

6 FF

7 GG

8 hh

9 II

Ten JJ

Rows selected

3 Replace

3.1 Inserting data

sql> INSERT into Tab1 (A, B) values (6, 66);

1 row inserted

Sql> commit;

Commit Complete

Sql> select * from Tab1;

A B

--- ----

1 11

2 22

3 33

4 44

5 55

6 66

6 Rows selected

3.2 Importing Data

[Email protected] ~]$ IMPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Replace

Import:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 20:53:09

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Master table "TUSER". " Sys_import_schema_01 "Successfully loaded/unloaded

Starting "TUSER". " Sys_import_schema_01 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Replace

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 "TUSER". " TAB1 "5.25 KB 5 rows

. . Imported "TUSER". " TAB2 "5.296 KB rows

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

Job "TUSER". " Sys_import_schema_01 "successfully completed at 20:53:25

3.3 View Data again

Sql> select * from Tab1;

A B

--- ----

1 11

2 22

3 33

4 44

5 55

When you view the data, there is no sixth data.

In addition to the newly created table, which is not in the backup, this table is not overwritten.

4 Skip

drop table TAB1,TAB2.

[Email protected] ~]$ IMPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser;

Import:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 21:34:20

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Master table "TUSER". " Sys_import_schema_01 "Successfully loaded/unloaded

Starting "TUSER". " Sys_import_schema_01 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp Schemas=tuser

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 "TUSER". " TAB1 "5.25 KB 5 rows

. . Imported "TUSER". " TAB2 "5.296 KB rows

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

Job "TUSER". " Sys_import_schema_01 "successfully completed at 21:34:25

Note: Even if the table structure has changed, the table name does not change. When the table is imported, it will be skipped.

5 Append

5.1 Deleting partial data

sql> Delete Tab1 where a = 1;

1 row deleted

sql> Delete tab2 where a = 2;

1 row deleted

Sql> commit;

Commit Complete

Sql> select * from Tab1;

A B

--- ----

2 22

3 33

4 44

5 55

Sql> select * from TAB2;

A B

--- ----

1 AA

3 cc

4 DD

5 EE

6 FF

7 GG

8 hh

9 II

Ten JJ

9 Rows selected

5.2 Importing Data

[Email protected] ~]$ IMPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Append

Import:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 21:50:40

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Master table "TUSER". " Sys_import_schema_01 "Successfully loaded/unloaded

Starting "TUSER". " Sys_import_schema_01 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Append

Processing Object Type Schema_export/pre_schema/procact_schema

Processing Object Type Schema_export/table/table

Ora-39152:table "TUSER". " TAB1 "exists. Data would be appended to existing table and all dependent metadata would be skipped due to table_exists_action of append

Ora-39152:table "TUSER". " TAB2 "exists. Data would be appended to existing table and all dependent metadata would be skipped due to table_exists_action of append

Processing Object Type Schema_export/table/table_data

Ora-31693:table data Object "TUSER". " TAB1 "failed to Load/unload and was being skipped due to error:

Ora-00001:unique constraint (TUSER. PK_TAB1) violated

Ora-31693:table data Object "TUSER". " TAB2 "failed to Load/unload and was being skipped due to error:

Ora-00001:unique constraint (TUSER. PK_TAB2) violated

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

Job "TUSER". " Sys_import_schema_01 "Completed with 4 error (s) at 21:50:45

Note: As long as the append data error, such as a unique key error, then what data can not be inserted.

5.3 Modifying the table structure

Sql> ALTER TABLE TAB1 Add (C varchar2 (4));

Table Altered

5.4 Re-importing data

[Email protected] ~]$ IMPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Append

Import:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 21:59:19

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Master table "TUSER". " Sys_import_schema_01 "Successfully loaded/unloaded

Starting "TUSER". " Sys_import_schema_01 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Append

Processing Object Type Schema_export/pre_schema/procact_schema

Processing Object Type Schema_export/table/table

Ora-39152:table "TUSER". " TAB1 "exists. Data would be appended to existing table and all dependent metadata would be skipped due to table_exists_action of append

Ora-39152:table "TUSER". " TAB2 "exists. Data would be appended to existing table and all dependent metadata would be skipped due to table_exists_action of append

Processing Object Type Schema_export/table/table_data

Ora-39014:one or more workers has prematurely exited.

Ora-39029:worker 1 with process name "DW01" prematurely terminated

Ora-31671:worker process DW01 had an unhandled exception.

Ora-00600:internal error code, arguments: [qerxtagentopen_911], [3], [2], [], [], [], [], []

Ora-06512:at "SYS. Kupw$worker ", line 1345

Ora-06512:at Line 2

Job "TUSER". " Sys_import_schema_01 "stopped due to fatal error at 21:59:57

Then there was a 600 error.

6 truncate

6.1 Inserting partial data

sql> INSERT into Tab1 (A, B) values (6, 66);

1 row inserted

Sql> commit;

Commit Complete

6.2 Importing Data

[Email protected] ~]$ IMPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Truncate

Import:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 22:18:21

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Master table "TUSER". " Sys_import_schema_03 "Successfully loaded/unloaded

Starting "TUSER". " Sys_import_schema_03 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Truncate

Processing Object Type Schema_export/pre_schema/procact_schema

Processing Object Type Schema_export/table/table

Ora-39153:table "TUSER". " TAB1 "exists and has been truncated. Data would be loaded and all dependent metadata'll be skipped due to table_exists_action of truncate

Ora-39153:table "TUSER". " TAB2 "exists and has been truncated. Data would be loaded and all dependent metadata'll be skipped due to table_exists_action of truncate

Processing Object Type Schema_export/table/table_data

. . Imported "TUSER". " TAB1 "5.25 KB 5 rows

. . Imported "TUSER". " TAB2 "5.296 KB rows

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

Job "TUSER". " Sys_import_schema_03 "Completed with 2 error (s) at 22:18:28

Note: The newly inserted data will be lost.

6.3 Change Table structure

Sql> ALTER TABLE TAB1 Add (C varchar2 (4));

Table Altered

6.4 Re-importing data

[Email protected] ~]$ IMPDP tuser/tuser directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Truncate

Import:release 10.2.0.1.0-64bit Production on Friday, 06 January, 2012 22:22:02

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to:oracle Database 10g Enterprise Edition Release 10.2.0.1.0-64bit Production

With the partitioning, OLAP and Data Mining options

Master table "TUSER". " Sys_import_schema_03 "Successfully loaded/unloaded

Starting "TUSER". " Sys_import_schema_03 ": tuser/******** directory=dir_dump dumpfile=expdp.dmp schemas=tuser table_exists_action= Truncate

Processing Object Type Schema_export/pre_schema/procact_schema

Processing Object Type Schema_export/table/table

Ora-39153:table "TUSER". " TAB1 "exists and has been truncated. Data would be loaded and all dependent metadata'll be skipped due to table_exists_action of truncate

Ora-39153:table "TUSER". " TAB2 "exists and has been truncated. Data would be loaded and all dependent metadata'll be skipped due to table_exists_action of truncate

Processing Object Type Schema_export/table/table_data

Ora-39014:one or more workers has prematurely exited.

Ora-39029:worker 1 with process name "DW01" prematurely terminated

Ora-31671:worker process DW01 had an unhandled exception.

Ora-00600:internal error code, arguments: [qerxtagentopen_911], [3], [2], [], [], [], [], []

Ora-06512:at "SYS. Kupw$worker ", line 1345

Ora-06512:at Line 2

Job "TUSER". " Sys_import_schema_03 "stopped due to fatal error at 22:22:42

At this point, the same 600 error occurred. It appears that the table structure before and after import and export cannot be changed.