My Oracle Support Banner

Unusable Index Segment Still Exists in DBA_SEGMENTS for Alter Table Move (Doc ID 1265948.1)

Last updated on FEBRUARY 18, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 02-May-2013***

Symptoms

The index segment for non partitioned tables are still listed in dba_segments even after the index is marked UNUSABLE.

This does not happen for the partitioned indexes once those are marked unusable.

Changes

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Used alter table move syntax.

Example

REM compare partitioned and non-partitioned table behavior

SQL> set lines 300 pages 300

SQL> connect / as sysdba
Connected.

SQL> drop user <USER_NAME> cascade;
drop user <USER_NAME> cascade
*
ERROR at line 1:
ORA-01918: user '<USER_NAME>' does not exist

SQL> create user <USER_NAME> identified by <PASSWORD> default tablespace sysaux;

User created.

SQL> grant dba to <USER_NAME>;

Grant succeeded.

SQL> connect <USER_NAME>/<PASSWORD>
Connected.

SQL> drop table segtestpart;
drop table segtestpart
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table <TABLE_NAME> ( a number not null , b varchar2(40) )
partition by range (a) (
partition p1 values less than (10 ),
partition p2 values less than (20 ),
partition p3 values less than (30 ));

Table created.

SQL> create unique index <TABLE_NAME>_pk on <TABLE_NAME>(a) local;

Index created.

SQL> create index <TABLE_NAME>_i1 on <TABLE_NAME>(b) local;

Index created.

SQL> alter table <TABLE_NAME> add primary key (a) using index <TABLE_NAME>_pk;

Table altered.

SQL> insert into <TABLE_NAME> values (1,'1');

1 row created.

SQL> insert into <TABLE_NAME> values (10,'1');

1 row created.

SQL> insert into <TABLE_NAME> values (20,'1');

1 row created.

SQL> commit;

Commit complete.

SQL> drop table <TABLE_NAME>;
drop table segtest
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table <TABLE_NAME> ( a number not null , b varchar2(40) );

Table created.

SQL> create unique index <INDEX_NAME> on <TABLE_NAME>(a);

Index created.

SQL> create index <INDEX_NAME> on <TABLE_NAME>(b);

Index created.

SQL> alter table <TABLE_NAME> add primary key (a) using index <INDEX_NAME>;

Table altered.

SQL> insert into <TABLE_NAME> values (1,'1');

1 row created.

SQL> insert into <TABLE_NAME> values (2,'1');

1 row created.

SQL> insert into <TABLE_NAME> values (3,'1');

1 row created.

SQL> insert into <TABLE_NAME> values (4,'1');

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name,partition_name,blocks from dba_segments where segment_type like 'INDEX%' and owner='<USER_NAME>' order by 1,2;

SEGMENT_NAME PARTITION_NAME BLOCKS
------------ -------------- ------
<INDEX_NAME>_I1 P1 8
<INDEX_NAME>_I1 P2 8
<INDEX_NAME>_I1 P3 8
<INDEX_NAME>_PK P1 8
<INDEX_NAME>_PK P2 8
<INDEX_NAME>_PK P3 8
<INDEX_NAME> 8
<INDEX_NAME> 8

8 rows selected.

SQL> select index_name, status from dba_indexes where owner='<USER_NAME>';

INDEX_NAME STATUS
------------------------------ --------
<INDEX_NAME>_I1 N/A
<INDEX_NAME>_PK N/A
<INDEX_NAME> VALID
<INDEX_NAME> VALID

SQL> select index_name,partition_name,status from dba_ind_partitions where index_owner='<USER_NAME>' order by 1,2;

INDEX_NAME PARTITION_NAME STATUS
------------ -------------- ------
<INDEX_NAME>_I1 P1 USABLE
<INDEX_NAME>_I1 P2 USABLE
<INDEX_NAME>_I1 P3 USABLE
<INDEX_NAME>_PK P1 USABLE
<INDEX_NAME>_PK P2 USABLE
<INDEX_NAME>_PK P3 USABLE

6 rows selected.

SQL> alter table <INDEX_NAME> move partition p1;

Table altered.

SQL> alter table <TABLE_NAME> move;

Table altered.

SQL> select segment_name,partition_name,blocks from dba_segments where segment_type like 'INDEX%' and owner='<USER_NAME>' order by 1,2;

SEGMENT_NAME PARTITION_NAME BLOCKS
------------ -------------- ------
<INDEX_NAME>_I1 P2 8
<INDEX_NAME>_I1 P3 8
<INDEX_NAME>_PK P2 8
<INDEX_NAME>_PK P3 8
<INDEX_NAME> 8
<INDEX_NAME> 8

6 rows selected.

SQL> select index_name, status from dba_indexes where owner='<USER_NAME>';

INDEX_NAME STATUS
---------- ------
<INDEX_NAME>_I1 N/A
<INDEX_NAME>_PK N/A
<INDEX_NAME> UNUSABLE
<INDEX_NAME> UNUSABLE

SQL> select index_name,partition_name,status from dba_ind_partitions where index_owner='<USER_NAME>' order by 1,2;

INDEX_NAME PARTITION_NAME STATUS
---------- -------------- ------
<INDEX_NAME>_I1 P1 UNUSABLE
<INDEX_NAME>_I1 P2 USABLE
<INDEX_NAME>_I1 P3 USABLE
<INDEX_NAME>_PK P1 UNUSABLE
<INDEX_NAME>_PK P2 USABLE
<INDEX_NAME>_PK P3 USABLE

6 rows selected.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.