ORA-00604 or ORA-02429 Dropping Tablespace in Oracle Solution

ORA-00604: error occurred at recursive SQL level 1

ORA-02429: cannot drop index used for enforcement of unique/primary key
 
you will get above error while you trying to dropping tablespace or droping index or constraint. here i have get this error while droping tablespace in oracle 10/11g.
Solution:
 
Example.
Suppose you have TABLESPACE with name TESTTBL_I for indexes. and your are trying to drop this using below command:
SQL> drop tablespace TESTTBL_I including  contents and datafiles;

drop tablespace TESTTBL_I 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

you can also trying using below command to drop tablespace using cascade constraints option.

STEP-1:
SQL>drop tablespace TESTTBL_I including contents and datafiles cascade constraints;

if you still get ora-00604 or ora-02429 error after using cascade constraints option you need to do following steps.
STEP-2:
connect using sysdba privileges and check below command.

SQL> SELECT INDEX_NAME,TABLE_NAME,TABLESPACE_NAME FROM               DBA_INDEXES WHERE TABLESPACE_NAME= ‘TESTTBL_I’;
STEP-3:
if you found any index or object in this DBA_INDEXES view you need to drop or rebuild that particulate index on other tablespace.

SQL> ALTER INDEX INDEX_NAME REBUILD TABLESPACE TSNAME NOLOGGING;
or
SQL> DROP INDEX index_name;

Where INDEX_NAME= index name found in step2 & tsname is other than you need to drop.

Now Try to drop tablespace
SQL> drop tablespace TESTTBL_I including  contents and datafiles;
Tablespace dropped.

–please give your expert comments if any others.

Advertisements

Trả lời

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Đăng xuất /  Thay đổi )

Google photo

Bạn đang bình luận bằng tài khoản Google Đăng xuất /  Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Đăng xuất /  Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Đăng xuất /  Thay đổi )

Connecting to %s