DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: error in droping tablespace

  1. #1
    Join Date
    Jan 2007
    Posts
    231

    error in droping tablespace

    hi all,

    ver-Oracle 9.2

    When i tried to drop tablespace(in test box) it shows me err.


    SQL> drop tablespace users including contents and datafiles;
    drop tablespace users including contents and datafiles
    *
    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

    already all datafiles are offlined.
    which index i have to drop ???

    Thanks in adv.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    are you sure you are dropping the right tablespace?
    It looks to me tablespace is alive and still has content in it.
    Please do this...
    select count(*) from dba_segments where tablespace_name = 'USERS';
    What it shows?
    Last edited by PAVB; 05-14-2007 at 08:55 AM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Its refering to Indexes and still being used.

    Use CASCADE CONSTRAINTS option to drop the tablespace.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Personally, I like to make sure a tablespace is empty before I drop it. It's much more cleaner and you don't run into such issues.
    Jeff Hunter

  5. #5
    Join Date
    Jan 2007
    Posts
    231
    SQL> drop tablespace users including contents and datafiles cascade constraints;
    drop tablespace users including contents and datafiles cascade constraints
    *
    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


    we have accidentally deleted two of our datafiles in this tablespaces and there is no backup (since it is testDB).we have another three datafiles in that tablespace.how to proceed further.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    May try to
    shutdown immediate
    startup restrict
    drop tablespace
    alter database disable restricted sessions

    good luck

  7. #7
    Join Date
    Jan 2007
    Posts
    231
    No Bore..,
    I followed your steps.
    It doesn't help me.

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    OK then
    select owner, constraint_name,table_name,index_owner,index_name
    from dba_constraints
    where (index_owner,index_name) in (select owner,index_name from dba_indexes where tablespace_name='YOUR_TABLESPACE IN UPPER CASE');

    Then disable all these constraints and drop the tablespace. Later recreate the tablespace and reenable the constraints

  9. #9
    Join Date
    Jan 2007
    Posts
    231
    Thanks Bore..
    followed your steps.
    Its working now..

  10. #10
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Nice to hear that. :-)

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width