Oracle database file path

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:. These structures are explained in "Mapping Structures". The external process is responsible for discovering the mapping libraries and dynamically loading them into its address space.

Oracle uses mapping libraries to discover mapping information for the elements that are owned by a particular mapping library. This information is used to populate dynamic performance views that can be queried by users.

Mapping libraries are vendor supplied. However, Oracle currently supplies a mapping library for EMC storage. The mapping libraries available to a database server are identified in a special file named filemap. The mapping structures and Oracle's representation of these structures are described in this section. You will need to understand this information in order to interpret the information in the mapping views.

A file mapping structure provides a set of attributes for a file, including file size, number of file system extents that the file is composed of, and the file type. A file system extent mapping structure describes a contiguous chunk of blocks residing on one element. This includes the device offset, the extent size, the file offset, the type data or parity , and the name of the element where the extent resides.

File system extents are not the same as Oracle extents. File system extents are physical contiguous blocks of data written to a device as managed by the file system. Oracle extents are logical structures managed by Oracle, such as tablespace extents. Elements may be mirrors, stripes, partitions, RAID5, concatenated elements, and disks. These structures are the mapping building blocks. This structure contains the subelement number, size, the element name where the subelement exists, and the element offset.

Consider an Oracle database which is composed of two data files X and Y. Both files X and Y reside on a file system mounted on volume A. File X is composed of two extents while file Y is composed of only one extent. Element A is striped over two elements B and C. Note that elements D, E, and F are physical disks. All of the mapping structures are illustrated in Figure The configuration ID captures the version information associated with elements or files.

The vendor library provides the configuration ID and updates it whenever a change occurs. File System —This default option creates database files that are managed by the file system of your operating system. You can specify the directory path where database files are to be stored. Oracle Database can create and manage the actual files. For environments with a large number of disks, this option simplifies database administration and maximizes performance. Andrei Z Andrei Z 31 3 3 bronze badges.

As I've mentioned in my question, using the dataFile view was not an option because it was only accessible to the database administrators. Could you expand on your answer, please?

This will help future searchers understand why, and not just the answer to a specific question. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Visit chat.

Related Hot Network Questions. Question feed. Stack Overflow works best with JavaScript enabled. Accept all cookies Customize settings.