TST03 Table grows Abonormally high

[Former Member]

Hi All,

Recently during the payroll run we had to add datafiles total 20GB(4GB

x 5) in a single day. Later we found out that the abnormal high growth

is due to Spool request ( Table TST03) generated by Payroll. We had

deleted spool request of 12GB size. Inspite of this and even after running consistency check, deleting OLD spool and Updating Statistics, the used space is not released to tablespace. Presently, this table has allocated space of 41GB but actualy has very little data.

We may consider of changing parameter RSPO/STORE_LOCATION to G, so that in future spool will be created in file system.

But how do we get back the release space to tablespace ? As per the Note : 541538 online reorganization of Table TST03 will not be possible because it contains a coloum of type LONG RAW.

Can we consider to drop this table and recreate by SQL script ?

Has anybody tried this method ?

Thanks & regards.

BASIS TEAM.

OIL INDIA LTD.

[Former Member · Accepted solution]

Hi,

used space is not released to tablespace becoz of high Water Mark.

Ref http://help.sap.com/saphelp_erp2005vp/helpdata/en/58/6bec38c9fa7e44b7f2163905863575/frameset.htm.

http://help.sap.com/saphelp_erp2005vp/helpdata/en/32/0d0c888839164ba4245b3ff7969c59/frameset.htm

Note 646681 - Reorganizing tables with BRSPACE

Regards

Vinod

[Former Member · Accepted solution]

Hi Vinod,

Thanks for the reply.

Yes because of the HWM spaces is not released. But this HWM is set after analyzing table. It is not happening.

Also, PCT_INCREASE is NULL not even 0 may be because of this, table could not be coalesces.

We had checked Note 646681. Asking for offline Reorg of table.

We would have prefer to do it in SQLPLUS by creating a new table from TST03 and then drop TST03 and re-create it with indexes and related objects(Triggers etc.). This TST03 table is spool table.

Any one has experience(good or bad) with this kind of Re-org of table.

Thanks

BASIS TEAM

OIL INDIA LTD.

[former_member204746 · Accepted solution]

you can drop or truncate TST03 but will obviously will lose all logs. A consistency check will be needed. After this, check how much space is being used by PSAPCLUD.

offline reorganization of PSAPCLUD took 8 hours on my system with tablespace usage of 45Gb.

[Former Member · Accepted solution]

Dear Team,

The ANALYZE TABLE command only

identifies the number of 'ever' used blocks or the high water mark

for the table.TRUNCATE deallocates the space from the deleted rows.

The PCT_INCREASE parameter is used by Oracle to determine the size of the next extent to be allocated to a database object. If this is set to 50, which is often the default, and the database has to extend the object, it chooses 150% of the last or the initial extent. That means an exponential growth of needed data and index space in case of having big tables with lots of data. For example, if you have an initial size of 100k and pct_increase on value 50, for the sixteenth extent the database needs more than 10 MB and for the 22nd extent more than 100 MB, even if you need just one byte more!

The PCT_INCREASE value has an effect only when the database has Dictionary Managed Tablespaces.When PCT_INCREASE value is set to "0" on the Tablespace, SMON will stop coalescing free extents. However, usually, this will not have much negative impact .

Export table and import the same Even u can impoprt without any data means only structure.

Have a look on links given by me which explains every step.

I know reading is diffclt direct ans are appr'td.

There are no bad results for reorg of tables.

Vinod

[Former Member]

1) The table TST03 was getting to be as high as 81GBs in our company.

We went to TemSe (rspo/store location = G) and then truncated the table TST03.

[Former Member]

Hi,

Would you please eloborate how the table TST03 was truncated ? Is it from SQLPLUS ? Did u use DROP STORAGE Option ? Was the space released to tablespaces ? Does SAP suggest this method of truncating table ? We understand by truncating all record will go.

We are also considering changing spool location to file system (rspo/store = G ).

Thanks & Regards.

BASIS TEAM

OIL INDIA LTD.

[Former Member]

TEam,

Ans to ur questions

1:sql>TRUNCATE table <table_name> DROP STORAGE;

By default drops storage even if DROP STORAGE is not specified.

2.yes this is from sqlplus

3.A truncate moves the High Water Mark of the table back to zero.no rollback of the transaction is possible.

4.SAP does not recmd this.

5.Yes table will become empty.

SAP recmds http://help.sap.com/saphelp_nw04s/helpdata/en/d9/4a8f9c51ea11d189570000e829fbbd/content.htm

http://help.sap.com/saphelp_47x200/helpdata/en/d9/4a98ba51ea11d189570000e829fbbd/frameset.htm

Vinod