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
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, @0x1f9b
tl: 5 fb: –H-FL– lb: 0×1 cc: 1
col 0: [ 1] 58
tab 0, row 1, @0x1f96
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
SQL> INSERT INTO DUAL VALUES(‘A’,SYSDATE);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DROP TABLE TTT;
DROP TABLE TTT
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
SQL> SELECT COUNT(*) FROM DUAL;
COUNT(*)
———-
1
SQL> DROP TABLE TTT;
DROP TABLE TTT
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows
SQL> DELETE FROM DUAL WHERE DUMMY =’A’
2 /
1 row deleted.
SQL> SELECT COUNT(*) FROM DUAL;
COUNT(*)
———-
1
SQL> DROP TABLE TTT;
DROP TABLE TTT
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01422: exact fetch returns more than requested number of rows.
Still we are facing this problem. Can u help us?
Hi, thanks for your comment. Can you please state your Oracle Database version. It looks like that there is not only one record too much, but maybe more. Is it possible for you to create a block dump of the involved dual table. You can do that by, find out what blocks are part of ‘DUAL’, dump the blocks:
select * from dba_extents where segment_name = ‘DUAL’
eg:
alter system dump datafile 1 block 801;
alter system dump datafile 1 block 802;
alter system dump datafile 1 block 803;
alter system dump datafile 1 block 804;
alter system dump datafile 1 block 805;
alter system dump datafile 1 block 806;
alter system dump datafile 1 block 807;
alter system dump datafile 1 block 808;
Check how many rows are in the dual table.
Regards, Gerwin