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

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 11.2.0.2 [Release 11.2]
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

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 csa cascade;
drop user csa cascade
*
ERROR at line 1:
ORA-01918: user 'CSA' does not exist


SQL> create user csa identified by csa default tablespace sysaux;

User created.

SQL> grant dba to csa;

Grant succeeded.

SQL> connect csa/csa
Connected.

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


SQL> create table segtestpart ( 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 segtestpart_pk on segtestpart(a) local;


Index created.

SQL> create index segtestpart_i1 on segtestpart(b) local;


Index created.

SQL> alter table segtestpart add primary key (a) using index segtestpart_pk;


Table altered.

SQL> insert into segtestpart values (1,'1');

1 row created.

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

1 row created.

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

1 row created.

SQL> commit;

Commit complete.

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


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

Table created.

SQL> create unique index segtest_pk on segtest(a);


Index created.

SQL> create index segtest_i1 on segtest(b);


Index created.

SQL> alter table segtest add primary key (a) using index segtest_pk;


Table altered.

SQL> insert into segtest values (1,'1');

1 row created.

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

1 row created.

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

1 row created.

SQL> insert into segtest 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='CSA' order by 1,2;


SEGMENT_NAME           PARTITION_NAME            BLOCKS
------------           --------------            ------
SEGTESTPART_I1         P1                        8
SEGTESTPART_I1         P2                        8
SEGTESTPART_I1         P3                        8
SEGTESTPART_PK         P1                        8
SEGTESTPART_PK         P2                        8
SEGTESTPART_PK         P3                        8
SEGTEST_I1                                       8
SEGTEST_PK                                       8

8 rows selected.

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


INDEX_NAME STATUS
------------------------------ --------
SEGTESTPART_I1 N/A
SEGTESTPART_PK N/A
SEGTEST_PK VALID
SEGTEST_I1 VALID

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


INDEX_NAME               PARTITION_NAME                STATUS
------------             --------------                ------
SEGTESTPART_I1           P1                            USABLE
SEGTESTPART_I1           P2                            USABLE
SEGTESTPART_I1           P3                            USABLE
SEGTESTPART_PK           P1                            USABLE
SEGTESTPART_PK           P2                            USABLE
SEGTESTPART_PK           P3                            USABLE

6 rows selected.

SQL> alter table segtestpart move partition p1;


Table altered.

SQL> alter table segtest move;


Table altered.

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


SEGMENT_NAME                  PARTITION_NAME              BLOCKS
------------                  --------------              ------
SEGTESTPART_I1                P2                          8
SEGTESTPART_I1                P3                          8
SEGTESTPART_PK                P2                          8
SEGTESTPART_PK                P3                          8
SEGTEST_I1                                                8
SEGTEST_PK                                                8

6 rows selected.

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


INDEX_NAME                     STATUS
----------                     ------
SEGTESTPART_I1                 N/A
SEGTESTPART_PK                 N/A
SEGTEST_PK                     UNUSABLE
SEGTEST_I1                     UNUSABLE

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


INDEX_NAME                  PARTITION_NAME     STATUS
----------                  --------------     ------
SEGTESTPART_I1              P1                 UNUSABLE
SEGTESTPART_I1              P2                 USABLE
SEGTESTPART_I1              P3                 USABLE
SEGTESTPART_PK              P1                 UNUSABLE
SEGTESTPART_PK              P2                 USABLE
SEGTESTPART_PK              P3                 USABLE

6 rows selected.





Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms