Home » RDBMS Server » Server Administration » unable to extend TEMP -- Did I do all that needs to be done?
| unable to extend TEMP -- Did I do all that needs to be done? [message #122098] |
Thu, 02 June 2005 17:07  |
lhradowy
Messages: 5
Registered: June 2005
Location: Winnipeg, Canada
|
Junior Member |
|
|
I just received an error from the alert log:
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP
Now I do understanding that the TEMP tablespace with not size down until the database is restarted, so stay 99.9% is not a bad thing. But, what I do what to know is if I did everything to correct the problem.
First I ran:
select sql_text,
sid,
c.username,
machine,
tablespace,
extents,
blocks
from sys.v_$sort_usage a,
sys.v_$sqlarea b,
sys.v_$session c
where a.sqladdr = b.address and
a.sqlhash = b.hash_value and
a.session_addr = c.saddr
order by sid
This gave me the Sid, User name, sql_text ( I did not create this query myself, but pulled from net)
Output:
222.00 USER1 WORKGROUP\LAPT TEMP 132.00 17,160.00
I do not know how the extents and blocks (132.00 & 17,160) plays into, or what it means??? But, it did return the user name and sid
I then ran:
select s.sid || ',' || s.serial# sid,
s.username,
u.tablespace,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)) sql_text,
round(((u.blocks*p.value)/1024/1024),2) size_mb
from v$sort_usage u,
v$session s,
v$sqlarea a,
v$parameter p
where s.saddr = u.session_addr
and a.address (+) = s.sql_address
and a.hash_value (+) = s.sql_hash_value
and p.name = 'db_block_size'
group by
s.sid || ',' || s.serial#,
s.username,
substr(a.sql_text, 1, (instr(a.sql_text, ' ')-1)),
u.tablespace,
round(((u.blocks*p.value)/1024/1024),2)
(again from net)
This came up with 3...
SID USERNAME TABLESPACE SQL_TEXT SIZE_MB
123,10674 ACAPP TEMP -- 10.16
193,1966 USER2 TEMP SELECT 704.84
222,3729 USER1 TEMP SELECT 134.06
I contacted User2 and had him log off. This still did not clear. So I did an:
Alter system kill session '193,1966';
User1, this query ended or the user killed him self. Now I understand, it is probably because they were quering on columns that were not indexed, and the query was quite huge. BUt, what I would like to know is if I covered all my steps to fix the problem.
Q1: Also, I did not touch the ACAPP, since this is part of the application and I do not know if I would break anything. How do I check to see what this SID is actually doing? Or to find more info on it.
Q2: The SIZE_MB? Is this the amount that the query is reserving in the temp tablespace to run?
Q3: What other checks can be run to be sure I cleared this problem up?
|
|
|
|
|
|
|
|
|
|
| Re: unable to extend TEMP -- Did I do all that needs to be done? [message #123076 is a reply to message #123035] |
Thu, 09 June 2005 15:43  |
Ricky_newbie
Messages: 12
Registered: May 2005
|
Junior Member |
|
|
Hello,
Didn't mean to jump into this Thread. But have the same kind of issue.
Here is an explanation of problem, i have a 4 tablespace and yesterday it gave an error ORA-1652: unable to extend temp segment by 128 in tablespace NAME. I added space to existing datafile instead of adding 1 more datafile. By the way i had 4 datafile 1 gb each and added 1 gb to one of the datafile, which makes my TS 4 GB. In no time 1 of the developers reported about the same error and i see that the datafiles are full again. I didn't ask him what he was doing, or what has he been running. I added 2 more datafile 2 GB each. Now works, fine. When i last saw the first 4 datafiles were at 99%. After adding another 2 datafiles( Not to mention, they are at 0%), i see that the first 4 datafiles are back to normal by
DF1 80%, DF2 10%, DF3 30%, DF4 50%.
Can any one tell me why did they grow very fast and how did they shrink? I read BOL on this and could not get the point.
Any explanation would be appriciated.
Thanks,
Rick
Editing: BTW it was not TEMP tablespace.
[Updated on: Thu, 09 June 2005 15:44] Report message to a moderator |
|
|
|
| Re: unable to extend TEMP -- Did I do all that needs to be done? [message #123084 is a reply to message #122098] |
Thu, 09 June 2005 16:34 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Could be any number of things. Oracle allocates "new things" as temporary segments while in the process of building them, and then turns them into "real things" (recognizable objects like indexes) when it is finished with the build process.
Maybe a developer made a copy of a table and then later deleted that copy?
Maybe a table with a couple bitmap indexes was being modified, which caused the index to swell or bloat many times its original size, and then later the index was rebuilt, shrinking it?
Maybe a large amount of data was direct path loaded into a table with indexes such that oracle had to maintain a copy of the existing index, a copy of the newly added data index, and then a third copy temporarily to merge the other two together before finally purging the other two?
So in other words, a temp segment in a "regular" tablespace is not the same as the TEMP tablespace.
|
|
|
|
|
|
| Re: unable to extend TEMP -- Did I do all that needs to be done? [message #123213 is a reply to message #122098] |
Fri, 10 June 2005 10:01 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
A regular view does not take up space like a table does. It is just a stored copy of a query. A materialized view, on the other hand, does occupy space.
But if this is a development environment, you can control disk space by only granting permission to developers to do the exact things that they need to do. If they don't need it, don't grant it to them. Also, you can set up quotas of disk space allowed to be used per developer per tablespace. That may help.
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 07:12:34 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
