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,

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply