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 sysdba

SQL*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 options

SQL> set pagesize 1000
SQL> select * from dual
2  /

D
-
X

SQL> create table xxx (id number);

Table created.

SQL> select count(*) from dual;

COUNT(*)
———-
1

SQL> insert into dual values (‘A’);

1 row created.

SQL> select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
———-
1

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

SQL> desc dual
Name                                      Null?    Type
—————————————– ——– —————————-
DUMMY                                              VARCHAR2(1)

SQL> delete from dual where dummy = ‘A’;

1 row deleted.

SQL>  select * from dual;

D
-
X

SQL> select count(*) from dual;

COUNT(*)
———-
1

SQL> 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

6 thoughts on “ORA-01422 while drop table (even DUAL looks fine)

  1. 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.

  2. 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

  3. 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.

  4. 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

  5. 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?

  6. 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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>