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: [EMAIL
Please Help - ora error
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).
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).
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).
