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 [EMAIL PROTECTED] 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: chaos 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).