-
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.
-
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.
-
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.
-
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"
-
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 ...
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|