How to Create Non-Sparse TEMP files

Temp Files

Temporary data files in Oracle are a special type of data file. Oracle will use temporary files to store the intermdiate results of a large sort operation, and hash operations, as well as to store global temporary table data, or result data when there is insufficient memory to hold it all in RAM. Temp files also never have redo generated for them, though they can generate undo. Temporary data files never need to have backups done and doing so is a waste of time since upon recovery they need to be recreated.

Once of the nuances with temp files is that if the OS permits, the temporary files will be created sparse, i.e. they will not actually consume disk storage until they need to. This is mostly the case from my experience on UNIX/Linux systems and can be seen by comparing the output of df anddu or ls for the file system on which the temp files were created.

This results in temp files being created almost instantneously since no storage is allocated, however, since you can create files larger than the space you actually have available, you will quickly run into errors such as 'no more space' when that space is then being requested.

Creating non-sparse TEMP files

There are a few ways to create non-sparse files, I'm just showing the way I've used and know work.

1. Create the OS file:

$> dd if=/dev/zero of=/u02/oradata/ORCL/temp1_01.dbf bs=1024k count=1024

2. Create the TEMP tablespace and/or temp file:

SQL> CREATE TEMPORARY TABLESPACE temp1 

TEMPFILE '/u02/oradata/ORCL/temp1_01.dbf' REUSE;

Using Oracle SPFILEs

Using Server Parameter Files (SPFILEs)

SPFILEs remove the proliferation of parameter files, as well as the ability to maintain such files outside the database using text editors. The default naming convention is

spfile$ORACLE_SID.ora - UNIX/Linux environment
spfile%ORACLE_SID%.ora - Windows environment

I recommended to maintain the default location, i.e. $ORACLE_HOME/dbs, as doing otherwise defeats the simplicity SPFILEs represent. Also, moving the file elsewhere involves you telling Oracle this new location and otherwise keeping your own internal audit of that location, leading to the similar problems caused by using parameter files.

SPFILEs and Oracle RAC

When using Oracle RAC, all instances share the same SPFILE. This single SPFILE contains all the parameters for all instances with instance-specific settings identified by the SID.

*.cluster_database_instance=2
*.db_name='ORCL'
ORCL1.undo_tablespace='UNDO_ORCL1'
ORCL2.undo_tablespace='UNDO_ORCL2'

In the above example, the *.db_name='ORCL' indicates all instances using this SPFILE will be mounting a database named 'ORCL'. The ORCL1.undo_tablespace='UNDO_ORCL1' indicates the instance named ORCL1 will use that specific undo tablespace and so on.

Setting & Unsettting Values in SPFILEs

The command below shows the format of the command used to manipulate settings using an SPFILE.

ALTER SYSTEM SET parameter=value ;

Portions in <> are optional, and the presence of the pipe symbol indicates "one of the list".

The various sections are pretty straight forward so I will no go into most of them here.

• The DEFERRED option which specifies the system change is to take place for subsequent sessions only (not currently established sessions). This is important since by default changes will take effect immediately, but some parameters cannot be changed 'immediately' - they can only be changed for newly established sessions. The error received for such commands is 'ORA-02096: specified initialization parameter is not modifiable with this option'.

• The SID='sid|*' is mostly useful in a clustered environment; SID='*' is the default. This allows you to specify a parameter setting uniquely for any given instance in the cluster. However, if you need to 'unset' a parameter such that it does not show in the SPFILE and is therefore defaulted, you will need to make use of SID='sid|*' component. For example, to 'unset' the SORT_AREA_SIZE parameter:

ALTER SYSTEM RESET sort_area_size SCOPE=spfile SID='*';

The general format to 'unset' parameters is

ALTER SYSTEM RESET parameter SID='sid|*';

SPFILEs Copies

You can create backup copies of your SPFILE as either a one-time parameter file
CREATE pfile FROM spfile;

Using an OS copy command, or if on a UNIX/Linux platform you can extract your settings using:

strings spfile$ORACLE_SID.ora

On Windows you can just open in WordPad and copy and paste the text which will be formatted as clear text into another file. You can also obtain all the non-default parameters in the alert.log file since this is displayed following each instance start and build your pfile from those settings.