-
Gerwin Hendriksen
-
Company profile
ORA-01422 while drop table (even DUAL looks fine)
Today on the OTN forum a question was comming up about an ORA-01422, in combination of a table drop.
SQL> drop table xxx
2 /
drop table xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
Due to the fact that this looked a bit weird, I did a small test on my 11.1.0.7 database. The following test case I did:
login as: oracle
oracle@ghperfsuite’s password:
Last login: Wed Apr 1 20:37:27 2009 from 10.10.1.1
[oracle@ghperfsuite ~]$ . oraenv
ORACLE_SID = [oracle] ? ghperf
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle
[oracle@ghperfsuite ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.1.0.7.0 - Production on Thu Apr 2 05:27:57 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing optionsSQL> set pagesize 1000
SQL> select * from dual
2 /D
-
XSQL> create table xxx (id number);
Table created.
SQL> select count(*) from dual;
COUNT(*)
———-
1SQL> insert into dual values (’A');
1 row created.
SQL> select * from dual;
D
-
XSQL> select count(*) from dual;
COUNT(*)
———-
1SQL> drop table xxx
2 /
drop table xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rowsSQL> desc dual
Name Null? Type
—————————————– ——– —————————-
DUMMY VARCHAR2(1)SQL> delete from dual where dummy = ‘A’;
1 row deleted.
SQL> select * from dual;
D
-
XSQL> select count(*) from dual;
COUNT(*)
———-
1SQL> drop table xxx
2 /Table dropped.
SQL>
The test case shows that even that you can’t see the extra row in the dual table it still can give lots of trouble. One of the important issues is that you can’t drop a table anymore.
See also the entry at the OTN forum.
Regards, Gerwin
Comments
4 Responses to “ORA-01422 while drop table (even DUAL looks fine)”
Got something to say?
thank you for good example…
It’s a bug, isn’t it?
I tested on 11gR2 ,,, no error…
11gR1:
SQL> insert into dual values (’A');
1 row created.
SQL> drop table xxx;
drop table xxx
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
11gR2:
SQL> insert into dual values (’A');
1 row created.
SQL> select * from dual;
D
-
X
SQL> drop table xxx;
Table dropped.
Surachart, thanks for your response. I have done some block dumps on the dual table itself and I saw still some evidence ot the records in the first data block of the table:
block_row_dump:
tab 0, row 0, @0×1f9b
tl: 5 fb: –H-FL– lb: 0×1 cc: 1
col 0: [ 1] 58
tab 0, row 1, @0×1f96
tl: 5 fb: –H-FL– lb: 0×2 cc: 1
col 0: [ 1] 41
end_of_block_dump
When checking the values:
select chr(88) from dual; –> X — hex 58 (first row)
select chr(65) from dual; –> A — hex 41 (second row)
I think, it might be very interesting what is in the blocks of the 11GR2 database. I would like to know if the insert is not done or that it does something else.
Regards, Gerwin
Hi Gerwin,
If you check the table itself by create test_dual as select * from dual you will see added rows in both versions of Oracle (11GR1 11GR2) but when you do 10046 trace, you will see that steps before dropping table on 11GR2 are different than versions before 11GR2. Looks like Oracle changed the logic.
Hi Coskan,
Thank you for your comment. I didn’t do the 10046 trace myself, but probably I try to do it soon and post the results here.
It surprised me that there is such a difference between versions. Anyway any comments are welcome.
Regards, Gerwin