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




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.

2 Responses to “Oracle: Change (default) TEMP and UNDO tablespace to bigfile tablespaces”

  1. Andy Says:

    it is a good idea!

    .. but you did not point to the physical file in the above command?

  2. Martin Klier Says:

    No, I am using Oracle Managed Files for everything.

Leave a Reply