OraFAQ Forum: Server Administration » Unable to calculate "Datafile Shrink possible" size

Home » RDBMS Server » Server Administration » Unable to calculate "Datafile Shrink possible" size (oracle 11.2.0.1.0 linux 2.6)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Unable to calculate "Datafile Shrink possible" size [message #517757]

Thu, 28 July 2011 00:12

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

Michel Cadot
Messages: 68645
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

Report message to a moderator

Previous Topic:	database link usage
Next Topic:	Resource Manager: instance caging

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 02:09:05 CDT 2024