How to Modify Table or Table Partition Tablespace Attibute

If you are moving a partitioned table from one tablespace to an other with following similar command:

alter table owner.table_name move partition partition_name tablespace tablespace_name ...;

Partitions will be moved to new tablespace but default tablespace attribute of the partitioned table will be remained the old one. You can see default tablespace attribute from the dba_part_tables system view:

select def_tablespace_name from dba_part_tables where table_owner='OWNER' and table_name='TABLE_NAME';

In order to set this attribute to new tablespace use following alter command:

alter table owner.table_name modify default attributes tablespace tablespace_name;

Similar case also occurs when you move subpartitions of a partition with following similar command:

alter table owner.table_name move subpartition subpartition_name tablespace tablespace_name ...;

Subpartitions will be moved to new tablespace but default tablespace of the partition will be remained the old one. You can see it from the dba_tab_partitions system view:

select tablespace_name from dba_tab_partitions where table_owner='OWNER' and table_name='TABLE_NAME' and partition_name='PARTITION_NAME';

In order to alter partition tablespace value use following alter command:

alter table owner.table_name modify default attributes for partition partition_name tablespace tablespace_name;