BR Tools Drop a table

[Former Member]

Hi

I have scheduled a job to delete the records in the table TST03 because it unusually went to occupy 89GB of data...

After I schedule this job to delete spools older than 2 weeks I can estimate that the total space occupied by TST03 at anytime will be not be higher than 9GB... and the 80GB remains wasted unless and until I do a offline re-organization of the table...

I have seen the following SAP recommendation in the database management guide

Saving the spool data in the file system: It is possible to set the spooler so that the data is not saved in the table TST03, but in files in the file system. To do this, set the profile value for u201Crspo/store locationu201D from u201Cdbu201D to u201CGu201D (see SAP Note 10551, release-independent). This option will improve performance during write and read operations for spool data, because the system is generally faster than the database. The disadvantage of this option is that the data will not be backed up along with regular database backups.

After I move this table to file system,

Can I drop the table from the database

Will the 89GB space be available to table space after the drop of the table

And lastly most important - 'How much time does it take to drop a table'...

[stefan_koehler · Accepted solution]

Hello Grame,

> Can I drop the table from the database

Officially not - just check sapnote #48400 how to delete data from this table. After you have deleted the data you should reorganize the table and indexes. This is the official way. Sometimes if this takes too long i am opening a sap call and asking for legitimation of such actions. Also dropping is not the best option, because of the structure is also gone (you need to create the table again) - just truncate the table.

> Will the 89GB space be available to table space after the drop of the table

Yes

> And lastly most important - 'How much time does it take to drop a table'...

Just a few seconds, if no locks are on this table - the same with TRUNCATE. These are DDL statements which are really fast.

Regards

Stefan

[Former Member · Accepted solution]

Thank you