Home » SQL & PL/SQL » SQL & PL/SQL » Drop table issue (oracle 10g)
Drop table issue [message #430709] Wed, 11 November 2009 22:23 Go to next message
brihaspatirai
Messages: 24
Registered: November 2006
Location: pune
Junior Member
Please help me out

I am getting below error while dropping a table

SQL> drop table aasa;

drop table aasa

ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows

SQL>

Please advice how it is coming.
Re: Drop table issue [message #430711 is a reply to message #430709] Wed, 11 November 2009 22:40 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of SELECT * from v$version.

Is AASA a global temp table???
Is AASA a Indexed Organized table?
Is AASA a partitioned table?
post DDL which created AASA table.

issue SQL below & post results back here
SELECT * FROM DUAL;

select * from USER_TRIGGERS where table_name = 'AASA';

SELECT COUNT(*) FROM AASA;

TRUNCATE TABLE AASA;
DROP TABLE AASA;

DROP TABLE AASA CASCADE CONSTRAINTS;

DROP TABLE AASA CASCADE CONSTRAINTS PURGE;

[Updated on: Wed, 11 November 2009 22:46]

Report message to a moderator

Re: Drop table issue [message #430712 is a reply to message #430711] Wed, 11 November 2009 22:51 Go to previous messageGo to next message
brihaspatirai
Messages: 24
Registered: November 2006
Location: pune
Junior Member
Thanks BlackSwan for your prompt reply

I have tried a lot and found that some appended dual table with more then one row that is why this error occured during drop table.

I have deleted other rows from dual table and then tried to drop table and table is dropped now.

Thanks once again
Re: Drop table issue [message #430713 is a reply to message #430712] Wed, 11 November 2009 22:54 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>I have tried a lot and found that some appended dual table with more then one row that is why this error occured during drop table.
which is exactly why I asked for
SELECT * FROM DUAL;

Re: Drop table issue [message #430727 is a reply to message #430713] Thu, 12 November 2009 01:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
BlackSwan wrote on Thu, 12 November 2009 05:54
>I have tried a lot and found that some appended dual table with more then one row that is why this error occured during drop table.
which is exactly why I asked for
SELECT * FROM DUAL;


If I recall correctly, sqlplus was "optimized" for some versions to only return 1 row from dual. It "knew" there was only 1 row in it.
I am not sure if a select * from dual would have shown there was something wrong with dual.
Re: Drop table issue [message #430729 is a reply to message #430712] Thu, 12 November 2009 01:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
brihaspatirai wrote on Thu, 12 November 2009 05:51

I have tried a lot and found that some appended dual table with more then one row that is why this error occured during drop table.

Who in his right mind would do that?
Why did that person have the privilege to do that in the first place? Why did (s)he have insert privs on dual?
Previous Topic: ORA-01779: cannot modify a column which maps to a non key-preserved table (not understand)
Next Topic: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
Goto Forum:
  


Current Time: Sat Oct 01 09:14:02 CDT 2016

Total time taken to generate the page: 0.07964 seconds