Re: ORA-01555 when trying to DROP TABLESPACE

On Feb 8, 12:27 am, Mladen Gogala <mgogala.SPAM_ME...._at_verizon.net> wrote:
> That is correct - provided you have enough space. There is no guarantee.
> The way to drop the tablespace is to offline it first. That will prevent
> UNDO generation for the objects within the tablespace.

Yes, that is exactly what we do. We locate the users that have the tablespace as a default and change them to point elsewhere, then we issue:

ALTER TABLESPACE ${DATATBS} OFFLINE;
DROP TABLESPACE ${DATATBS} INCLUDING CONTENTS; When we import the new tablespace, we point those users back to the new tablespace that was loaded.

One more question that was not answered by Metalink for me. Let's say I have 90% of my queries completing in 10 seconds, but I have 10% of the queries that may takes as much as 1000 seconds. If I understand this correctly, it means I should set my UNDO_RETENTION=1200 to be safe and help my query avoid ORA-01555 errors.

But what happens to the other 90% that produced undo data? Does this mean that even though my query completed and was committed with no errors in 10 seconds, Oracle will retain that data in the UNDO tablespace for the full 1200 seconds anyway? Or when the transaction completes and is committed, does that segment of undo data get flagged as re-usable at that point?

If I am tying up undo space for 2hrs at a time, would I then possibly have to increase the size of the UNDO tablespace to prevent having all the undo space in use and not expired?

Just trying to understand exactly how it works, and I did not see that info in the three links about UNDO provided by Metalink yet. (only read 2 of 3 so far)...

Steve Received on Thu Feb 08 2007 - 10:25:22 CST