DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: dropping a tablespace ?

  1. #1
    Join Date
    Sep 2003
    Posts
    7

    dropping a tablespace ?

    Hi.
    i'm trying to drop a tablespace called XDB from my testing DB (9i).
    the scenario was like that :
    1. I've created a database with the database configuration assitant.
    2. this has created a tablespace called XDB for XML Stuff (irrelevant for me).
    3. I connected to the server as sys.
    4. wrote "DROP TABLESPACE XDB INCLUDING CONTENTS AND DATAFILES;".
    i got an error message :
    "ORA-29857 domain indexes and/or secondary objects exist in the tablespace".
    ok, i know that i need to drop the domain indexes (wich i dont know where are they) and secondary objects (wich i dont know what are they ?).
    i tryied to select objects from DBA/USER tables,objects,segments that are in tablespace XDB but when i tryied to drop them i got an error that the object or table/view does not exsist.
    the objects started with XDB$SOMETHING and indexes started with SYS_SOMETHING.
    1. why couldnt i dro them?
    2. what do i need to do to drop the tablespace ?
    thanks....

    kashkash.
    Last edited by kashkash; 09-09-2003 at 09:47 AM.

  2. #2
    Join Date
    Nov 2002
    Posts
    170
    You have to take the tablespace offline first and then drop the tablespace, same for data file.I beleive it is the same for 8i and 9i too.

  3. #3
    Join Date
    Sep 2003
    Posts
    7
    generally you are right but i think that the purpose of taking the tablespace offline (in 9i) is to ensure that no transaction will access the segments on the tablespace while droping it.
    i tryied it offline as well (i forgot to mention it before).

    what are the steps or preperations i need to perform before droping a tablespace ?

    thanks... micky.

  4. #4
    Join Date
    Nov 2002
    Posts
    170
    You might have IOT in the tablespace your are trying to drop. Check if it has any IOT's. Drop all the Index organised tables(IOT) first and then drop the tablepsace.

  5. #5
    Join Date
    Nov 2002
    Posts
    170
    To check for IOT's in the tablespace(Just in case)

    select table_name from dba_tables
    where iot_type = 'IOT_OVERFLOW'
    and tablespace_name = 'TS1';

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You basically have dependencies between objects in the tablespace and objects outside of the tablespace.

    For example, you might have a partitioned tablee with some partitions in that TS and some in others.

    You can use DBA_DEPENDENCIES view to help find these objects.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Posts
    170
    Originally posted by slimdave
    You basically have dependencies between objects in the tablespace and objects outside of the tablespace.

    For example, you might have a partitioned table with some partitions in that TS and some in others.

    You can use DBA_DEPENDENCIES view to help find these objects.
    DBA_DEPENDENCIES won't give you tablespace_name, so it's hard to figure out if any objects in that particular tablespace has dependencies. Since your error message is pointing to some index, I'd either check dba_indexes or dba_tables to look for dependencies in that tablepsace.

    Anyway as slimdave mentioned the point is there are dependencies in the tbsp.

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by dbasupuser
    DBA_DEPENDENCIES won't give you tablespace_name, so it's hard to figure out if any objects in that particular tablespace has dependencies.
    Join with dba_segments
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Sep 2003
    Posts
    7
    thank you all for your replies and sorry for not answering for a long period, i just wasnt home.

    anyway, its still not working and its one stubborn tablespace.

    i'll try find in oracle faq for more information.

    thanks anyway for your help.

    mickey.

  10. #10
    Join Date
    Sep 2003
    Posts
    7
    Hi All.
    i just cant believe it. i couldnt find any answer on the net nor in oracle official site !!!

    there was a question exactly like my post but with no reply.

    I'm really ...

    Thanks, mickey.

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