Unable to calculate "Datafile Shrink possible" size [message #517757] |
Thu, 28 July 2011 00:12 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
kesavansundaram
Messages: 183 Registered: October 2007 Location: MUMBAI
|
Senior Member |
|
|
Dear Sir/Madam,
I need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now).
my tablespace is having 11.74 gb free space now. it has 3 datafile.
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992
TABLESPACE_NAME FILE_ID FILE_NAME Size(MB)
------------------ ---------- ------------------------------------------------------- ----------
CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688
CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480
CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512 I used below script to find out HWM in order to resize the datafile.
db_block_size is 16KB.
select a.file_id, file_name, hwm, blocks total_blocks,
((blocks-hwm+1)*16384)/(1024*1024) "shrinkage_possible(MB)"
from dba_data_files a,
( select file_id, max(block_id+blocks) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id
and a.file_id= <datafile_id>;
FILE_ID FILE_NAME HWM TOTAL_BLOCKS shrinkage_possible(MB)
---------- ------------------------------------------------------- ---------- ------------ ----------------------
334 +DATA/dedw/datafile/cfc_data.4473.757566557 1310720 1310720 .015625
FILE_ID FILE_NAME HWM TOTAL_BLOCKS shrinkage_possible(MB)
---------- ------------------------------------------------------- ---------- ------------ ----------------------
71 +DATA/dedw/datafile/cfc_data.4074.731085435 4146688 4194302 743.984375
FILE_ID FILE_NAME HWM TOTAL_BLOCKS shrinkage_possible(MB)
---------- ------------------------------------------------------- ---------- ------------ ----------------------
1710 +DATA/dedw/datafile/cfc_data.2012.728095695 4110085 4128768 291.9375 But "shrink possible(mb)" is very less..but at tablespace level, free size shows around 11.74 gb..
Please guide me to find out the discrepancy....i need to remove this extra size ( 11gb ) from this tablesapce now.
note: in TOAD, we have an option, that is "Minimum size" button against each datafile.. Could anyone provide me the SQL
which is running behind when we press this button from TOAD ?
Thank you
kesavan
[Updated on: Thu, 28 July 2011 00:48] by Moderator Report message to a moderator
|
|
|
Re: Unable to calculate "Datafile Shrink possible" size [message #517764 is a reply to message #517757] |
Thu, 28 July 2011 00:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:Please guide me to find out the discrepancy
Without the first query, it is not possible.
Quote:note: in TOAD, we have an option, that is "Minimum size" button against each datafile.. Could anyone provide me the SQL which is running behind when we press this button from TOAD ?
NEVER trust a tool that does not provide its underlying queries.
Regards
Michel
|
|
|