| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: why is this happening
Catching up on e-mails ...as usual :-(
and I noticed no one had answered this question. The ORA-3232 is usually not related to tablespaces but to the relationship between the size of your temp tablespace INITIAL and NEXT extent in relation to sort_area_size (and hash joins).
This hit me not too long ago when I had a really small temp extent size for a sandbox I was planning with. For me, I just created my sandbox temp space with larger initial extent. There are also Metalink Notes about changing the HASH_MULTIBLOCK_IO_COUNT to resolve this.
When you "sort_area_size=20M", all sorts were done in memory so you didn't trigger this condition.
-----Original Message-----
George
Sent: Thursday, November 07, 2002 10:59 AM
To: Multiple recipients of list ORACLE-L
The following query is causing the following error
ERROR at line 1:
ORA-03232: unable to allocate an extent of 22 blocks from tablespace 3
select count(l.processid) from tmslog l, tmslogtimeout t where l.processid = t.processid and l.statifiedflag='Y' and t.processcompleteflag='Y'
Tablespace #3 is temp, 800 MB, 128K extent size locally managed. The user is also set to use temp.
If I do a alter session set sort_area_size=20M then it completes. Currently the sort_area_size is set via the init file as 5 mb.
Ideas ?
George
You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!
-----Original Message-----
Sent: 07 November 2002 15:24 PM
To: Multiple recipients of list ORACLE-L
Thanks Kevin, good to hear from you. As usual you're Johnie on spot with TFM. It's interesting that this can be overcome with the inline view technique posted earlier by Raj.
Steve
-----Original Message-----
Sent: Wednesday, November 06, 2002 5:23 AM
To: Multiple recipients of list ORACLE-L
Directly from TFM....
Notes on Hierarchical Queries:
If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause.
The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:
-----Original Message-----
Sent: Tuesday, November 05, 2002 3:29 PM
To: Multiple recipients of list ORACLE-L
I was also able to confirm this works on O9i.
-----Original Message-----
Sent: Tuesday, November 05, 2002 11:14 AM
To: Multiple recipients of list ORACLE-L
I get an error on 8.1.7.2. Is "siblings" new?
SQL> l
1 SELECT LEVEL, treenode.*
2 FROM treenode
3 START WITH parentid=0
4 CONNECT BY PRIOR ID = parentid
5* ORDER SIBLINGS BY PARENTid , nodeorder
SQL> /
ORDER SIBLINGS BY PARENTid , nodeorder
*
ERROR at line 5:
ORA-00924: missing BY keyword
-----Original Message-----
Sent: Tuesday, November 05, 2002 11:02 AM
To: 'ORACLE-L_at_fatcity.com'; Orr, Steve
SELECT LEVEL, treenode.*
FROM treenode
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
ORDER SIBLINGS BY PARENTid , nodeorder
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Tuesday, November 05, 2002 12:24 PM
To: Multiple recipients of list ORACLE-L
Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode (
id number not null
constraint pk_treenode primary key,
parentid number not null,
nodeorder number not null,
description varchar2(20) null);
insert into treenode values(1,0,0,'top folder');
insert into treenode values(9,1,0,'1st subfolder');
insert into treenode values(7,1,2,'3rd subfolder');
insert into treenode values(2,1,1,'2nd subfolder');
insert into treenode values(8,7,1,'folder 3 item 2');
insert into treenode values(6,2,3,'folder 2 item 3');
insert into treenode values(5,7,0,'folder 3 item 1');
insert into treenode values(3,2,2,'folder 2 item 2');
insert into treenode values(4,2,1,'folder 2 item 1');
ID PARENTID NODEORDER DESCRIPTION
---------- ---------- ---------- --------------------
1 0 0 top folder
9 1 0 1st subfolder
7 1 2 3rd subfolder
8 7 1 folder 3 item 2
5 7 0 folder 3 item 1
2 1 1 2nd subfolder
6 2 3 folder 2 item 3
3 2 2 folder 2 item 2
4 2 1 folder 2 item 1
-----------------------------------------------------
Desired SQL statement results:
ID PARENTID NODEORDER DESCRIPTION
---------- ---------- ---------- --------------------
1 0 0 top folder
9 1 0 1st subfolder
2 1 1 2nd subfolder
4 2 1 folder 2 item 1
3 2 2 folder 2 item 2
6 2 3 folder 2 item 3
7 1 2 3rd subfolder
5 7 0 folder 3 item 1
8 7 1 folder 3 item 2
-----------------------------------------------------
Kudos to anyone who can figure out how to do this via SQL.
Steve Orr
Bozeman, Montana
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: sorr_at_rightnow.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Fri Nov 15 2002 - 16:19:23 CST
(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: Orr, Steve INET: sorr_at_rightnow.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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: Orr, Steve INET: sorr_at_rightnow.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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: Toepke, Kevin M INET: ktoepke_at_trilegiant.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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: Orr, Steve INET: sorr_at_rightnow.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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: Leonard, George INET: george.leonard_at_farnell.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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: Babette Turner-Underwood INET: babette_at_rogers.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (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).
 |
 |