-
Hi,
Recently I got the following error in my alert.log file
ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORARY
My temproray tablespace is 300M and is Localy managed with uniform extents of 1M each.
How will this effect the database or the query/statement because of which this error occurred.
Should I just keep on increasing the tablespace till this error goes away even if the temp tablespace has to be of 10G. I mean how much is enough. How do I find out.
How to size the temporary tablespace.
Also is there a way to find out which statement triggered this error?
Please suggest.
Thanks
[Edited by ronnie on 06-13-2002 at 05:28 PM]
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Size requirements are purely based on your requirements.
Keeping it simple TEMP depends on you Table Sizes.
Then it varies for what your are doing -- unions, joins, group by, order by ...
Apart from these temp is used when you create indexes.
If your are doing complex queries with multiple joins and group by's on tables with millions of rows, then you really need big TEMP.
I went upto 6 GIG for a TEMP before
Sridhar R Patnam
-
There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...
Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..
Reddy,Sam
-
Originally posted by sreddy
There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...
Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..
and how harmful is this error for the database and what what will happen to the statement which triggered it.
sreddy can you please let us know about the issues with Temo LMT because I recently changed all my TEMP's to LMT's from Dictionary managed.
Also I have a couple of databases where RBS is LMT and they alsi seem to be working fine. Any thing which I should look for...
Thanks
Ronnie
ronnie_yours@yahoo.com
You can if you think you can.
-
Originally posted by: sreddy
There are some problems in Using LMTs for TEMP...So I create LMTs only for DATA and INDEX but not TEMP. Anyway LMTs do not support SYSTEM & RBS...
Yeah! Query depends on TEMP tablespace. Say for example If your query is trying SORT and your SORT_AREA_SIZE is limited it has to use TEMP tablespace for sorting and it can't extend for the space needed you get ORA-1652 error..
I too never heard of TEMP having issues with LMT.
LMT support RBS and from version 9 it supports SYSTEM as well.
Sanjay
-
I should have been little specific about the version I'm talking. Iam talking about LMTs on 8i.
I had little weird experience on creating TEMP tablespace as LMT... I can't see that tablespace in the list when I query dba-tablespaces view...
9i LMTs support all types of tablespaces... as SANJAY say. I'm not sure about the oracle version you are talking about ..
This error is not going to harm your database anyway, if it can't extend TEMP tablespace. Probably your query will not come back OR if someone trying run a complex query this error might get repeated. You don't need to be panic...
Reddy,Sam
-
Originally posted by sreddy
I had little weird experience on creating TEMP tablespace as LMT... I can't see that tablespace in the list when I query dba-tablespaces view...
Well, I see mine when I run select * from dba_tablespaces; :-)
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
LMT support RBS and from version 9 it supports SYSTEM as well.
Sanjay
Only from 9.2!
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
LMT RBS is supported since 8i but since it had many bugs.....
-
Originally posted by ronnie
Hi,
ORA-1652: unable to extend temp segment by 128 in tablespace TEMPORARY
[Edited by ronnie on 06-13-2002 at 05:28 PM]
Have you set MAXSIZE for temporary tablespace?
Oracle Certified Master - September, 2003, the Second OCM in China
*** LOOKING for PART TIME JOB***
Data Warehouse & Business Intelligence Expert
MCSE, CCNA, SCJP, SCSA from 1998
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|