seg re-org

From: Noor Mulla <Noor.Mulla_at_hcl.in>
Date: Thu, 10 Jun 2010 12:04:15 +0530
Message-ID: <9A0AE9C5280E604E97A3FA37030D302C3F0DB50A17_at_CHN-HCLT-EVS08.HCLT.CORP.HCL.IN>

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<http://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.

---

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jun 10 2010 - 01:34:15 CDT