Convert your database to use a BIGFILE tempfile

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;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s