RE: How to clear UNDOTBS- Slightly OT

From: Goulet, Richard <Richard.Goulet_at_parexel.com>
Date: Mon, 18 Jan 2010 13:32:44 -0500
Message-ID: <6B0D50B70F12BD41B5A67F14F5AA887F0392D9AA_at_us-bos-mx022.na.pxl.int>

Kelly,  

    I'm not exactly sure about that. I've worked with a couple of
"canned" applications that really do use, and spec out in the install
guide, a lot of undo and temp. Sadly your right it is BAD code, but then they'll tell you "DISK IS CHEAP, BUY SOME MORE". Well it may be true that disk is cheap, until you have no more available & you have to go ask for some to be purchased. Then it's a close cousin to Platinum in cost. What gets me is that it's so easy to get around if people would:  

1. use procedural code vs. mass updates
2. define what a transaction is and then stick to that definition.

Had a developer who was doing a commit across a cursor in his code. Never managed to finish without generating errors that flustered his users. Added to his cursor definition "for update of" and never saw a problem from the users again. Sadly, or gladly that is, he never heard the end of it.  

Dick Goulet
Senior Oracle DBA/NA Team Lead
PAREXEL International  

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pedersen Sent: Monday, January 18, 2010 1:20 PM
To: 'Oracle-L Freelists'; ganstadba_at_hotmail.com Subject: RE: How to clear UNDOTBS- Slightly OT

And anybody who would be concerned that if they set their temp and undo to set sizes, folks would be less than understanding if they were to hit the threshold, (even when it's poor coding as the problem...) have you considered using dbms_resumable? We have it set in all our databases and a script that monitors for it. If anything does go awry, the transaction simply goes into a time out until we can take a quick look and address the problem, (like kill the query that is using all the temp) allowing the rest of the transactions to "resume" once temp or undo issues are resolved.

Kellyn Pedersen
Multi-Platform DBA
I-Behavior Inc.
http://www.linkedin.com/in/kellynpedersen www.dbakevlar.blogspot.com <http://www.dbakevlar.blogspot.com/>  

"Go away before I replace you with a very small and efficient shell
script..."

• On Mon, 1/18/10, Michael McMullen <ganstadba_at_hotmail.com> wrote:

	From: Michael McMullen <ganstadba_at_hotmail.com>
	Subject: RE: How to clear UNDOTBS- Slightly OT
	To: "'Oracle-L Freelists'" <oracle-l_at_freelists.org>
	Date: Monday, January 18, 2010, 10:56 AM
	
	

	I never set my undo or temp to autoextend. It's the easiest

database "tell" that somebody has written something screwy if the tablespaces blow up                   

---

        From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kellyn Pedersen

	Sent: Monday, January 18, 2010 12:26 PM
	To: ecandrietta_at_gmail.com; wellmetus_at_gmail.com
	Cc: ORACLE-L
	Subject: Re: How to clear UNDOTBS- Slightly OT

	  

 

How many DBA's inspect what is utilizing temp and undo? How many DBA's see a large temp and/or UNDO tablespace as a red flag in a database?

Kellyn Pedersen

Multi-Platform DBA

I-Behavior Inc.

http://www.linkedin.com/in/kellynpedersen

www.dbakevlar.blogspot.com <http://www.dbakevlar.blogspot.com/>  

"Go away before I replace you with a very small and efficient shell
script..."

• On Mon, 1/18/10, Roger Xu <wellmetus_at_gmail.com> wrote:

Hi,              

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 18 2010 - 12:32:44 CST