| Drop table issue [message #446061] |
Fri, 05 March 2010 06:17  |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi Experts,
I am facing issue while dropping the table. Need your advice on the below issue when i try to drop the table.
drop table PS_ORD_ADDR;
drop table PS_ORD_ADDR
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-04052: error occurred when looking up remote object LINKFDM.S_ADDR_ORG@SBLPROD
ORA-00604: error occurred at recursive SQL level 2
ORA-12154: TNS:could not resolve the connect identifier specified
I am able to drop other tables of the schema. But this specific table is giving error.
There is no lock or other session that is using this table.
Thanks in advance for your guidance.
Regards
Lalit
|
|
|
|
|
|
| Re: Drop table issue [message #446111 is a reply to message #446061] |
Fri, 05 March 2010 08:50  |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Thanks Michel for the prompt reply.
You are right. There are triggers on table.
I am able to drop the table after dropping the trigger.
ep75@FS90CNV>@trigger_name
Enter value for tablename: PS_ORD_ADDR
old 1: select trigger_name, status from user_triggers where table_name='&TABLENAME'
new 1: select trigger_name, status from user_triggers where table_name='PS_ORD_ADDR'
PS_ORD_ADDR_SIEBEL DISABLED
BIN$gQ2/2JKNHlngRAAUT3p4NA==$5 DISABLED
BIN$gQ2/2JKOHlngRAAUT3p4NA==$5 DISABLED
BIN$gQ2/2JKPHlngRAAUT3p4NA==$5 DISABLED
Elapsed: 00:00:03.54
ep75@FS90CNV>drop trigger PS_ORD_ADDR_SIEBEL;
Trigger dropped.
Elapsed: 00:00:03.25
ep75@FS90CNV>drop trigger BIN$gQ2/2JKNHlngRAAUT3p4NA==$5;
drop trigger BIN$gQ2/2JKNHlngRAAUT3p4NA==$5
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
Elapsed: 00:00:03.39
ep75@FS90CNV>drop table PS_ORD_ADDR;
Table dropped.
But when i try to import the tables using impdp, i got the below errors. The same error again during import. 
Starting "EP75"."SYS_IMPORT_TABLE_03": ep75/********@fs90cnv directory=pump_dir_cn1 dumpfile=cn1_exp.dmp logfile=cn1_imp3.log table_exists_action=TRUNCATE TABLES=PS_IN_DEMAND,PS_ORD_SCHEDULE, PS_SHIP_INF_INV, PS_ORD_ADDR
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "EP75"."PS_SHIP_INF_INV" 2.494 GB 3382920 rows
. . imported "EP75"."PS_IN_DEMAND" 2.460 GB 3457406 rows
. . imported "EP75"."PS_ORD_SCHEDULE" 963.6 MB 1422976 rows
. . imported "EP75"."PS_ORD_ADDR" 166.3 MB 901683 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"EP75"."QUEST_SX_IDX92C629AB41769ACBC7" already exists
ORA-31684: Object type INDEX:"EP75"."QUEST_SX_IDX92C629AB41764888D7" already exists
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"EP75"."QUEST_SX_IDX92C629AB41769ACBC7" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"EP75"."QUEST_SX_IDX92C629AB41764888D7" already exists
Processing object type TABLE_EXPORT/TABLE/TRIGGER
ORA-39082: Object type TRIGGER:"EP75"."ACCORD_ORD_ADDR_UPD_TRG" created with compilation warnings
ORA-39082: Object type TRIGGER:"EP75"."ACCORD_ORD_ADDR_INS_TRG" created with compilation warnings
ORA-39083: Object type TRIGGER failed to create with error:
[b]ORA-04052: error occurred when looking up remote object LINKFDM.S_ADDR_ORG@SBLPROD[/b]
ORA-00604: error occurred at recursive SQL level 3
ORA-12154: TNS:could not resolve the connect identifier specified
Failing sql is:
ALTER TRIGGER "EP75"."PS_ORD_ADDR_SIEBEL" COMPILE PLSQL_OPTIMIZE_LEVEL= 2 PLSQL_CODE_TYPE= INTERPRETED
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "EP75"."SYS_IMPORT_TABLE_03" completed with 7 error(s) at 06:38:04
Now during import, why i am getting this error. Is it because of the triggers in recyclebin.
Let me try to purge the recyclebin and start import again.
Regards
Lalit
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
