VERITAS Storage Foundation 4.1 Oracle Administrator's Guide

Handling Oracle Temporary Tablespaces and Quick I/O Prerelease 8 September 2005, 8:54am
98 VERITAS Storage Foundation for Oracle Administrator’s Guide
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:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> select file_name from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name and b.contents <>
'TEMPORARY';
▼ 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