Handling Oracle Temporary Tablespaces and Quick I/O

You can create a new temporary tablespace using Quick I/O files. However, you cannot convert existing temporary tablespaces using regular files to Quick I/O with the qio_getdbfiles command on Oracle9i.

By default, qio_getdbfiles skips any tablespaces marked TEMPORARY because they can be sparse, which means that not all blocks in the file are allocated. Quick I/O files cannot be sparse, as Quick I/O provides a raw-type interface to storage. If a sparse file is converted to a Quick I/O file, the Oracle instance can fail if Oracle attempts to write into one of these unallocated blocks. When you initially create a temporary tablespace on Quick I/O files, however, Oracle sees them as raw devices and does not create sparse files.

To convert a temporary tablespace using regular files to Quick I/O files, you can drop your existing temporary tablespaces using regular files and recreate them using Quick I/O files. You can also leave the temporary tablespaces as regular files.

  To obtain a list of file names that are not temporary

Use the following SQL statements:

  To drop an existing temporary tablespace and recreate using Quick I/O files

1. Drop the temporary tablespace, including its contents:
      $ sqlplus /nolog
      SQL> connect / as sysdba;
      SQL>   drop tablespace tablespace_name including contents;
2. Create a Quick I/O file on a VxFS file system:
      $ /opt/VRTS/bin/qiomkfile -h header_size -s size \
      /mount_point/filename.dbf
3. Create a new temporary tablespace using the create tablespace or create temporary tablespace command.

   To use the create tablespace command:

   
      $ sqlplus /nolog
      SQL>   connect / as sysdba;
      SQL>   create tablespace tablespace_name \
      datafile '/mount_point/filename.dbf' \
      size size reuse \
      temporary;

   To use the create temporary tablespace command:

   
      $ sqlplus /nolog
      SQL>   connect / as sysdba;
      SQL>   create temporary tablespace tablespace_name \
      tempfile '/mount_point/new_filename.dbf' \
      size size reuse;

Example

To drop tablespace tempts, create a Quick I/O file temp01.dbf, and then create a new temporary tablespace tempts using the create tablespace command:

Example

To drop tablespace tempts, create a Quick I/O file temp01.dbf, and then create a new temporary tablespace tempts using the create temporary tablespace command: