Thursday, November 26, 2009

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

Windows (powershell) counterparts of Linux commands

You may find Windows mostly powershell equivalent of frequently used Linux commands here. I will update this post, with newer ones by the ti...