Oracle7 Server Administrator's Guide | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
See Also: This chapter contains several references to Oracle Server Manager. For more information about performing specific tasks using Server Manager/GUI or Server Manager/LineMode, see the Oracle Server Manager User's Guide.
Datafiles can also be created as part of database recovery from a media failure. For more information, see page 24 - 7.
For information on tablespaces and datafiles in Trusted Oracle7 Server, see the .
You can add datafiles to tablespaces, subject to the following operating system-specific datafile limits:
operating system limit
Each operating system sets a limit on the maximum number of files per process. Regardless of all other limits, more datafiles cannot be created when the operating system limit of open files is reached.
Oracle7 system limit
control file upper bound
At database creation, you must indicate the maximum number of datafiles expected for the database so that an adequate amount of space can be reserved in the database's control file. You set this limit with the MAXDATAFILES parameter in the CREATE DATABASE statement. This maximum cannot exceed the Oracle7 system limit or any operating system limit. If you are not sure how to set this parameter, use a high number to avoid unnecessary limitation. The default value is operating system-specific.
Note: You can increase space in the database by resizing datafiles. Resizing existing datafiles is useful if you are nearing the MAXDATAFILES limit.
instance or SGA upper bound
The use of DB_FILES and MAXDATAFILES is optional. If neither is used, the default maximum number of datafiles is the operating system-specific Oracle7 system limit.
See Also: For more information on operating system limits, see your operating system-specific Oracle documentation.
For information about Parallel Server operating system limits, see the manual.
For more information about MAXDATAFILES, see the Oracle7 Server SQL Reference.
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.
If you multiplex your redo log files, then the likelihood of your losing all of your redo log files is low, so you can store datafiles on the same drive as some redo log files.
To add datafiles to a tablespace, use either the Add Datafile dialog box of Server Manager/GUI, or the SQL command ALTER TABLESPACE. 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:
ALTER TABLESPACE rb_segs
ADD DATAFILE 'filename1' SIZE 1M;
If you add new datafiles to a tablespace and do not fully specify the filenames, Oracle7 creates the datafiles in the default directory of the database server. Unless you want to reuse existing files, make sure the new filenames do not conflict with other files; the old files that have been previously dropped will be overwritten.
Setting your datafiles to extend automatically results in the following:
You can specify automatic file extension when you create datafiles via the following SQL commands:
The following example enables automatic extension for a datafile, FILENAME2, added to the USERS tablespace:
ALTER TABLESPACE users ADD DATAFILE 'filename2' 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 automatic extension for the datafile FILENAME2:
ALTER DATABASE DATAFILE 'filename2' AUTOEXTEND OFF
See Also: For more information about the SQL commands for creating or altering datafiles, see the .
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 this example, assume that the datafile FILENAME2 has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.
The following command decreases the size of datafile FILENAME2:
ALTER DATABASE DATAFILE 'filename2' RESIZE 100M
Note: It is not always possible to decrease the size of a file to a specific value.
See Also: For more information about the implications resizing files has for downgrading, see the Oracle7 Server Migration.
For more information about the ALTER DATABASE command, see the .
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 Oracle7 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.
Note: 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.
Offline datafiles cannot be accessed. Bringing a datafile in a read-only tablespace online 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 command.
To bring a datafile online or take it offline, in either archiving mode, you must have the ALTER DATABASE system privilege. You can perform these operations only when the database is open in exclusive mode.
Note: To use this option of the ALTER DATABASE command, 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.
The following statement brings the specified datafile online:
ALTER DATABASE DATAFILE 'filename' ONLINE;
See Also: For more information about bringing datafiles online during media recovery, see page 24 - 7.
The following statement brings the specified datafile offline:
ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;
You can rename datafiles to change either their names or locations. Oracle7 provides options to make the following changes:
You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.
To Rename or Relocate Datafiles for a Single Tablespace
For example, the following statement renames the datafiles FILENAME1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:
ALTER TABLESPACE users
RENAME DATAFILE 'filename1', 'filename2'
TO 'filename3', 'filename4';
The new file must already exist; this command 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 filename exactly as it appears in the DBA_DATA_FILE view of the data dictionary.
To Rename and Relocate Datafiles for Multiple Tablespaces
For example, the following statement renames the datafiles FILENAME 1 and FILENAME2 to FILENAME3 and FILENAME4, respectively:
ALTER DATABASE
RENAME FILE 'filename1', 'filename2'
TO 'filename3', 'filename4';
The new file must already exist; this command 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 filename exactly as it appears in the DBA_DATA_FILE view of the data dictionary.
SELECT file_name, bytes FROM sys.dba_data_files
WHERE tablespace_name = 'USERS';
FILE_NAME BYTES
---------------------------
FILENAME1 102400000
FILENAME2 102400000
See Also: For more information about the DBA_DATA_FILES data dictionary view, see the .
For more information about taking a tablespace offline, see "Taking Tablespaces Offline" .
For more information about mounting a database without opening it, see Chapter 3.
When you enable block checking, Oracle7 computes a checksum for each block written to disk. Checksums are computed for all data blocks, including temporary blocks.
The DBWR 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 Oracle7 reads a data block, it uses the checksum to detect corruption in the block. If a corruption is detected, Oracle7 returns message ORA-01578 and writes information about the corruption to a trace file.
Warning: 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.
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.
![]() ![]() Prev Next |
![]() Copyright © 1996 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |