TABLE_EXISTS_ACTION during IMPORT (IMPDP)
In data pump import the parameter TABLE_EXISTS_ACTION help to do the job. The default value of this parameter is SKIP which means if table to be imported already existed in the database table will be skipped and data not to be imported and continue processing next object. However if in your import job if CONTENT=DATA_ONLY is specified, the default is APPEND, and then data will be appended into existing table.
TABLE_EXISTS_ACTION can have following values.
1)SKIP: It leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
2)APPEND: This option loads rows from the source and leaves existing rows unchanged. This is a default option is CONTENT=DATA_ONLY is specified.
3)TRUNCATE: This option deletes existing rows and then loads rows from the source.
4)REPLACE: This option drops the existing table in the database and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
Note
- If you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.
- If you use SKIP, APPEND, or TRUNCATE then existing table dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. In case of REPLACE, the dependent objects are dropped and again created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.
- If you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action. If the existing table has active constraints and triggers, it is loaded using the external tables access method. If any row violates an active constraint, the load fails and no data is loaded. You can override this behavior by specifying DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS on the Import command line.
If you want data must be loaded but causes constraint voilations, you can disable constraints, import data, delete the rows which causes problems and then enable constraints.
- When you use APPEND, the data is always loaded into new space. So if you have any existing space available the space is not reused. So after the import operation, you may wish to compress your data after the load.
- TRUNCATE cannot be used on clustered tables or over network links.
In case of original import use ignore=y option to append data into an existing table. ignore=y causes rows to be imported into existing tables without any errors or messages being given.
In data pump import the parameter TABLE_EXISTS_ACTION help to do the job. The default value of this parameter is SKIP which means if table to be imported already existed in the database table will be skipped and data not to be imported and continue processing next object. However if in your import job if CONTENT=DATA_ONLY is specified, the default is APPEND, and then data will be appended into existing table.
TABLE_EXISTS_ACTION can have following values.
1)SKIP: It leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
2)APPEND: This option loads rows from the source and leaves existing rows unchanged. This is a default option is CONTENT=DATA_ONLY is specified.
3)TRUNCATE: This option deletes existing rows and then loads rows from the source.
4)REPLACE: This option drops the existing table in the database and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
Note
- If you use TRUNCATE or REPLACE, make sure that rows in the affected tables are not targets of any referential constraints.
- If you use SKIP, APPEND, or TRUNCATE then existing table dependent objects in the source, such as indexes, grants, triggers, and constraints, are ignored. In case of REPLACE, the dependent objects are dropped and again created from the source, if they were not explicitly or implicitly excluded (using EXCLUDE) and they exist in the source dump file or system.
- If you use APPEND or TRUNCATE, checks are made to ensure that rows from the source are compatible with the existing table prior to performing any action. If the existing table has active constraints and triggers, it is loaded using the external tables access method. If any row violates an active constraint, the load fails and no data is loaded. You can override this behavior by specifying DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS on the Import command line.
If you want data must be loaded but causes constraint voilations, you can disable constraints, import data, delete the rows which causes problems and then enable constraints.
- When you use APPEND, the data is always loaded into new space. So if you have any existing space available the space is not reused. So after the import operation, you may wish to compress your data after the load.
- TRUNCATE cannot be used on clustered tables or over network links.
In case of original import use ignore=y option to append data into an existing table. ignore=y causes rows to be imported into existing tables without any errors or messages being given.
No comments:
Post a Comment