dbms_space

From: Noor Mulla <Noor.Mulla@xxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 24 May 2010 15:32:53 +0530
Hi DBAs,
Written 2 sql below based on those I want to make changes based on the 
requirement as written below-

I want to create script based on highest percentage of reclaimable_space and 
consider only those which are exceeding this percentage(take 10 percent for now)

Condition should be ...where reclaimable_space/allocated_space > 10 percent and 
order by reclaimable space percent.



set lines 120

set feed off verify off pages 99

ttitle 'Automated Segment Advisor Recommended Segment Reorgs|Large Segments 
Accessed via Full Scan'

select segment_owner,segment_name,round(allocated_space/1024/1024,0) alloc_mb,

round(used_space/1024/1024,0) used_mb, round(reclaimable_space/1024/1024,0) 
reclm_mb,

round(100*reclaimable_space/allocated_space,0) reclm_pct from 
table(dbms_space.asa_recommendations())

  --Testing with reclm_pct:10

where 100*reclaimable_space/allocated_space > &reclm_pct

and used_space/1024/1024 > &used_mb



--Generates report of Segments to Reorg:

select segment_owner,segment_name,ALLOCATED_SPACE/1024/1024  alloc_mb,

USED_SPACE/1024/1024 used_mb,RECLAIMABLE_SPACE/1024/1024 rclm_mb

  ,100*RECLAIMABLE_SPACE/ALLOCATED_SPACE recl_pct ,CHAIN_ROWEXCESS

   from table(dbms_space.asa_recommendations())

  --rclm_pct will be a command line parameter, Using 25% as the default

  where  100*RECLAIMABLE_SPACE/ALLOCATED_SPACE>&rclm_pct

  --segment_name like 'ROW%'

  order by recl_pct desc;



DISCLAIMER:
-----------------------------------------------------------------------------------------------------------------------

The contents of this e-mail and any attachment(s) are confidential and intended 
for the named recipient(s) only. 
It shall not attach any liability on the originator or HCL or its affiliates. 
Any views or opinions presented in 
this email are solely those of the author and may not necessarily reflect the 
opinions of HCL or its affiliates. 
Any form of reproduction, dissemination, copying, disclosure, modification, 
distribution and / or publication of 
this message without the prior written consent of the author of this e-mail is 
strictly prohibited. If you have 
received this email in error please delete it and notify the sender 
immediately. Before opening any mail and 
attachments please check them for viruses and defect.

-----------------------------------------------------------------------------------------------------------------------
dbms_space

Other related posts:
