Home > tips > Tablespace defragmentation by reorg

Tablespace defragmentation by reorg


I had a space porblem on data disk and after analysing I found fragmentation on a 128 GB tablespace.
The database objects index, tables, even partitioned and unparititoned are occupying only 10 G of tablespace.
In order to gain space I used Quest Space Manager with Live Reorg.
First created a newer tablespace and move the objects.
It is also interesting that some of partitions of tables were on different tablespaces. In order to make th reorg to work, I need to move the partitions residing on other tablespaces to new tablespace.
Thus the granularity of moving is table by table not partitions. I filtered the objects by giving the OWNER and OBJECT_NAME (in my case it was table name) to Quest Space Manager.
It took around 10 hours and the result is impressive, now I have no objects in the tablespace.

Eventually, I dropped the former tablespace with INCLUDING CONTENTS and DATAFILES option.

Surprise! HPUX OS did not show my earned disk space.

[oracle@oppa08]:/home/oracle> bdf
Filesystem   kbytes   used   avail   %used    Mounted on
/dev/vg01/lvoradata01  568590336 528623358 37469105   93%      /export/oradata01
After googling, I found that I am not alone:)

It was because Oracle was using those datafiles, and they are open, which OS can not give the space back. Bouncing the database worked.

[oracle@oppa08]:/home/oracle> bdf
Filesystem   kbytes   used   avail   %used    Mounted on
/dev/vg01/lvoradata01   568590336    405011080    153355613   73%      /export/oradata01

Advertisements
Categories: tips Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

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

Don Charisma

because anything is possible with Charisma

Carol no Mundo!

Aventuras de uma intercambista a trabalho pelo mundo!

Blog do PHP

Oracle DBA for rookies!

nimaidba

Welcome to the world of Oracle with me....

Tech

News and reviews from the world of gadgets, gear, apps and the web

WordPress.com

WordPress.com is the best place for your personal blog or business site.

Gurcan Orhan's ODI and DWH Blog

Some words about Oracle Data Integrator and Data Warehousing.

%d bloggers like this: