| DEALLOCATE UNUSED KEEP In Oracle 9i [message #60615] |
Thu, 19 February 2004 08:30  |
Vinny75
Messages: 44
Registered: October 2003
|
Member |
|
|
Hello,
I would like to understand more on how Oracle 9i Locally managed tbs option. I have several tables on a locally managed tbs. After truncating with drop storage option, I checked the space usage and found out that the initial extent was not adjusted to tbs default of 64K. Instead it continued to remain as it was originally before the table was truncated. This means there is a lot of wasted allocations sitting as initial extent.Is this acceptable?
If thats the case, my tablespace is showing a usage that does not accurately reflect the actual space consumption by the individual objects.
I was able to reclaim the unused space after truncation by using alter table DEALLOCATE UNUSED KEEP 1K command. My question is why am even required to do this? My understanding was that in 9i these issues were handled by oracle automatically. Another thing I noticed is that If i tried to run DEALLOCATE UNUSED without the KEEP option, it does not really adjust the initial extent on an empty table. My only option was to set the keep value.
By deallocating all emty tables to 1k I saved nearly 4 Gig worth of datafile size on my tablespaces. I am sure there is something in 9i which should address this.
Can somenoe please explain?
|
|
|
|
| Re: DEALLOCATE UNUSED KEEP In Oracle 9i [message #60633 is a reply to message #60615] |
Sat, 21 February 2004 13:46  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Inorder to deallocate space below the initial_Extent of the table(but not below the uniform extent size of the tablespace), you can use that (ie deallocate unused keep ) . When you specify the KEEP option,the intial extent size tries to shrink down to the keep size.
You can also move the table to change the initial_extent and release the space.
thiru@9.2.0:SQL>truncate table t2 drop storage;
Table truncated.
thiru@9.2.0:SQL>alter table t2 deallocate unused keep 512k;
Table altered.
thiru@9.2.0:SQL>analyze table t2 compute statistics;
Table analyzed.
thiru@9.2.0:SQL>select table_name,blocks,empty_blocks,initial_Extent from user_tables where table_name='T2';
TABLE_NAME BLOCKS EMPTY_BLOCKS INITIAL_EXTENT
------------------------------ ---------- ------------ --------------
T2 0 143 589824
thiru@9.2.0:SQL>select segment_name,bytes,blocks from user_Segments where segment_name='T2';
SEGMENT_NAME BYTES BLOCKS
------------------------------ ---------- ----------
T2 589824 144
thiru@9.2.0:SQL>alter table t2 move storage(initial 64k);
Table altered.
thiru@9.2.0:SQL>analyze table t2 compute statistics;
Table analyzed.
thiru@9.2.0:SQL>select table_name,blocks,empty_blocks,initial_Extent from user_tables where table_name='T2';
TABLE_NAME BLOCKS EMPTY_BLOCKS INITIAL_EXTENT
------------------------------ ---------- ------------ --------------
T2 0 15 65536
thiru@9.2.0:SQL>select segment_name,bytes,blocks from user_Segments where segment_name='T2';
SEGMENT_NAME BYTES BLOCKS
------------------------------ ---------- ----------
T2 65536 16
-Thiru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
