Reorganising Table space after client delete BRTOOLS/BRSPACE

[Former Member]

Hi All,

I am looking to reorganise the SAP tablespaces after I have deleted a client as part of my learning excercise(I am running out of disk space on the harddisk). As you can imagine I am fairly new to this field. All help appreciated.

The version of SAP is ECC 6.0 with ABAP 702, BASIS 702.

Look forward to hearing from the SAPers.

Thanks.

Manish

[paul_power]

Hi Manish,

As guided in note 35952, when the clients are deleted the blocks are
allocated as free, however these are not yet visible as freespace yet.
You will need to perform a reorganization to resolve this.
Regarding reorganization, please kindly check the following notes:

Note 541538 - FAQ: Reorganization
Note 646681 - Reorganizing tables with BRSPACE
Note 53062 - Database reorganization and data archiving

See also SAP Note 821687 - FAQ: Space utilization and fragmentation in Oracle

for details.

Let us know if questions persist.

Regards,

Paul

[Former Member]

Hi Paul,

Thanks for your immediate reply. As mentioned earlier, I am new to this. I have gone through all the notes prior to posting the question and still could not figure out what to really do. I am in BRSPACE, but don't know what options to choose. Alternatively I have also tried to see if there is a command line option and could not workout what that should be either.

I have a question, rather a few questions:

In order to reorganise a tablespace is it mandatory to create a new one and copy all tables there?

And then delete the old tablespace?

In such a case, how does SAP then connect to this new tablespace? Do we need to do changes somewhere?

Apologies if these sound like dumb questions, but this shows how much I really know.:)

Manish

[Former Member]

Hi Manish,

The OSS note 646681 explains it all however assuming you have a decent understanding of oracle. First you need to decide whether this is going be an online or offline reorg. All tables or select few tables. LOB tables need special approach for online reorgs.Based on this work out a strategy. BRSPACE will come in the end.

Regards.

Ruchit Khushu

[Former Member]

Thanks for the reply Ruchit. Unfortunately, I don't have that detailed understanding of Oracle.

As there is no one using the DB really, I can do Online reorg. Because, I have deleted a client, which would normally impact all tables, I am guessing, I would like to reorg all tables.

When you say LOB tables need special approach, what approach is suggested ?

Look forward to hearing from you and thanks for your time.

Manish

[Former Member]

Hello Manish,

Even though unsolicited I would advise you to shore up your oracle knowledge before you getting into complicated activity like reorg which need good technical know-how as well good planning. It is good to learn such things but your basics need to be reallyh good.

For online re-org you need to have free space equal to the object(s) being reorged. So if we are talking about an object showing up like 2 GB then you need additional 2 GB for online re-org. So keep that in mind.

For LOBs I actually meant the tables with "LONG field". You need to first convert such fields to "LOB" and then do an online re-org.. Offline reorg has no such conversion requirements.

You need to thoroughly check all the notes given by Paul and first work out your scenarios and then proceed with reorg.

As for brspace commands OSS note 541538  gives all commands for various scenarios.. Just you need to which one is yours

Regards.

Ruchit.

[paul_power]

Hello Manish,

One quick point. You mentioned that there is no one using the database at present.

In that case, an offline reorg would complete faster as it would have all the locks it needs on tables during reorg without worrying about users being acitve and changing the tables being reorged.

Please go through the admin guides and also the notes provided and experiment on a sandbox system to get a feel for the process. Do feel free to revert back with any open issues.

Regards,

Paul