Re: index rebuild out of space

On Fri, 17 Dec 2004 21:24:19 GMT, "Charles J. Fisher" <cfisher_at_rhadmin.org> wrote:

>I am running out of space trying to rebuild an index under v8174, and I
>don't know why. My alert log error:
>
>=====
>ORA-1652: unable to extend temp segment by 64 in tablespace INDEX1M
>=====
>
>What I am doing:
>
>=====
>alter index cf_eim_org_ext_xm unusable;
>truncate table eim_org_ext_xm;
>
>INSERT /*+APPEND */ INTO eim_org_ext_xm NOLOGGING
>(SELECT /*+FULL(EOM) */ xm.par_row_id
> ,xm.x_amp_prod_id
> ,xm.x_amp_busns_plan_plant
> ,xm.x_amp_mkt_code
> ,xm.x_amp_strtgy_code
> ,xm.x_amp_sub_strtgy_code
> ,xm.x_amp_scorecard_date
> ,xm.name
> ,xm.x_amp_bcklg_wgt
> ,xm.x_amp_bcklg_rev
> ,xm.x_amp_bkngs_wgt
> ,xm.x_amp_bkngs_rev
> ,xm.x_amp_rmang_ship_wgt
> ,xm.x_amp_rmang_ship_rev
> ,xm.x_amp_shipmt_wgt
> ,xm.x_amp_shipmt_rev
> ,xm.x_amp_shipmt_total_rev
> ,xm.x_amp_retrn_wgt
> ,xm.x_amp_retrn_rev
> ,xm.x_amp_cust_demnd_wgt
> ,xm.x_amp_frcst_wgt
> ,xm.x_amp_frcst_rev
> ,xm.x_amp_frcst_svcwgt
> ,xm.x_amp_frcst_svcrev
> ,xm.TYPE,attrib_22
> ,xm.x_amp_mkt_seg_id
> FROM s_org_ext_xm XM, eim_order_metrics EOM
> WHERE XM.par_row_id = EOM.accnt_id
> AND XM.x_amp_prod_id = EOM.prod_id
> AND nvl(XM.x_amp_busns_plan_plant,'xX') =nvl(EOM.x_amp_busns_plan_plant,'xX')
> AND nvl(XM.x_amp_mkt_code,'xX') = nvl(EOM.x_amp_mkt_code,'xX')
> AND nvl(XM.x_amp_strtgy_code,'xX') = nvl(EOM.x_amp_strtgy_code,'xX')
> AND nvl(XM.x_amp_sub_strtgy_code,'xX') =nvl(EOM.x_amp_sub_strtgy_code,'xX')
> AND XM.x_amp_scorecard_date = EOM.scorecard_date
> AND XM.TYPE in ('Orders','Non-AMP Orders'));
>
>alter index cf_eim_org_ext_xm rebuild;
>=====
>
>INDEX1M is an LMT uniform size 1M. The index is 9M.
>
>I seem to have plenty of space:
>
>=====
>SQL> select bytes from dba_free_space where tablespace_name = 'INDEX1M';
>
> BYTES
>----------
> 1048576
> 1048576
> 1048576
> 1048576
> 1048576
> 1048576
> 1048576
> 1048576
> 1048576
> 52428800
> 12582912
> 3145728
>
>12 rows selected.
>=====
>
>And here is the size of the index:
>
>=====
>SQL> select tablespace_name, bytes from dba_extents where
> 2 segment_name='CF_EIM_ORG_EXT_XM';
>
>TABLESPACE_NAME BYTES
>------------------------------ ----------
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>INDEX1M 1048576
>
>9 rows selected.
>=====
>
>Questions:
>
>1. AFAIK, when a table is truncated, all storage except for INITIAL
> returns to the free list(s). Is the same true for indexes?
>
>2. Is there any reason that I would run out of space assuming that all of
> the above was free at the moment of the index rebuild?
>
>I'm probably just being profoundly stupid, but I am perplexed.
>
> ---------------------------------------------------------------------------
> / Charles J. Fisher | "Four hostile newspapers are more to be feared /
> / cfisher_at_rhadmin.org | than a thousand bayonets." /
> / http://rhadmin.org | --Napoleon /
>---------------------------------------------------------------------------

As Anurag has stated your index is VALID when you start inserting into the table. Have you checked your index size and freespace immediately after inserting? If your index is type bitmap it may blow up considerably when loading the table, leaving no roam for rebuilding.

Jaap. Received on Sat Dec 18 2004 - 04:29:16 CST