How to save space from old table partitions in an Oracle database
Certifications:
When we have very large tables it helps a lot to use Table Partitions. The Table Partition divides the table into smaller pieces depending on the field we have defined as a criterion, e.g. "date of insertion", these pieces can be either in the same tablespace or in different ones. By using them you improve the query performance but we are also given more possibilities during the backup, e.g. not to backup old partitions that do not change. The problem, however, is that over time, even if the partitions no longer have records, they take up some space. In this article we will see how we can free up this space in Oracle Database.
How we find Table Partitions
With the following query that I have written we can find for a specific shape e.g. the “SH”, how many Table Partitions there are that do not contain this year in their name and do not currently contain records.
Through this query we can see the size of each partition and generate the commands we will need for the process with dynamic SQL:
select ALLP.TABLE_OWNER,ALLP.TABLE_NAME,ALLP.PARTITION_NAME,ALLP.NUM_ROWS,ALLP.TABLESPACE_NAME,SEGM.GB_size,
'select * from ' ||ALLP.TABLE_OWNER||'.'||ALLP.TABLE_NAME ||' PARTITION ("'||ALLP.PARTITION_NAME||'");' as select_partition,
'ALTER TABLE '||ALLP.TABLE_OWNER||'.'||ALLP.TABLE_NAME ||' TRUNCATE PARTITION "'||ALLP.PARTITION_NAME||'" UPDATE INDEXES;' as truncate_partition
from all_tab_partitions ALLP
left join (select owner,partition_name,round(bytes/1024/1024/1024,3) as GB_size from dba_segments) SEGM on SEGM.PARTITION_NAME=ALLP.PARTITION_NAME and SEGM.OWNER=ALLP.TABLE_OWNER
where 1=1
and ALLP.table_owner = 'SH'
and ALLP.partition_name not like (select '%'||extract(year from sysdate)||'%' from dual)
and ALLP.num_rows = 0
order by partition_name;
In the column SELECT_PARTITION generate the query for us to see what records each partition contains. In our case, whatever partition we run, we will see that it returns no records, since we have set it to bring us only partitions with zero records:
select * from SH.COSTS PARTITION ("COSTS_1995");
How do we free up space?
In the column TRUNCATE_PARTITION generate the command query for each partition separately, which will delete the entries (if there are any inside) and it will release the space it occupies without erasing the partition. To update the Index Online at the same time and not turn it into Unusable, the command contains the Update Indexes parameter:
ALTER TABLE SH.COSTS TRUNCATE PARTITION "COSTS_1995" UPDATE INDEXES;
To see the total size of the schema before running Truncate Partition, we run the following:
select round(sum(bytes)/1024/1024/1024,3) as GB_size from dba_segments SEGM where SEGM.OWNER ='SH';
How can the process run automatically for all Table Partitions that meet the criteria
To make our lives easier, we can make the above query with some modifications so that when we execute it as an executable file on top of Oracle Database Server to create the commands and then execute them one by one by itself.
All we need to do is take the following query and save it in a file locally on the Server with a name, for example, truncate_tables.sql:
set linesize 200; set heading off; set feedback off; set echo off; set pagesize 0; spool truncate_partitions_commands.txt select 'ALTER TABLE '||ALLP.TABLE_OWNER||'.'||ALLP.TABLE_NAME ||' TRUNCATE PARTITION "'||ALLP.PARTITION_NAME||'" UPDATE INDEXES;' as truncate_partition from all_tab_partitions ALLP left join (select owner,partition_name from dba_segments) SEGM on SEGM.PARTITION_NAME=ALLP.PARTITION_NAME and SEGM.OWNER=ALLP.TABLE_OWNER where 1=1 --and ALLP.table_owner = 'SH' and ALLP.partition_name not like (select '%'||extract(year from sysdate)||'%' from dual) and ALLP.num_rows = 0; spool off; @truncate_partitions_commands.txt exit;
Then we create an executable file that will call it with a name e.g. truncate_partitions.sh:
#!/usr/bin/ksh sqlplus / as sysdba @truncate_partitions.sql
All that's left to do is run it:
[opc@dp-gr ~]$ chmod +x truncate_partitions.sh
[opc@dp-gr ~]$ ./truncate_partitions.sh
