{"id":3402,"date":"2023-07-31T07:00:00","date_gmt":"2023-07-31T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3402"},"modified":"2024-01-08T02:37:33","modified_gmt":"2024-01-07T23:37:33","slug":"pos-glytonoyme-choro-apo-palia-partitions-pinak","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-glytonoyme-choro-apo-palia-partitions-pinak-2\/","title":{"rendered":"How to save space from old table partitions in an Oracle database"},"content":{"rendered":"

When we have very large tables it helps a lot to use Table Partitions<\/strong>. The Table Partition <\/strong>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 <\/strong>or in different ones. By using them you improve the query performance<\/strong> 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<\/strong>.<\/p>\n\n\n\n

How we find Table Partitions<\/h2>\n\n\n\n

With the following query that I have written we can find for a specific shape e.g. the \u201cSH\u201d, how many Table Partitions there are that do not contain this year in their name and do not currently contain records. <\/p>\n\n\n\n

Through this query we can see the size of each partition and generate the commands we will need for the process with dynamic SQL:<\/p>\n\n\n\n

select ALLP.TABLE_OWNER,ALLP.TABLE_NAME,ALLP.PARTITION_NAME,ALLP.NUM_ROWS,ALLP.TABLESPACE_NAME,SEGM.GB_size,\n'select * from ' ||ALLP.TABLE_OWNER||'.'||ALLP.TABLE_NAME ||' PARTITION (\"'||ALLP.PARTITION_NAME||'\");' as select_partition,\n'ALTER TABLE '||ALLP.TABLE_OWNER||'.'||ALLP.TABLE_NAME ||' TRUNCATE PARTITION \"'||ALLP.PARTITION_NAME||'\" UPDATE INDEXES;' as truncate_partition\nfrom all_tab_partitions ALLP\nleft 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\nwhere 1=1\nand ALLP.table_owner = 'SH'\nand ALLP.partition_name not like (select '%'||extract(year from sysdate)||'%' from dual)\nand ALLP.num_rows = 0\norder by partition_name;<\/pre>\n\n\n\n
\"\"
1<\/figcaption><\/figure>\n\n\n\n
In the column SELECT_PARTITION <\/strong>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:<\/p>\n\n\n\n
select * from SH.COSTS PARTITION (\"COSTS_1995\");<\/pre>\n\n\n\n
\"\"
2<\/figcaption><\/figure>\n\n\n\n
How do we free up space? <\/h2>\n\n\n\n
In the column TRUNCATE_PARTITION <\/strong>generate the command query for each partition separately, which will delete the records (if there are any inside) and it will release the space it occupies without erasing the partition<\/strong>. To update the Index Online at the same time and not turn it into Unusable, the command contains the Update Indexes parameter:<\/p>\n\n\n\n
ALTER TABLE SH.COSTS TRUNCATE PARTITION \"COSTS_1995\" UPDATE INDEXES;<\/pre>\n\n\n\n
To see the total size of the schema before running Truncate Partition, we run the following:<\/p>\n\n\n\n
select round(sum(bytes)\/1024\/1024\/1024,3) as GB_size from dba_segments SEGM where SEGM.OWNER ='SH';<\/pre>\n\n\n\n
\"\"
3<\/figcaption><\/figure>\n\n\n\n
How can the process run automatically for all Table Partitions that meet the criteria <\/h2>\n\n\n\n
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<\/strong> to create the commands and then execute them one by one by itself.<\/p>\n\n\n\n
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:<\/p>\n\n\n\n
set linesize 200;\nset heading off;\nset feedback off;\nset echo off;\nset pagesize 0;\nspool truncate_partitions_commands.txt\nselect \n'ALTER TABLE '||ALLP.TABLE_OWNER||'.'||ALLP.TABLE_NAME ||' TRUNCATE PARTITION \"'||ALLP.PARTITION_NAME||'\" UPDATE INDEXES;' as truncate_partition\nfrom all_tab_partitions ALLP\nleft join (select owner,partition_name from dba_segments) SEGM on SEGM.PARTITION_NAME=ALLP.PARTITION_NAME and SEGM.OWNER=ALLP.TABLE_OWNER\nwhere 1=1\n--and ALLP.table_owner = 'SH'\nand ALLP.partition_name not like (select '%'||extract(year from sysdate)||'%' from dual)\nand ALLP.num_rows = 0;\nspool off;\n@truncate_partitions_commands.txt\nexit;<\/pre>\n\n\n\n
Then we create an executable file that will call it with a name e.g. truncate_partitions.sh:<\/p>\n\n\n\n
#!\/usr\/bin\/ksh\nsqlplus \/ as sysdba @truncate_partitions.sql<\/pre>\n\n\n\n
All that's left to do is run it:<\/p>\n\n\n\n
[opc@dp-gr ~]$ chmod +x truncate_partitions.sh\n[opc@dp-gr ~]$ .\/truncate_partitions.sh\n<\/code><\/pre>\n\n\n\n
Sources:<\/h2>\n\n\n\n