Hi,

I will explain How to Check and Modify Default Attributes of Tables and Indexes Partitions in Oracle Database in this post.

 

ORA-02327: cannot create index on expression with datatype LOB | Move Lob Objects to New Tablespace using dba_lobs and dba_lob_partitions views

 

ORA-14257: cannot move a partition which is a composite partition | Move Table, Partition, Subpartitions to New Tablespace

 

 

Check Default Attributes of Tables and Indexes Partitions

You need to modify default attributes for tables, tables partitions, index or index partitions  During the tables,tables partitions, index and index partitions moving to new tablespaces.

 

If you move the tables and indexes to new tablespace, then drop the old tablespace, you need to modify default attributes for tables, tables partitions, index or index partitions for the future partitions, or subpartitions, that are created.

You can check Default atributes of partitioned table as follows.

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

 

You can check Default atributes of partitioned index as follows.

 

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

 

You can check Default atributes of table as follows.

 

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

 

Modify Default Attributes of Tables and Indexes Partitions

then you can modify Default Attributes for a Table partitions as follows.

 

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

 

or you can Modify Default Attributes for a Index as follows.

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

 

or you can modify Default Attributes for a Index partitions :

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

 

 

You can generate script to modify all tables’ default attributes under old tablespace as follows.

SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||'  MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;' FROM DBA_PART_TABLES WHERE DEF_TABLESPACE_NAME='OLD_TABLESPACE_NAME';

 

 SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' ||PARTITION_NAME ||' TABLESPACE TABLESPACE_NAME;' FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME ='OLD_TABLESPACE_NAME';

 

select 'ALTER TABLE '||TABLE_OWNER ||'.'||TABLE_name||' MOVE SUBPARTITION '||subpartition_name||' TABLESPACE TABLESPACE_NAME UPDATE INDEXES parallel 4;' from dba_tab_subpartitions where tablespace_name= 'OLD_TABLESPACE_NAME';


 SELECT 'ALTER TABLE ' ||TABLE_OWNER ||'.' ||TABLE_NAME ||' MODIFY DEFAULT ATTRIBUTES FOR PARTITION ' ||PARTITION_NAME ||' TABLESPACE TABLESPACE_NAME;' FROM DBA_TAB_PARTITIONS WHERE TABLESPACE_NAME ='OLD_TABLESPACE_NAME';


select 'ALTER TABLE "'||owner ||'".'||table_name||' MOVE TABLESPACE TABLESPACE_NAME parallel 32;' from dba_tables where tablespace_name='OLD_TABLESPACE_NAME';

 

You can generate script to modify all indexes’ default attributes under old tablespace as follows.

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;' FROM DBA_PART_INDEXES WHERE DEF_TABLESPACE_NAME='OLD_TABLESPACE_NAME';

 

You can generate the modify script for indexes and other objects.

 

 

You can read the following post to learn more details about Tables and Indexes moving.

How to Move Tables, Index Rebuild, Partitions and Subpartitions to Another Tablespace in Oracle

How to Rebuild Unused Indexes or Move Another Tablespace in Oracle