RE: TEMP tablespace growing abnormally -Sort Area
Simon - My apologies. Tim's response was for the other problem that is getting a lot of responses. In trying to read the postings as well as get some work done here, I confused the two. I was thinking that the response said that the loader first loads into the TEMPORARY segment, then merged with the table segment. I confused the two since they both involved "temp". I don't have a specific solution to your problem, but my survival instincts as a crusty old DBA (but still employed) say "don't make your TEMP tablespace autoextend". It just feels like you are trolling for trouble. I am a big fan of autoextend and have it on all my datafiles except for a couple, and guess what -- TEMP is not on autoextend for me. Maybe once you lick this one, you can give talks worldwide on either a) why you should never let TEMP autoextend or b) why you're missing the best part of Oracle by not letting your TEMP autoextend. I promise I'll attend your talk. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 4:13 PM To: Multiple recipients of list ORACLE-L Dennis, I haven't seen Tim's posting !!? as regards my woos -Original Message- Sent: Tuesday, May 14, 2002 10:13 PM To: Multiple recipients of list ORACLE-L Simon - I think Tim has given you a good answer to your TEMP tablespace growth. I just wanted to warn you to be cautious about extremely large sizes of SORT_AREA_SIZE. This is a per-process setting, which means that if you have several processes, each process may acquire a lot of memory and the overall system memory may be overallocated. I speak from experience, bad experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: DENNIS WILLIAMS 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: Simon Waibale 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: DENNIS WILLIAMS 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
RE: TEMP tablespace growing abnormally -Sort Area
Dennis, I haven't seen Tim's posting !!? as regards my woos -Original Message- Sent: Tuesday, May 14, 2002 10:13 PM To: Multiple recipients of list ORACLE-L Simon - I think Tim has given you a good answer to your TEMP tablespace growth. I just wanted to warn you to be cautious about extremely large sizes of SORT_AREA_SIZE. This is a per-process setting, which means that if you have several processes, each process may acquire a lot of memory and the overall system memory may be overallocated. I speak from experience, bad experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: DENNIS WILLIAMS 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: Simon Waibale 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: TEMP tablespace growing abnormally -Sort Area
TEMPORARY -Original Message- Sent: Tuesday, May 14, 2002 9:39 PM To: Multiple recipients of list ORACLE-L Is your TEMP tablespace set to TEMPORARY or PERMANENT? -Original Message- Sent: Tuesday, May 14, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Wong, Bing 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: Simon Waibale 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: TEMP tablespace growing abnormally -Sort Area
I think you meant 8MB not 8GB, right? Take a look at v$sort_usage and find these sessions that are filling the TEMP TS. >From there find the sql behind this temp space and try to figure out if it's normal to allocate this space or there is a CARTESIAN join for example that is giving you this problem. It's not weired to fill a 32 GB temp TS in a data warehouse application. Regards, Waleed -Original Message- Sent: Tuesday, May 14, 2002 2:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Khedr, Waleed 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: TEMP tablespace growing abnormally -Sort Area
Simon - I think Tim has given you a good answer to your TEMP tablespace growth. I just wanted to warn you to be cautious about extremely large sizes of SORT_AREA_SIZE. This is a per-process setting, which means that if you have several processes, each process may acquire a lot of memory and the overall system memory may be overallocated. I speak from experience, bad experience. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, May 14, 2002 1:14 PM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: DENNIS WILLIAMS 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: TEMP tablespace growing abnormally -Sort Area
Is your TEMP tablespace set to TEMPORARY or PERMANENT? -Original Message- Sent: Tuesday, May 14, 2002 11:14 AM To: Multiple recipients of list ORACLE-L Forgot to tell you that myparameter for sort_area_size= apx. 8GB -Original Message- Sent: Tuesday, May 14, 2002 8:02 PM To: '[EMAIL PROTECTED]' Hi all, Having a bad start of week. My temp tablespace is growing abnormally -from 3GB -> 32 GB (Filled up disk). DB is 81700 on Tru64 Unix, 120GB in size, appx 150 front-End users running billing application for appx 200,000 customer base. Initially set-up as OPS but undone though init.ora file still has PARALEL_SERVER=TRUE, only one node is active. I've seen a refernce to OPS in relation to de-allocation of extents but can't get further. Any help will be appreciated. --- CSW -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Simon Waibale 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: Wong, Bing 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).
