Hi Everyone:
I have an 8.1.7.3 database running on Win2k. I created the
TEMP tablespace as locally managed and temporary. When I run a
query that needs sort space I get a...
ERROR at line 5:
ORA-01652: unable to extend temp segment by 256 in tablespace TEMP
...error. Then I try to find out what's going on with...
SQL> select * from v$temp_space_header;
TS Name File# BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE
------------------ ----- ---------- ----------- ---------- -----------
RELATIVE_FNO
------------
TEMP 1 2097152000 256000 0 0
1
1 row selected.
SQL> select * from v$temp_extent_pool;
TS Name File# EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED
------------------ ----- -------------- ------------ -------------
BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO
----------- ------------ ---------- ------------
TEMP 1 999 0 255744
0 2095054848 0 1
1 row selected.
SQL> select * from v$sort_segment;
TS Name SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS
------------------ ------------ ------------- ----------- -------------
TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS
------------- ------------ ------------ ----------- ------------
FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS
----------- ------------- ----------- ------------- -------------
MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE
---------- ---------- ------------- --------------- -------------
MAX_SORT_BLOCKS RELATIVE_FNO
--------------- ------------
TEMP 0 0 256 0
999 255744 0 0 999
255744 0 3683 0 0
999 255744 999 255744 999
255744 0
1 row selected.
SQL> select * from v$sort_usage;
no rows selected
SQL>
...and it looks like a sort segment that is in use, but nobody's
claiming it. The V$TEMP_EXTENT_POOL shows blocks as cached but
not used.
I do not understand what's going on. Can somebody give me a hint
or a place to look in the docs to figure this out? Do I needs to
add more TEMP space? Can I un-cache the unused space?
Help!
And Thanx,
Mike
---
===========================================================================
Michael P. Vergara
Oracle DBA
Guidant Corporation
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Vergara, Michael (TEM)
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
