-
Tablespace size releasing
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
Nwcomer
Student
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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
Nwcomer
Student
-
copy to another temporary table with records you want, truncate the original table and insert back the good data
-
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
Nwcomer
Student
-
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
-
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
sumit
-
huh what are you saying, you just cut and paste my post....?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|