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;
Solution for Oracle, Java, Php, Javascript, Mybatis. Introduction on Free Document Management System.
Thursday, November 12, 2009
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;
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;
Subscribe to:
Posts (Atom)