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.