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.... :confused:

kashkash.

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.

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.

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.

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

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.

Quote:

---

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.

Quote:

---

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

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.

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 :confused: ...

Thanks, mickey.

Just drop the user XDB then drop the tablespace

YES... YES !!!!!!!

Y O U--A R E--R I G H T !!!!

IT WORKED !;)

i tryied to drop a tablespace called "TOOLS" - no broblem, just wrote :
DROP TABLESPACE TOOLS INCLUDING CONTENTS AND DATAFILES;

worked just fine. then i tryied the same for the tablespace XDB and it didnt worked. so, i started to think that the XDB tbs is somehow more problematic to mannage (like SYSTEM tbs or somthing).

BUT Then You Came along and you were absolutly RIGHT !
I Dropped the user XDB and then the tbs :

DROP USER XDB CASCADE;
(Cascade - Drops all the user Objects as well.)
and then :

DROP TABLESPACE XDB INCLUDING CONTENTS AND DATAFILES;

Worked Just Fine !!!

Thank You guru_heaven You are ACE ;).

:cool:

that was the brute force solution :D