| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Win2000/8.1.7.3/Temp Space Question
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: mvergara_at_guidant.com 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: ListGuru_at_fatcity.com (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).Received on Tue Jul 16 2002 - 19:43:21 CDT
 |
 |