| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: index rebuild out of space
"Charles J. Fisher" <cfisher_at_rhadmin.org> wrote in message news:Pine.BSO.4.58.0412171504060.22531_at_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:
>
> 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 /
> ---------------------------------------------------------------------------
I'm wondering where you got this idea of making index unusable and trying the rest of the steps? Recently I've seen a lot of posters trying this and running into problems.
Try this out for example.
SQL> create table xyz as select * from all_objects;
Table created.
SQL> create index xyz_idx on xyz (object_name);
Index created.
SQL> select index_name, status from user_indexes where index_name = 'XYZ_IDX';
INDEX_NAME STATUS ------------------------------ -------- XYZ_IDX VALID
SQL> alter index XYZ_IDX unusable;
Index altered.
SQL> select index_name, status from user_indexes where index_name = 'XYZ_IDX';
INDEX_NAME STATUS ------------------------------ -------- XYZ_IDX UNUSABLE
SQL> truncate table xyz;
Table truncated.
SQL> select index_name, status from user_indexes where index_name = 'XYZ_IDX';
INDEX_NAME STATUS ------------------------------ -------- XYZ_IDX VALID *************************************************? as soon as you truncate the table, the index is marked "valid" and if its valid, the rebuild will require twice as much space as you think it requires
Maybe you should instead have tried this (*after truncate*):
SQL> alter index XYZ_IDX unusable;
Index altered.
SQL> select index_name, status from user_indexes where index_name = 'XYZ_IDX';
INDEX_NAME STATUS ------------------------------ -------- XYZ_IDX UNUSABLE
SQL> insert /*+ append */ into xyz select * from all_objects; insert /*+ append */ into xyz select * from all_objects
*
SQL> alter session set skip_unusable_indexes=true;
Session altered.
SQL> insert /*+ append */ into xyz select * from all_objects;
13893 rows created.
SQL> select index_name, status from user_indexes where index_name = 'XYZ_IDX';
INDEX_NAME STATUS ------------------------------ -------- XYZ_IDX UNUSABLE
SQL> select segment_name, bytes from user_segments where segment_name = 'XYZ_IDX';
SEGMENT_NAME
BYTES
SQL> commit;
Commit complete.
SQL> alter index XYZ_IDX rebuild;
Index altered.
SQL> select segment_name, bytes from user_segments where segment_name = 'XYZ_IDX';
SEGMENT_NAME
BYTES
Makes sense?
BTW: I'm not sure if this method is supported by oracle. Thats why I ask where you read how to do this from?
HTH Anurag Received on Fri Dec 17 2004 - 17:11:47 CST
 |
 |