Home » RDBMS Server » Server Administration » Problem -Undo Tablespace Full - Unable to Extend Segment
| Problem -Undo Tablespace Full - Unable to Extend Segment [message #175469] |
Fri, 02 June 2006 07:15  |
kumarsz
Messages: 180
Registered: March 2005
Location: canada
|
Senior Member |
|
|
Hi,
i am using Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
We Got following error while creating a stored procedure
ORA- 00604: error occurred at recursive SQL level 1
ORA- 30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
I fired the following query which gave me result 'no rows selected'
SELECT r.name "RBS", s.sid, s.serial#, s.username "USER", t.status,
t.cr_get, t.phy_io, t.used_ublk, t.noundo,
substr(s.program, 1, 78) "COMMAND"
FROM sys.v_$session s, sys.v_$transaction t, sys.v_$rollname r
WHERE t.addr = s.taddr
and t.xidusn = r.usn
ORDER BY t.cr_get, t.phy_io;
Then i fired following query
select rs.usn,name,waits,rssize from sys.v_$rollstat rs, sys.v_$rollname rn where rs.usn = rn.usn
which gave me following results
USN NAME WAITS RSSIZE
--------- ------------------------------ ---------- ----------
0 SYSTEM 0 385024
1 _SYSSMU1$ 0 4186112
2 _SYSSMU2$ 0 3268608
3 _SYSSMU3$ 0 5365760
4 _SYSSMU4$ 1 2220032
5 _SYSSMU5$ 0 4317184
6 _SYSSMU6$ 0 6414336
7 _SYSSMU7$ 0 5365760
8 _SYSSMU8$ 8 393658368
9 _SYSSMU9$ 0 5365760
10 _SYSSMU10$ 1 4317184
seeing that first query didn't return any reults and undo tablespace used 100% i added one datafile to the Undo Tablespace.
On this context i have following queries, please suggest me on that,
1> If adding datafile, is is the only option, to get rid of "ORA- 30036: unable to extend"
2> If we found "WAITS" in above query what action can be taken to bring the waits on the respecive Undo Segments to 0.
3> i added the datafile yesterday, so there were two datafiles at that time; one with 100% usage and another just added;
however even today i found that the earlier file is still showing 100% usage. If rollback segments are used in round robbin
fashion whay this is happening?
4> how i can find uncommited transactions?
Thanks and Regards,
Pratap
|
|
|
|
| Re: Problem -Undo Tablespace Full - Unable to Extend Segment [message #175517 is a reply to message #175469] |
Fri, 02 June 2006 10:16  |
aciolac
Messages: 242
Registered: February 2006
|
Senior Member |
|
|
As I understand, You use automatic undo segment managment of Oracle 9i. This mean, that You cannot mane any change in rollback management, exept addnig new space. In this situation adding a new file is single option for preventing error. Also you must to know that this undo managment mode manage in such mode, that no one session didn't receive ORA-01555 error. This means, that if You have selects, that lasts very many time, all blocks changed during select, and queried by this selects, remains in rollback segments. Because of this thing, rollback segments can grow significantly(it is stirct different than in manual undo managment mode, where all changed blocks become free after commit). You must analyze tasks, performing in Your database and to make necessary changes, if need. Possible that You simple must to add more file space in 'UNDOTBS1'. I think, that response for 2) : nothing  . If You cannot change any parameter of undo managment in automatic mode: Oracle single make decisions. About 3) - don't worry - this is not a problem. In some cases, Oracle can decide to grow rollback segment instead shrinking them. Oracle think ,that he can use all free 'UNDOTBS1' space as good for him.
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 12:46:02 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
