|Oracle8i Administrator's Guide
Release 2 (8.1.6)
This chapter describes the various aspects of datafile management, and includes the following topics:
This section describes aspects of managing datafiles, and includes the following topics:
Every datafile has two associated file numbers: an absolute file number and a relative file number.
An absolute file number uniquely identifies a datafile in the database. In earlier releases of Oracle, the absolute file number may have been referred to as simply, the "file number."
A relative file number uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number will differ from the absolute file number. You can locate relative file numbers in many data dictionary views.
At least one datafile is required for the SYSTEM tablespace of a database; a small system might have a single datafile. In general, keeping a few large datafiles is preferable to many small datafiles, because you can keep fewer files open at the same time.
You can add datafiles to tablespaces, subject to the following operating system-specific datafile limits:
Each operating system sets a limit on the maximum number of open files per process. Regardless of all other limits, more datafiles cannot be created when the operating system limit of open files is reached.
Oracle imposes a maximum limit on the number of datafiles for any Oracle database opened by any instance. This limit is port-specific.
When you issue CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. Later, if you add a file whose number exceeds MAXDATAFILES but is less than or equal to the value specified by the DB_FILES initialization parameter, the control file automatically expands to allow the datafile portion to accommodate more files.
When starting an Oracle8 instance, the database's initialization parameter file indicates the amount of SGA space to reserve for datafile information; the maximum number of datafiles is controlled by the DB_FILES initialization parameter. This limit applies only for the life of the instance.
With the Oracle Parallel Server, all instances must set the instance datafile upper bound to the same value.
When determining a value for DB_FILES, take the following into consideration:
Theoretically, an Oracle database can have an unlimited number of datafiles. Nevertheless, you should consider the following when determining the number of datafiles:
Oracle allows more datafiles in the database than the operating system-defined limit; this can have a negative performance impact. When possible, adjust the operating system limit on open file descriptors so that it is larger than the number of online datafiles in the database.
The operating system specific limit on the maximum number of datafiles allowed in a tablespace is typically 1023 files.
For information about Parallel Server operating system limits, see Oracle8i Parallel Server Administration, Deployment, and Performance.
For more information about MAXDATAFILES parameter of the CREATE DATABASE or CREATE CONTROLFILE statement, see the Oracle8i SQL Reference.
The first datafile (in the original SYSTEM tablespace) must be at least 7M to contain the initial data dictionary and rollback segment. If you install other Oracle products, they may require additional space in the SYSTEM tablespace (for online help, for example); see the installation instructions for these products.
Tablespace location is determined by the physical location of the datafiles that constitute that tablespace. Use the hardware resources of your computer appropriately.
For example, if several disk drives are available to store the database, it might be helpful to store table data in a tablespace on one disk drive, and index data in a tablespace on another disk drive. This way, when users query table information, both disk drives can work simultaneously, retrieving table and index data at the same time.
Datafiles should not be stored on the same disk drive that stores the database's redo log files. If the datafiles and redo log files are stored on the same disk drive and that disk drive fails, the files cannot be used in your database recovery procedures.
If you multiplex your redo log files, then the likelihood of losing all of your redo log files is low, so you can store datafiles on the same drive as some redo log files.
Ideally, when creating a tablespace, you should estimate the potential size of the database objects and add sufficient files or devices, so as to ensure that data is spread evenly across all devices. Later, if needed, you can create and add datafiles to a tablespace to increase the total amount of disk space allocated for the tablespace, and consequently the database.
To add datafiles to a tablespace, you use the ALTER TABLESPACE...ADD DATAFILE statement. You must have the ALTER TABLESPACE system privilege to add datafiles to a tablespace.
The following statement creates a new datafile for the RB_SEGS tablespace:
If you add new datafiles to a tablespace and do not fully specify the filenames, Oracle creates the datafiles in the default database directory or the current directory, depending upon your operating system. Oracle recommends you always specify a fully qualified name for a datafile. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files. Old files that have been previously dropped will be overwritten.
This section describes the various ways to alter the size of a datafile, and includes the following topics:
You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The files increase in specified increments up to a specified maximum.
Setting your datafiles to extend automatically results in the following:
To find out if a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.
You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:
You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile using the SQL statement ALTER DATABASE.
The following example enables automatic extension for a datafile added to the USERS tablespace:
ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M AUTOEXTEND ON NEXT 512K MAXSIZE 250M;
The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.
The next example disables the automatic extension for the datafile.
For more information about the SQL statements for creating or altering datafiles, see the Oracle8i SQL Reference.
You can manually increase or decrease the size of a datafile using the ALTER DATABASE statement.
Because you can change the sizes of datafiles, you can add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.
Manually reducing the sizes of datafiles allows you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.
In the next example, assume that the datafile
/u02/oracle/rbdb1/stuff01.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.
The following statement decreases the size of datafile
For more information about the implications resizing files has for downgrading, see Oracle8i Migration
This section describes ways to alter datafile availability, and includes the following topics:
In very rare situations, you might need to bring specific datafiles online (make them available) or take specific files offline (make them unavailable). For example, when Oracle has problems writing to a datafile, it can automatically take the datafile offline. You might need to take the damaged datafile offline or bring it online manually
You can make all datafiles in a tablespace, other than the files in the SYSTEM tablespace, temporarily unavailable by taking the tablespace offline. You must leave these files in the tablespace to bring the tablespace back online.
For more information about taking a tablespace offline, see "Taking Tablespaces Offline".
Offline datafiles cannot be accessed. Bringing online a datafile in a read-only tablespace makes the file readable. No one can write to the file unless its associated tablespace is returned to the read-write state. The files of a read-only tablespace can independently be taken online or offline using the DATAFILE option of the ALTER DATABASE statement.
To bring a datafile online or take it offline, you must have the ALTER DATABASE system privilege. You can perform these operations only when the database is open in exclusive mode.
To bring an individual datafile online, issue the ALTER DATABASE statement and include the DATAFILE clause.The following statement brings the specified datafile online:
To take the same file offline, issue the following statement:
For more information about bringing datafiles online during media recovery, see the Oracle8i Backup and Recovery Guide.
To take a datafile offline when the database is in NOARCHIVELOG mode, use the ALTER DATABASE statement with both the DATAFILE and OFFLINE DROP clauses. This allows you to take the datafile offline and drop it immediately. It is useful, for example, if the datafile contains only data from temporary segments and has not been backed up and the database is in NOARCHIVELOG mode.
The following statement takes the specified datafile offline:
You can rename datafiles to either change their names or relocate them. Some options, and procedures which you can follow, are described in the following sections:
For example, renaming filename1 and filename2 in tablespace1, while the rest of the database is open.
For example, renaming filename1 in tablespace1 and filename2 in tablespace2, while the database is mounted but closed.
When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database's control file, are changed; they do not physically rename any operating system files, nor do they copy files at the operating system level. Therefore, renaming and relocating datafiles involves several steps. Read the steps and examples carefully before performing these procedures.
These are some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.
To rename datafiles from a single tablespace, follow this procedure.
For example, the following statement renames the datafiles filename1 and filename2 to filename3 and filename4, respectively:
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf', '/u02/oracle/rbdb1/user2.dbf' TO '/u02/oracle/rbdb1/users01.dbf', '/u02/oracle/rbdb1/users02.dbf';
The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
Here is an example that illustrates the steps involved for relocating a datafile.
Assume the following conditions:
These are the steps:
The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the USERS tablespace:
SELECT file_name, bytes FROM sys.dba_data_files WHERE tablespace_name = 'USERS'; FILE_NAME BYTES ------------------------------------------ ---------------- /U02/ORACLE/RBDB1/USERS01.DBF 102400000 /U02/ORACLE/RBDB1/USERS02.DBF 102400000
Before making any structural changes to a database, such as renaming and relocating the datafiles of one or more tablespaces, always completely back up the database.
The datafile pointers for the files that make up the USERS tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.
If the tablespace is offline but the database is open, use the ALTER TABLESPACE...RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE...RENAME FILE statement.
If the USERS tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.
You can rename and relocate datafiles of one or more tablespaces using ALTER DATABASE statement with the RENAME FILE option. This option is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation, or rename or relocate datafiles of the SYSTEM tablespace. If the database must remain open, consider instead the procedure outlined in the previous section.
To rename datafiles of several tablespaces in one operation or to rename datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system privilege.
To rename datafiles in multiple tablespaces, follow these steps.
For example, the following statement renames the datafiles filename1 and filename2 to filename3 and filename4, respectively:
ALTER DATABASE RENAME FILE '/u02/oracle/rbdb1/sort01.dbf', '/u02/oracle/rbdb1/user3.dbf' TO '/u02/oracle/rbdb1/temp01.dbf', '/u02/oracle/rbdb1/users03.dbf;
The new file must already exist; this statement does not create a file. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
If you want to configure Oracle to use checksums to verify data blocks, set the initialization parameter DB_BLOCK_CHECKSUM to TRUE. The value of this parameter can be changed dynamically, or set in the initialization parameter file. The default value of DB_BLOCK_CHECKSUM is FALSE. Regardless of the setting of this parameter, checksums will always be used to verify data blocks in the system tablespace.
When you enable block checking, Oracle computes a checksum for each block written to disk. Checksums are computed for all data blocks, including temporary blocks.
The DBWn process calculates the checksum for each block and stores it in the block's header. Checksums are also computed by the direct loader.
The next time Oracle reads a data block, it uses the checksum to detect corruption in the block. If a corruption is detected, Oracle returns message ORA-01578 and writes information about the corruption to a trace file.
The following data dictionary views provide useful information about the datafiles of a database:
Provides descriptive information about datafiles, including the tablespace to which it belong and the file id. The file id can be used to join with other views for detail information.
Lists the extents comprising all segments in the database. Contains the file id of the datafile containing the extent.
Lists the free extents in all tablespaces. Includes the file id of the datafile containing the extent.
Contains datafile information from the control file.
Contains information from datafile headers.
This example illustrates the use of one of these views, V$DATAFILE.
Assume you are using a database that contains two tablespaces, SYSTEM and USERS. USERS is made up of two files, FILE1 (100MB) and FILE2 (200MB); the tablespace has been taken offline normally. Here, you query V$DATAFILE to view status information about datafiles of a database:
SELECT name, file#, status, checkpoint_change# "CHECKPOINT" FROM v$datafile; NAME FILE# STATUS CHECKPOINT -------------------------------- ----- ------- ---------- filename1 1 SYSTEM 3839 filename2 2 OFFLINE 3782 filename3 3 OFFLINE 3782
FILE# lists the file number of each datafile; the first datafile in the SYSTEM tablespace created with the database is always file 1. STATUS lists other information about a datafile. If a datafile is part of the SYSTEM tablespace, its status is SYSTEM (unless it requires recovery). If a datafile in a non-SYSTEM tablespace is online, its status is ONLINE. If a datafile in a non-SYSTEM tablespace is offline, its status can be either OFFLINE or RECOVER. CHECKPOINT lists the final SCN written for a datafile's most recent checkpoint.
For a complete description of these views, see Oracle8i Reference.