Drop table issue [message #430709] |
Wed, 11 November 2009 22:23  |
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   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
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   |
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 #430727 is a reply to message #430713] |
Thu, 12 November 2009 01:14   |
Frank
Messages: 7901 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  |
Frank
Messages: 7901 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?
|
|
|