The following commands may be used to convert an 11g or 12c database to use a BIGFILE temporary tablespace datafile instead of the default regular file, which is limited to 32GB. BIGFILEs can grow to 32TB.

This can be useful when loading very large amounts of data into a database, for example for a POC test.

The following commands can be issued on a live database.  No outage is required.

SQL> create bigfile temporary tablespace bigtemp;

Tablespace created.

SQL> alter database default temporary tablespace bigtemp;

Database altered.

SQL> drop tablespace temp;

Tablespace dropped.

SQL> create bigfile temporary tablespace temp;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace bigtemp;