Oracle DBA – A lifelong learning experience

  • Meta

  • Categories

  • Blog Stats

    • 1,634,354 hits
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 376 other followers

Posts Tagged ‘coalesce’

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


Posted in Oracle | Tagged: , , , , | Leave a Comment »