error in droping tablespace

[ams-jamali]

hi all,

ver-Oracle 9.2

When i tried to drop tablespace(in test box) it shows me err.


SQL> drop tablespace users including contents and datafiles;
drop tablespace users including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

already all datafiles are offlined.
which index i have to drop ???

Thanks in adv.

[PAVB]

are you sure you are dropping the right tablespace?
It looks to me tablespace is alive and still has content in it.
Please do this...
select count(*) from dba_segments where tablespace_name = 'USERS';
What it shows?

[dbasan]

Its refering to Indexes and still being used.

Use CASCADE CONSTRAINTS option to drop the tablespace.

[marist89]

Personally, I like to make sure a tablespace is empty before I drop it. It's much more cleaner and you don't run into such issues.

[ams-jamali]

SQL> drop tablespace users including contents and datafiles cascade constraints;
drop tablespace users including contents and datafiles cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key


we have accidentally deleted two of our datafiles in this tablespaces and there is no backup (since it is testDB).we have another three datafiles in that tablespace.how to proceed further.

[Bore]

May try to
shutdown immediate
startup restrict
drop tablespace
alter database disable restricted sessions

good luck

[ams-jamali]

No Bore..,
I followed your steps.
It doesn't help me.

[Bore]

OK then
select owner, constraint_name,table_name,index_owner,index_name
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes where tablespace_name='YOUR_TABLESPACE IN UPPER CASE');

Then disable all these constraints and drop the tablespace. Later recreate the tablespace and reenable the constraints

[ams-jamali]

Thanks Bore..
followed your steps.
Its working now..

[Bore]

Nice to hear that. :-)

Cheers