Re: Problem with temp tablespace

Stan Brown wrote:

>I'm trying to build an index on a 7.3.4.5 system.
>
>I;ve run the following as user "sys":
>
>ALTER USER flink TEMPORARY TABLESPACE TEMP ;
>ALTER USER flink DEFAULT TABLESPACE TEMP ;
>
>Yet, when i try to create an index as this suer, I get.
>
>
>ERROR at line 2:
>ORA-01630: max # extents (1) reached in temp segment in tablespace INDEX2_TS
>
>
>The _index_ is declared to be in IDEX2_TS, but this message, says I'm out
>of TEMP space.
>
>What's going on here?
>
>I think I have spce for this index in the specifed tablespace:
>
>TSPACE TOT_MB ORA_BLKS TOT_USED PCT_USED
>--------------- ---------- ---------- ---------- ----------
>CONFIG_TS 20 2560 190 7.42
>DATA_LOG_TS 30 3840 1360 35.42
>INDEX1_TS 8200 1049600 938125 89.38
>INDEX2_TS 8300 1062400 664940 62.59
>RBS_TS 1000 128000 25600 20
>SYSTEM 40 5120 1923 37.56
>TEMP 250 32000 11970 37.41
>TOOLS 15 1920 485 25.26
>TREND_TS 16500 2112000 1476030 69.89
>USERS 1 128
>
>
>Here's the index ceate statement:
>
>CREATE UNIQUE INDEX P_STA_PUMP4_2
> ON P_STA_PUMP4
> (
> DSTAMP
> )
> TABLESPACE INDEX2_TS
> STORAGE (
> INITIAL 79000K
> NEXT 0K
> MAXEXTENTS 1
> PCTINCREASE 0
> )
> PCTFREE 1;
>
>
>

First make sure your tablespace named TEMP is actually a temp space and not a permanent space named TEMP.
Second never ever use a TEMP tablespace as the default. That is preposterous on its face.
Third the error message is explicitly telling you what is wrong. Deal with it head on. Go into all_tablespaces and look at the options for the index. Look at the options for the tablespace in dba_tablespaces.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)