Pre-allocating space for TEMP files in Oracle

We all know that  temporary tablespaces use sparse files  (specified using the TEMPFILE keyword in the ALTER/ADD TABLESPACE command)  meaning that the actual file created on disk is initially very small and grows to the specified size only as data is written to the file.

However, I was was working on a system that was low on disk space and needed to add 20GB of  space to the database’s default temporary tablespace.  In this situation I wanted to make sure that the entire 20GB was pre-allocated on disk and assigned to the temporary tablespace immediately — but how to do it?

There isn’t any way of specifying to Oracle that the tempfile should be pre-allocated, so the solution I came up with was to create a normal datafile (via a transitory tablespace) and then add it to the temporary tablespace using the REUSE clause as follows:

create tablespace MY_TBLSP datafile '/oracle/oradata/DEV/temp.dbf004' size 20480m;
drop tablespace MY_TBLSP;
alter tablespace TEMP add tempfile '/oracle/oradata/DEV/temp.dbf004' reuse;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

Blog at WordPress.com.

Up ↑

%d bloggers like this: