Release Space at file system level

[former_member190251]

Hello All,

We did Online DB table reorg in the production system and it reduced 152GB at table level but not refelected in the os level. During this

activity we added 300GB to the tablespaces to perform the DB reorg and

increased the DB size from 3.4 TB to 4 TB

Our intention is to reduce the space at file system level.

Please confirm what steps we need to perform in order to release space

at filesystem level

1) By doing offline reorg at Tablespace level

2) By doing export/import database using system copy methods

3) By doing shrinking the database after completing online table rerog

(online/offline)

[janos_mucsi-besze]

Hi Subbu,

Just perform an OFFLINE tablespace reorg with BRSPACE. This tool makes everything automatically, so you don't need to do anything manually.

Check SAP note:

646681 Reorganization of tables with BRSPACE

Regards,

János

[Former Member]

Just perform an OFFLINE tablespace reorg with BRSPACE

Why an offline reorg is required if everything can be handled by an Online reorg?

Regards,

Nick Loy

[janos_mucsi-besze]

Hi Nick,

If I am not wrong, during an offline tablespace reorg, the datafiles are recreated with correspondingly smaller size.
Wher in case of an online, recreating the datafiles are not possible since the datafiles are still in use.

Regards,

János

[Former Member]

Hi Janos,

This is customer's Production instance and his intention was to claim the free space.

Getting required downtime for offline reorg is always a challenge (at least for production instances) & You can define the size by creating new TS and then move the data from old to new TS (with reorg ON) so that everything get migrated to new TS (smaller one where you defined the size).

Regards,

Nick Loy

[janos_mucsi-besze]

Yes, sure. You are right. The only prerequisite to know the basic size of the new tablespace. To avoid to much extent allocation for the tablespaces.

Regards,

János

[Former Member]

Hi,

I would perform the tablespace migration (reorg+migration) which converts the data from old to new tablespace. Post completion of activity you can just drop the old tablespace which deletes all the datafiles and contents. So you can release the space from OS.

For a better space management, create new TSs in separate storage/FSs, so that post completion of your activity you can directly release all the old storage from server.

Regards,

Nick Loy

[ACE-SAP]

Hi

Reorg first purpose should be to defragment the DB in order to have faster access to data.

If you did not perform some archving it won't be very useful to reclaim space as it will be by the end consumed by the DB growth.

You could use the here under script to check if some datafiles could be shrinked.

If you are in 11g and migrate from 10g it can be useful to perform a system copy as you could save some more space with deferred segment creation.

Regards

1583303 - Deferred Segment Creation

Set pages 200 lines 156
Select 'ALTER database datafile ''' || File_name || ''' resize ' || smallest_Mb || ' M;' FROM 
(  SELECT    file_name as File_name, ceil( (nvl(hwm,1)*C.VALUE)/1024/1024 ) as smallest_Mb  
 FROM dba_data_files a,
       (select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) b,
       (select value from v$parameter where name = 'db_block_size') C
 WHERE a.file_id = b.file_id(+) AND a.file_name in (select FILE_NAME from dba_data_files)
) WHERE 1=1;

[Former Member]

during an online re-org you have the option to specify new tablespace in brspace

1. create new tablespace PSAP<SID>new,

2. re-org all the tables from old tablespace say PSAP<SID> to a new tablespace PSAP<SID>new,

3. drop the old tablespace & datafiles ### here you will re-claim the space ###

4. rename the PSAP<SID>new back to PSAP<SID>

[Reagan]

Online table reorg will not reduce the space consumed by the database at the file system level as it will not reduce the size of the datafiles.

In order to reclaim the space used by the database you will need to do database reorganization.

You will need to export the tablespace

Drop the tablespace

Recreate the tablespace

Import the tablespace dump.

Regards

RB

[former_member188883]

Hi Subbu,

As your database size is large, you should be using export/import method to reduce space on the file system level.

Hope this helps.

Regards,

Deepak Kori