RE: seg re-org
- From: Abhijeet Rajurkar <Abhijeet.Rajurkar@xxxxxxxxxx>
- To: "Noor.Mulla@xxxxxx" <Noor.Mulla@xxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 14 Jun 2010 16:50:35 +0530
Here is the query I use…. DBMS_SPACE gives the recommendations as well as
commands for the same in the columns c1,c2 and c3.
select
CASE WHEN upper(substr(recommendations,1,20)) like '%SHRINK%' THEN C1
WHEN upper(substr(recommendations,1,20)) like '%COMPACT%' THEN C2
WHEN upper(substr(recommendations,1,20)) like '%ENABLE ROW%' THEN C3
END ||' ;' cmd
from table(dbms_space.asa_recommendations());
Kind Regards
Abhijeet Rajurkar
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Noor Mulla
Sent: 10 June 2010 12:04
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-db-l@xxxxxxxxxxxxxxxxxxxx
Subject: seg re-org
Hi,
I have this below sql…..
SELECT 'ALTER '||segment_type||'
'||segment_owner||'.'||rpad(segment_name,35)||' SHRINK SPACE;'
from
(
select * from table(dbms_space.asa_recommendations())
WHERE (100*reclaimable_space/allocated_space > 10) AND segment_owner like 'D%'
and segment_type in ('TABLE','INDEX')
order by reclaimable_space/allocated_space desc
)
where rownum<=10;
======================================================================================
The above query is executing fine.
Only hitch is that an Index-Organized Table which has segment_type='INDEX' does
not use ALTER INDEX,
but uses ALTER TABLE instead, then what to do if segment is an IOT.
Can we join dba_indexes or dba_tables and use a DECODE statement in outer
select...as shown below.
Can we incorporate this in the above sql and check iot_type and execute.
eg:
SELECT 'ALTER'||decode(iot_type,null, segment_name,'TABLE') ||...........
Help would be appreciated...
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.
-----------------------------------------------------------------------------------------------------------------------
MASTEK LTD.
Mastek is in NASSCOM's 'India Top 20' Software Service Exporters List.
In the US, we're called MAJESCOMASTEK
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Opinions expressed in this e-mail are those of the individual and not that of
Mastek Limited, unless specifically indicated to that effect. Mastek Limited
does not accept any responsibility or liability for it. This e-mail and
attachments (if any) transmitted with it are confidential and/or privileged and
solely for the use of the intended person or entity to which it is addressed.
Any review, re-transmission, dissemination or other use of or taking of any
action in reliance upon this information by persons or entities other than the
intended recipient is prohibited. This e-mail and its attachments have been
scanned for the presence of computer viruses. It is the responsibility of the
recipient to run the virus check on e-mails and attachments before opening
them. If you have received this e-mail in error, kindly delete this e-mail from
desktop and server.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
��i��0���zX���+�� n��{�+i�^
Other related posts:
- » seg re-org- Noor Mulla
- » RE: seg re-org - Abhijeet Rajurkar
