Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 882
  • Last Modified:

Urgent-Need Expert input-unable to extend temp segment by 128 in tablespace TEMP

hi
i am querying somethign from oracle database from asp.net.and i show in a grid. sometimes it throws this error when i run the page
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-06512: at "SYS.DBMS_LOB", line 436
ORA-06512: at "SYS.COMPRESSOR", line 58

adn then after sometime the error automatically goes away and my webpage runs. i checked on the google and possible cause site is that my temp tablespace get filled. but my question is ... why do sometimes my application works and then doesnt. is it an dot net limitation or what
0
samir25
Asked:
samir25
1 Solution
 
Chirag1211Commented:
See the solution at the following link

http://www.experts-exchange.com/Databases/Oracle/Q_21109541.html

Regards,

Chirag Patel
0
 
GGuzdziolCommented:
It may depend on number of concurent connections, tempspace-intensive operations beeing currently performed - http://www.oracle.com/technology/oramag/code/tips2006/050106.html - here is query that will show You actual temp usage.
0
 
samir25Author Commented:
i checked on that expert exchange link b4hand.
1. i checked on my server. the temp01.dbf is 500mb.
running the script from teh 2nd link gives me below result:
Mb. in sort segments Mb. High Water Mark
-------------------- -------------------
                 499          74.0703125

what do tehy mean and how can i solve it. i am not a dba so i cannot simply follow the websites or solutions, i need to know what i am doing and need direct inputs
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
schwertnerCommented:
Sometimes due bugs TEMP goes to big
and do not shrink. Your DBA has to investigate
the size of TEMP and possibli recreate it.
0
 
samir25Author Commented:
i check through this tabel i found through google
SQL> select * from DBA_TEMP_FILES
  2  ;

FILE_NAME
------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
C:\ORACLE\ORADATA\TEMP01.DBF
         1 TEMP                            524288000      64000 AVAILABLE
           1 YES  524288000      64000          640  523239424       63872

0
 
schwertnerCommented:
This is a known bug in 9i.

TEMP tablespace is full!
To clear it:

CREATE
    TEMPORARY TABLESPACE "TEMP1" TEMPFILE
    'E:\ORACLE_DB_MAIN\ORADATA\MAIN\TEMP02.DBF' SIZE 200M
    REUSE AUTOEXTEND
    ON NEXT  640K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL UNIFORM
    SIZE 1024K;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP1";

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

To see who is caused this:

select v$session.username, sql_text
from v$sort_usage ,v$sqlarea , v$session
where V$sort_usage.tablespace='TEMP'
and v$sort_usage.sqladdr=v$session.sql_Address
and v$sort_usage.sqladdr=v$sqlarea.address;


select user, tablespace, blocks
from v$sort_usage
order by blocks;
0
 
Mark GeerlingsDatabase AdministratorCommented:
I would not say this is a known bug in Oracle9i.  This can happen in any version of any Oracle database.
No, this is definitely not caused by dot.net.  This is an Oracle issue.  The temp tablespace in any Oracle database must be sized based on how large the tables in the database are and on how the application uses the data in the tables.  As the tables grow over time, the temp tablespace may need to also be made larger.

Oracle uses the temp tablespace for large sort operations, and for grouping large sets of records in order to calculate sub-totals, so reports from Oracle tend to encoutnter this error from time to time if the temp tablespace is not sized correctly for the database and the application.  The frequency of this error may seem inconsistent because it will depend on how many other users are doing things in the database at the same time.
0
 
samir25Author Commented:
hi Markgeer. thanks for your comments. i closed the thread without reading ur inputs. its quite informative.
0
 
schwertnerCommented:
To Markgeer:

I am trying (sometimes also succeed!) not to desinform people asking help here. Writing "this is known Oracle bug" means also "I know this for sure!". Please pay attention on the following discussion on Metalink:

Metalink Note: 457834.999

Hi,
I have locally managed temporary tablespace, which grew to 60Gb (from 3Gb!!!).
I can't shrink it - I get ORA-03297.
I don't know how to interpret V$TEMP_EXTENT_MAP resultset, e.g. what is OWNER 0, or RELATIVE_FNO 1?
I couldn't find anything in documentation. I know I can drop it and recreate. But is there a way to find out what causes it to grow to large and how to troubleshoot it? Is there a way to free space in it without restarting the DB?



From: Oracle, Helen Schoone 15-Apr-03 20:21
Subject: Re : Temp Tablespace Management
Hi. There is a bug (bug 2193406) filed against 9.2, although it appears to effect 8i (bug 1003703) as well, which is an issue where you are unable to resize a temporary tablespace even though it is not currently being used. The bug is assigned to development. The workaround is to drop and re-add the tempfile(s).

You can monitor the v$tempseg_usage view to see what user is currently utilizing the temporary space. You may also want to turn off autoextend and/or set a maxsize.
Regards,
Helen
Oracle Support Services

0
 
schwertnerCommented:
Going back to this issue I recall that it takes me a day to investigate the case on the net ....
I collected a big Word document devoted to this issue. It is 15 pages long ..... There are complains of many, many
Oracle users.
Be also aware that the same problem exists also with UNDO tablespace in Oracle 9i.
To workaround the victims have to use another technology because of the RETENTION_INTERVAL parameter
in SPFILE that prevents the simple drop of the damaged UNDO.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now