Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback.
Just e-mail: and include the URL for the page. All rights reserved by Burleson. 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. This section describes the various ways to alter the size of a datafile, and contains 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. The following example enables automatic extension for a datafile added to the users tablespace:. The value of NEXT is the minimum size of the increments added to the file when it extends.
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. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.
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. 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:. You can make all datafiles in any tablespace, except 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".
The following statement brings the specified datafile online:. This enables you to take the datafile offline and drop it immediately. You are required only to enter the tablespace name, not the individual datafiles or tempfiles.
However, the database must not be open if the tablespace is the system tablespace, an undo tablespace, or the default temporary tablespace.
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. 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. The section offers some procedures for renaming and relocating datafiles in a single tablespace.
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. 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 users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.
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. There is no SQL statement that specifically drops a datafile.
The only means of dropping a datafile is to drop the tablespace that contains the datafile. For example, if you want to remove a datafile from a tablespace, you could do the following:.
For example:. The value of this parameter can be changed dynamically, or set in the initialization parameter file. 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 DBW n 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 and writes information about the corruption to a trace file.
In an environment where datafiles are simply file system files or are created directly on a raw device, it is relatively straight forward to see the association between a tablespace and the underlying device. This poses a problem because it becomes difficult to determine your "hottest" files when they are hidden behind a "black box".
This section presents Oracle's approach to resolving this problem. It provides an easy to use graphical interface for mapping files to physical devices. Oracle provides a mechanism to show a complete mapping of a file to intermediate layers of logical volumes to actual physical devices. Using these views, you can locate the exact disk on which any block of a file resides.
Oracle communicates with these libraries through an external non-Oracle process that is spawned by an Oracle background process called FMON. FMON is responsible for managing the mapping information. This section describes the components of Oracle's file mapping interface and how the interface works. It contains the following topics:. The following sections briefly describes these components and how they work together to populate the mapping views:.
FMON is responsible for:. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. Copy the datafiles to be renamed to their new locations and new names, using the operating system. Skip Headers. Renaming and Relocating Datafiles You can rename datafiles to either change their names or relocate them.
Some possible procedures for doing this are described in the following sections: Procedures for Renaming and Relocating Datafiles in a Single Tablespace Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database control file, are changed. Procedures for Renaming and Relocating Datafiles in a Single Tablespace The section suggests some procedures for renaming and relocating datafiles that can be used for a single tablespace.
See Also: "Taking Tablespaces Offline" for more information about taking tablespaces offline in preparation for renaming or relocating datafiles. Procedure for Renaming Datafiles in a Single Tablespace To rename datafiles in a single tablespace, complete the following steps: Take the tablespace that contains the datafiles offline.
The database must be open. Procedure for Relocating Datafiles in a Single Tablespace Here is a sample procedure for relocating a datafile. Assume the following conditions: An open database has a tablespace named users that is made up of datafiles all located on the same disk.
0コメント