9.7 How do I…Configure database files to extend automatically?

Problem

I have sized my database files as carefully as I can, but I am concerned that they will run out of space during a crucial database operation. How can I tell the Oracle8 server to extend data files automatically without any DBA intervention?

Technique

Use the AUTOEXTEND option on the create tablespace or alter database commands to enable autoextend capability on data files. Through these statements you can also specify the size of the increment for Oracle8 to use when the server must autoextend a data file. Query the DBA_DATA_FILES data dictionary view to determine or confirm the autoextend status of data files. This view appears in Table 9.5.

Table 9.5 The DBA_DATA_FILES data dictionary view

Column Column Description
FILE_NAME Physical file name
FILE_ID File number in database
TABLESPACE_NAME Tablespace to which the file belongs
BYTES File size in bytes
BLOCKS File size in blocks
STATUS File status: AVAILABLE or INVALID
RELATIVE_FNO File number in tablespace
AUTOEXTENSIBLE File can autoextend (YES or NO)
MAXBYTES Limit on file’s growth in bytes
MAXBLOCKS Limit on file’s growth in blocks
INCREMENT_BY Autoextension increment in blocks

Steps

1. Run SQL*Plus and connect as the WAITE user. The script CHP9_13.SQL is operating-system–specific because it contains physical file names. Modify it as necessary for your operating system prior to execution. Use the START command to load and execute CHP9_13.SQL. The script and its output appear in Figure 9.17.

The first statement creates a new tablespace called AUTO09, which is a single data file. The initial size of the file is 2MB, but it can autoextend as necessary up to 50MB. The second SQL statement modifies an existing data file so that it can autoextend.

2. Use the START command to load and execute the CHP9_14.SQL script, which queries the DBA_DATA_FILES data dictionary view and lists all data files with autoextend capability. Figure 9.18 contains the script and its output.

How It Works

Step 1 illustrates two methods for enabling autoextend capability for a data file: The first is to create a tablespace consisting of an autoextending file, and the second is to modify an existing data file to autoextend. Step 2 queries the appropriate data dictionary view to confirm the success of Step 1.

Comments

The autoextension defaults do not serve every Oracle8 installation. Figure 9.18 shows that the defaults are accepted for the MAXSIZE and NEXT parameters in the USERS tablespace. Oracle8 set the values of these parameters to 8 gigabytes and 1 block, respectively.

Remember that it is a waste of time to enable autoextension for a data file residing on a full, or nearly full, device. Autoextension in this case fails because of inadequate space availability at the physical level. In such a situation, the options are to rename the data file using the alter database rename... statement or to add an additional autoextensible data file, residing on a less populated device, to the appropriate tablespace.

It is tempting to combine the information in How-To’s 9.5 and 9.7 so that database objects and data files can extend without limit. It would appear that this architecture frees DBAs from space management altogether! A database like this, however, will not run out of space until Oracle8 reaches a hardware limitation (usually, a full disk). When this happens, though, options for resolution are limited to freeing disk space, adding another disk, or reorganizing the database to move some data files to less populated media. These operations take time and will delay the offending database operation until the DBA resolves the space limitation. Paradoxically, the autoextend options can increase the need for space management monitoring, so use them with care.