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 Read the rest of this entry »