Oracle DBA – A lifelong learning experience

Reorganising data within tablespaces

Posted by John Hallas on December 19, 2016

I am currently interested in managing storage on our Exadata platform which is primarily used for running a data warehouse.

The ongoing challenge is that most of the large tablespaces use a bigfile datafile and we insert data using append. That causes data gaps within the datafile and it is constantly growing. A recent example showed a 7Tb datafile containing only 2Tb of data and yet the ASM diskgroups were reporting we were running out of space.

What I intend to share here is how we are organising object to reclaim some of that space and I will include the scripts I am using. I am fully aware of the DBMS_REDEFINITION package and also the capabilities from within Oracle Enterprise Manager but I decided to stick to a method where it was all under my control and I knew exactly where I was. Daily batch on this system can run for up to 24 hours and is normally between 16 and 20 hours long so I had to be aware of when certain tablespaces were not being used.

I have been working through a tablespace at a time so that is the process I will demonstrate – using a tablespace DW_BIA_RTL_TBS_BKP as an example

From a standard script of which there are many versions around on the net to show tablespace usage I select the tablespace I am interested in

In an example I have just run I order by column 4 – free space and the last few rows show tablespaces with the most free space

Tablespace                 (Mb)     (Mb)     (Mb)     (Mb)   (%)

------------------- ----------- -------- -------- -------- ------

DW_BASE_TBS             7918828 7528228   390600   436.00   4.9

DW_STAGING_TBS         3806209 3223318   582891 8064.00   15.3

DW_ODI_TEMP_DFLT_TBS   2024000 1408692   615308 8064.00   30.4

DW_DERIVED_TBS         6690850 6061266   629584 6229.00   9.4

DW_AGGREGATE_TBS       5169408 4405481   763927 8064.00   14.8

DW_BIA_RTL_TBS_BKP     2908660 1454631 1454029 8064.00   50.0

DW_STAGING_ERR_TBS     11266838 8437749 2829089 8064.00   25.1

 

The one I am interested in for this example is the DW_BIA_RTL_TBS_BKP tablespace which has 1.4Tb free but I suspect there will not be much opportunity to resize it downwards without work.

Firstly a script to show the layout of segments at the top (HWM) end of the datafile. I think I got this from Jonathan Lewis’s site

select
block_id,
block_id + blocks - 1   end_block,
blocks*((16384)/1024/1024) Mb_free,
owner,
segment_name,
partition_name,
segment_type
from
dba_extents
where
tablespace_name = 'DW_BIA_RTL_TBS_BKP'
--   and owner = 'ODI_TEMP'
union all
select
block_id,
block_id + blocks - 1   end_block,
blocks*((16384)/1024/1024) Mb_free,
'free'         owner,
'free'         segment_name,
null           partition_name,
null           segment_type
from
dba_free_space
where
tablespace_name = 'DW_BIA_RTL_TBS_BKP'
order by
2 desc
/

coalesce1

As you can see two segments which could be released to bring the HWM of the datafile down but we then have a table partition for November 2014 at the top followed by index segments (which are not shown on the screen shot). Knowing the application I am pretty comfortable that the partition data will not be in use during the main batch (which normally updates data for only the last 2-3 days).

So I will run a script to select all the segments in order from the HWM downwards and map tables to index (as the indexes need a rebuild after the table has been moved).

I have two scripts for that, both written by in-house developers. The first one is a general one that I use which takes each object and moves/rebuilds it, it compresses the table partition for older tables, however it can lead to indexes being left unusable. I therefore do it in small chunks of around 50-100 objects and then rebuild any unusable indexes immediately afterwards.

SET SERVEROUTPUT ON; SET TIMING ON; DECLARE CURSOR c_list_items IS SELECT ilv.* FROM ( SELECT MAX(end_block), owner, segment_name, partition_name, segment_type FROM ( SELECT block_id, block_id + blocks - 1 end_block, owner, segment_name, partition_name, segment_type FROM dba_extents WHERE tablespace_name = 'DW_STAGING_TBS' --WHERE tablespace_name = 'DW_BIA_RTL_TBS_BKP' AND segment_type IN ('TABLE PARTITION','INDEX PARTITION') ) GROUP BY owner, segment_name, partition_name, segment_type ORDER BY 1 desc )ilv WHERE rownum <= 50 ; -- v_segment_name VARCHAR2(30); -- v_partition_name VARCHAR2(30); -- v_segment_type VARCHAR2(100); -- BEGIN FOR i IN c_list_items LOOP v_segment_type := i.segment_type; v_partition_name := i.partition_name; v_segment_name := i.segment_name; -- IF v_segment_type = 'TABLE PARTITION' THEN DBMS_OUTPUT.PUT_LINE('ALTER TABLE '||v_segment_name||' MOVE PARTITION '||v_partition_name||' COMPRESS FOR QUERY HIGH'||';'); -- ELSIF v_segment_type = 'INDEX PARTITION' THEN DBMS_OUTPUT.PUT_LINE('ALTER INDEX '||v_segment_name||' REBUILD PARTITION '||v_partition_name||' online ;'); END IF; -- END LOOP; -- EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error Occurred in PL/SQL block. Error Message is :'||SUBSTR(SQLERRM,1,500)); END; /[/CODE} <pre>ALTER TABLE DWA_X_PO_ORG_ITEM_INV MOVE PARTITION DOII_20141107 COMPRESS FOR QUERY HIGH; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140602 online ; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140526 online ; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140512 online ; ALTER TABLE RET_DWI_TILL_TNDR_DENM MOVE PARTITION PART_4385 COMPRESS FOR QUERY HIGH; ALTER INDEX DWD_X_SHELF_ITEM_SPCE_DAY_PK REBUILD PARTITION DXSISD_20141106 online ; ALTER INDEX DWD_X_SHELF_ITEM_SPCE_DAY_PK REBUILD PARTITION DXSISD_20141107 online ; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140825 online ; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140707 online ; ALTER TABLE DWA_X_PO_ORG_ITEM_INV MOVE PARTITION DOII_20141029 COMPRESS FOR QUERY HIGH; ALTER TABLE STG_PICKLINE MOVE PARTITION PART_W924 COMPRESS FOR QUERY HIGH; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140630 online ; ALTER TABLE DWB_X_PLNGRM_ATTR_DAY MOVE PARTITION DXPAD_20140630 COMPRESS FOR QUERY HIGH; ALTER TABLE STG_PICKLINE MOVE PARTITION PART_W953 COMPRESS FOR QUERY HIGH; ALTER TABLE DWB_X_PLNGRM_ATTR_DAY MOVE PARTITION DXPAD_20140512 COMPRESS FOR QUERY HIGH; ALTER TABLE DWA_X_RTL_SL_MRG_ITM_WK MOVE PARTITION DXRSMIW_20140818 COMPRESS FOR QUERY HIGH; ALTER TABLE DWA_X_RTL_SL_MRG_ITM_WK MOVE PARTITION DXRSMIW_20140728 COMPRESS FOR QUERY HIGH; ALTER INDEX DWA_X_RTL_SL_MRG_ITM_WK_PK REBUILD PARTITION DXRSMIW_20140623 online ; ALTER TABLE DWA_X_RTL_SL_MRG_ITM_WK MOVE PARTITION DXRSMIW_20140602 COMPRESS FOR QUERY HIGH; ALTER TABLE RET_DWI_RTL_TRX MOVE PARTITION PART_4407 COMPRESS FOR QUERY HIGH; ALTER TABLE DWB_X_PLNGRM_ATTR_DAY MOVE PARTITION DXPAD_20140526 COMPRESS FOR QUERY HIGH; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140513 online ; ALTER INDEX DWB_X_PLNGRM_ATTR_DAY_PK REBUILD PARTITION DXPAD_20140407 online ; ALTER INDEX DWB_X_PLNGRM_FXTR_DAY_PK REBUILD PARTITION DXPFD_20140424 online ; ALTER TABLE RET_DWI_RTL_TNDR_HIST MOVE PARTITION PART_4359 COMPRESS FOR QUERY HIGH; ALTER TABLE DWB_X_PLNGRM_ATTR_DAY MOVE PARTITION DXPAD_20140407 COMPRESS FOR QUERY HIGH; ALTER TABLE DWB_X_PLNGRM_FXTR_DAY MOVE PARTITION DXPFD_20140424 COMPRESS FOR QUERY HIGH; ALTER TABLE DWA_X_PO_ORG_ITEM_INV MOVE PARTITION DOII_20141109 COMPRESS FOR QUERY HIGH; ALTER TABLE DWA_X_PO_ORG_ITEM_INV MOVE PARTITION DOII_20141027 COMPRESS FOR QUERY HIGH; ALTER INDEX DWB_X_LOC_PLNGRM_SF_SPC_DAY_PK REBUILD PARTITION DXLPSSD_20140825 online ; ALTER TABLE STG_PICK MOVE PARTITION PART_W953 COMPRESS FOR QUERY HIGH;</pre> <pre>

coalesce2

Note that the maximum segment size is 8Gb due to the block size being 8k. In the example above I have to work out that I can release 6*8Gb + 5Gb+1Gb and work out what that comes to and take that off the datafile size.

Therefore the following script works well

select sq2.tablespace_name,
max(sq2.bytes_running_total/1024/1024/1024) as max_occupied_GB,
'ALTER TABLESPACE '||sq2.tablespace_name||' RESIZE '||TO_CHAR(ROUND((max(sq2.bytes_running_total)/1024/1024/1024)+1))||'G;' as resize_command
from (select sq1.tablespace_name,
sq1.block_id,
sq1.end_block,
DECODE(sq1.owner,'free','free','occupied') as state,
sq1.bytes,
SUM(sq1.bytes) over (order by sq1.tablespace_name, sq1.block_id) bytes_running_total
from (select tablespace_name,
block_id,
block_id + blocks - 1   end_block,
blocks*((select block_size from dba_tablespaces where tablespace_name = UPPER(:tbs_name))/1024/1024) Mb_free,
owner,
segment_name,
partition_name,
segment_type,
bytes
from dba_extents
where tablespace_name = UPPER(:tbs_name)
union all
select tablespace_name,
block_id,
block_id + blocks - 1   end_block,
blocks*((select block_size from dba_tablespaces where tablespace_name = UPPER(:tbs_name))/1024/1024) Mb_free,
'free'          owner,
'free'          segment_name,
null            partition_name,
null            segment_type,
bytes
from dba_free_space
where tablespace_name = UPPER(:tbs_name)
order
by 3 desc) sq1
order
by sq1.block_id desc) sq2
where sq2.state like 'occupied'
group
by sq2.tablespace_name
;

Producing a line of text to paste and run (screenshot from a different tablespaces which explains why the sizes do not make sense)

coalesce3

Finally the key thing is to ensure there are no invalid indexes. I run both these scripts quite frequently

select 'alter index '||owner||'.'||index_name||' rebuild ;' from dba_indexes where status = 'UNUSABLE';

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';'
from dba_ind_partitions where status not in ('USABLE','N/A');

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: