Database Cleaning

[log_ora]

Hi Friends...

I am working on a Production System. And I have to do some database cleaning work on daily basis.

I am deleting 5 Lacks Records from a table on daily basis, But whenever I am deleting records and checking the tablespace, there is no improvement in the free space. It is as it is.

How space of the tablespace will be increasing even deleting rows also.

Yours responce in this regard is appreciated...

Thanks

Sandeep

[tangqiang]

Hi, Sandeep

Deleting rows from table will not release space to tablespace,

you must re-create the table as following:

1) export table
2) drop table
3) re-create table with smaller storage parameter
4) import data



good luck

Tang Qiang

[Dave_A]

Deleting records from a table does not free up space to be used by other Oracle tables. The space remains allocated to that table.

When the amount of data deleted from a particular block is such that the total amount of space used in the block falls below the value of the PCT_USED parameter, the block is placed on the free list for the table that owns it (i.e. fresh data can be inserted in that block).

Your best bet for freeing up space from the table, and making it available for use by other database objects is to take a copy of the table data, truncate the table and repopulate it from the copy. Bear in mind that this process is not a minor undertaking, so you had better be freeing a significant amount of space to make it worthwhile.

It is probably worth considering the idea of partitioning the table in question - it is possible to drop a partition from a partitioned table, thus freeing up the space.

HTH

David.

[log_ora]

Dear David

Thanks ..

Presently I am having 5 GB space in the Tablespace and I want to increase space in that tablespace without adding any data file.

Is there any way to increase space of that tablespace by deleting record from tables.

Thanks once again.

Sandeep

[sreddy]

Originally posted by Dave_A

Your best bet for freeing up space from the table, and making it available for use by other database objects is to take a copy of the table data, truncate the table and repopulate it from the copy. Bear in mind that this process is not a minor undertaking, so you had better be freeing a significant amount of space to make it worthwhile.

Before populating the table from the copy make sure you have reset the PCT_USED parameter per your requirement.

[sreddy]

Not sure, you have this issue just with the problem of PCTUSED parametrer OR even with the fragmentation of tablespace. Solution is REORG which is real pain as its big.

Couple of options:

Truncate table and coalesce the tablespaces, See that helps little. Not of much helpful if you have set your PCTUSED parameter.

What dave suggested, which is nothing but, reoraganisin the objects with redefined storage clauses.

[dragon99]

What I am doing is to do an exp of database and recreate the datafiles with new parameters.and imp the database. And then, for those dynamically growing tables, watch for the normal amount of data in each of them, whenever the data grows above the average amount, I will let the developers( in my case) to purge their data to the average level. In this way, the database size grows in a significantly slow speed.