Release 2 (9.2)
Part Number A96521-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Database Administrator's Guide Release 2 (9.2) Part Number A96521-01 |
|
This chapter describes the various aspects of datafile management, and contains the following topics:
| See Also:
Chapter 3, "Using Oracle-Managed Files" for information about creating datafiles and tempfiles that are both created and managed by the Oracle database server |
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:
File numbers are displayed in many data dictionary views. You can optionally use file numbers instead of file names to identify datafiles or tempfiles in SQL statements. When using a file number, specify the file number that is displayed in the FILE# column of the V$DATAFILE or V$TEMPFILE view. This file number is also displayed in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view.
This section describes aspects of managing datafiles, and contains the following topics:
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.
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.
When determining a value for DB_FILES, take the following into consideration:
DB_FILES is too low, you cannot add datafiles beyond the DB_FILES limit without first shutting down the database.DB_FILES is too high, memory is unnecessarily consumed.You can add datafiles to tablespaces, subject to the following limitations:
DB_FILES initialization parameter.CREATE DATABASE or CREATE CONTROLFILE statements, the MAXDATAFILES parameter specifies an initial size of the datafile portion of the control file. However, if you attempt to add a new file whose number is greater than MAXDATAFILES, but less than or equal to DB_FILES, the control file will expand automatically so that the datafiles section can accommodate more files.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:
|
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.
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.
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.
When creating a tablespace, you should estimate the potential size of database objects and create sufficient datafiles. 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. Preferably, place datafiles on multiple devices, so as to ensure that data is spread evenly across all devices.
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... |
|---|---|---|
|
|
Creates a tablespace and the datafiles that comprise it |
|
|
|
Creates a locally-managed temporary tablespace and the tempfiles (tempfiles are a special kind of datafile) that comprise it |
|
|
|
Creates and adds a datafile to a tablespace |
|
|
|
Creates and adds a tempfile to a temporary tablespace |
|
|
|
Creates a database and associated datafiles |
|
|
|
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.
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.
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 ALTER DATABASE statement.
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 |
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;
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:
|
Note: You can make all datafiles in any tablespace, except the files in the For more information about taking a tablespace offline, see "Taking Tablespaces Offline". |
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;
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;
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).
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. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.
To rename datafiles from a single tablespace, complete the following steps:
SYSTEM tablespace that contains the datafiles offline.
For example:
ALTER TABLESPACE users OFFLINE NORMAL;
ALTER TABLESPACE statement with the RENAME DATAFILE clause 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.
Here is an example that illustrates the steps involved for relocating a datafile.
Assume the following conditions:
users that is made up of datafiles all located on the same disk.users tablespace are to be relocated to different and separate disk drives.Complete the following steps:
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
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';
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.
You can rename and relocate datafiles of one or more tablespaces using ALTER DATABASE statement with the RENAME FILE clause. 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.
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.
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:
You can, however, drop a tempfile using the ALTER DATABASE statement. For example:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING 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 |
| See Also:
Oracle9i Database Reference for information about checksums and the |
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. Oracle provides views, such as DBA_TABLESPACES, DBA_DATA_FILES, and V$DATAFILE, that provide a mapping of files onto devices. These mappings, along with device statistics can be used to evaluate I/O performance.
However, with the introduction of host based Logical Volume Managers (LVM), and sophisticated storage subsystems that provide RAID (Redundant Array of Independent Disks) features, it is not easy to determine file to device mapping. 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.
The following topics are contained in this section:
To acquire an understanding of I/O performance, one must have detailed knowledge of the storage hierarchy in which files reside. Oracle provides a mechanism to show a complete mapping of a file to intermediate layers of logical volumes to actual physical devices. This is accomplished though a set of dynamic performance views (V$ views). Using these views, you can locate the exact disk on which any block of a file resides.
To build these views, storage vendors must provide mapping libraries that are responsible for mapping their particular I/O stack elements. 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. Oracle provides a PL/SQL package, DBMS_STORAGE_MAP, that you use to invoke mapping operations that populate the mapping views.
This section describes the components of Oracle's file mapping interface and how the interface works. It contains the following topics:
The following figure shows the components of the file mapping mechanism.
The following sections briefly describes these components and how they work together to populate the mapping views:
FMON is a background process started by Oracle whenever the FILE_MAPPING initialization parameter is set to TRUE. FMON is responsible for:
These structures are explained in "Mapping Structures".
You help control this mapping using procedures that are invoked with the DBMS_STORAGE_MAP package.
FMON spawns an external non-Oracle process called FMPUTL, that communicates directly with the vendor supplied mapping libraries. This process obtains the mapping information through all levels of the I/O stack, assuming that mapping libraries exist for all levels. On some platforms the external process requires that the SETUID bit is set to ON because root privileges are needed to map through all levels of the I/O mapping stack.
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. Through these mapping libraries information about individual I/O stack elements is communicated. This information is used to populate dynamic performance views that can be queried by users.
Mapping libraries need to exist for all levels of the stack for the mapping to be complete, and different libraries may own their own parts of the I/O mapping stack. For example, a VERITAS VxVM library would own the stack elements related to the VERITAS Volume Manager, and an EMC library would own all EMC storage specific layers of the I/O mapping stack.
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.ora.
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.
The following are the primary structures that compose the mapping information:
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.
An element mapping structure is the abstract mapping structure that describes a storage component within the I/O stack. Elements may be mirrors, stripes, partitions, RAID5, concatenated elements, and disks. These structures are the mapping building blocks.
A subelement mapping structure describes the link between an element and the next elements in the I/O mapping stack. This structure contains the subelement number, size, the element name where the subelement exists, and the element offset.
All of these mapping structures are illustrated in the following example.
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. Element B is a partition of element D and element C is mirrored over elements E and F. Note that elements D, E, and F are physical disks. Subelement B0 connects the parent element A to element B, subelement C1 connects A to C, ....
All of the mapping structures are illustrated in Figure 12-2.
Note that the mapping structures represented are sufficient to describe the entire mapping information for the Oracle instance and consequently to map every logical block within the file into a (element name, element offset) tuple (or more in case of mirroring) at each level within the I/O stack.
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. Without a configuration ID, there is no way for Oracle to tell whether the mapping has changed.
There are two kinds of configuration IDs:
These configuration IDs are persistent across instance shutdown
The configuration IDs are not persistent across instance shutdown. Oracle is only capable of refreshing the mapping information while the instance is up.
This section discusses how to use Oracle's file mapping interface. It contains the following topics:
The following steps enable the file mapping feature:
filemap.ora file exists in the $ORACLE_HOME/rdbms/filemap/etc directory.
The filemap.ora file is the configuration file that describes all of the available mapping libraries. FMON requires that a filemap.ora file exists and that it points to a valid path to mapping libraries. Otherwise, it will not start successfully.
The following row needs to be included for each library :
lib=vendor_name:mapping_library_path
where:
Note that the ordering of the libraries in this file is extremely important. The libraries are queried based on their order in the configuration file.
The file mapping service can be even started even if no mapping libraries are available. The filemap.ora file still needs to be present even though it is empty. In this case, the mapping service is constrained in the sense that new mapping information cannot be discovered. Only restore and drop operations are allowed in such a configuration.
FILE_MAPPING initialization parameter to TRUE.
FILE_MAPPING=TRUE
The instance does not have to be shut down to set this parameter. It can be set using an ALTER SYSTEM statement.
DBMS_STORAGE_MAP mapping procedure. You have two options:
DBMS_STORAGE_MAP.MAP_ALL procedure to build the mapping information for the entire I/O subsystem associated with the Oracle database.DBMS_STORAGE_MAP.MAP_SAVE procedure to save the mapping information in the data dictionary. (Note that this procedure is invoked in DBMS_STORAGE_MAP.MAP_ALL() by default.) This forces all of the mapping information in the SGA to be flushed to disk.
Once you restart the database, use DBMS_STORAGE_MAP.RESTORE() to restore the mapping information into the SGA. If needed, DBMS_STORAGE_MAP.MAP_ALL() can be called to refresh the mapping information.
The DBMS_STORAGE_MAP package enables you control the mapping operations. The various procedures available to you are described in the following table.
See Also:
|
Mapping information generated by DBMS_STORAGE_MAP package is captured in dynamic performance views. Brief descriptions of these views are presented here.
| See Also:
Oracle9i Database Reference contains complete descriptions of the dynamic performance views |
However, the information generated by the DBMS_STORAGE_MAP.MAP_OBJECT procedure is captured in a global temporary table named MAP_OBJECT. This table displays the hierarchical arrangement of storage containers for objects. Each row in the table represents a level in the hierarchy. A description of the MAP_OBJECT table follows.
The following examples illustrates some of the powerful capabilities of Oracle's file mapping feature. This includes :
Consider an Oracle instance which is composed of two datafiles:
These files are created on a Solaris UFS file system mounted on a VERITAS VxVM host based striped volume, /dev/vx/dsk/ipfdg/ipf-vol1, that consists of the following host devices as externalized from an EMC Symmetrix array:
Note that the following examples require the execution of a MAP_ALL()operation.
The following query returns all Oracle files associated with the /dev/vx/rdmp/c2t1d1s2 host device:
SELECT UNIQUE me.ELEM_NAME, mf.FILE_NAME FROM V$MAP_FILE_IO_STACK fs, V$MAP_FILE mf, V$MAP_ELEMENT me WHERE mf.FILE_MAP_IDX = fs.FILE_MAP_IDX AND me.ELEM_IDX = fs.ELEM_IDX AND me.ELEM_NAME = /dev/vx/rdmp/c2t1d1s2;
The query results are:
ELEM_NAME FILE_NAME ------------------------ -------------------------------- /dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db2.f
The following query displays a topological graph of the /oracle/dbs/t_db1.f datafile:
WITH fv AS (SELECT FILE_MAP_IDX, FILE_NAME FROM V$MAP_FILE WHERE FILE_NAME = /oracle/dbs/t_db1.f) SELECT fv.FILE_NAME, LPAD(' ', 4 * (LEVEL - 1)) || el.ELEM_NAME ELEM_NAME FROM V$MAP_SUBELEMENT sb, V$MAP_ELEMENT el, fv, (SELECT UNIQUE ELEM_IDX FROM V$MAP_FILE_IO_STACK io, fv WHERE io.FILE_MAP_IDX = fv.FILE_MAP_IDX) fs WHERE el.ELEM_IDX = sb.CHILD_IDX AND fs.ELEM_IDX = el.ELEM_IDX START WITH sb.PARENT_IDX IN (SELECT DISTINCT ELEM_IDX FROM V$MAP_FILE_EXTENT fe, fv WHERE fv.FILE_MAP_IDX = fe.FILE_MAP_IDX) CONNECT BY PRIOR sb.CHILD_IDX = sb.PARENT_IDX;
The resulting topological graph is:
FILE_NAME ELEM_NAME ----------------------- ------------------------------------------------- /oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipfdg/ipf-vol1_-1_-1 /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_0_0 /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d0s2 /oracle/dbs/t_db1.f _sym_symdev_000183600407_00C /oracle/dbs/t_db1.f _sym_hyper_000183600407_00C_0 /oracle/dbs/t_db1.f _sym_hyper_000183600407_00C_1 /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipfdg/ipf-vol1_0_1_0 /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 /oracle/dbs/t_db1.f _sym_symdev_000183600407_00D /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0 /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1
This example displays the block distribution at all levels within the I/O stack for the scott.bonus table.
A MAP_OBJECT() operation must first be executed as follows:
EXECUTE DBMS_STORAGE_MAP.MAP_OBJECT('BONUS','SCOTT','TABLE');
The query is as follows:
SELECT io.OBJECT_NAME o_name, io.OBJECT_OWNER o_owner, io.OBJECT_TYPE o_type, mf.FILE_NAME, me.ELEM_NAME, io.DEPTH, (SUM(io.CU_SIZE * (io.NUM_CU - DECODE(io.PARITY_PERIOD, 0, 0, TRUNC(io.NUM_CU / io.PARITY_PERIOD)))) / 2) o_size FROM MAP_OBJECT io, V$MAP_ELEMENT me, V$MAP_FILE mf WHERE io.OBJECT_NAME = 'BONUS' AND io.OBJECT_OWNER = 'SCOTT' AND io.OBJECT_TYPE = 'TABLE' AND me.ELEM_IDX = io.ELEM_IDX AND mf.FILE_MAP_IDX = io.FILE_MAP_IDX GROUP BY io.ELEM_IDX, io.FILE_MAP_IDX, me.ELEM_NAME, mf.FILE_NAME, io.DEPTH, io.OBJECT_NAME, io.OBJECT_OWNER, io.OBJECT_TYPE ORDER BY io.DEPTH;
The following is the result of the query. Note that the o_size column is expressed in KB.
O_NAME O_OWNER O_TYPE FILE_NAME ELEM_NAME DEPTH O_SIZE ------ ------- ------ ------------------- ----------------------------- ------ ------ BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/dsk/ipfdg/ipf-vol1 0 20 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_plex_/dev/vx/rdsk/ipf 1 20 pdg/if-vol1_-1_-1 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ 2 12 ipfdg/ipf-vol1_0_1_0 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_subdisk_/dev/vx/rdsk/ipf 2 8 dg/ipf-vol1_0_2_0 BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d1s2 3 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f /dev/vx/rdmp/c2t1d2s2 3 8 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00D 4 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_symdev_000183600407_00E 4 8 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_0 5 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00D_1 5 12 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_0 6 8 BONUS SCOTT TABLE /oracle/dbs/t_db1.f _sym_hyper_000183600407_00E_1 6 8
The following data dictionary views provide useful information about the datafiles of a database:
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 descriptions of these views |
|
 Copyright © 2001, 2002 Oracle Corporation. All Rights Reserved. |
|