Turn on Event logs in SQLplus

If you want to turn on the Event log in SQLplus for troubleshooting, do this:

1. Turn on the event

alter system set events '1017 trace name errorstack level 10';

2. Run the query your want to trace

3. Turn off the event

alter system set events '1017 trace name errorstack off';

4. Check trace file in bdump folder.

Shrink UNDO tablespace

Oracle 11g 10g 9i  tablespace database undo 

How to shrink the undo tablespace in Oracle? 

We need to create a tablespace temporary so that we can redirect undo usage to this temperorary tablespace. 

Steps:

1. Create a temporary tablespace for swapping

CREATE undo TABLESPACE undotbs2 DATAFILE

     '/dbora01/oradata/ORCL/ora02/undotbs2_01.dbf' size 100M;

2. Use it as default UNDO tablespace

ALTER SYSTEM SET undo_tablespace=undotbs2;

3. Drop the old UNDO tablespace

DROP TABLESPACE undo including contents;

4. Create back the original UNDO tablespace

CREATE undo TABLESPACE undo DATAFILE

     '/dbora01/oradata/ORCL/ora02/undo01.dbf' size 5000M REUSE,

     '/dbora01/oradata/ORCL/ora03/undo02.dbf' size 5000M REUSE;

5. Set the default UNDO tablespace back to the original one

ALTER SYSTEM SET undo_tablespace=undo;     

6. Drop the temporary one

DROP TABLESPACE undotbs2 including contents and datafiles;

7. End

Shrink Temporary Tablespace

Steps:

1. Create a temporary tablespace for swapping

create temporary tablespace temp_temp tempfile

'/dbora01/oradata/ORCL/ora02/temp_temp01.dbf' size 100M

extent management local uniform size 1m;

2. Use it as default temporary tablespace

alter database default temporary tablespace temp_temp;

3. Drop the old temporary tablespace

alter tablespace temp tempfile offline;

drop tablespace temp including contents;

4. Create back the original temporary tablespace and put online

create temporary tablespace temp tempfile

       '/dbora01/oradata/ORCL/ora02/temp01.dbf' size 5000M reuse,

       '/dbora01/oradata/ORCL/ora03/temp02.dbf' size 5000M reuse

       extent management local uniform size 1m ;

alter tablespace temp tempfile online;

5. Set the default temporary back to the original one

alter database default temporary tablespace temp;

6. Drop the temporary one

drop tablespace temp_temp including contents and datafiles;

7. End