- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: Select statement causing ORA-1652
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2004 01:12 PM
тАО04-06-2004 01:12 PM
Select statement causing ORA-1652
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2004 02:32 PM
тАО04-06-2004 02:32 PM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2004 04:08 PM
тАО04-06-2004 04:08 PM
Re: Select statement causing ORA-1652
when appropriate indexes are not available temporary segments will also be used.
regards,
Thierry.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2004 04:11 PM
тАО04-06-2004 04:11 PM
Re: Select statement causing ORA-1652
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2004 04:30 PM
тАО04-06-2004 04:30 PM
Re: Select statement causing ORA-1652
check again to make sure that default temp tablespace for user not TAB1.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-06-2004 05:29 PM
тАО04-06-2004 05:29 PM
Re: Select statement causing ORA-1652
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-07-2004 01:50 PM
тАО04-07-2004 01:50 PM
Re: Select statement causing ORA-1652
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-07-2004 05:24 PM
тАО04-07-2004 05:24 PM
Re: Select statement causing ORA-1652
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-07-2004 05:44 PM
тАО04-07-2004 05:44 PM
Re: Select statement causing ORA-1652
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-07-2004 07:45 PM
тАО04-07-2004 07:45 PM
Re: Select statement causing ORA-1652
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