Removing tablespace error ORA-00604ORA-02429 Resolution Process

An error ORA-00604ORA-02429 was reported when I deleted the tablespace today. Let's share the process of solving the problem below.

An error ORA-00604ORA-02429 was reported when I deleted the tablespace today. Let's share the process of solving the problem below.

Today, when removing the tablespace encountered an error ORA-00604 & ORA-02429, below to share the process of solving the problem.

Test Environment
I am operating on Oracle 10g + Windows Server 2008 Standard R2.

SQL>

SQL> select * from v $ version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bi

PL/SQL Release 10.2.0.4.0-Production

CORE 10.2.0.4.0 Production

TNS for 64-bit Windows: Version 10.2.0.4.0-Production

NLSRTL Version 10.2.0.4.0-Production

SQL>

1. An error occurred while deleting the tablespace.
When I run the drop tablespace statement, the database reports an error. The prompt is:
ORA-00604: recursive SQL Level 1 error
ORA-02429: The index used to force a unique/primary key cannot be deleted ".
Based on the error message, I added the cascade constraints option and still reported an error. The process is as follows:

SQL> drop tablespace HOEGH;

Drop tablespace HOEGH

*

Row 3 has an error:

ORA-01549: The tablespace is not empty, use the including contents Option

SQL> drop tablespace HOEGH including contents;

Drop tablespace HOEGH including contents

*

Row 3 has an error:

ORA-00604: recursive SQL Level 1 error

ORA-02429: Unable to delete index used to force unique/primary key

SQL>

SQL> drop tablespace HOEGH including contents cascade constraints;

Drop tablespace HOEGH including contents cascade constraints

*

Row 3 has an error:

ORA-00604: recursive SQL Level 1 error

ORA-02429: Unable to delete index used to force unique/primary key

SQL>

2. Search for unique/primary key indexes
Generally, multiple errors are caused by one of them. Next, we need to check the unique/primary key index stored in the HOEGH tablespace. As follows:

SQL> select 'alter table' | owner | '.' | table_name | 'drop constraint' | constraint_name | ';'

2 from dba_constraints

3 where constraint_type in ('U', 'P ')

4 and (index_owner, index_name) in

5 (select owner, segment_name

6 from dba_segments

7 where tablespace_name = 'hoegh ');

'Altertable' | OWNER | '.' | TABLE_NAME | 'dropconstraint' | CONSTRAINT_NAME | ';'

Bytes -----------------------------------------------------------------------------------------------------

Alter table HOEGH. HOEGH drop constraint PK_HOEGH;

Alter table HOEGH1.HOEGH drop constraint PK_HOEGH;

Alter table HOEGH2.HOEGH drop constraint PK_HOEGH;

SQL>
From the query results, the tablespace contains three primary key indexes.

3. Delete the unique/primary key index
Delete the unique/primary key index based on the preceding query results. As follows:

SQL> alter table HOEGH. HOEGH drop constraint PK_HOEGH;

The table has been changed.

SQL> alter table HOEGH1.HOEGH drop constraint PK_HOEGH;

The table has been changed.

SQL> alter table HOEGH2.HOEGH drop constraint PK_HOEGH;

The table has been changed.

SQL>

4. The tablespace is deleted successfully.
Run the drop tablespace statement again.

SQL>

SQL> drop tablespace HOEGH including contents cascade constraints;

The tablespace has been deleted.

SQL>

This article permanently updates the link address: