After SAP Data Archiving, must have Table Reorg ?

[Former Member]

Dear all,

My SAP consultant said, after having SAP Data Archiving, we have to have Table Reorg so that Oracle would release the free hard disk space to us.

But my question is :

I found our Oracle still released the free tablespace to us even we hadn't done any Table Reorg after Data Archiving.

So, if we have had Data Archive, is it necessary to have Table Reorg otherwise Oracle will NEVER release its free tablespace to us ?

Or, Table Reorg is just "Nice to have", that means : if no Table Reorg, Oracle will still release its free tablespace to us ?

Which one is correct ?

Can you tell me ?

Thanks a lot.

[Former Member · Accepted solution]

Hi,

Perform database reorganization only when necessary.

Archiving is often followed by database reorganization to reclaim the disk space freed by archiving. Since reorganization requires system downtime, consider carefully whether this is really necessary. For example, if a business application generates 100,000 business documents a week and they are archived on a weekly basis, nothing is gained by reorganizing after each archiving session, because the released disk space is reused every week. However, it is advisable to perform a database reorganization if the database index tables are highly fragmented.

Hope this answers your question.

Regards,

Sam

[Former Member · Accepted solution]

Thanks for your reply.

But I just want to know :

After data archiving, if I don't do anything, will SAP Oracle release its free tablespace automatically ?

Or we have to do table reorg manually, otherwise, SAP will not relase its free tablespace ?

I ask this question because our SAP developers are doing data archiving from time to time and our SAP used disk space sometimes will drop by several GB, I guess these were due to data archive but I am not sure whether I am correct as our SAP consultant gave me an impression that we had to do table reorg otherwise SAP will never release its free space.

What do you think ?

[Former Member · Accepted solution]

Hi,

After the archiving db releases the space,ie it brings down the HWM(High water mark) ,but the space is still assigned to the database.

If we do a reorg(a reorg is basically an Export/Import Process) we can release the space from the OS level as well.For example if your sapdata04 Filesystem usage is 100gb; it will remain 100GB after the archival,but if you do a reorg your Filesystem size can drop to 40GB(just an example).

Regards,

sam

[Former Member · Accepted solution]

Thanks.

But why my SAP free tablespace (DB02, "Space statistics", then double click a tablespace) drops some times, were these due to data archiving (I didn't do any table reorg) ?

[Former Member · Accepted solution]

Yes that is what i said, you can reclaim the space at the database level after the archive(ie why you see drops at the table space level) .If you wan to reclaim it at the os level as well then you need to do a re-org.

Hope its clear...

Regards,

sam

[Former Member · Accepted solution]

Thanks

[fidel_vales · Accepted solution]

Hello,

I think there are some confusion and not correct information here.

After you archive data (delete data from the tables) the space is NOT released.

To release it you need to reorganize the tables affected by the reorganization (not the database)

This reorganization does not mean downtime. It will depend on your Oracle version and the table affected.

If you are in a current oracle version and the table affected does not have a long raw field, you can do the reorganization online.

If you were on 10g and you want to migrate from long raw to lob, then you could also do the reorganization online.

If the database indexes are highly fragmented, do not reorganize the database, do it only for the indexes (I think it is a typo from Sam, but better clarify it)

> After data archiving, if I don't do anything, will SAP Oracle release its free tablespace automatically ?

Not sure what you mean here, oracle never release tablespaces. I assume that you mean if oracle will release the free space automatically.

The answer already given is "NO"

> After the archiving db releases the space,ie it brings down the HWM(High water mark) ,but the space is still assigned to the database.

Not correct. Archiving is deleting rows from the table.

This activity will not bring down the HWM.

In normal DB operations, the high water mark only moves upwards, not downwards. to move it downward you have to truncate the table (not good in this case) or shrink it.

Check the oracle documentation [Space Reclamation|http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/mgmt_db.htm#sthref2240]

and sap note 910389 FAQ: Oracle Segment Shrinking

[Former Member · Accepted solution]

Hello.

When I archive, is the space delete reused if I do not execute reorganization?

Thanks.

Mario