Temporary Tablespaces

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 which use regular files to Quick I/O with the qio_getdbfiles command on Oracle9.

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 which use 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

Drop the temporary tablespace, including its contents:

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> drop tablespace tablespace_name including contents;
Create a Quick I/O file on a VxFS file system:

# /opt/VRTS/bin/qiomkfile -h header_size -s size \
/mount_point/filename.dbf
Change the owner and group permissions on the file

# chown oracle:dba .filename
# chmod 660 .filename
Create a new temporary tablespace using the create temporary tablespace command.

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;
This example shows how to drop tablespace tempts, create a Quick I/O file temp01.dbf, change permissions and then create a new temporary tablespace tempts using the create temporary tablespace command:

$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> drop tablespace tempts including contents;
Tablespace dropped.
# /opt/VRTS/bin/qiomkfile -h 32k -s 100M /db01/temp01.dbf
# chown oracle:dba .temp01
# chmod 660 .temp01
$ sqlplus /nolog
SQL> connect / as dba;
SQL> create temporary tablespace tempts \
tempfile ‘/db01/temp01.dbf’ \
size 100M reuse;
Tablespace created.

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