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