RE: Win2000/8.1.7.3/Temp Space Question
Michael, I have seen the same behavior. Even stopping and starting the database did not seem to clear up the temp segments as described in the documentation. I did some research on it, but could not find anything on metalink nor could I find any patterns with testing. Adding more space to the tablespace only delayed the onset of the problem (we had a temp tablespace of > 20G with 3 users on the system). The only solution was to shutdown the database, start it up, drop the temp tablespace and recreate it. I think it is a bug, but we never took the time to put in a TAR. Dan -Original Message- Sent: Wednesday, July 17, 2002 9:43 AM To: Multiple recipients of list ORACLE-L No, I created it as you suggested. Here is the SQL from my script... CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\OraNT\oradata\SID\temp01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 2000M EXTENT MANAGEMENT local UNIFORM SIZE 2M; Does that help? Thanks, Mike -Original Message- Sent: Tuesday, July 16, 2002 8:08 PM To: Multiple recipients of list ORACLE-L 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 -- 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: Fink, Dan 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).
RE: Win2000/8.1.7.3/Temp Space Question
No, I created it as you suggested. Here is the SQL from my script... CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'E:\OraNT\oradata\SID\temp01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 2000M EXTENT MANAGEMENT local UNIFORM SIZE 2M; Does that help? Thanks, Mike -Original Message- Sent: Tuesday, July 16, 2002 8:08 PM To: Multiple recipients of list ORACLE-L 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 -- 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).
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 [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 NameFile# 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 NameFile# EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED >-- - -- - >BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO >--- -- >TEMP 19990255744 > 0 2095054848 01 > > >1 row selected. > >SQL> select * from v$sort_segment; > >TS NameSEGMENT_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 2557440 0 999 > 255744 03683 0 0 > 999 255744 999 255744 999 > 2557440 > > >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).
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 NameFile# 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 NameFile# EXTENTS_CACHED EXTENTS_USED BLOCKS_CACHED -- - -- - BLOCKS_USED BYTES_CACHED BYTES_USED RELATIVE_FNO --- -- TEMP 19990255744 0 2095054848 01 1 row selected. SQL> select * from v$sort_segment; TS NameSEGMENT_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 2557440 0 999 255744 03683 0 0 999 255744 999 255744 999 2557440 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).
