Home » RDBMS Server » Server Administration » undo tablespace (oracle10g, windows)
undo tablespace [message #377252] Mon, 22 December 2008 02:42 Go to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
In our database the undo tablespace is growing by 3 to 4 times of the original size. And i am running out of disk space.

Can anybody tell me the possible reasons for this.
Re: undo tablespace [message #377254 is a reply to message #377252] Mon, 22 December 2008 02:50 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Do you use automatic undo management? What is your retention period set to? Do you have any long running jobs or queries running?

Please post your DB's undo parameters:
SQL> show parameters undo

Re: undo tablespace [message #377267 is a reply to message #377252] Mon, 22 December 2008 03:14 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
Undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1

How to check the long running sql queries.
Re: undo tablespace [message #377284 is a reply to message #377267] Mon, 22 December 2008 04:04 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Please post the result of the following query:
SELECT TO_CHAR(begin_time, 'DD-MON-RR HH24:MI'),
       TO_CHAR(end_time, 'DD-MON-RR HH24:MI'),
       tuned_undoretention,
       maxquerylen, maxqueryid
FROM v$undostat ORDER BY end_time
/

Re: undo tablespace [message #377289 is a reply to message #377252] Mon, 22 December 2008 04:25 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
please find the output./fa/5461/0/
  • Attachment: sqloutput.png
    (Size: 26.43KB, Downloaded 420 times)
Re: undo tablespace [message #377291 is a reply to message #377289] Mon, 22 December 2008 04:29 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
There you have it. Oracle automatically increased your undo_retention to 20237 seconds (almost 6 hours!) because you have a query that is running that long. Get the query's text from v$sql and tune it.
Re: undo tablespace [message #377496 is a reply to message #377252] Tue, 23 December 2008 04:35 Go to previous messageGo to next message
samg4ug
Messages: 33
Registered: July 2007
Location: India
Member
Hi,

Have you faced "ORA-01555 Snapshot Too Old" problem frequently in past time?

Then why the undo retention period is that large ?

More over try to put commits within the operation if possible and check if there are user/jobs that require the old data for that much time and also try to schedule the dependent jobs with some proper interval.

Regards,
Sam G
Re: undo tablespace [message #377509 is a reply to message #377496] Tue, 23 December 2008 05:07 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Sam, that's not going to help. As you can see Oracle increased his undo_retention automatically to cater for the long running queries in his database.
Re: undo tablespace [message #377542 is a reply to message #377252] Tue, 23 December 2008 07:04 Go to previous messageGo to next message
saiphani723
Messages: 36
Registered: July 2006
Location: Hyderabad
Member
Thank you very much frank,

Problem identified and rectified. Problem was because of the new reports developed by one of my team member.
Re: undo tablespace [message #377697 is a reply to message #377542] Wed, 24 December 2008 06:22 Go to previous message
samg4ug
Messages: 33
Registered: July 2007
Location: India
Member
Hi,

Thanks Frank, that gives a new idea and tuning is obvoiusly required.

But I have got to monitor some tera bytes of database that has only 900 secs undo_retention and works fine. More over it is a Oracle Application database and have huge finance related operations.

Another thing is that undo tablespace should not be set to unlimited maxsize. And same to temp tablespace also.And whenever some temporary or undo segment related issue comes find the queries and ask the developer to tune it. Increase only there is no other way.

Well these are the processes we follow here.

If you people can give new inputs, that would help me and others in present and in future who ever will see the post in need.

Regards,
Sam G
Previous Topic: analyze
Next Topic: Cannot start sqlplus
Goto Forum:
  


Current Time: Wed Dec 07 10:19:14 CST 2016

Total time taken to generate the page: 0.18753 seconds