OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » undo tablespace (oracle10g, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

undo tablespace [message #377252]

Mon, 22 December 2008 02:42

saiphani723
Messages: 38
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.

Report message to a moderator

Re: undo tablespace [message #377254 is a reply to message #377252]

Mon, 22 December 2008 02:50

Frank Naude
Messages: 4580
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

Report message to a moderator

Re: undo tablespace [message #377267 is a reply to message #377252]

Mon, 22 December 2008 03:14

saiphani723
Messages: 38
Registered: July 2006
Location: Hyderabad

Member

Undo_management AUTO
undo_retention 10800
undo_tablespace UNDOTBS1

How to check the long running sql queries.

Report message to a moderator

Re: undo tablespace [message #377284 is a reply to message #377267]

Mon, 22 December 2008 04:04

Frank Naude
Messages: 4580
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
/

Report message to a moderator

Re: undo tablespace [message #377289 is a reply to message #377252]

Mon, 22 December 2008 04:25

saiphani723
Messages: 38
Registered: July 2006
Location: Hyderabad

Member

please find the output /forum/fa/5461/0/

Attachment: sqloutput.png
(Size: 26.43KB, Downloaded 1406 times)

Report message to a moderator

Re: undo tablespace [message #377291 is a reply to message #377289]

Mon, 22 December 2008 04:29

Frank Naude
Messages: 4580
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.

Report message to a moderator

Re: undo tablespace [message #377496 is a reply to message #377252]

Tue, 23 December 2008 04:35

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

Report message to a moderator

Re: undo tablespace [message #377509 is a reply to message #377496]

Tue, 23 December 2008 05:07

Frank Naude
Messages: 4580
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.

Report message to a moderator

Re: undo tablespace [message #377542 is a reply to message #377252]

Tue, 23 December 2008 07:04

saiphani723
Messages: 38
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.

Report message to a moderator

Re: undo tablespace [message #377697 is a reply to message #377542]

Wed, 24 December 2008 06:22

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

Report message to a moderator

Previous Topic:	analyze
Next Topic:	Cannot start sqlplus

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 03:45:44 CDT 2024