3.3. Managing Datafiles

If you are not using OMF, you will need to manage datafiles yourself. The database will create or reuse one or more datafiles in the sizes and locations that you specify whenever you create a tablespace. A datafile belongs to only one tablespace and only one database at a time. Temp files are a special variety of datafile that are used in temporary tablespaces. When the database creates or reuses a datafile, the operating system file is allocated and initialized—filled with a regular pattern of mostly binary zeros. This initialization will not occur with temp files.

Operations that you may need to perform on datafiles include the following:

  • Resizing them

  • Taking them offline or online

  • Moving (renaming) them

  • Recovering them

A useful technique for managing disk space used by datafiles is to enable AUTOEXTEND, which tells the database to automatically enlarge a datafile when the tablespace runs out of free space. The AUTOEXTEND attributes apply to individual datafiles and not to the tablespace.

To resize a datafile manually, use the ALTER DATABASE DATAFILE statement, like this:

ALTER DATABASE DATAFILE
   'C:ORACLEORADATAORA10DATA01.DBF' RESIZE 2000M;

To configure a datafile to automatically enlarge as needed by adding 100MB at a time up to a maximum of 8,000MB, execute the following:

ALTER DATABASE DATAFILE
   'C:ORACLEORADATAORA10DATA01.DBF'
AUTOEXTEND ON NEXT 100M MAXSIZE 8000M;

NOTE

Even if you do not plan to manage disk space using AUTOEXTEND, consider enabling it on your datafiles to avoid out-of-space failures in your applications.

To relocate a datafile, take it offline, move it using an operating system command, rename it, recover it (sync the file header with the rest of the database), and then bring it back online. Here is an example:

  1. Take it offline:

    ALTER DATABASE DATAFILE
   'C:ORACLEDATA02.DBF' OFFLINE;

  2. Copy it:

    HOST COPY C:ORACLEDATA02.DBF
   C:ORACLEORADATAORA10DATA02.DBF

  3. Change the filename in the control files:

    ALTER DATABASE RENAME FILE
   'C:ORACLEDATA02.DBF' TO
   'C:ORACLEORADATAORA10DATA02.DBF';

  4. Sync the file header with the database:

    RECOVER DATAFILE 'C:ORACLEORADATAORA10DATA02.DBF';

  5. Bring it back online so it can be used.

    ALTER DATABASE DATAFILE
   'C:ORACLEORADATAORA10DATA02.DBF' ONLINE;

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset