RE: Please Help - ora error
I knew that this was incorrect answer - and instead of pointing that I asked if he was sure. Alex Hillman -Original Message- Sent: Saturday, August 18, 2001 1:06 PM To: Multiple recipients of list ORACLE-L Alex: This is a fallacy that's been around since Oracle 6. In actuality, one wants to set the initial and next extents of a temporary tablespace to the same size as the size of the average sort in memory to ensure that most disk sorts can be accommodated within a single temporary tablespace extent. This avoids the unnecessary overhead of extent allocation. You can determine the size of the average disk sort with the following query: SELECT DECODE(disk_sorts, NULL, 0, 0, 0, ROUND((blocks_written * block_size) / disk_sorts, 0)) AS avg_sort_size FROM (SELECT VALUE AS disk_sorts FROM sys.v_$sysstat WHERE name = 'sorts (disk)') , (SELECT NVL(SUM(fs.phyblkwrt), 0) AS blocks_written FROM dba_data_files df , sys.v_$filestat fs , dba_tablespaces ts WHERE df.file_id = fs.file# AND df.tablespace_name = ts.tablespace_name AND ts.contents = 'TEMPORARY') , (SELECT TO_NUMBER(VALUE) AS block_size FROM sys.v_$parameter WHERE name LIKE 'db_block_size'); For example, I just ran this query against one of our production systems and it said the average disk sort was 61,349,888 bytes. Now, if I set sort_area_size to just 1M (1,048,576), then Oracle would have to do an additional 58 extent allocations, if the extents are not already present in a temporary tablespace, and would possibly increase the number of multiblock reads necessary to fetch the data in merging together the sorted result set. For more information on this topic, take a look at Steve Adams' website http://www.ixora.com.au. Jon Walthour -Original Message- Alex Sent: Friday, August 17, 2001 11:52 AM To: Multiple recipients of list ORACLE-L Are you sure enough to post an answer? Alex Hillman -Original Message- Sent: Friday, August 17, 2001 2:45 AM To: Multiple recipients of list ORACLE-L Hi In case of Temporary Tablespace the Initital segemnt and next segment should be equal to the sort_area_size parameter. bye G.Subrahmanyam -Original Message- Sent: Thursday, August 16, 2001 8:28 PM To: Multiple recipients of list ORACLE-L Hello all, I have a strage error. 'ORA-01658: unable to create INITIAL extent for segment in tablespace TEMP'. The clients TEMP tablespace is 200M. The sql is:- SELECT a.username, length(sql_text), substr(sql_text,1,100) as "SQL TEXT", substr(sql_text,101,250) as "Second TEXT", substr(sql_text,351,250) as "Third TEXT", substr(sql_text,601,250) as "Fourth TEXT", substr(sql_text,851,250) as "Fifth TEXT", substr(sql_text,1101,250) as "Sixth TEXT" FROM sys.dba_users a, v$session, v$sqlarea WHERE parsing_user_id=user_id AND address=sql_address(+) ORDER BY executions desc / Can you please explain why I am getting this error and cannot run the simple SQL statement? Rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: G, Subrahmanyam (CAP) 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: Hillman, Alex 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 m
RE: Please Help - ora error
Alex: This is a fallacy that's been around since Oracle 6. In actuality, one wants to set the initial and next extents of a temporary tablespace to the same size as the size of the average sort in memory to ensure that most disk sorts can be accommodated within a single temporary tablespace extent. This avoids the unnecessary overhead of extent allocation. You can determine the size of the average disk sort with the following query: SELECT DECODE(disk_sorts, NULL, 0, 0, 0, ROUND((blocks_written * block_size) / disk_sorts, 0)) AS avg_sort_size FROM (SELECT VALUE AS disk_sorts FROM sys.v_$sysstat WHERE name = 'sorts (disk)') , (SELECT NVL(SUM(fs.phyblkwrt), 0) AS blocks_written FROM dba_data_files df , sys.v_$filestat fs , dba_tablespaces ts WHERE df.file_id = fs.file# AND df.tablespace_name = ts.tablespace_name AND ts.contents = 'TEMPORARY') , (SELECT TO_NUMBER(VALUE) AS block_size FROM sys.v_$parameter WHERE name LIKE 'db_block_size'); For example, I just ran this query against one of our production systems and it said the average disk sort was 61,349,888 bytes. Now, if I set sort_area_size to just 1M (1,048,576), then Oracle would have to do an additional 58 extent allocations, if the extents are not already present in a temporary tablespace, and would possibly increase the number of multiblock reads necessary to fetch the data in merging together the sorted result set. For more information on this topic, take a look at Steve Adams' website http://www.ixora.com.au. Jon Walthour -Original Message- Alex Sent: Friday, August 17, 2001 11:52 AM To: Multiple recipients of list ORACLE-L Are you sure enough to post an answer? Alex Hillman -Original Message- Sent: Friday, August 17, 2001 2:45 AM To: Multiple recipients of list ORACLE-L Hi In case of Temporary Tablespace the Initital segemnt and next segment should be equal to the sort_area_size parameter. bye G.Subrahmanyam -Original Message- Sent: Thursday, August 16, 2001 8:28 PM To: Multiple recipients of list ORACLE-L Hello all, I have a strage error. 'ORA-01658: unable to create INITIAL extent for segment in tablespace TEMP'. The clients TEMP tablespace is 200M. The sql is:- SELECT a.username, length(sql_text), substr(sql_text,1,100) as "SQL TEXT", substr(sql_text,101,250) as "Second TEXT", substr(sql_text,351,250) as "Third TEXT", substr(sql_text,601,250) as "Fourth TEXT", substr(sql_text,851,250) as "Fifth TEXT", substr(sql_text,1101,250) as "Sixth TEXT" FROM sys.dba_users a, v$session, v$sqlarea WHERE parsing_user_id=user_id AND address=sql_address(+) ORDER BY executions desc / Can you please explain why I am getting this error and cannot run the simple SQL statement? Rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: G, Subrahmanyam (CAP) 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: Hillman, Alex 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: Jon Walthour INET:
RE: Please Help - ora error
Are you sure enough to post an answer? Alex Hillman -Original Message- Sent: Friday, August 17, 2001 2:45 AM To: Multiple recipients of list ORACLE-L Hi In case of Temporary Tablespace the Initital segemnt and next segment should be equal to the sort_area_size parameter. bye G.Subrahmanyam -Original Message- Sent: Thursday, August 16, 2001 8:28 PM To: Multiple recipients of list ORACLE-L Hello all, I have a strage error. 'ORA-01658: unable to create INITIAL extent for segment in tablespace TEMP'. The clients TEMP tablespace is 200M. The sql is:- SELECT a.username, length(sql_text), substr(sql_text,1,100) as "SQL TEXT", substr(sql_text,101,250) as "Second TEXT", substr(sql_text,351,250) as "Third TEXT", substr(sql_text,601,250) as "Fourth TEXT", substr(sql_text,851,250) as "Fifth TEXT", substr(sql_text,1101,250) as "Sixth TEXT" FROM sys.dba_users a, v$session, v$sqlarea WHERE parsing_user_id=user_id AND address=sql_address(+) ORDER BY executions desc / Can you please explain why I am getting this error and cannot run the simple SQL statement? Rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: G, Subrahmanyam (CAP) 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: Hillman, Alex 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: Please Help - ora error
Hi In case of Temporary Tablespace the Initital segemnt and next segment should be equal to the sort_area_size parameter. bye G.Subrahmanyam -Original Message- Sent: Thursday, August 16, 2001 8:28 PM To: Multiple recipients of list ORACLE-L Hello all, I have a strage error. 'ORA-01658: unable to create INITIAL extent for segment in tablespace TEMP'. The clients TEMP tablespace is 200M. The sql is:- SELECT a.username, length(sql_text), substr(sql_text,1,100) as "SQL TEXT", substr(sql_text,101,250) as "Second TEXT", substr(sql_text,351,250) as "Third TEXT", substr(sql_text,601,250) as "Fourth TEXT", substr(sql_text,851,250) as "Fifth TEXT", substr(sql_text,1101,250) as "Sixth TEXT" FROM sys.dba_users a, v$session, v$sqlarea WHERE parsing_user_id=user_id AND address=sql_address(+) ORDER BY executions desc / Can you please explain why I am getting this error and cannot run the simple SQL statement? Rgds, raja Get 250 color business cards for FREE! http://businesscards.lycos.com/vp/fastpath/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viraj Luthra 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: G, Subrahmanyam (CAP) 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: Please Help - ora error
On Thu, 16 Aug 2001, [EMAIL PROTECTED] wrote: > 'ORA-01658: unable to create INITIAL extent for segment in > tablespace TEMP'. This is saying that Oracle is trying to use the TEMP tablespace for some, temporary work, but it can't gain control of anymore space. [...] > ORDER BY executions desc This is the part of the SQL where Oracle needs temp space for. It writes the results of the select to a temporary work area so that it can then sort it. Your solution is found in the action part of the documentation on the error. ,[ From: http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76999/e1500.htm#1003914 ] | ORA-01658 unable to create INITIAL extent for segment in | tablespace string | | Cause: Failed to find sufficient contiguous space to allocate | INITIAL extent for segment being created. | | Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space | to the tablespace or retry with a smaller value for INITIAL. ` -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Galen Boyer 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).