| Error during Impdp "ORA-39325: TABLE_EXISTS_ACTION cannot be applied to <table_name> [message #601198] |
Mon, 18 November 2013 08:14  |
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 #601206 is a reply to message #601205] |
Mon, 18 November 2013 10:54  |
cookiemonster
Messages: 13963
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 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
