Tag: Import data bump
ORA-39152: Table exists
Cause :
Using APPEND to import the existing tables, as to not overrite them gives the following error:
Solution :
Truncating the table preserves the structure of the table for future use, so you are seeing this error message because there is a constraint or index in place.
To get around this you can use the following DataPump import parameters:
CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=APPEND EXCLUDE=INDEX,CONSTRAINT
Thank you
Osama mustafa
Use TABLE_EXISTS_ACTION in Impdp
TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}
This parameter is used when you import a table which is already exists in import schema. The default value is ‘SKIP‘, so if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.
Now you may interested about rest of the three values-
APPEND – The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).
TRUNCATE – If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.
REPLACE – This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.
Export Data Bump , Import Data Bump In Oracle
Steps :
In Sql Plus (Conn /as sysdba)
CREATE OR REPLACE DIRECTORY "Dir-name" AS "Dir-path";
GRANT READ, WRITE ON DIRECTORY "Dir-path" TO "username";
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
TABLES
parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR
dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR
dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
FULL
parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.expdp system/password@db10g full=Y directory=TEST_DIR
dumpfile=DB10G.dmp logfile=expdpDB10G.log
impdp system/password@db10g full=Y directory=TEST_DIR
dumpfile=DB10G.dmp logfile=impdpDB10G.log
You can Use For more information :
"expdp help=y"
Or
"impdb help=y"