index rebuild out of space

From: Charles J. Fisher <cfisher_at_rhadmin.org>
Date: Fri, 17 Dec 2004 21:24:19 GMT
Message-ID: <Pine.BSO.4.58.0412171504060.22531@bart.rhadmin.org>

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:

AFAIK, when a table is truncated, all storage except for INITIAL returns to the free list(s). Is the same true for indexes?
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              /
---------------------------------------------------------------------------

Received on Fri Dec 17 2004 - 15:24:19 CST