About

Martin Klier

usn-it.de

Oracle: Change (default) TEMP and UNDO tablespace to bigfile tablespaces

That’s how to change the Oracle Database’s TEMP and UNDO tablespaces to bigfile (as possible in 10g and above). It’s something I usually do for every DB I install.

create bigfile temporary tablespace TEMP1 tempfile \
     size 10G autoextend on next 1G maxsize 20G;
create bigfile undo tablespace UNDO1 datafile \
     size 10G autoextend on next 1G maxsize 20G;
alter system set undo_tablespace=UNDO1 scope=both sid='*';
alter database default temporary tablespace TEMP1;
drop tablespace UNDOTBS1;
drop tablespace TEMP;

Be careful,
Usn

Oracle 11g look-at’s
Oracle: Query/Monitoring autoextend of datafiles

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.