Tablespace size releasing

[newcomer]

Hey Guys,

I have a production table which is a cluster and residing in one tablespace size of 57GB as on today. I run the process to delete records from that table, which is fine but oracle is not releasing space from the tablespce. Can you give me the possible causes of not releasing the space from the tablespace even deleting records from tables.

For example: 1 reason is re verify the PCTUSED value.

Any other cause, which will help me to learn and implement?


Thanks and Regards

[jmodic]

Deleting records wil *never* release any space from the tablespace, i.e. used space and free space in the tablespace will remain unchanged.

Oracle is releasing the space within the table you are deleting from, that is it is making room for new records or updates of the existing records *in the table you are deleting from*, but physicaly that table remains unchanged, it is not shrinking during the delete.

[newcomer]

Thanks you very much.

This table is growing rapidly and that's how it acts. In every 3 months or 6 months I would like to release some space in my database
for that tablespace. But I "CAN NOT TRUNCATE" that table.

I will appreciate if you please suggest me the best solution for that or any other work around.

Regards

[pando]

copy to another temporary table with records you want, truncate the original table and insert back the good data

[newcomer]

Pando,

I also thought about that. But truncating a table of size 53 GB of 100,00,00,000 will be a real pain. And also restoring back too.

What do u think?

One more clarification from you. Which one do you think will be faster.

1. Copy to another temp table.
2. Rename original table and restore it to original.
3. Using sqlplus "Copy as" command.

Regards

[pando]

if that table has 1000 million of rows the proper way to deal with is range partition it using the criteria you use to delete your rows, then instead of deleting you drop partitions

what I do before 9iR2 is

1. insert append to a temp table with data I need
2. disbale the constraints
3. truncate the table
4. set indexes to unusable
5. change table to nologging and run ALTER SESSION SET SKIP_UNUSABLE_INDEXES = true
6. insert the data back to the original table
7. change table to logging
8. drop the temp table
9. enable novalidate PK/UK
10. enable novalidate FKs
11. rebuild the indexes nologging parallel
12. change indexes back to logging and noparallel

[sumit]

Pando,

Thanks a lot again. But I have few queries and info for you.

if that table has 1000 million of rows the proper way to deal with is range partition it using the criteria you use to delete your rows, then instead of deleting you drop partitions
(Table is not partitioned)

what I do before 9iR2 is

1. insert append to a temp table with data I need
(Which Method you would follow)
================================
2. disbale the constraints
3. truncate the table
4. set indexes to unusable
5. change table to nologging and run ALTER SESSION SET SKIP_UNUSABLE_INDEXES = true
6. insert the data back to the original table
7. change table to logging
8. drop the temp table
9. enable novalidate PK/UK
10. enable novalidate FKs
11. rebuild the indexes nologging parallel
12. change indexes back to logging and noparallel

[pando]

huh what are you saying, you just cut and paste my post....?