Home » RDBMS Server » Server Utilities » Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> (Oracle 11gR2 , AIX)
Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601198] Mon, 18 November 2013 08:14 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,

During import process we experience the below error. Here error on the table caused its dependent object to fail.

ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <Table_name>
ORA-39112: Dependent object type OBJECT_GRANT:"<user_name>" skipped, 
base object type TABLE:"<table_name>" creation failed


The parameter provided during import are TABLE_EXISTS_ACTION=REPLACE, CONTENT=ALL , EXCLUDE=STATISTICS and EXCLUDE=MATERIALIZED_VIEW.

Actually the table is TABLE PARTITION. please let me know the reason for this error and how to overcome it.

[Updated on: Tue, 19 November 2013 08:52] by Moderator

Report message to a moderator

Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601200 is a reply to message #601198] Mon, 18 November 2013 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
39112,00000, "Dependent object type %s skipped, base object type %s creation failed"
// *Cause:  During a Data Pump import job, a dependent object is being skipped
//          because its base object creation failed.
// *Action: If the object from the dump file is wanted, drop the base and 
//          dependent objects and try to import again using desired filters
Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601201 is a reply to message #601200] Mon, 18 November 2013 08:56 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member

ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <Table_name>


I can see due to the above error the dependent object has been skipped.
So any idea regarding this error.
Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601202 is a reply to message #601201] Mon, 18 November 2013 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>So any idea regarding this error.
We don't have your tables.
We don't have your data.
We don't have your code.
I don't what what you did.
You could have an error of commission or could have an error of omission, but don't know any details to conclude one way or another.
Either you have done something that results in this error or might have hit an Oracle bug.
We have no way to decide which is the case.
Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601203 is a reply to message #601202] Mon, 18 November 2013 09:49 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi, please excuse on the table level details and nothing releated code , the table is on RANGE PARTITION.

And I performed the import based on the parameter TABLE_EXISTS_ACTION=REPLACE, CONTENT=ALL , EXCLUDE=STATISTICS and EXCLUDE=MATERIALIZED_VIEW
Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601205 is a reply to message #601203] Mon, 18 November 2013 10:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Either you have done something that results in this error or might have hit an Oracle bug.
We have no way to decide which is the case.
Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601206 is a reply to message #601205] Mon, 18 November 2013 10:54 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
39325, 00000, "TABLE_EXISTS_ACTION cannot be applied to %s."
// *Cause:   An object was already in existence that uses a name in common with
//           this table. The name collision could be the table name, a
//           constraint, a LOB storage table, or something else in the table
//           definition.
// *Action:  Drop the existing object before running Data Pump.


Either another object is causing a name clash that's stopping the current table being dropped and re-created, or you've got an oracle bug.
Re: Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601312 is a reply to message #601206] Tue, 19 November 2013 08:48 Go to previous message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi All,

I have experiment on this and verified that we have MATERIALIZED VIEW with the same table name based on the "ON PREBUILT TABLE WITHOUT REDUCED PRECISION" clause in MATERIALIZED VIEW. So here we are dropping the MATERIALIZED VIEW later may be after dropping the table ( but the table can't be dropped without dropping the MV ) so table drop errored out and still the table presists in DB. So by refreshing (Import) the same MV is trying to recreate in the DB and it could have thrown the error based on the parameter TABLE_EXISTS_ACTION=REPLACE and CONTENT=ALL.

Below are action performed.

1. Created the table and MATERIALIZED VIEW as same name SAMPLE_MV.
2. checked the objects in the DB
SQL> select object_name,object_type,status from user_objects where object_name = 'SAMPLE_MV';

OBJECT_NAME                                                                                                                      OBJECT_TYPE         STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
SAMPLE_MV                                                                                                                        TABLE               VALID
SAMPLE_MV                                                                                                                        MATERIALIZED VIEW   VALID


3. Dropped the table first and shows below error
SQL> drop table SAMPLE_MV;
drop table SAMPLE_MV
           *
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "ODW_DW"."SAMPLE_MV"


4.Then used the Drop MV and dropped, but the table still persists in the DB
SQL> DROP MATERIALIZED VIEW SAMPLE_MV;

Materialized view dropped.

SQL> select object_name,object_type,status from user_objects where object_name = 'SAMPLE_MV';

OBJECT_NAME                                                                                                                      OBJECT_TYPE         STATUS
-------------------------------------------------------------------------------------------------------------------------------- ------------------- -------
SAMPLE_MV                                                                                                                        TABLE               VALID


5. So here the MV has been dropped and tried to import it again and it throws the below error saying "Object type MATERIALIZED_VIEW failed to create"

$> impdp xxx/***** DIRECTORY=dump_dir DUMPFILE=samp.dmp logfile=samp1.log table_exists_action=replace

Import: Release 11.2.0.3.0 - Production on Tue Nov 19 11:49:39 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "XXXX"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "XXXX"."SYS_IMPORT_FULL_01":  XXXX/******** DIRECTORY=dump_dir DUMPFILE=samp.dmp logfile=samp1.log table_exists_action=replace
Processing object type SCHEMA_EXPORT/MATERIALIZED_VIEW
ORA-39083: Object type MATERIALIZED_VIEW failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE MATERIALIZED VIEW "xxxx"."SAMPLE_MV" (col1,col2...)
Job "xxxx"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 11:49:41


So probably this may caused the issue since we are dropping the MV but object with same name persists as with Table name.

So let me know whether the findings above are appropriate for the error

ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <Table_name>
ORA-39112: Dependent object type OBJECT_GRANT:"<user_name>" skipped, 
base object type TABLE:"<table_name>" creation failed


Thanks in Advance

[Updated on: Tue, 19 November 2013 08:52] by Moderator

Report message to a moderator

Previous Topic: Oracle 11.2.0.1 imp.exe Appcrash
Next Topic: data from M$ SQL into Oracle DB
Goto Forum:
  


Current Time: Thu Mar 28 15:24:43 CDT 2024