How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes there is situation when you need to modify default attributes for tables, tables partitions, index or index partitions in Oracle database. When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition. Here I'll show you how to do these things:

Check Default atributes for partitioned Table :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_TABLES WHERE TABLE_NAME='TABLE_NAME';

Check Default atributes for partitioned Index :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES WHERE INDEX_NAME='INDEX_NAME';

Modify Default Attributes for a Table :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Table partitions :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index partitions :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

ORA-01502: index or partition of such index is in unusable state

Hello,
here I'll show you how to find and rebuild unusable indexes partition in oracle:

SQL> select distinct status from dba_ind_partitions;
STATUS
——–
USABLE
UNUSABLE
SQL>select count(1) from dba_ind_partitions where status='UNUSABLE';
  COUNT(1)
———-
         2

we have two unusable indexes, get code for rebuild this indexes

SQL>select  ' alter index '||index_owner||'.'||index_name||' rebuild partition '||
partition_name||';' from dba_ind_partitions where status='UNUSABLE';

and execute that code

SQL>alter index IKA.TEST_TBL_idx rebuild partition P_2014_02;
Index altered.
alter index IKA.TEST_TBL_idx rebuild partition P_2014_03;
Index altered.

check that we have all indexes usable state

SQL>select distinct status from dba_ind_partitions;
STATUS
——–
USABLE

that's all.