Oracle9i UNDO

[julian]

Originally posted by ashley75
what would you use if you have 9i on your production database???

I would not use OMFs.

What is the best way for DBA to manage their database???

Thanks

Learn, learn, learn.

[ashley75]

What is the best way for DBA to manage their database???

Thanks

Learn, learn, learn.
[/B][/QUOTE]

What I mean was would you use UNDO_MANAGEMENT=AUTO on your production??? and how would you change your undo segment (naming convention)on the database ???? Please show me some instructions.

[thanigai]

I am using oracle created roll back segment only..
it works fine to me.
Though we have option to use user created rollback i did not try.
note:I have created 516 m UN_DO tablespace because we have huge loading oftenly.
Thanks.
Thanigaivasan.

[TimHall]

You should definitely use the automatic undo management. In future versions manual will be deprecated.

Why do you care about the name of the undo segments. You should never need to do anything with them anyway???

[julian]

What I mean was would you use UNDO_MANAGEMENT=AUTO on your production???

Yes, definitely.

and how would you change your undo segment (naming convention)on the database ???? Please show me some instructions.

When using AUM you cannot rename ORA_whatever to for example QWERTY. Oracle using naming convention for AUM.

[pando]

what would you guys do if this happens

undo tablespace is eating up 99% of a filesystem however there is only one transaction going on but not committed. We have to reduce the size of undo tablespace but since it is not possible whatshould we do? I can only think of creating another undo tablespace set the new undo to this new one and drop the old undo but since there is a transaction going in the old undo we cannot drop it, if this were manually managed we could shrink the RBS manually but I dont think this is possible in undo.... this could happen sort of tricky

[TimHall]

Autoextend gone mad

[pando]

yea but what if file system is 99% full and you need to free the file system, obviously we could add datafiles in other file systems but since we cannot reduce undo tablespace size this can get tricky!
And considering smon only wakes up every 12 hours..... to free the unused undo

[julian]

Originally posted by pando
and drop the old undo but since there is a transaction going in the old undo we cannot drop it

I could, just tried (9.2). I got really surprised, did not expect for that to happen. I had a pending transaction in UNDOTBS1 (non-committed). I created UNDOTBS2, switched the system to the new tablespace, ran drop tablespace UNDOTBS1. It got dropped. The transaction was automatically commited by Oracle after dropping UNDOTBS1.

[jmodic]

Originally posted by julian
I could, just tried (9.2). I got really surprised, did not expect for that to happen. I had a pending transaction in UNDOTBS1 (non-committed). I created UNDOTBS2, switched the system to the new tablespace, ran drop tablespace UNDOTBS1. It got dropped. The transaction was automatically commited by Oracle after dropping UNDOTBS1.

That's extremely strange! I can't test this right now, but I would be really surprised if Oracle really automatically commited that pending transaction. Are you sure, Julian?

Unless, of course, you performed DROP TABLESPACE from within the same session that started that uncommited transaction. If this is the case, then of course it was you that implictly commited the transaction, not the database system itself.