Thursday, November 12, 2009

Switch the default system temporary tablespace

create temporary tablespace temp_new tempfile
'd:\oracle\oradata\test\temp1.dbf' size 500m autoextend on next 10m maxsize unlimited
extent management local;

Switch to new temporary tablespace. You may need to switch the temporary tablespace to new one to reduce the size of the current tempfile and remove it.

alter database default temporary tablespace temp_new;

You may not switch until the tablespace is in use.

Drop the old one .

drop tablespace temp_od including contents and datafiles;

Switch undo to new undo tablespace Oracle 9i onwards

Create New Undo Tablespace

create undo tablespace undo_new datafile
'd:\oracle\oradata\test\undodata1.dbf' size 1024m autoextend off maxsize unlimited online;

Issue these command one or more times to force the commited data write in datafiles.

alter system checkpoint;

alter system switch logfile;

alter database archive log current;

Modify the SIDinit.ora file parameter

undo_tablespace=undo_new

Shutdown the database

shutdown immediate;

Check the parameter value

show parameter undo_tablespace

Drop the old undo tablespace and physical datafiles

drop tablespace undo_old including contents and datafiles;