ORA-1652 unable to extend temp segment

[Harry_Potter]

how can i handle this problem?

[bang_dba]

Tune your query so that it uses less temporary space......

[Harry_Potter]

Thanks bang dba... any other solutions rather than tuning my query statements? thanks!!!

[davey23uk]

make the temp tablespace bigger (resizing a datafile or adding one) - then tune your queries and look at memory settings

[marist89]

any other solutions rather than tuning my query statements?

Wave your magic wand?

[anandkl]

set sort_area_size=200000000;

and then try your query also tuning is very important.

regards
anandkl

[Markelenkov]

how can i handle this problem?

SQL*Plus: Release 9.2.0.5.0 - Production on Fri Oct 1 21:58:01 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production

SQL> create tablespace ttt datafile 'd:\ttt.ora' size 512k uniform size 64k;

Tablespace created.

SQL> create table t tablespace usr as select * from all_objects;

Table created.

SQL> select sum(vsize(owner))+ sum(vsize(object_name))+ sum(vsize(object_id))+ sum(vsize(object_type)) from t;

SUM(VSIZE(OWNER))+SUM(VSIZE(OBJECT_NAME))+SUM(VSIZE(OBJECT_ID))+SUM(VSIZE(OBJECT
--------------------------------------------------------------------------------
1049895

SQL> create index t_i on t(owner,object_name,object_id,object_type) tablespace ttt;
create index t_i on t(owner,object_name,object_id,object_type) tablespace ttt
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 16 in tablespace TTT

SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 4096
SQL>

[davey23uk]

your exmaple doesnt show him how to fix it though des it

[dbtoo]

make the temp tablespace bigger (resizing a datafile or adding one) - then tune your queries and look at memory settings

Temp segment is not in the 'TEMP' tablespace. It has to do with freespace available in the tablespace that is listed (not given in the original post). Extend the tablespace you are building the object in.

[davey23uk]

true, good spot - but it might have been in the temporary tablespace, so i might be right in the end