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