Re: Win2000/8.1.7.3/Temp Space Question

Vergara, Michael (TEM)£¬

    Hi , i think you created the tablespace like:

	Create tablespace temp datafile 'xxx' extent management local ...., right?
	Drop it and recreate it like:
	Create temporary tablespace temp tempfile 'xxx' ...

Good luck!

            chaos
            chaospku_at_163.net

zhu chao
DBA of Eachnet.com
86-021-32174588-667

شع 2002-07-16 16:43:00 You wrote:
>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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: chaos
  INET: chaospku_at_163.net

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).