How to reduce UNDO tablespace

Undo tablespace is used to keep unrolled data. Sometime it may increase tremendously because some of the SQL statement is not committed.

We can reduce the tablespace sizing for undo tablespace by following method. Assuming your current undo tablesapce name is 'UNDOTBS':
1. Ensure there' s no application is connected to the database.
2. Login as Oracle user and connect to SQL as sysdba. Then create another undo tablespace and set as default.
SQL>create undo tablespace undonew datafile '/undonew.dbf' size 1024M;
SQL>alter system set undo_tablespace='undonew';
By doing this, it will change the default undo_tablespace id undonew in spfile. If you are not using spfile. then you may skip this and edit the undo_tablespace parameter in initfile.
3. Drop the old undo tablespace including all.
SQL>drop undotbs tablespace including contents and datafiles;

4. Shutdown database.
5. After database has been shutting down, you may edit your initfile to change the undo_tablespace parameter to 'undonew'.
6. Startup database
7. Create the old database 'UNDOTBS' and set it as default.
SQL>create undo tablespace UNDOTBS datafile '/undo01.dbf' size 1024M;
SQL>alter system set undo_tablespace='UNDOTBS';

As usual, if you are using initcfile, change the parameter value after shutting down database in initfile.

8. Drop the undonew tablespace including contents.
SQL>drop undonew tablespace including contents and datafiles;

9. Shutdown database.
10.Change the undo_tablespace parameter from undonew to undotbs if you are using initfile.
11. startup database.

ORA-04021: timeout occurred while waiting to lock object

This morning I received an e-mail from my college... she got an error while doing upgrade on her environment. This had happen few times when they finish do Fresh Install, and bring up the middleware and later when middleware has been brought down, they got error while doing upgrade.

I was a looking for a solution about this, without using DBA studio (still using Oracle9i). So what I asked them to do:

1. Bring down the middleware.
2. Connect to SQL as sysdba, and check the processes running using that schema/dbuser.
SQL>select sid, serial#, schemaname from v$session;

3.They will see some output and kill the processes using:
SQL>alter system kill session 'sid,serial#';

4.At one point, they schemaname still exist but we should check the status for that session.
SQL>select sid, serial#, status, process from v$session where schemaname='SCHEMANAME';
If the status given is 'KILLED', means all processes has been killed. Then they able to proceed with their installation.

whatever

This is all about my dreamworld... not so real but some are reals..

happy reading..