1 2 Previous Next 20 Replies Latest reply: Mar 12, 2013 12:31 PM by Wile E Coyote Go to original post RSS
      • 15. Re: not able to drop a table in 10g for strange reason... help please..
        Gerwin Hendriksen
        Hi,

        I just looked over the issue you have and I saw you checked select * from dual and found one row. In newer versions from Oracle this is normal behaviour, but still the dual table can contain more than one record. Can you please checkout also the query:
        select count(*) from dual;

        The update of setting the session in sql trace is a very good suggestion. I would expect to find in the raw trace file something with "err". If the above query does not give more than 1, you should continue start tracing the session.

        Regards,

        Gerwin
        • 16. Re: not able to drop a table in 10g for strange reason... help please..
          Gerwin Hendriksen
          I just did a small test, and was able to reproduce your issue. Please look at the number of represented numbers of records in the dual table:

          --------------------------------------------------------------------

          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>


          --------------------------------------------------------------------

          In my case I was able to identify the wrong added row, but probably in your case you just should delete all records and add the letter "X" again as the dummy record.

          With kind regards,

          Gerwin
          • 17. Re: not able to drop a table in 10g for strange reason... help please..
            277993
            it is started coming after i created foreign key in a table using
            ALTER TABLE xxx ADD CONSTRAINT xxx FOREIGN KEY (xxx) REFERENCES xxx (xxx) DEFERRABLE;
            Did you add the constraint to this table (t1) or another table? Could you confirm which user you created this constraint as and which table you have created this constraint on? Ws this on a SYS table? Also, could you check the alert log to see if there are any errors that are logged at the time you get this error message.

            For now, you can rename the able and try dropping it:

            1 rename t1 to t1_drop;
            2. drop table t1_drop cascade constraints purge;
            • 18. Re: not able to drop a table in 10g for strange reason... help please..
              732383
              Check Metalink 453528.1. I think this may be your problem.
              • 19. Re: not able to drop a table in 10g for strange reason... help please..
                user10693802
                Thanks so much for this post. The resolution from metalink worked well for me
                • 20. Re: not able to drop a table in 10g for strange reason... help please..
                  Wile E Coyote
                  I found another instance where this caused the same error but with a different cause. I found in my DB that I had duplicated records in the sys.mlog$ table that made deleting the log table and the main table impossible until the duplicate was removed.
                  1 2 Previous Next