Unable to release space from table

[Former Member]

Hi all,

We are unable to release space from a table called TST03 even after deletion of records.

Followings are the information.

Database : 9.2

Table Name : TST03

Tablespace : LOCALY MANAGED.

Previously there were lots of rows.

At Present only 9 No of Rows.

Space allocated : 41 GB

PCT_INCREASE : Null

One of the column is of LONG RAW type.

Since the table is in LMTS, we were expecting that the space allocated will be released automaticaly after deletion of records.

Now, what are the option left with us to release 41GB of space ?

A. Is there any effect of "Drop storage" option of 'Truncate table' command ?

B. If yes , can i copy all the 9 rows to a new table, then use "Truncate table TST03 drop storage", check if space is released and then copy back all the 9 rows to this table.

C. Do you have any other easy solution apart from export/import ?

D. Checked all the relevant Note(646681,48400,10551), could not find an easy solution. I want to avoid offline export/import option.

Thanks .

Naba J Neog

[Former Member]

Replies from various persons(Mr. Bredman and Willian) are very helpfull and informative.

Their reply Will help lot to decide course of action to solve the problem.

[former_member197668]

Hi All,

By the way, reclaim space from specific single table...

1.how to perform REORG Table with offline option truncate in db6/db2 udb 9.7?

2.how to check spaces of a table has reclaimed enough spaces in db6 database if a reorg table offline ran to completed status?

Regards

Susi

[lbreddemann]

> Hi all,

Hi !

> We are unable to release space from a table called

> TST03 even after deletion of records.

>

> Followings are the information.

> Database : 9.2

> Table Name : TST03

> Tablespace : LOCALY MANAGED.

> Previously there were lots of rows.

> At Present only 9 No of Rows.

> Space allocated : 41 GB

> PCT_INCREASE : Null

> One of the column is of LONG RAW type.

>

> Since the table is in LMTS, we were expecting that

> the space allocated will be released automaticaly

> after deletion of records.

Sorry, but that is not what LMTS is for. Wrong assumption.

MaxDB e.g. returns space immediately - Oracle does not.

> Now, what are the option left with us to release 41GB

> of space ?

> A. Is there any effect of "Drop storage" option of

> 'Truncate table' command ?

Yes, the effect is, that after the truncate the table is empty and only one extent is allocated - the rest is returned to freespace. That's the DEFAULT behaviour of TRUNCATE TABLE.

> B. If yes , can i copy all the 9 rows to a new table,

> then use "Truncate table TST03 drop storage", check

> if space is released and then copy back all the 9

> rows to this table.

Nope - you would have to copy the LONG RAW columns as well and this cannot be done easily from sqlplus.

> C. Do you have any other easy solution apart from

> export/import ?

Nope again - you'd have to use this offline reorganisation as long as you're not on Oracle 10g and the long raw fields got converted into LOBs. With 10g you might also use the SHRINK table command. But with Oracle 9i - sorry: exp and imp (will be pretty fast for 9 rows...)

> D. Checked all the relevant Note(646681,48400,10551),

> could not find an easy solution. I want to avoid

> offline export/import option.

Sorry - no way to avoid it and still get the free space back.

Anyhow you might want to take actions to prevent this situtation from reoccuring.

These kinds of questions are covered in the notes

<a href="http://service.sap.com/sap/support/notes/48400">#48400</a>

<a href="http://service.sap.com/sap/support/notes/66290">#66290</a>

>

> Thanks .

> Naba J Neog

You're welcome.

Lars

[lbreddemann]

upps... the second note should have been <a href="http://service.sap.com/sap/support/notes/666290">#666290</a>

Sorry about that.

KR Lars

[Former Member]

Hi Lars Breddemann ,

Thanks for your reply point by point.

Against the question

> A. Is there any effect of "Drop storage" option of 'Truncate table' command ?

Your Reply was

Truncate command will release space to the Tablespace.

But your reply to another question, You have suggested Export/Import is only solution.

Why should not I use truncate command (easiest way)? I understand I will lose data but I do not require it. Because we have already moved spool to file system and are not planning to come back again to Database. My main purpose is to get back space from TST03 and make it available to the tablespace so that other growing table use it. I had asked this question to OSS guys, they do not suggest truncate command instead suggested method is export/import from Brtools.

Even If I have to use export/Import Do I need a downtime ? Please consider that table TST03 is no longer being used.

Your reply will be appreciated.

Thanks once again.

Regards.

Basis TEam.

[lbreddemann]

Hi again!

> But your reply to another question, You have

> suggested Export/Import is only solution.

>

> Why should not I use truncate command (easiest way)?

> I understand I will lose data but I do not require

> it. Because we have already moved spool to file

> system and are not planning to come back again to

> Database. My main purpose is to get back space from

> TST03 and make it available to the tablespace so that

> other growing table use it. I had asked this question

> to OSS guys, they do not suggest truncate command

> instead suggested method is export/import from

> Brtools.

Ok, first of all: I am also one of those "OSS guys"

Well the point here is, what is less "dangerous"? The use of export/import with NO danger of loosing data or the truncate command, which cannot even be rolled back? Therefore my collegues where surely right to propose the save way here.

You stated that you've moved over to TemSe and that TST03 does not contain any data needed anymore. Well, than I propose you perform a reorganisation of this spool table (that should remove the leftover contents -> it's completely described in the already mentioned note <a href="http://service.sap,com/sap/support/notes/48400">#48400 - Reorganization of TemSe and Spool</a>).

After SAP has removed correctly all the data from this table it will be save to use TRUNCATE on it.

This way you can be sure that there is nothing lost that should not be deleted somehow.

BTW: when you've truncated the TST03 table you should also rebuild the index of it:

alter index <owner>."TST03~0" rebuild;

> Even If I have to use export/Import Do I need a

> downtime ? Please consider that table TST03 is no

> longer being used.

hmm... technically it MIGHT be possible to do the export/import while SAP is up and running, but I certaintly won't propose this.

>

> Your reply will be appreciated.

>

> Thanks once again.

>

> Regards.

>

> Basis TEam.

KR Lars

[Former Member]

Hi,

Many Thanks once again.

I like the way you reply my query point by point ( not avoiding anything).

I agree with you that Brtools Export/Import is the safest method.

Regarding you being an OSS guy, I found you very helpful and straightforward.

This same question was put to OSS, he seems to not read the question properly.

I had mentioned in OSS msg that My database is Oracle 9i and table contains a LONG RAW. He replied that reorganisation to be done with the Help of

Brtools->Segment Management->Reorganize tables and asked me to read BRSPACE manual. Then I wrote back in next msg that the manual says a table with LONG RAW can not be reorganised with this Brtools Option. In reply to this he wrote that ICNV may be a possibilty but ICNV can not used with TST03( actualy reproducing exactly a part of the note 806554, mentioned a note 96515 which is applicable to Database DB2/AIX). However, later he had mentioned that Brtools Export/Import is the only solution with <u><b>downtime</b></u>.

Please note that If i had followed his first advice I might have face problem. Moreover, since ICNV can not be used for TST03 , no point at all mentioning it.

Thanks once again for your valuable suggestions. We shall implement it within next week.

Best Regards. Keep up the sprit.

BAsis Team

[former_member204746]

online reorg of LONG RAW is not supported.

a few options:

1. export/import of this table

2. convert LONG RAW to BLOB, once this is done, online reorg is possible but performance on this table will drop by 30%

so, this is your call

[Former Member]

After switching to TemSe and with SAP stopped, we also did...

- Truncated TST03 (we didn't care about the old data in TST03)

- Rebuilt index TST03~0

- Checked the consistency in SP12 & SPAD

- Modifed Job SAP_REORG_SPOOL to run RSPO1041 and not RSPO0041. This cleans up cleans up the spool requests.

We did this on 40 servers with no problems.

[lbreddemann]

> Hi,

> Many Thanks once again.

> I like the way you reply my query point by point (

> not avoiding anything).

Avoid something ? What for? never do so...

> I agree with you that Brtools Export/Import is the

> safest method.

>

> Regarding you being an OSS guy, I found you very

> helpful and straightforward.

Wow, thanks for the nice feedback. Really appreciate that.

> This same question was put to OSS, he seems to not

> read the question properly.

[...]

> Please note that If i had followed his first advice I

> might have face problem. Moreover, since ICNV can not

> be used for TST03 , no point at all mentioning it.

Hmm... sad to hear that. Usually I consider most of my collegues to do a rather good job.

Hopefully that one took the opportunity to rethink his answers afterwards.

> Thanks once again for your valuable suggestions. We

> shall implement it within next week.

>

> Best Regards. Keep up the sprit.

>

> BAsis Team

Thanks again for the nice feedback.

Good luck for your implementation (always try first on a test system - just to be sure )

KR Lars

[Former Member]

Hi,

Nice to hear that you could reclaim the space to the tablespace.

Please let me know

1. Did you switch TemSe to OS file System ?

2 What Database and Its Version.

3. Type of Tablespace (LMTS or Dictionery Managed ) where TST03 exist.

Thanks

Basis TEam

[Former Member]

1. Did you switch TemSe to OS file System ? Yes

2 What Database and Its Version. Oracle 9.2.0.6 & 10.2.0.2

3. Type of Tablespace (LMTS or Dictionery Managed ) where TST03 exist. LMTS

[Former Member]

1) Every few months when ousr TST03 table got tobe 85 GBs, we used to stop SAP and then truncate the table TST03. I do not know if you want to do this?

2) Then we switched to TemSe that stores the files in the O/S and not the database. You may want to look into this.

[former_member204746]

problem with HWM (High water mark)

I usually put table that keep on growing/shrinking in their own tablespaces. TST03 is a good candidate for this. move it to PSAPTST03D and its index in PSAPTST03I.