dropping a tablespace ?

[kashkash]

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.

[dbasupuser]

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.

[kashkash]

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.

[dbasupuser]

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.

[dbasupuser]

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';

[slimdave]

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.

[dbasupuser]

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.

[abhaysk]

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

[kashkash]

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.

[kashkash]

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.