12 Replies Latest reply on Jun 27, 2008 1:16 PM by 71981

    Drop User Cascade?

    71981
      I just want to completely drop a user and all his objects...

      SQL> drop user mcproto cascade;
      drop user mcproto cascade
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-02429: cannot drop index used for enforcement of unique/primary key

      I am running this as system...on 10.2.0.3/AIX 5.3

      This works fine on other platforms and versions,

      Any ideas
        • 1. Re: Drop User Cascade?
          Nicolas.Gasparotto
          Set a trace event on your database, and rerun the drop command.

          Nicolas.
          • 2. Re: Drop User Cascade?
            71981
            OK, Nick I did this and got back about a yard of output all of which shows a lot of activity in the data dictionary but nothing meaningful to me.

            Meanwhile, I have opened an SR with Oracle and there seems to be a consensus among the "notes" they point to that there may be something about the fact that this schema (user) was created by an import utility and that there are some known problems.

            Also, maybe something about the fact that there may be a bug or two regarding the recycle bin.

            Do you know of some way to "brute force" the dropping of a user regardless of the constraints etc?

            One note pertaining to AQ suggests "manually cleaning up the data dictionary"...not sure what that would involve?
            • 3. Re: Drop User Cascade?
              Bolev
              What if to drop constraints first ?
              You may write a simple program for this (using user_constraints)
              • 4. Re: Drop User Cascade?
                121256
                Any ideas
                SQL> create table tmp(i int);

                Table created.

                SQL> create user to_be_dropped_cascade identified by tmp default tablespace system quota 1m on system;

                User created.

                SQL> create index to_be_dropped_cascade.tmp$i on tmp(i);

                Index created.

                SQL> alter table tmp add primary key(i);

                Table altered.

                SQL> select owner, index_name, table_owner, table_name from dba_indexes where table_name = 'TMP';

                OWNER                          INDEX_NAME                     TABLE_OWNER                    TABLE_NAME
                ------------------------------ ------------------------------ ------------------------------ -----------
                TO_BE_DROPPED_CASCADE          TMP$I                          NOT_THE_PREVIOUS_ONE           TMP

                SQL> drop user to_be_dropped_cascade cascade;
                drop user to_be_dropped_cascade cascade
                *
                ERROR at line 1:
                ORA-00604: error occurred at recursive SQL level 1
                ORA-02429: cannot drop index used for enforcement of unique/primary key


                SQL> alter table tmp drop primary key;

                Table altered.

                SQL> drop user to_be_dropped_cascade cascade;

                User dropped.

                SQL> drop table tmp;

                Table dropped.
                • 5. Re: Drop User Cascade?
                  71981
                  Thanks for the ideas..I went thru and dropped ALL constraints then emptied the recycle bin then set recyclebin-off then tried to fdrop user and still get the "recursive SQL error" but, well here is where we are now with this...

                  SQL> select table_name from user_indexes where table_name like 'ACC%';

                  TABLE_NAME
                  ------------------------------
                  ACC_ACCOUNT

                  SQL> select index_name from user_indexes where table_name like 'ACC%';

                  INDEX_NAME
                  ------------------------------
                  ACC_PRIMARY_KEY


                  SQL> drop table ACC_ACCOUNT cascade constraints;
                  drop table ACC_ACCOUNT cascade constraints
                  *
                  ERROR at line 1:
                  ORA-00942: table or view does not exist


                  SQL> select count (*) from ACC_ACCOUNT;
                  select count (*) from ACC_ACCOUNT
                  *
                  ERROR at line 1:
                  ORA-00942: table or view does not exist


                  SQL> select table_name from tabs where table_name = 'ACC_ACCOUNT';

                  TABLE_NAME
                  ------------------------------
                  ACC_ACCOUNT
                  • 6. Re: Drop User Cascade?
                    Sven W.
                    does this return a result?

                    SELECT * FROM USER_TABLES
                    WHERE TABLE_NAME LIKE 'ACC%';

                    Also consider if there are some blanks included in the table name:

                    select '|'||table_name||'|', dump(table_name) from user_indexes where table_name like 'ACC%';


                    You might have to do something like

                    select count (*) from "ACC_ACCOUNT ";
                    • 7. Re: Drop User Cascade?
                      71981
                      yes, the first suggested query returns a row...I changed the * to table_name to abbreviate the output...

                      SQL> select table_name from user_tables where table_name like 'ACC%';

                      TABLE_NAME
                      ------------------------------
                      ACC_ACCOUNT

                      SQL> select count (*) from ACC_ACCOUNT ;

                      COUNT(*)
                      ----------
                      0

                      SQL> drop table ACC_ACCOUNT;
                      drop table ACC_ACCOUNT
                      *
                      ERROR at line 1:
                      ORA-00604: error occurred at recursive SQL level 1
                      ORA-00942: table or view does not exist
                      ORA-06512: at line 19


                      SQL> select count (*) from "ACC_ACCOUNT ";
                      select count (*) from "ACC_ACCOUNT "
                      *
                      ERROR at line 1:
                      ORA-00942: table or view does not exist


                      SQL> select count (*) from ACC_ACCOUNT ;

                      COUNT(*)
                      ----------
                      0

                      SQL> drop table ACC_ACCOUNT cascade constraints;
                      drop table ACC_ACCOUNT cascade constraints
                      *
                      ERROR at line 1:
                      ORA-00604: error occurred at recursive SQL level 1
                      ORA-00942: table or view does not exist
                      ORA-06512: at line 19


                      SQL> select '|'||table_name||'|', dump(table_name) from user_indexes where table_name like 'ACC%';

                      no rows selected

                      SQL> select table_name from user_tables where table_name like 'ACC%';

                      TABLE_NAME
                      ------------------------------
                      ACC_ACCOUNT

                      SQL> drop table ACC_ACCOUNT;
                      drop table ACC_ACCOUNT
                      *
                      ERROR at line 1:
                      ORA-00604: error occurred at recursive SQL level 1
                      ORA-00942: table or view does not exist
                      ORA-06512: at line 19


                      SQL>

                      Is this wierd or what?
                      • 8. Re: Drop User Cascade?
                        Bolev
                        Is this only one table (generating an error) left?

                        Is this table used any TYPES?

                        Can you put the creation script for this table?
                        • 9. Re: Drop User Cascade?
                          71981
                          Boley-

                          No this not the only table...this situation occurs for ALL users/schemas and ALL tables...

                          No create statements available except inside *.dmp files

                          It is looking like a bug on Oracle 10g/AIX associated with schemas ceated by virtue of running imports...

                          I have opened an SR with Metalink and they are presently reviewing the output of hcheck which reported over a thousand possible errors in the data dictionary...
                          • 10. Re: Drop User Cascade?
                            Bolev
                            There is always a possibility of a bug...

                            Anyway let us know if you get a respond from Oracle
                            • 11. Re: Drop User Cascade?
                              MihaiVigariu
                              Hi there,

                              A similar issue i have in my company also, but not involving the table name, but data inside the table.

                              There is one column: ACCOUNT_NO NUMBER NOT NULL[b] and is primary key.

                              In this column there are about 8 values that you simply cannot extract the rows from that table, for those 8 values in the ACCOUNT_NO column. IF i:

                              select * from table where account_no = 123456;

                              then zero results will be displayed.

                              BUT if i run my query like:

                              select * from table where account_no like '%123456%' then it works!!

                              Of course it's a problem of trailing or leading blank spaces, because the same works:

                              select * from table where trim(account_no) = 123456;

                              So, how come that i have blank spaces next to a number that basically will not be a number anymore, but a string, in a column that is defined as number ...I think it's because of dumps, exported and imported, maybe something occurred.

                              Anyway, good luck!

                              Michael
                              • 12. Re: Drop User Cascade?
                                71981
                                Boley et al-

                                Well, we finally got this problem resolved after quite a time with Metalink trying this and trying that, running RDA, hcheck and setting traces.

                                The RDA revealed that the Oracle Spatial objects were hosed up, invalid or some missing so we dropped the user MDSYS after following Note.179472.1 : Steps for Manual De-installation of Oracle Spatial.

                                Then the problems with dropping all the other users/objects ceased.

                                Oracle Spatial is something we do not use so we leaving it off the system, it installed by default evidently since we never explicitly called for it to be installed. We have it on other platforms (LINUX, HPUX) and likewise never use it but it never caused problems either...just on AIX.

                                The lesson we learned is that we need to be way more careful during installations to make sure NOT to install superfluous features...they can really cause grief, this was the last place we would have looked and it took over two weeks with Metalink to work through this.