Operating System - HP-UX
1748076 Members
5322 Online
108758 Solutions
New Discussion юеВ

Select statement causing ORA-1652

 
kenny chia
Regular Advisor

Select statement causing ORA-1652

Hi
When I ran a select statement that returns alot of data (300825 rows),
There is no sorting in the select statement (eg order by)
the following oracle error is logged into the alert file

ORA-1652: unable to extend temp segment by 256000 in tablespace TAB1

Why is there a temp segment in the tablespace TAB1? Will a select statement create temp segments in a tablespace?
The tablespace TAB1 is used to store tables only
Oracle 8 is used

All Your Bases Are Belong To Us!
15 REPLIES 15
Hein van den Heuvel
Honored Contributor

Re: Select statement causing ORA-1652


You don't give us much to go on.

Why not include the select statement in question. I suspect that the oracle optimizer decided it needed a 'merge join'.
I'm sure that is described in many places, including the Oracle doc.

Google pointed me to http://www.adp-gmbh.ch/ora/explainplan.html
Which reads "MERGE JOIN
Usually, you want to select data across multiple table which is referred to as to join the tables. MERGE JOIN is just one possible method of joining the data (the others are HASH JOIN, NESTED LOOPS and CLUSTER JOIN). A Sort Merge Join basically sorts all relevant rows in the first table by the join key (ba), and also sorts the relevant rows in the second table by the join key (aa), and then merges these sorted rows."

You can verify whether this is happening through EXPLAIN PLAN.

An other possible reason is that one of the (secondary) tables used in the select is not a base table but a VIEW.

Hope this helps,
Hein.
Thierry Poels_1
Honored Contributor

Re: Select statement causing ORA-1652

Hi,

when appropriate indexes are not available temporary segments will also be used.

regards,
Thierry.
All unix flavours are exactly the same . . . . . . . . . . for end users anyway.
Yogeeraj_1
Honored Contributor

Re: Select statement causing ORA-1652

hi,

two things may be occuring.

1. The tablespace TAB1 has very little space left.
2. The default temporary tablespace of this user is TAB1.

Please post the output of this SQL command:
===========================================
select temporary_tablespace
from dba_users
where username=''
===========================================

You may also have to review your query by analysing your execution plan and add an index on that table accordingly.

You might also be using PERMANENT tablespace for temporary use. how was your temporary tablespace created?

I usually create all my temporary tablespaces as follows (example):
============================================
create temporary tablespace test_temp
tempfile '/tmp/test_temp.dbf' size 512k reuse
extent management local
uniform size 64k
/
============================================

best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Printaporn_1
Esteemed Contributor

Re: Select statement causing ORA-1652

Hi,

check again to make sure that default temp tablespace for user not TAB1.

enjoy any little thing in my life
Nicolas Dumeige
Esteemed Contributor

Re: Select statement causing ORA-1652

Hello,

You refer to the alertSID.log, but don't you have the same error in SQL*Plus ?

To get the explain plan for the SELECT statement :

SET ECHO OFF
SET LINESIZE 120
COLUMN PDEB FORMAT A4
COLUMN PFIN FORMAT A4
COLUMN QUERY_PLAN FORMAT A60
COLUMN PLAN FORMAT A80
SET ECHO ON

explain plan
for
---------------------
YOUR SELECT STATEMENT
---------------------
SET ECHO OFF
SELECT
id,
position,
lpad(' ',2*(level-1))|| operation || ' ' || options || ' ' || object_name || ' ' || object_type || ' ' ||
object_instance || ' ' || decode(id,0, 'Cost = ' || position) Query_Plan,
level,
PARTITION_START PDEB,
PARTITION_STOP PFIN
FROM
plan_table
START WITH
id = 0
CONNECT BY PRIOR
id = parent_id
;
-- Or the Oracle script
@?/rdbms/admin/utlxpls
rollback;
SET ECHO ON

Nicolas
All different, all Unix
Brian Crabtree
Honored Contributor

Re: Select statement causing ORA-1652

1. Check your user temporary tablespace setting. It is possible that it is set to this tablespace.

2. Check your object's PARALLEL settings. It could be using a parallel query to pull data from the table, which means that 4 queries are pulling data, and the information is stored in a temporary segment.

Even though you don't have an order by, any join is still going to need temporary space to hold data from the join so that it can select the rows needed.

Thanks,

Brian
kenny chia
Regular Advisor

Re: Select statement causing ORA-1652

Hi all
This is the result of the SQL Explain plan statement

0 SELECT STATEMENT Cost = 1
1 1 SORT UNIQUE 2
2 1 NESTED LOOPS 3
3 1 NESTED LOOPS 4
4 1 NESTED LOOPS 5
5 1 TABLE ACCESS BY INDEX ROWID LOT_DATA 2 6
6 1 INDEX RANGE SCAN LOT_DATA_PRD_INDEX NON-UNIQUE 7
7 2 TABLE ACCESS BY INDEX ROWID WIP_DATA 1 6
8 1 INDEX RANGE SCAN WIP_DATA_WIP_LOT NON-UNIQUE 7
9 2 TABLE ACCESS BY INDEX ROWID EQ_DATA 3 5
10 1 INDEX UNIQUE SCAN EQ_DATA UNIQUE 6
11 2 INDEX RANGE SCAN WTEST_DATA UNIQUE 4
All Your Bases Are Belong To Us!
Hein van den Heuvel
Honored Contributor

Re: Select statement causing ORA-1652

So there is a sort right there in the explain plan to accomplish a unique (or group by?) clause in the select.
Now we know why the temp segment may needed as well as to hold the collecttion.

Now check the temp dataspace for the user as requested. Just do a select * from dba_users where username = 'xxx';

Btw... 256000 is not too much. If it is just sort, can you not avoid the disk sort by increasing sort_area_size and sort_area_retained_size? (sqlplus> show parameter sort )

Hein.

Yogeeraj_1
Honored Contributor

Re: Select statement causing ORA-1652

hi,

important that you post the output of the following:
===========================================
select temporary_tablespace
from dba_users
where username=''
===========================================

Also, post a more complete trace statistics of the SQL command:
============================================
e.g.
SQL> set autotrace traceonly
SQL> select sysdate from dual;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DUAL'


Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
1 consistent gets
0 physical reads
0 redo size
299 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL>
============================================

These statistics will shed some more light on the problem that you are encountering.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)