CHANGE PARTITION STORAGE TO NEW TABLESPACE WITH MODIFY ATTRIBUTE ORACLE

Partition maintenance is often a challenging task specially during large chunks of partitions with highly critical tables.

Consider a case when the tablespace cannot store any more partition for a table and they need to be redirected to a different tablespace to continue

Create a sample interval partitioned table with default tablespace USERS

kIsH@Xhydra<>create table partition
(p_id number(10),
  2    3   c_id number(10),
  4   t_id date,
 ch_id char(1))
 tablespace USERS partition by range( t_id)
  5    6    7   interval (numtoyminterval(1, 'MONTH'))
 (partition p0 values less than (to_date('1-1-2022', 'DD-MM-YYYY')) tablespace USERS);  8

Table created.

Populate some data

kIsH@Xhydra<>begin
for i in 1 .. 10 loop
insert into partition values (i,i,to_date(trunc(dbms_random.value(to_char(date '1979-01-01','J'),to_char(date '9999-12-31','J'))),'J'),'a');
end loop;
commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

kIsH@Xhydra<>col TABLE_NAME for a20
col PARTITIONING_TYPE for a20
col PARTITION_COUNT for 999999999
col DEF_TABLESPACE_NAME for a20
select TABLE_NAME,
           PARTITIONING_TYPE,
           PARTITION_COUNT,
           DEF_TABLESPACE_NAME
  from dba_part_tables
  where TABLE_NAME='PARTITION';kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>  2    3    4    5    6

TABLE_NAME           PARTITIONING_TYPE    PARTITION_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------------- --------------------
PARTITION            RANGE                        1048575 USERS

Consider that the tablespace can no longer hold any partition under USERS. Check the partition storage. Here partitions are stored in USERS tablespace

kIsH@Xhydra<>select TABLESPACE_NAME,count(*) from dba_tab_partitions where TABLE_NAME='PARTITION' group by TABLESPACE_NAME;

TABLESPACE_NAME        COUNT(*)
-------------------- ----------
USERS                       147

To modify the attribute to new tablespace CORRUPT, use the below DDL

kIsH@Xhydra<>alter table partition modify default attributes tablespace CORRUPT;

Table altered.

Populate new data

kIsH@Xhydra<>begin
for i in 1 .. 2 loop
insert into partition values (i,i,to_date(trunc(dbms_random.value(to_char(date '1979-01-01','J'),to_char(date '9999-12-31','J'))),'J'),'a');
end loop;
commit;
end;
/  2    3    4    5    6    7

PL/SQL procedure successfully completed.

Now observe that the new data entered into new tablespace CORRUPT. Subsequent partitions which will be generated get stored in CORRUPT tablespace not USERS

kIsH@Xhydra<>col TABLE_NAME for a20
col PARTITIONING_TYPE for a20
col PARTITION_COUNT for 999999999
col DEF_TABLESPACE_NAME for a20
select TABLE_NAME,
           PARTITIONING_TYPE,
           PARTITION_COUNT,
           DEF_TABLESPACE_NAME
  from dba_part_tables
  where TABLE_NAME='PARTITION';kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>kIsH@Xhydra<>  2    3    4    5    6

TABLE_NAME           PARTITIONING_TYPE    PARTITION_COUNT DEF_TABLESPACE_NAME
-------------------- -------------------- --------------- --------------------
PARTITION            RANGE                        1048575 CORRUPT

kIsH@Xhydra<>select TABLESPACE_NAME,count(*) from dba_tab_partitions where TABLE_NAME='PARTITION' group by TABLESPACE_NAME;

TABLESPACE_NAME                  COUNT(*)
------------------------------ ----------
CORRUPT                                 6
USERS                                 147

Leave a Reply