how can i handle this problem?
Printable View
how can i handle this problem?
Tune your query so that it uses less temporary space......
Thanks bang dba... any other solutions rather than tuning my query statements? thanks!!!
make the temp tablespace bigger (resizing a datafile or adding one) - then tune your queries :) and look at memory settings
Wave your magic wand? :D :DQuote:
Originally Posted by Harry_Potter
set sort_area_size=200000000;
and then try your query also tuning is very important.
regards
anandkl
Quote:
Originally Posted by Harry_Potter
Code: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>
your exmaple doesnt show him how to fix it though des it
Quote:
Originally Posted by davey23uk
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.
true, good spot - but it might have been in the temporary tablespace, so i might be right in the end :)