| Temp segment [message #50640] |
Thu, 28 March 2002 01:36  |
Subha Viswanathan
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
Hi,
We are stuck with a problem. We have a large database with around 125 million rows pr table ( 10 tables in all with 3 views created on them).
When we start reading the views we get this problem.
Error message: [[SERVERMESSAGE]] ORA-01630: max # extents (505) reached in temp segment in tablespace TEMP
I had read in some of the FAQs abt this and did the following.
ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 1000M NEXT 100M
I get the following error after this.
Error message: [[SERVERMESSAGE]] ORA-01658: unable to create INITIAL extent for segment in tablespace TEMP
We had added 10 GB of datafiles to TEMP segment, thinking it was die to lack of disk space. Can anyone explain why this problem occurs, is it b'coz of lack of disk space or the segment size,etc..
Thanks a lot,
subha
|
|
|
|
| Re: Temp segment [message #50641 is a reply to message #50640] |
Thu, 28 March 2002 03:33  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
You MUST be doing a FULL table scan and to do this, you do not have enough space in the TEMP tablspace. There are possible solutions:
1. Create an index based on the query so that it hits the index.
2. Increase the TEMP size ..again.
3. Since you millions of rows, have you though of partitions ? Very usefull, specially for large tables like yours.
As for the error messaged, the first one is becoz there is not enough space in TEMP. Second on is bcoz your initial is 1 GB and there is no such big chunk in the tablespace.
Good luck.
|
|
|
|
| Re: Temp segment [message #50645 is a reply to message #50640] |
Thu, 28 March 2002 06:54 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
You actually want to do it the other way around. Initial extent should be greater than SORT_AREA_SIZE and next extent and maxextents should be large. I would also set PCTINCREASE to zero to reduce fragmentation. The way you have it set you would be lucky to have 2 users on the system using the views. But at this point your not even getting that. You did not list all your tablespace parameters or the datafile sizes. The number of datafiles matters because the extents cannot cross files. So making the initial 1G is a bit much. Give this a try:
ALTER TABLESPACE TEMP DEFAULT STORAGE (INITIAL 10M NEXT 100M MAXEXTENTS UNLIMITED PCTINCREASE 0)
Then shutdown the database to clear the temp segments and bring it back up and enter:
SQL> alter tablespace TEMP coalesce;
This will clean it up and you should be able to run your query. You may even be able to lower it more like NEXT 50M.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
