Home » RDBMS Server » Server Administration » Drop table issue (10.2.0.4 , Solaris 10)
Drop table issue [message #446061] Fri, 05 March 2010 06:17 Go to next message
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 #446085 is a reply to message #446061] Fri, 05 March 2010 07:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have a trigger on the drop of this table.
Check all constraints in DBA_TRIGGERS.

Regards
Michel
Re: Drop table issue [message #446111 is a reply to message #446061] Fri, 05 March 2010 08:50 Go to previous message
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. Sad
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
Previous Topic: RC-50004 in adcfgclone.pl in db tier
Next Topic: INVALID Objects
Goto Forum:
  


Current Time: Wed Dec 07 23:55:17 CST 2016

Total time taken to generate the page: 0.09961 seconds