Add datafile to UNDOTBS

[claire]

We have a production process that fills up UNDOTBS very frequently and that causes process to be aborted.

In emergency, I'd like to add more datafile for UNDO tablespace, would it drop/block/reject user transactions?

In stead of adding new data file, would it be better to just change existing data file to grow unlimited.

Thanks
Claire

[davey23uk]

adding datafiles doesnt cause blocks or waits

[PAVB]

We have a production process that fills up UNDOTBS very frequently...

If you UNDO space and related parameters are properly set up may be is time to look at the application and fix fix your offending production process.

[oraclej]

hi,

check the parameter of undo rentention . if it is 3600 or more than than reduce to 900 seconds then the performance will increase.


thanks
oraclej

[PAVB]

check the parameter of undo rentention . if it is 3600 or more than than reduce to 900 seconds then the performance will increase.

1- Performance would not "increase", that's false.
2- Performance is not the issue poster is facing.
3- Lowering undo_retention would increase ORA-01555 likelihood.

Are you trying to misslead poster on purpose?

[davey23uk]

hi,

check the parameter of undo rentention . if it is 3600 or more than than reduce to 900 seconds then the performance will increase.


thanks
oraclej

what a mongy statement - please stay clear of databases

[claire]

Please pardon my ignorance, I heard that UNDO should overwrite if it hit the space ceiling.

Is this false information?

Only Archive log should overwrite isnt it?

[PAVB]

Please pardon my ignorance, I heard that UNDO should overwrite if it hit the space ceiling.

Providing undo segments expired... yes.

Is this false information?

No, it's not.

Only Archive log should overwrite isnt it?

Nope.

[groucho47]

I have a large (3 TB) data warehouse, with a motley rabble of end users unleashing horrible queries at it all day. We are on v. 10.2.0.3.

Auto undo management is one of the best things Oracle ever rolled out. I admit to being skeptical at first, but I tried it (in 9i), and have never regretted that decision.

HOWEVER:

Considering the apocalyptic queries dispatched by our users, I have set the undo retention to 24000; that's right, 24000, as in 6.666666 hours. The undo tablespace itself has one 30G file.

We very rarely experience an ora-01555 error. When we get one, it is usually traced to a really bad query that would never complete, even if we had a 30 terabyte undo tablespace and unlimited retention. So Oracle puts the offending query out of its misery; a fitting end to a wasted life.

Oracle onlines and offlines the undo segments as needed, and does a good job of managing the undo space.

Here is a link to a website that pasted an Oracle Bulletin on undo management into a word document. You may find it useful.

http://www.globleat.com/articles/ora...0and%20sol.doc

[claire]

Hat off to all!