| Extents [message #61505] |
Thu, 29 April 2004 14:36  |
Monica
Messages: 37
Registered: February 2000
|
Member |
|
|
I have a tablespace that runs out of extents about every two weeks when the data itself is not growing.
This is my process:
Load data into table1 using sqlloader, about 20 million records.
delete records from table1 based on criteria in the select statement (sysdate-0.4) but amounts to close to 20 million.
I repeat this process about 4 times a day.
I cannot drop the table each time.
I end up having to increase the extents about every two weeks which solves the problem.
Thanks for you help.
|
|
|
|
| Re: Extents [message #61506 is a reply to message #61505] |
Thu, 29 April 2004 20:30  |
IA
Messages: 91
Registered: March 2004
|
Member |
|
|
Hi Monica,
Basically, when you perform a 'delete' operation, the extents for this table are still allocated.
Run this command after each delete operation and you should be fine.
ALTER TABLE table1 DEALLOCATE UNUSED;
Hope this helps ... IA
|
|
|
|
| Re: Extents [message #61508 is a reply to message #61505] |
Thu, 29 April 2004 20:53 |
Anatol Ciolac
Messages: 113
Registered: December 2003
|
Senior Member |
|
|
Or run this command after delete:
SQL>alter table <tbl_name> move;
During the execution of this command table will be available on read_only mode. After this operation all indexes on this table will be invalid an must to be rebuilded:
SQL>alter index <index_name> rebulid;
|
|
|
|
| Re: Extents [message #61512 is a reply to message #61506] |
Thu, 29 April 2004 22:46 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
"ALTER TABLE table1 DEALLOCATE UNUSED" will only deallocate the unused blocks which are above high water mark. And after deleting data using "delete" the high water mark doesn't decrease, so i think it would not help much.
Yeah Its true that we can use
ALTER TABLE MOVE command to reinitialize the extents. And it will work fine.
Moreover Monica, if u r deleting all the rows from ur table than its better to use TRUNCATE instead of DELETE.
Daljit Singh
|
|
|
|
| Re: Extents [message #61514 is a reply to message #61506] |
Fri, 30 April 2004 00:23 |
Ronald
Messages: 15
Registered: October 2001
|
Junior Member |
|
|
Hi
IA is right, to verify you can check a high watermark.
ALTER TABLE table1 DEALLOCATE UNUSED;
|
|
|
|
| Re: Extents [message #61516 is a reply to message #61514] |
Fri, 30 April 2004 02:37 |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
I am dam sure that it will just free the extents above the high water mark, and delete statement will not reset the high water mark, so the data will be deleted but the HWM will still be there and no free space will be free up.
Now its ur turn to explain how the space which is free up using delete can be deallocated from the segment using
ALTER TABLE table1 DEALLOCATE UNUSED;
waiting for ur reply
Daljit Singh
|
|
|
|
| Re: Extents [message #61532 is a reply to message #61512] |
Mon, 03 May 2004 17:16 |
IA
Messages: 91
Registered: March 2004
|
Member |
|
|
Hi,
Actually you are correct. Apologies Monica ... performing an alter tablespace move is your best option.
Thanks ... IA
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
