Oracle9i Database Administrator's Guide
Release 1 (9.0.1)

Part Number A90117-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

12
Managing Datafiles

This chapter describes the various aspects of datafile management, and contains the following topics:

Guidelines for Managing Datafiles

Datafiles are physical files of the operating system that store the data of all logical structures in the database. They must be explicitly created for each tablespace. Oracle assigns each datafile two associated file numbers, an absolute file number and a relative file number, that are used to uniquely identify it. These numbers are described in the following table:

Type of File Number  Description 

Absolute 

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." 

Relative 

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 differs from the absolute file number. 

File numbers are displayed in many data dictionary views.

This section describes aspects of managing datafiles, and contains the following topics:

Determine the Number of Datafiles

At least one datafile is required for the SYSTEM tablespace of a database. A small system might have a single datafile. The following are some guidelines to consider when determining the number of datafiles for your database.

Determine the Value of the DB_FILES Initialization Parameter

When starting an Oracle instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance. This limit applies for the life of the instance. You can change the value of DB_FILES (by changing the initialization parameter setting), but the new value does not take effect until you shut down and restart the instance.


Note:

The default value of DB_FILES is operating system specific. 


When determining a value for DB_FILES, take the following into consideration:

Limitations When Adding Datafiles to a Tablespace

You can add datafiles to tablespaces, subject to the following limitations:

Consider the Performance Impact

The number of datafiles comprising a tablespace, and ultimately the database, can have an impact upon performance.

Oracle allows more datafiles in the database than the operating system defined limit. Oracle's DBWn processes can open all online datafiles. Oracle is capable of treating open file descriptors as a cache, automatically closing files when the number of open file descriptors reaches 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.

See Also:

 

Determine the Size of Datafiles

The first datafile (in the original SYSTEM tablespace) must be at least 150M to contain the initial data dictionary and rollback segment. If you install other Oracle products, they may require additional space in the SYSTEM tablespace. See the installation instructions for these products for information about their space requirements.

Place Datafiles Appropriately

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, consider placing potentially contending datafiles on separate disks.This way, when users query information, both disk drives can work simultaneously, retrieving data at the same time.

Store Datafiles Separate from Redo Log Files

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.

Creating Datafiles and Adding Datafiles to a Tablespace

When creating a tablespace, you can estimate the potential size of database objects and create sufficient files on multiple devices, so as to ensure that data is spread evenly across all devices. Later, if needed, you can create additional datafiles and add them to a tablespace to increase the total amount of disk space allocated to it, and consequently the database.

You can create datafiles and associate them with a tablespace using any of the statements listed in the following table. In all cases, you can either specify the file specifications for the datafiles being created, or you can use the Oracle Managed Files feature to create files that are created and managed by the database server. The table includes a brief description of the statement, as used to create datafiles, and references the section of this book where use of the statement is most completely described:

SQL Statement  Description  For more information... 

CREATE TABLESPACE 

Creates a tablespace and the datafiles that comprise it 

"Creating Tablespaces" 

CREATE TEMPORARY TABLESPACE 

Creates a locally-managed temporary tablespace and the tempfiles (tempfiles are a special kind of datafile) that comprise it 

"Creating a Locally Managed Temporary Tablespace" 

ALTER TABLESPACE ... ADD DATAFILE 

Creates and adds a datafile to a tablespace 

"Altering a Dictionary-Managed Tablespace" 

ALTER TABLESPACE ... ADD TEMPFILE 

Creates and adds a tempfile to a temporary tablespace 

"Creating a Locally Managed Temporary Tablespace" 

CREATE DATABASE 

Creates a database and associated datafiles 

"Manually Creating an Oracle Database" 

ALTER DATABASE ... CREATE DATAFILE 

Creates a new empty datafile in place of an old one--useful to re-create a datafile that was lost with no backup. 

Not discussed in this book. See Oracle9i User-Managed Backup and Recovery Guide

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.

If a statement that creates a datafile fails, Oracle removes any created operating system files. However, because of the large number of potential errors that can occur with file systems and storage subsystems, there can be situations where you must manually remove the files using operating system commands.

Changing a Datafile's Size

This section describes the various ways to alter the size of a datafile, and contains the following topics:

Enabling and Disabling Automatic Extension for a Datafile

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 provides these advantages:

To determine whether 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.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' 
    AUTOEXTEND OFF;

See Also:

Oracle9i SQL Reference for more information about the SQL statements for creating or altering datafiles 

Manually Resizing a Datafile

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 enables 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 /u02/oracle/rbdb1/stuff01.dbf:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
   RESIZE 100M;


Note:

It is not always possible to decrease the size of a file to a specific value. 


Altering Datafile Availability

You can take individual datafiles or tempfiles of a tablespace offline or similarly, bring them online. Offline datafiles are unavailable to the database and cannot be accessed until they are brought back online.You also have the option of taking all datafiles or tempfiles comprising a tablespace offline or online simply by specifying the name of a tablespace.

One example of where you might be required to alter the availability of a datafile is when Oracle has problems writing to a datafile and automatically takes the datafile offline. Later, after resolving the problem, you can bring the datafile back online manually.

The files of a read-only tablespace can independently be taken offline or brought online just as for read-write tablespaces. Bringing a datafile online 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.

To take a datafile offline, or bring it online, you must have the ALTER DATABASE system privilege. To take all datafiles or tempfiles offline using the ALTER TABLESPACE statement, you must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege. In an Oracle Real Application Clusters environment, the database must be open in exclusive mode.

This section describes ways to alter datafile availability, and contains the following topics:

Bringing Datafiles Online or Taking Offline in ARCHIVELOG 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:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;

To take the same file offline, issue the following statement:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;


Note:

To use this option of the ALTER DATABASE statement, the database must be in ARCHIVELOG mode. This requirement prevents you from accidentally losing the datafile, since taking the datafile offline while in NOARCHIVELOG mode is likely to result in losing the file. 


See Also:

Oracle9i User-Managed Backup and Recovery Guide for more information about bringing datafiles online during media recovery 

Taking Datafiles Offline in NOARCHIVELOG Mode

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 enables 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:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;

Altering the Availability of All Datafiles or Tempfiles in a Tablespace

Clauses of the ALTER TABLESPACE statement allow you to change the online or offline status of all of the datafiles or tempfiles within a tablespace. Specifically, the statements that affect online/offline status are:

You are required only to enter the tablespace name, not the individual datafiles or tempfiles. All of the datafiles or tempfiles are affected, but the online/offline status of the tablespace itself is not changed.

In most cases the above ALTER TABLESPACE statements can be issued whenever the database is mounted, even if it is not open. However, the database must not be open if the tablespace is the system tablespace, an undo tablespace, or the default temporary tablespace. The ALTER DATABASE DATAFILE and ALTER DATABASE TEMPFILE statements also have ONLINE/OFFLINE clauses, however in those statements you must enter all of the filenames for the tablespace.

The syntax is different from the ALTER TABLESPACE ... ONLINE|OFFLINE statement that alters a tablespace's availability, because that is a different operation. The ALTER TABLESPACE statement takes datafiles offline as well as the tablespace, but it cannot be used to alter the status of a temporary tablespace or its tempfile(s).

Renaming and Relocating Datafiles

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:

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. The procedures do not physically rename any operating system files, nor do they copy files at the operating system level. Renaming and relocating datafiles involves several steps. Read the steps and examples carefully before performing these procedures.

Renaming and Relocating Datafiles for a Single Tablespace

The section offers 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.

Renaming Datafiles in a Single Tablespace

To rename datafiles from a single tablespace, complete the following steps:

  1. Take the non-SYSTEM tablespace that contains the datafiles offline.

    For example:

    ALTER TABLESPACE users OFFLINE NORMAL;
    
    
  2. Rename the datafiles using the operating system.

  3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE option to change the filenames within the database.

    For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, 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.

  4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Relocating and Renaming Datafiles in a Single Tablespace

Here is an example that illustrates the steps involved for relocating a datafile.

Assume the following conditions:

Complete the following steps:

  1. Identify the datafile names of interest.

    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 DBA_DATA_FILES
    WHERE TABLESPACE_NAME = 'USERS';
    
    FILE_NAME                                  BYTES
    ------------------------------------------ ----------------
    /U02/ORACLE/RBDB1/USERS01.DBF              102400000
    /U02/ORACLE/RBDB1/USERS02.DBF              102400000
    
       
    
  2. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.

  3. Copy the datafiles to their new locations and rename them using the operating system.


    Note:

    You can execute an operating system command to copy a file by using the SQL*Plus HOST command. 


  4. Rename the datafiles within Oracle.

    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.

    ALTER TABLESPACE users
        RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
                        '/u02/oracle/rbdb1/users02.dbf'
                     TO '/u03/oracle/rbdb1/users01.dbf', 
                        '/u04/oracle/rbdb1/users02.dbf';
    
    
  5. Bring the tablespace online, or open the database.

    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.

  6. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Renaming and Relocating Datafiles for Multiple Tablespaces

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.

  1. Ensure that the database is mounted but closed.

  2. Copy the datafiles to be renamed to their new locations and new names, using the operating system.

  3. Use ALTER DATABASE to rename the file pointers in the database's control file.

    For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, 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 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.

  4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Verifying Data Blocks in Datafiles

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 are always 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.


Caution:

Setting DB_BLOCK_CHECKSUM to TRUE can cause performance overhead. Set this parameter to TRUE only under the advice of Oracle Support personnel to diagnose data corruption problems. 


See Also:

Oracle9i Database Reference for information about checksums and the DB_BLOCK_CHECKSUM initialization parameter 

Viewing Datafile Information

The following data dictionary views provide useful information about the datafiles of a database:

View  Description 

DBA_DATA_FILES 

Provides descriptive information about each datafile, including the tablespace to which it belongs and the file id. The file id can be used to join with other views for detail information. 

DBA_EXTENTS

USER_EXTENTS 

DBA view describes the extents comprising all segments in the database. Contains the file id of the datafile containing the extent. USER view describes extents of the segments belonging to objects owned by the current user. 

DBA_FREE_SPACE

USER_FREE_SPACE 

DBA view lists the free extents in all tablespaces. Includes the file id of the datafile containing the extent. USER view lists the free extents in the tablespaces accessible to the current user. 

V$DATAFILE 

Contains datafile information from the control file 

V$DATAFILE_HEADER 

Contains information from datafile headers 

This example illustrates the use of one of these views, V$DATAFILE.

SELECT NAME,
    FILE#,
    STATUS,
    CHECKPOINT_CHANGE# "CHECKPOINT"   
  FROM   V$DATAFILE;

NAME                                      FILE#     STATUS       CHECKPOINT
--------------------------------          -----     -------       ----------
/u01/oracle/rbdb1/system01.dbf                1     SYSTEM              3839
/u02/oracle/rbdb1/temp01.dbf                  2     ONLINE              3782
/u02/oracle/rbdb1/users03.dbf                 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 (system change number) written for a datafile's most recent checkpoint.

See Also:

Oracle9i Database Reference for a complete description of these views 


Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback