how 2 offline a undo TS

[DaDuck]

How come an undo TS can not be offline after:
alter system set undo_tablespace=undo2 scope=memory;

I need this to decrease undo size.

regards,

DaDuck

[jkoopmann]

you are more than likely comming up against active a user who has not commited thier transaction and has active undo in the old undo tablespace that you are trying to offline. that being said, i have gone through this process and even though there were not "current" transactions in the old undo tablespace, i still was unable to take it offline. i remedied this by switching between the two undo tablespaces again and it cleared itself up. the case seems to be that there can't be any "ACTIVE" transactions in the current undo tablespace as you are issuing the DDL to switch to a new undo tablespace.

three things to think about to get around this issue.
1. look at the dba_undo_extents view and look for "ACTIVE" extents, if there are none you may be able to squeek in and get the statement issued.
2. you might want to checkout implementing resource management and then you will be able to issue the alter system quiesce restricted statement and this will allow you to then switch and take offline the undo tablespace.
3. you might just want to see if you can use the alter database ... datafile resize option to resize the datafile for your current undo tablespace.

[DaDuck]

Thank you very much for your reply

The undo tablespace shrink is ment to run after a database startup.
So i'm creating and testing this in an enviroment where no other users are logged in.

An undo tablespace can hardly be reduced in size by decreasing the datafile, due to fragmentation.

Manualy i am able switch from one undo_ts to another. However when i try to do the same thing using dbms_sql.parse i does not seem to switch. No error is raised during the switch statement. But since offlining the orginal undo_ts, does raise an error, it is clear that the switch was not succesful.
Also after the procedure is ended with the ora-30042 error, the same error is returned, when i issue the undo tablespace offline statement menualy. This offline undo tablespace statent only works when the undo tablespace swith is issued manualy.

I planned to issue the switch statement again and again (with waits), untill the switch is succesful. However i do not know how i can determine if the switch was succesful. I tried to use the begin or and_time from v_$undostat, but that also does not seem to help.

Kind regards,

Duncan van der Zalm
Oracle DBA
Truston Group
Nieuwegein - Netherlands

[DaDuck]

Well apparently the undo ts switch was not possible using dbms_sql.parse.

So i've tried a complete other way.

One could change the rate of which de tablespace is reduced.

Check out the attachment.

Regards,
D.