Using Oracle's AUTOEXTEND With Quick I/O Files
Oracle supports an automatic extend feature that automatically grows a database file by a prespecified amount, up to a prespecified maximum size.
For regular file system files, AUTOEXTEND works transparently, provided the underlying file system has enough space. For example, suppose the current size of a database file emp.dbf is 100MB, but this file is expected to triple in size over time. To accommodate this growth using AUTOEXTEND feature, you can specify the next size at 20MB and maxsize at 300MB. This will automatically grow the file by 20MB until its size reaches 300MB. For example:
alter database datafile 'emp.dbf' autoextend on next 20m\
maxsize 300m;
(See the Oracle Server SQL Reference Guide for more information about the alter database command, as well as the next and maxsize parameters.)
Note
You must have sufficient space on the underlying file system to AUTOEXTEND a file, and the underlying storage device must be large enough to contain the new, larger file system.
For Quick I/O files or raw devices, AUTOEXTEND does not know how to grow the underlying Quick I/O files or devices. Therefore, the Quick I/O file size must be large enough to accommodate the new size before AUTOEXTEND can grow the datafile.
You can use AUTOEXTEND with Quick I/O files in the following ways:
- Preallocate the Quick I/O file to a size at least as big as the maximum growth size expected for this database file.
Using this method, you would need to preallocate the Quick I/O file emp.dbf for the entire 300MB. The drawback is that this can unnecessarily lock up excess disk space. Raw devices have a similar requirement.
- Monitor the free space available in the Quick I/O file, and grow the file as necessary with the qiomkfile command.
Unlike raw devices, you can easily extend Quick I/O files online. Using this method, you can monitor the free space available in the Oracle datafiles and use the qiomkfile command to grow the Quick I/O files online as and when needed (typically when the file is about 80 to 90 percent full). This method does not require you to lock out unused disk space for Quick I/O files. The free space on the file system is available for use by other applications.
Options
For the qiomkfile command:
-e
|
Extends the file by a specified amount to allow Oracle tablespace resizing.
|
-r
|
Increases the file to a specified size to allow Oracle tablespace resizing.
|
Usage Notes
- You can grow underlying VxFS file systems online (provided the underlying disk or volume can be extended) using the fsadm command. See the fsadm_vxfs(1M) manual page for more information.
To monitor the free space available in an Oracle tablespace
Check the free space currently available in the Oracle tablespace using the following Oracle SQL command:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> select * from dba_free_space where \
tablespace_name = 'tablespace_name';
SQL> exit
To extend a Quick I/O file using qiomkfile
If the datafile is running low on free blocks, use the qiomkfile command to extend the Quick I/O file:
$ /opt/VRTS/bin/qiomkfile -e extend_amount /mount_point/filename
Example
To monitor the free space on the tablespace EMP on file system /db01:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> select * from dba_free_space where \
tablespace_name = 'EMP';
SQL> exit
To extend the Oracle datafile emp.dbf by 20MB (the specified next size) using the qiomkfile command:
$ /opt/VRTS/bin/qiomkfile -e 20M /db01/emp.dbf
|
