Skip to Main Content
  • Questions

  • Resize Undo Tablespace / Shared-Dedicated Server

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Neeraj.

Asked: March 01, 2002 - 8:46 pm UTC

Last updated: October 18, 2008 - 10:54 pm UTC

Version: 9i

Viewed 1000+ times

You Asked

Hi Tom,


1. In my database UNDO_MANAGEMENT = AUTO, but the size of

UNDO_TABLESPACE has grown to 6GB. How do i reduce/shrink it ? Do i need to manage the size of UNDO segements or anything ?

2. Whts the difference between Shared and Dedicated servers ? My application is deployed on WebLogic-Application Server, should i be using Oracle in a Shared Mode ? IF yes then:-
a. Currently its in Dedicated mode, how do i configure it in Shared Mode ? Do i need to re-install Oracle or recreate the database ?
b. How should i determine the parameters for shared server (no. of shared servers, max shared servers, dispatchers and max dispatchers ?


Thanks You,

Neeraj

and Tom said...

1) you need to look at your undo_retention_period. This controls the growth of the undo tablespace. If you ask us to save 1 hours worth of undo, we'll grow the undo tablespace to be big enough to hold at least 1 hours worth of undo at peak (for the hour you generate the MOST undo).

since you have 6gig now, that means there was some period of time during which you generated 6gig of undo during your undo_retention_period. Unless you did something extraordinary that you will never be doing again, shrinking your undo space will only result in it GROWING again.

you can shrink your space by creating a new, smaller undo tablespace, altering the system to use that new one(use scope=both if you are using spfiles, else make sure to update the init.ora pfile as well), waiting for all active transactions to finish in the old one and dropping it. But again, it'll most likely just grow to be 6gig all over again (which in the grand scheme of things today is not that big). I would leave it be.

Read chapter 13 of the ADMIN guide for details on managing an undo tablespace.


2) shared server is like connection pooling. You have a number of shared servers (say 25) that service a large community (say 1000) of users.

Web Logic typically uses a connection pool. They'll have a pool of say 25 connections that they keep open and share amongst all of the web requests.

Just as you would not want to have double buffering going on (eg: it is best to turn off the unix file system buffer cache on mounted file systems that have Oracle data/log/control files on them, we buffer, unix buffers -- it just wastes ram and time), you don't want double connection pooling going on. In general (warning ROT -- rule of thumb -- here), you would not use shared server with a connection pool. You would stick with dedicated server.

In any case, read the server concepts manual -- it explains the difference between the two. Read the Oracle9i net guide for setup. All docs available here for 9i:

</code> http://www.oracle.com/pls/db901/db901.to_toc?pathname=index.htm <code>


...



Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Resize Undo Tablespace / Shared-Dedicated Server

Neeraj Mehta, March 02, 2002 - 9:37 pm UTC

Thanks a lot Tom !


Resize Undo Tablespace / Shared-Dedicated Server

Neeraj, March 02, 2002 - 10:58 pm UTC

Where can i find the undo_retention_period ? And also do i alter the value of the same ?

Thank You


Tom Kyte
March 03, 2002 - 10:11 am UTC 

sorry - it is just "undo_retention"

it is an init.ora parameter

ops$tkyte@ORA9I.WORLD> show parameter undo_ret

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     900
ops$tkyte@ORA9I.WORLD> 

if you are using a pfile (text based parameter file) edit that.  If you are using an spfile (stored parameter file, new with 9i) use:

ops$tkyte@ORA9I.WORLD> alter system set undo_retention=1000 scope=spfile;
System altered.

Query Tom's solution

Simon, November 04, 2005 - 7:55 am UTC

Hi Tom,

You say "you can shrink your space by creating a new, smaller undo tablespace, altering
the system to use that new one <snip>, waiting for all active
transactions to finish in the old one and dropping it".

Why not just use the alter database datafile resize option?

Is there a good reason not to do it this way?

And is undo_management is set to auto will it still resize if need be?

Thanks

Tom Kyte
November 04, 2005 - 8:52 am UTC

how do you get Oracle to give up the extents is the question.


The undo tablespace is generally 100% allocated, it would not be shrinkable.

shrinkage

Si, November 04, 2005 - 11:41 am UTC

It's a 1000M tablespace with 3.33% space used.

I tried resize and it does work on the tablespace's datafile.

Given this fact, would this be detrimental to resize it to a smaller size?

Tom Kyte
November 04, 2005 - 5:30 pm UTC

you can shrink a file if there is space to be shrunk, NORMALLY an undo tablespace will be "allocated" and shrinking it smaller than what it is allocated is not possible.


You have a big tablespace.
you NEVER grew that big in the undo segment.

therefore, you can shrink.

old undo tablespace drop in grid

ravi, October 18, 2008 - 5:45 am UTC

1>i have physical standby database at remote site .In production database i created new undo tablespace of less size and assign new undo tablespace .when i tried to drop old undo tablespace i got error as
sql> drop tablespace UNDOTBS1 including contents;
ORA-01156: recovery in progress may need access to files
2>on physical standby database new undotbs2 not shown
sql>show parameter undo;
old undotbs1
what i should do to clearly drop untbs1 at production database and what will be effect if undotbs name differ in standby database
kindly suggest pl

Tom Kyte
October 18, 2008 - 10:54 pm UTC

you do the operations on the primary, we cascade them to the standby

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm#sthref1041

you would need to update the standby init.ora to open it later in order to update the undo tablespace name.

study this chapter
http://docs.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm#i1022518
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.