Oracle Free Tutorial

Web based School

Previous Page Main Page Next Page


11

Managing the Database

In this chapter, you learn how to perform the following tasks:

  • Make a new Oracle database

  • Manage the instance by bringing the instance up and down

  • Obtain information from the Oracle system tables

  • Modify the number of control and redo log files

The Parameter File: INIT.ORA

An instance is the set of background processes and the memory structures that Oracle uses. Every instance must have a parameter file, known as the INIT.ORA file, from which it retrieves the parameters that it uses to run and the mode of the database. This parameter file is used by the Oracle SQL*DBA tool when the instance is first started. Once the instance has been started, the parameter file is not used again until the next time the instance is started. The parameter file must be on the same machine as the SQL*DBA tool.

INIT.ORA parameter files are used to specify

  • Amount of storage allocated for the Oracle memory structures

  • Which rollback segments to use for the instance

  • National language settings

  • Settings for running Oracle in parallel server mode

  • Which database and control file to use

  • When to issue checkpoints

  • Limits for database control structures

  • Which non-mandatory background processes to initialize

The file is given the generic name INIT.ORA, but the Oracle instance identifier is usually part of the filename. The name can be any filename, but it usually begins with INIT.

The parameter file is an ordinary text file, which you modify using any character-based operating system text editor. The parameters can be included in any order, and they can be specified in upper, lower, or mixed case.


Even though the parameters can be included in mixed case, the names of operating system files must be specified according to the operating system's rules for file naming. In UNIX, for example, a filename must be specified exactly as it appears on the file system.

You specify over 130 parameters. Most of them are common to all the platforms on which Oracle runs. The number of the parameters increase from one release of the Oracle RDBMS software to the next.

An example parameter file comes with the Oracle software; its default name is INIT.ORA. You can use this file could to run your instance, although you might have to adjust the parameters manually to obtain optimal performance.

It is possible to many parameter files for the same instance, although only one is used at a time for the instance startup. If you want to use another set of parameters for the run, you must shut down and restart the instance.

Many parameters have a minimum value. If they are set below the minimum value, the Oracle instance does not start up. The values of some parameters are derived from settings in other parameters. Normally, you do not need to set them. There are even some parameters that you should not set unless the Oracle support desk instructs you to set them. Appendix A of the Oracle7 Server Administrator's Guide describes all the different parameters.


Use one parameter file for the instance for normal online operations during the day. At night, use a parameter file that optimizes performance for a batch job.

In the INIT.ORA file, the only parameter that is mandatory is the CONTROL_FILES parameter, which names one or more control files to be used for the instance. This control files are used during instance startup to determine the names and locations of the database and redo log files. Table 11.1 describes additional parameters for the INIT.ORA file.

    Table 11.1. Important INIT.ORA parameters.
Parameter Name


Description


AUDIT_TRAIL

Must be set to TRUE to enable auditing rows to be inserted, even if the auditing facility has been set up by issuing the AUDIT SQL command.

BACKGROUND
_DUMP_DEST

Controls the directory location where the trace files from the Oracle background processes are written. The alert file, which records significant database events and messages, is also written to this location. You can delete the trace and alert files without affecting the performance of the database.

CHECKPOINT_
PROCESS

Controls whether the Oracle CKPT background process is to be run. By running the CKPT process, load is decreased from the LGWR process in updating the database file headers whenever a checkpoint occurs.

CONTROL_FILES

Names one or more control files to be used for this instance. Control files are used during instance startup to determine the name and location of the database and redo log files.

DB_BLOCK_BUFFERS

Specifies the number of Oracle database blocks to cache in the database buffers area of the SGA. The default value of 32 is too small for all but test databases.

DB_BLOCK_SIZE

Specifies the size of each Oracle block in the database files. Set this parameter when you first create the database and then do not alter it.

DML_LOCKS

Specifies the maximum number of DML locks that can be taken out by the instance. There is one DML lock for each user for each table undergoing an INSERT, UPDATE, or DELETE operation at any time for the instance.

INIT_SQL_FILES

Specifies the name of the sql files to be executed when the database is created. These files are typically the Oracle-supplied files used to create the data dictionary, the views on the data dictionary, and views that enable the export and import utilities to run. After these files have been run during the CREATE DATABASE operation, they are never executed again.

LICENSE_MAX_
SESSIONS

Controls the maximum number of user sessions that can be connected to the Oracle database at any time.

LOG_ARCHIVE_DEST

Controls the location to which the archived redo log files are written if the database runs in ARCHIVELOG mode.

LOG_ARCHIVE_
FORMAT

Controls the naming format of the archived files if the database is running in ARCHIVELOG mode. That way, the redo log sequence number, instance thread number, or a fixed string can be inserted into the filename.

LOG_BUFFERS

Specifies the size of the redo log buffer in the SGA into which all changes are recorded from all transactions on the instance.

LOG_
CHECKPOINT_
INTERVAL

Used to specify whether a checkpoint should occur earlier than the default time of a redo log file switch. This should not normally be set for most systems, because more frequent checkpointing incurs extra system overhead in writing out the changed database blocks in the database buffer cache in the SGA.

LOG_CHECKPOINT
_TIMEOUT

Controls the amount of time to wait before issuing a checkpoint on the database. For example, if this parameter is set to 1800 seconds (30 minutes), a checkpoint is performed every 30 minutes, even if no activity has occurred on the system in the period.

MAX_DUMP
_FILE_SIZE

Specifies the maximum size of any trace files generated. If you use the trace facility to trace large amounts of work, use this parameter to limit how much trace information can be produced.

OPTIMIZER_MODE

Specifies the default mode for the Oracle optimizer, which determines the execution plan for a SQL statement. The Oracle6 method is RULE, which means that the optimizer does not take account of any statistics that were gathered on tables by using the ANALYZE command. The default value, CHOOSE, uses statistics if they exist. Otherwise, it defaults to the rule based on the optimization method.

OS_AUTHENT
_PREFIX

Specifies the proxy login. The Oracle user name defaults to the operating system user name prefixed by OPS$. You can control the password by using this parameter. The default on most platforms is OPS$, but you can change it to any prefix or even NULL.

PROCESSES

Controls the maximum number of operating system processes that can connect to the Oracle database through this instance, which includes the background processes. Four background processes are mandatory, and the instance cannot start without them.

RESOURCE_LIMIT

Must be set to TRUE to enforce checking of resource limits if system resources are controlled through database profiles assigned to Oracle users. To set this parameter online, use the ALTER SYSTEM command.

ROLLBACK_
SEGMENTS

Contains the names of private rollback segments that the instance should use for storing the before-image of any changes made to the database. Rollback segments not mentioned here can still be activated by using the ALTER ROLLBACK SEGMENT command. If no rollback segments are activated, the SYSTEM rollback segment—normally reserved for recording changes made to the Oracle data dictionary—is used for recording the before-image of the Oracle data blocks. If more than one instance is using the Oracle database, they cannot use the same rollback segments.

SEQUENCE_CACHE_
ENTRIES

Specifies the number of sequence objects to cache in the SGA for generating unique numbers using sequences. The sequences must have been created with the CACHE parameter specified as part of the CREATE SEQUENCE statement.

SHARED_POOL_SIZE

Specifies the size of the shared pool area in the SGA that is used to hold the data dictionary cache, cursors for SQL statements, and PL/SQL code units.

SMALL_TABLE_
THRESHOLD

Sets the low threshold for the data blocks in an Oracle table. Tables with Oracle data blocks below this limit are read into the SGA completely when you perform a full table scan on them. If a table has more data blocks than this limit, the full table access of the table uses only a portion of the lower part of the database buffers cache of the SGA. This ensures that active blocks in the SGA from other tables are not removed from memory when a full table scan is performed on a large table.

SQL_TRACE

Turns on the trace facility for the instance when set to TRUE. With tracing on, execution plans and statement performance can be determined by formatting the trace files produced with the TKPROF utility. Timing information is provided only if the TIMED_STATISTICS parameter is also set to TRUE.

TIMED_STATISTICS

Determines whether timing information is recorded in the trace files when tracing is enabled for the instance or for user sessions.

USER_DUMP_DEST

Determines the location of user process trace files produced when tracing is enabled for the instance or for user sessions.

Table 11.1 does not describe all the parameters in the INIT.ORA file. Refer to Appendix A of the Oracle7 Server Administrator's Guide for descriptions of the all parameters.


Use the IFILE parameter to name a file that contains a common set of parameters for all the instances running on the machine.

To see the parameters that the current instance is using, enter the following in SQL*DBA:

SQLDBA> SHOW PARAMETERS

You can also see only those parameters that contain a given string. For example,

SQLDBA>  SHOW PARAMETERS buffer

shows all the parameters that contain the string buffer.

To see a list of the parameters when the instance first comes up, include the word LIST in the first line of the INIT.ORA file.

To access the parameters from any Oracle tool, run a select operation against the v$parameter table, as in

SQLPLUS> SELECT * FROM v$parameter.

You must have access to the v$parameter table, which is owned by the Oracle user SYS.

The Oracle SID

The Oracle System Identifier (SID) identifies the Oracle instance on the machine. It is usually set up as an operating system variable— the $ORACLE_SID environment variable in UNIX or the ORA_SID symbol in VMS—that is used to name of the Oracle background processes and to identify the SGA area in memory. Whenever a user process wants to connect to a database, the SID set up in that operating system account determines to which instance and, therefore, to which database the user will connect. The same user name can exist in different databases. For example, the Oracle system username SYS exists in all Oracle databases. With the SID, you can connect to the correct instance.

Many instances might be running on the same machine, each doing the processing for a different database. By looking at the processes running on the machine, you see many occurrences of the DBWR background process. The name of the process itself usually has the SID somewhere in it.

For client/server setups, the client machine identifies the network protocol and machine address to which the client machine wants to attach and the name of the SID of the connection. Requests are then processed against the database for which the instance is processing.

Creating a New Database

When you create a database, you create files on disk that Oracle has formatted and prepared. The structure of the database does not have to remain the same as initial structure. You can add other files later.

On some platforms, you can create a new database when the Oracle software is first installed. To create a database manually after that, follow the steps in this section. It is assumed that the software has been installed correctly on your machine. The steps provided here are general steps that apply to most platforms. Creating a database involves operating system-specific steps, of course. Refer to the Oracle7 Installation and User's Guide for their platform for specific information on your platform.

The crux of creating a database lies in running the CREATE DATABASE SQL statement. You must perform other steps, however, before you can run it. The syntax of the CREATE DATABASE SQL statement is

CREATE DATABASE mydb

  LOGFILE file1, file2 SIZE nM

  MAXLOGFILES n

  MAXLOGMEMBERS n

  MAXLOGHISTORY n

  DATAFILE file1 SIZE nM

  MAXDATAFILES n

  MAXINSTANCES n

  ARCHIVELOG | NOARCHIVELOG

For example,

CREATE DATABASE mydb

     DATAFILE '/disk03/mydb/system1.dbf' SIZE 20M

     LOGFILE '/disk04/mydb/log1.log',

     'disk05/mydb/log2.log' SIZE 512K;

The datafile parameter will physically create the first database file on the disk specified and with the size specified. If the file already exists the command will fail (unless the REUSE option is specified as well in which case the file will be over-written). The same is true with the LOGFILE parameter which creates the very first two redo log files.


Dont use the REUSE option of the CREATE DATABASE command, for you could accidentally overwrite a file that is being used by another database. Instead, physically delete the file. You can use REUSE on some platforms when contiguous disk space that you want to reuse has already been allocated to a file.

The default mode of the database is NOARCHIVELOG.

To specify the maximum number of data files, redo logs, and log members that can exist on the database, use the MAXDATAFILES, MAXLOGMEMBERS, and MAXLOGFILES parameters. If you specify lower limits, fewer resources are used in the SGA. The MAXINSTANCES parameter controls the maximum number of instances that can be connected to database.

If you set these maximum limits lower than the predefined maximum as part of the CREATE DATABASE statement, you must recreate the control file manually to increase them. Instead, you should specify the limits by using the INIT.ORA parameters, which set the limits for that startup of the instance. That way, I you need to increase the limits, simply increase the INIT.ORA parameter and restart the instance.


Leave the database mode set to NOARCHIVELOG—the default—in the CREATE DATABASE command. Change the mode to ARCHIVELOG when all the application objects have been created and populated. This improves performance while the application objects and data are initially loaded.

The steps in creating new database are

  1. Plan for the creation. Make sure that there is adequate disk space for the database. It is useful to do a sizing exercise to estimate the amount of storage required to hold the data for the application system. Determine the names and locations of the database files, the control file, and the redo log files.

    Make sure that the memory on the machine is enough for the new database, including the memory for the SGA, the PGA, and the programs on the database server machine. All the Oracle memory structures should fit into the machine's real memory.

  2. The operating system user account that you will use should have the INTERNAL privilege. This privilege enables your operating system account to run the CREATE DATABASE, STARTUP, and SHUTDOWN commands from the SQL*DBA tool. A lower-level form of the INTERNAL privilege is the OSOPER and OSDBA roles. INTERNAL, OSOPER, and OSDBA are assigned using operating system security mechanisms—groups on a UNIX machine or process rights on a VMS machine. Operating system administrators generally grant your account these privileges.

  3. Decide what the Oracle instance identifier is going to be, and set the appropriate operating system variable names. The Oracle SID should not have the same as an existing instance on the same machine.

  4. Create the INIT.ORA file for your database. The easiest way to do this is to copy an existing INIT.ORA file. Rename this file to indicate the SID used for the instance—for example, INITMYDB.ORA. Specify the name and location of the control file in the CONTROL_FILES parameter. Specify the name of the database—typically the same as the instance identifier— in the DB_NAME parameter. The name of the database must match the name given in the CREATE DATABASE command. It is recorded in all the database files, the control file, and the redo logs for the database. If you want to change the DB_BLOCK_SIZE parameter, you must do so now. It cannot be modified later.

  5. Initiate the SQL*DBA tool, and make a connection to the Oracle RDBMS software by using the INTERNAL account. Although the syntax of the CONNECT command looks similar to a normal connect, no password is specified:

    SQLDBA> CONNECT INTERNAL

  6. Start the Oracle background processes, and allocate memory for the SGA by starting up the Oracle instance. For example,

    SQLDBA> STARTUP NOMOUNT PFILE=INITMYDB.ORA

  7. Run the CREATE DATABASE statement with the parameters specific to your requirements. For example,

    SQLDBA> CREATE DATABASE mydb DATAFILE '/disk03/mydb/system1.dbf' SIZE 20M

    LOGFILE '/disk04/mydb/log1.log',
    'disk05/mydb/log2.log' SIZE 512K;

Once the database has been created, it is immediately available for use. No further steps are necessary. At this stage, though, you have not created any objects on the database other than the ones automatically by the CATALOG.SQL file. You can now start building the structure of your application database by defining tablespaces, usernames, tables, and so on.

You might need to run additional scripts to install the objects that enable the procedural database option or to store Oracle forms and other programs on the database.

The First Objects Created

Many objects are present on the database immediately after you create it. They are

  • The Oracle users SYS and SYSTEM

  • A tablespace called SYSTEM

  • A rollback segment called SYSTEM

  • The core data dictionary tables in the user account SYS

  • The CONNECT, RESOURCE, and DBA roles, which that provide backward compatibility with granting privileges

SYS has the default password CHANGE_ON_INSTALL, and SYSTEM has the default password MANAGER. To test whether the database was created, try to make a connection using these user names and passwords. The default passwords should be changed. The SYS account is the Oracle user that owns the core data dictionary tables. There is usually little reason to use this account for day-to-day access to the database. The SYSTEM account does not own any of the core data dictionary tables. Instead, it uses a set of views created on them. These views help the DBA and developers obtain information on the structure of the database and its objects.

The first set of data files created as part of the CREATE DATABASE statement are allocated to the SYSTEM tablespace. Its initial contents are usually the data dictionary tables and other objects.

Additional rollback segments should be created on the database.

Along with the core data dictionary tables in the user account SYS, data dictionary views that make the information in the core tables easier to view and manage are created. Public synonyms are created for most of the data dictionary views to give Oracle users access to basic information on their user accounts.

Startup and Shutdown

Before you can use an Oracle database, an instance must be running. An instance is the combination of the Oracle background processes and the SGA. This section discusses how to initiate and shut down an instance.

Startup Stages

For a database to be fully open and usable, the instance must be brought through three stages of startup:

  1. Starting the instance

  2. Mounting the database

  3. Opening the database

The first stage involves initiating the Oracle background processes and allocating memory for the SGA. During this stage, the INIT.ORA parameter file is used to determine the mode and instance initialization parameters, such as the sizes of the SGA structures in memory.

The second stage is mounting the database. It involves opening the control file—whose location is specified in the INIT.ORA parameter file used in stage 1—and determining the locations and names of the other database and redo log files.

The third stage opens the actual database files and the redo logs. Once this stage has been completed, you can access and use the database objects.


In MOUNT or NOMOUNT state, the database can be used only by the DBA.

In some cases, such as when recovery is to be initiated, the database might not have gone through all three stages. For example, it might be at stage 2, which means that that only the control file can be accessed. The actual database files are closed, so no operations on database objects are possible.

The tool used to perform the startup and shutdown is the Oracle SQL*DBA utility, which comes with the database software. This tool has a basic menu and Òfill in the blankÓ screens.

Before we can issue a startup or shutdown from the SQL*DBA tool, you must be connected as the INTERNAL user by means of a dedicated server connection. In other words, the multi-threaded server configuration cannot be used. This is a check whether you have operating system privileges and can perform these operations. Operating system-specific privileges, such as UNIX groups or VAX VMS process rights, determine who has access to perform these operations.

SQLDBA>  CONNECT INTERNAL

Once you are connected as the INTERNAL user, you can use the STARTUP command to initiate the instance startup in whatever mode you want. During the first stage of startup, the instance parameter file must be provided. In its simplest form, the startup command brings the instance through all three stages and makes the database available for all Oracle users to access:

SQLDBA> STARTUP

Note that he parameter file that the instance will use has not been specified. On most platforms, you can set an operating system environment variable. The default location for the parameter file on a UNIX platform is the /dbs directory under the home directory of the Oracle software.

The name of the database is assumed to have been specified in the INIT.ORA parameter file with the DB_NAME parameter. The name of the database specified is checked against the name of the database stored in the control file. If a mismatch occurs, the instance fails to start up and an error message is generated.

If you do not use this syntax, you must name the parameter file explicitly in the STARTUP command. In the following command, the DB_NAME parameter has not been specified in the INIT.ORA file. Therefore, you must specify it explicitly as part of the STARTUP command.

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' db_mydbname

You can also use this syntax when you want to start up the instance with a set of parameters different from the default values. For example, you might want to use parameters that have been tuned to optimize batch jobs.

If you use the parallel server option, you can specify additional parameters as part of the STARTUP command. The PARALLEL and EXCLUSIVE parameters control whether other instances can access the database once the instance has started up. If you use the EXCLUSIVE parameter, any attempt to start up the instance on another processor fails.

In many cases, you might not want to take the instance through all three stages. For example, you might need to perform maintenance and recovery operations. If this is the case, specify additional parameters, depending what stage you want.

If you use NOMOUNT parameter, only the Oracle background processes start and the SGA area in memory is allocated. You typically use NOMOUNT only when the database is created. For example,

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' NOMOUNT

The MOUNT option takes the instance up to the point when the control file is opened. You use MOUNT, for example, when you want to recover the database and the database files or redo logs are not present. You need to have the control file open, which is the case if the database is mounted, because it contains information that helps ensure that the files that make up the database remain in a synchronized state. For example,

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' MOUNT mydbname

The next word after MOUNT or OPEN in the STARTUP command is assumed to be the name of the database. If the name of the database has already been specified in the INIT.ORA file, move the MOUNT or OPEN keyword to the end of the command.

Another option is to bring the database through all three stages but to limit access to the tables to the DBA. This is useful for performing a full export of the database, because the data will not change during the export. For example,

SQLDBA> STARTUP PFILE ='/disk04/initmydb.ora' RESTRICT OPEN

Only Oracle accounts that have been given the RESTRICTED SESSION system privilege can connect to the database after the instance is brought up with the RESTRICT keyword.

On some operating systems, a special facility is provided to databases to be started automatically when the machine is booted up. Refer to the user's guide for your platform to see how to implement this facility if it is available on your platform.

Shutdown Stages

A database is brought down in three stages:

  1. Closing the database files and the redo log files

  2. Dismounting the database

  3. Shutting down the Oracle background processes and releasing the memory occupied for the SGA

In the first stage, the database files and the redo log files are closed. Before this happens, information in the SGA is flushed down to the database files for system and user data. Entries from the redo buffer cache in the SGA are also flushed down to the active redo log file, and a marker is made in the redo log to indicate that a shutdown has occurred.

In the second stage, the database is dismounted. This means that the control file is updated with synchronization information and closed.

In the third stage, the Oracle background processes are shut down, and the memory occupied for the SGA is released.

The SQL*DBA tool is used to perform the shutdown, and the user must be connected to the database as INTERNAL. For example,

SQLDBA>  CONNECT INTERNAL

The SHUTDOWN command takes the instance through all three shutdown stages:

SQLDBA>  SHUTDOWN

If the database is not fully open, informational messages indicate which shutdown stage is not necessary. The other stages are performed.


If users are connected to the database, the SHUTDOWN command without any parameters waits indefinitely for them to disconnect from the database. You can disconnect users manually by using the ALTER SYSTEM KILL SESSION command or by means of the SQL*DBA menu options.

You can shut down the instance so that connected users are immediately disconnected and work in their current transactions is rolled back. Use the IMMEDIATE keyword after the SHUTDOWN command:

SQLDBA>  SHUTDOWN IMMEDIATE

You should send a message notifying all the users currently connected to disconnect from the database. There is no Oracle facility for doing this, so you must use an operating system utility. This is a problem especially for client/server setups, because the users might be running the tools on another machine.

In extreme cases, you can shut down the instance without waiting for information to be flushed from the SGA to the database and redo log files. Use the ABORT option of the SHUTDOWN command:

SQLDBA>  SHUTDOWN ABORT

Like the IMMEDIATE option, ABORT disconnects all the users currently connected, but the rollback operation is not be performed when the instance is shut down. Instead, if a rollback is performed, it happens the next time the instance is started up. This option is quicker than the IMMEDIATE option, especially when a large update transaction has to be rolled back before the instance can be shut down. Essentially, the rollback is delayed until you next start up the instance.

A more cruder way of bringing down the database on some platforms is to kill the Oracle background processes that are currently running. If the background processes are no longer running, the instance is not up. This method is not recommended, though.

Regardless of how you bring the instance down, the integrity of the database is not in any danger. The values of any changes are recorded in the redo logs; on COMMIT, they are forced to the redo log files on disk. Changes to the database blocks in the database buffer cache in the SGA that have not been written to the database files before SHUTDOWN IMMEDIATE, SHUTDOWN ABORT, or even an instance crash, are reapplied automatically the next time the instance is brought up. This is totally invisible to the user and the DBA. The only noticeable effect after SHUTDOWN IMMEDIATE or SHUTDOWN ABORT is a increase in the time taken during the opening stage of bringing up the instance.

Suppose that you started the instance in NOMOUNT or MOUNT state and need to take it from one stage of startup to another. The easy way to do this is to shut down the instance entirely and then restart it in the mode you want. Likewise, you could use the ALTER DATABASE command, which takes the database from one stage of startup to another. Refer to the Oracle SQL Language Reference Guide for more information on this command.

The Data Dictionary

The Oracle data dictionary is a set of tables that the Oracle software uses to record information about the structure of the database. These core system tables are owned by the Oracle user present on all Oracle databases—the SYS user. SYS is rarely used, even by DBAs, for maintenance or inquiry work. Instead, another Oracle user with a high-level system privileges is used. Typically, only one other DBA account is created for the user of the DBA.

The DBA does not usually use the SYSTEM user, which is also automatically defined when the database is created. This is because product-specific tables are installed in SYSTEM, and accidental modification or deletion of these tables can interfere with the proper functioning of some of the Oracle products.

Core System Tables

The core data dictionary tables have short names, such as tab$, col$, ind$. These core system tables are rarely referenced directly, for the information is available in more readily digestible form in the data dictionary views defined when the database is created. To obtain a complete list of the data dictionary views, query the DICT view.

Data Dictionary Views

The data dictionary views are based on the X$ and V$ tables. They make information available in a readable format. These names of these views are available by selecting from the DICT data dictionary view. Selecting all the rows from this view shows a complete list of the other accessible views.

SQL*Plus provides basic column formatting, whereas the SQL*DBA utility does not. Therefore, you use SQL*Plus for running queries on these views.


If you are not sure which data dictionary view contains the information that you want, write a query on the DICT view. Suppose, for example, that you want to find all the data dictionary views that give information about SYNONYMS—aliases for other database objects. You would write the following query:

SQLPLUS> SELECT * FROM DICT WHERE TABLE_NAME LIKE '%SYNONYM%';

The list of views produced by this query is a list of other views that you can query.

Most views used for day-to-day access begin with USER, ALL, ROLE, or DBA.

The USER views show information on objects owned by the Oracle user running the query. Suppose, for example, that a table called FOOTBALL is owned by the Oracle user LINEKER and a table called BOXING is owned by the Oracle user BRUNO. If you log into the Oracle account LINEKER and query the USER_TABLES view, the only table that you will see is the FOOTBALL table. If you disconnect from that user and connect to the Oracle user BRUNO and then run the same query, the only table you will see is the BOXING table.

The data dictionary views beginning with ALL show information on objects owned by the current Oracle user as well as objects to which the user has been given access. Suppose, for example, that BRUNO gives SELECT access on the BOXING table to LINEKER. When you log into the Oracle database using the LINEKER account and run a query on the ALL_TABLES view, you will see information on both the FOOTBALL and BOXING tables. One is owned by the account, and the other is a table that the user can access. Note that BRUNO still sees only the BOXING table.

If you connect to the Oracle database using a more privileged account—such as SYS or SYSTEM—you can access the DBA data dictionary views. The DBA views are typically used only by the DBA. They show information for all the users of the database. The SELECT ANY TABLE system privilege enables other users to access these views. Querying the DBA_TABLES view shows the tables owned by all the Oracle user accounts on the database. If you log in as SYS and query the DBA_TABLES view, you will see that LINEKER owns the FOOTBALL table and that BRUNO owns the BOXING table.

Dynamic Performance Tables

Another set of tables often considered to be part of the data dictionary are the dynamic performance tables. They do not actually occupy storage on the database; they are in-memory tables that exist while the instance is running and disappear when the instance is shut down. Their names typically begin with X$. You rarely go directly to the X$ tables. Instead, you use views that begin with V$ to access the information in a more readable form.

The dynamic performance tables are divided into two groups: static and dynamic. Because they are held in memory, the overhead of keeping them updated is negligible.

The tables that hold static information on the instance and the database setup always show the same information while the instance is running regardless of whatever activity is occurring. For example, the v$parameter table shows the settings for the initialization parameters that have been defaulted or set in the INIT.ORA file.

The dynamic tables are updated continuously when activity occurs and provide data that is useful for tuning. For example, the v$sysstat table holds system-level statistics that show the number of Oracle blocks physically accessed from database files and the number of logical Oracle block accesses. These statistics are updated whenever a SQL command is executed on the database—whether it is user SQL or system-generated SQL.

Other Data Dictionary Views

Other data dictionary views provide the same information as the views discussed previously. These are either ANSI-standard views or views that are present for compatibility with previous versions of the Oracle database. These ANSI-standard views, such as CATALOG, show information in ANSI-standard format. Suppose, for example, that you used the ANSI-standard view names on another database. You can work with the same view names and see the same information on an Oracle database. The views, however, do not show information about the parts of the Oracle database that have surpassed the ANSI-standards.

Redo Logs

The Oracle system auditing is affected by the redo logs. There must be a minimum of two redo log files for every database, and in this part, I discuss how to maintain redo log files and how they can be multiplexed.

Creating, Altering, and Dropping Redo Logs

The online redo log files are used to record changes made to the database files. They are also used in rotation. That is, when one redo log file fills, a checkpoint is performed and entries are written to the other redo log file. Once that file has filled and the checkpoint on the next has completed, the next file is used. If the checkpoint on a redo log file has not completed, it cannot be used until the checkpoint has completed. This causes the instance to hang temporarily until the checkpoint has completed. For this reason, additional redo log files can be created.


As a general rule, size the redo log files so that a switch from one online redo log file to another occurs roughly every 30 minutes. This ensures that not too many checkpoints occur. (Remember: At each checkpoint, the changed database blocks must be written to disk, which causes more disk I/O.) Likewise, the time needed to recover the instance if it fails is not too great; only 30 minutes of changes must be reapplied. Use the Oracle alert file or the V$ dynamic performance views to determine how often log switches occur.

You can create more than two online redo log files when you create the database or by using the ALTER DATABASE SQL command. Because you are modifying the structure of the database, you must start the instance in a mounted state. Having the instance in a mounted state means that the control file is open—the names of the redo logs are recorded in the control file.

To start the instance up in a mounted state, go into the SQL*DBA tool and issue the STARTUP MOUNT command. This assumes that the INIT.ORA parameter file is in the default location and that the name of the database is specified in the INIT.ORA file with the DB_NAME parameter. For example,

SQLDBA> STARTUP MOUNT

To add another online redo log file, enter

ALTER DATABASE ADD LOGFILE

     '/disk03/mydb/log3.rdo' SIZE 512K;

When you restart the instance, the log writer background can write to three online redo log files, which reduces the chance of having to wait for a checkpointing process to occur.

To move the redo log files, first make sure that the instance has been started in a mounted state. Use an operating system file copy command, and make a copy of the file that you want to rename or move to a new location. Then issue the following command:

ALTER DATABASE RENAME FILE

     '/disk03/mydb/log3.rdo' TO

     '/disk04/mydb/log4.rdo';

This command records the new name and location of the redo log file in the control file. Note that it is always necessary to copy the files manually to the new names or locations. Oracle does not make a copy of the files for you.

To drop an online redo log file, first make sure that the instance has been started in a mounted state. Use your operating system's file delete command to remove the file manually from disk. To notify Oracle that the control file must be updated because the redo log file can no longer to be used, issue the following statement:

ALTER DATABASE DROP LOGFILE MEMBER

     '/disk04/mydb/log4.rdo';

The redo log files should all be the same size.


Make sure that there are always two or more online redo log files. Otherwise, you will not be able to restart the instance.

Multiplexing Redo Logs

The redo log files protect the database files in case of failure. If you lose the changes in the SGA, you can reapply them from the online redo log files for the committed transactions. Likewise, if the database has been set up to run in ARCHIVELOG mode, the changes made to the database file since the last backup can be reapplied automatically to a backup of the file, thereby protecting against media failure.

If the online redo log files themselves are lost, however, you run the risk of losing committed work. For this reason, it is a good idea to mirror redo log files by using redo log groups. Essentially, multiple copies of the online redo log files are kept. If one copy is lost, there is always an exact mirror copy that the instance can use. You might think that this would slow the instance down. In fact, the opposite is often the case, because the LGWR background process needs to receive a success signal from only one of the copies. If one of the copies of the redo log files in a redo log group cannot be used, the file is marked as invalid and the instance continues to run as normal.

You can mirror the redo log files when you create the database or by using the ALTER DATABASE command. A redo log group consists of one or more redo log file members, which are the redo log files. All the members of the group have the same information recorded in them—they are mirror copies of one another. The redo log groups are numbered sequentially, starting at 1. To see which group each of the existing files belongs to, access the v$log dynamic view.

In the default setup, a database has two redo log file groups, each of which has one member. This means that there are only two redo log files; the instance writes cyclically from one to the other.

When you mirror an existing redo log file, you create another redo log file member of a redo log group. The following command adds another file to redo log group 1:

ALTER DATABASE ADD LOGFILE MEMBER

     '/disk04/mydb/log11.rdo' TO GROUP 1;

The command to drop a redo log file is similar. To drop an entire group, issue the following command:

ALTER DATABASE DROP LOGFILE

     GROUP 3;

Control Files

The control file is used in the second stage of instance startup to find out the names and locations of the database files and the redo logs. Losing this file causes a major headache in recovery. Recovery options restore the control file either from a previous backup or use the CREATE CONTROL FILE SQL command to create it directly. You can set up a further level of protection by having the Oracle instance keep more than one copy of the control file always updated. This ensures that you always have another copy if one copy is lost.

Multiplexing Control Files

To make the Oracle instance update more than one copy of the control file, follow these steps:

  1. Use SHUTDOWN NORMAL to bring down the instance.

  2. Use an operating system utility to copy the existing control file to a different name.

  3. In the CONTROL_FILES INIT.ORA parameter, specify both control files. Separate them with a comma.

  4. Bring up the instance. The instance now maintains the two control files.

If you make a copy of the control file, keep the other control file on a disk different from the original to guard against losing the entire disk drive. To provide an extra level of protection, make more than two copies. The extra overhead of keeping extra copies of the control file is negligible compared to the extra protection they provide.

To ensure that more than one copy of the control file is being maintained, you can query the v$parameter table to see the names of the control files currently being updated. This table, however, often truncates the names of the control files. Another way is to put LIST on a line by itself at the start of the INIT.ORA file. When the instance starts, this parameter lists all the parameters used by the instance. The control files parameter shows the full names and locations of the control files used.

If a failure occurs with a control file—for example, if it is accidentally deleted—remove the name of the missing control file from the CONTROL_FILES parameter of the INIT.ORA file. Then shut down and restart the instance.

Trace and Alert Files

In every Oracle instance, the background processes produce trace files and an alert file. The alert file records significant events in the life of the instance, such as instance startups and shutdowns and redo log file switches. It provides information on errors that occur on the Oracle database.

The BACKGROUND_DUMP_DEST INIT.ORA parameter specifies the location of the trace files and the alert file. You can safely delete the trace and alert files, for they are recreated when the background processes need them.

In addition to the trace produced by the background processes, user processes produce trace files, which are usually used for debugging and performance optimization. These trace files are produced in different ways in the Oracle tools. For example, alter session set SQL_TRACE true is used to start tracing in a SQL*Plus or an Oracle reports module. Likewise, setting the statistics option in Oracle Forms shows the number of cursors used during the runform session and produces a trace file.

The name of the trace file is platform-dependent, and the location is given by the USER_DUMP_DEST INIT.ORA parameter. The raw trace files produced are not easy to deal with. Use the TKPROF Oracle utility to format the trace files so that they are easier to read. You can see information such as the number of logical and physical block reads, parses, executions, and fetches for the different SQL and PL/SQL statements that occurred while the trace was running. To sort the trace output, use the command line parameters of TKPROF. Show the slowest statements at the top of the trace output.

Information about the CPU and elapsed time taken by the statements appears only if the TIMED_STATISTICS INIT.ORA parameter is set to TRUE.

You can also set tracing on for the whole Oracle instance. To do this, set the SQL_TRACE INIT.ORA parameter to TRUE. The usual effect, however, is drastically reduced instance performance because all the user-generated and system-generated statements must be recorded in the trace files.

Database Modes

In this part, I discuss two modes in which the database can operate: the default, which means that redo logs are not archived when filled, and ARCHIVELOG mode, which essentially means that copies of the redo log files are made when they fill.

NOARCHIVELOG Mode

The default mode of a database is NOARCHIVELOG. When one of the online redo log files fills, a checkpoint is made and further old and new values are written to the next online redo log file. The redo log files are not archived to the archive destination. Recovery is limited to instance recovery. That is, you can protect against the instance crashing. However, if you lose the database or other files, you must restore the files to the latest backup, and all changes made to the database since the last backup cannot be reapplied. This provides the simplest setup. This setup could be applicable for a development database where losing the database is not such a big deal and where the resources to manage archiving are not available. It is also simpler to maintain because the problem of not having enough space to archive the redo logs does not occur.

ARCHIVELOG Mode

If the database runs in ARCHIVELOG mode, the redo logs are copied over to the archive destination whenever one of the online redo log files fills. The background process, ARCH, should be set up to copy the online redo log file to the archive destination automatically. Otherwise, the DBA must manually copy the files when they fill. If the online redo log files cannot be archived—either automatically by the ARCH background process or manually by the DBA—the whole database waits until the archiving is done. Setting the database in ARCHIVELOG mode obviously involves more work for the DBA, but it provides an extra level of protection. If the database, control, or redo log files is lost, they can be restored from the most recent backup, and all the changes made since the last backup can be automatically reapplied from a combination of the archived and online redo logs.

Changing the Mode of the Database

To change the mode of the database from the default mode, NOARCHIVELOG, to ARCHIVELOG so that redo log files are archived, perform the following steps in the SQL*DBA tool:

  1. Shut down and restart the instance in STARTUP MOUNT mode.

  2. Run the ALTER DATABASE ARCHIVELOG statement to start archiving.

  3. Ensure that the ARCHIVELOG_START INIT.ORA parameter is set to TRUE to enable automatic archiving and that ARCHIVELOG_DEST is set to the destination where you want the offline redo log files copied.

  4. Shut down and restart the instance normally. Check that the offline redo log files are being produced by looking at which files are produced in the archive destination or by checking the alert file.

Useful Data Dictionary Views

Table 11.2 describes the data dictionary objects. The objects are classified by purpose:

AUDIT

Views related to the Oracle audit facility

DB STRUCT

Information about the structure of the database as a whole

DUP

Information shown in other views

LOOK

Lookup tables that hold static data or data not likely to change often for the database or instance

MISC

Miscellaneous views

MON

Views that are useful in monitoring current activity in the instance

OBJ STRUCT

Information about the structure of objects within the database

PREV

Views for Oracle5 and Oracle6

SEC

Security Information, including users, roles, and privileges that have been granted or received

TUN

Views useful in tuning


If you are not familiar with the dictionary tables and views, ignore the AUDIT, DUP, and PREV objects. Oracle auditing is rarely used. DUP views show information that is available elsewhere. PREV views are provided for compatibility with previous versions of Oracle.

    Table 11.2. Data dictionary objects.
Object Purpose


Prefix


Name of Object


Description


AUDIT

USER, DBA

_AUDIT_CONNECT

Information about connections and disconnections to the database.

AUDIT

DBA

_AUDIT_DBA

Subset of DBA_AUDIT_TRAIL that lists the audit entries related to the different AUDIT statements.

AUDIT

DBA

_AUDIT_EXISTS

Subset of DBA_AUDIT_TRAIL that lists the audit entries related to the AUDIT_EXISTS and AUDIT_NOT_EXISTS statements.

AUDIT

USER

_AUDIT_OBJECT

If statement-level auditing is set up on objects, the audit trail for them can be viewed through this view. Lists the operating system user, Oracle user, terminal, and object that are affected.

AUDIT

USER, DBA

_AUDIT_SESSION

Audit trail entries for user connections and disconnections from the database. Includes operating system user, Oracle user, time logged on/off, amount of I/O performed, and deadlocks detected during session.

AUDIT

USER, DBA

_AUDIT_STATEMENT

Audit trail records for GRANT, REVOKE, AUDIT, NOAUDIT, and ALTER SYSTEM commands, including information about which privileges were granted or revoked.

AUDIT

USER, DBA

_AUDIT_TRAIL

A complete list of everything that has been audited. This is a superset of the other audit data dictionary views.

AUDIT

ALL

_DEF_AUDIT_OPTS

If auditing is enabled, this view shows which audit options have been set up.

AUDIT

USER, DBA

_OBJ_AUDIT_OPTS

Shows which audit options have been set up for tables and views.

AUDIT

DBA

PRIV_AUDIT_OPTS

Lists the system privileges currently being audited.

AUDIT

DBA

_STMT_AUDIT_OPTS

Lists the system auditing options currently in effect.

AUDIT

USER, DBA

_TAB_AUDIT_OPTS

Auditing options for the user's tables and views.

DB STRUCT

DBA

_DATA_FILES

The name, tablespace, and size of the database files, but not the control file or the redo logs.

DB STRUCT

ALL, USER, DBA

_DB_LINKS

Database links that enable access to other databases.

DB STRUCT

USER, DBA

_FREE_SPACE

Free space in the tablespaces, including an entry for each fragment of free space.

DB STRUCT

ALL, USER, DBA

_REFRESH

All the refresh groups that the user can touch.

DB STRUCT

ALL, USER, DBA

_REFRESH_CHILDREN

All the objects in the refresh groups that the user can touch.

DB STRUCT

DBA

_ROLLBACK_SEGS

The rollback statements that have been created in the database, including the static storage parameters and whether the rollback segment is currently in use. Use the v$rollstat dynamic view to see the current state of the rollback segment.

DB STRUCT

USER, DBA

_TABLESPACES

Describes the accessible tablespaces, including the default storage parameters to use if the storage parameters are not specified when objects are created in this tablespace.

DB STRUCT

USER, DBA

_TS_QUOTAS

If storage is allocated by the DBA on a tablespace by tablespace basis, this view shows how much storage the user can use in a tablespace. Information is given in terms of the number of Oracle blocks and the number of bytes.

DB STRUCT

ALL, USER, DBA

_USERS

Information about the current user or other users on the database, including the default and temporary tablespaces that are defined and encrypted passwords.

DB STRUCT

GLOBAL_NAME

Contains one row that shows the global name of the database. It is used in conjunction with database links.

DB STRUCT

NLS_DATABASE_
PARAMETERS

National language settings specified when the database was created.

DB STRUCT

NLS_INSTANCE_
PARAMETERS

National language settings specified in the INIT.ORA file for this instance.

DB STRUCT

NLS_SESSION_
PARAMETERS

National language settings currently in effect for this session.

DB STRUCT

PRODUCT_COMPONENT_VERSION

Version numbers for the database server products.

DB STRUCT

PUBLICSYN

Public synonyms currently defined for the database.

DUP

ALL, USER, DBA

_CATALOG

Objects—tables, views, synonyms, and sequences. This is a subset of the information shown in USER_OBJECTS.

DUP

ACCESSIBLE_
COLUMNS

Columns on all the tables accessible to the user.

DUP

CAT

Synonym for USER_CATALOG.

DUP

CATALOG

Oracle5 view that shows the objects on the database.

DUP

CLU

Synonym for USER_CLUSTERS.

DUP

COL

Oracle5 view that shows the columns on tables and views.

DUP

COLS

Synonym for USER_TAB_COLUMNS.

DUP

COLUMN_PRIVILEGES

Oracle6 view that shows whether column-level privileges have been granted.

DUP

DB

Synonym for V_$DATABASE that shows information about the database.

DUP

DICT

Synonym for DICTIONARY.

DUP

IND

Synonym for USER_INDEXES.

DUP

OBJ

Synonym for USER_OBJECTS.

DUP

SEQ

Synonym for USER_SEQUENCES.

DUP

SYN

Synonym for USER_SYNONYMS.

DUP

SYNONYMS

Oracle5 view that shows synonym information.

DUP

SYSCATALOG

Oracle5 view that shows information about all the objects in the database.

DUP

TABLE_PRIVILEGES

Oracle6 view that shows grants on objects for which the user is the grantor, grantee, owner, or an enabled role or for which PUBLIC is the grantee.

DUP

TABS

Synonym for USER_TABLES.

LOOK

AUDIT_ACTIONS

Lookup table for the audit action codes that lists the audit action names.

LOOK

DICT_COLUMNS

Describes the columns in data dictionary tables and views. It provides information on what each column means.

LOOK

DICTIONARY

Lists all the data dictionary views and dynamic performance tables.

LOOK

RESOURCE_MAP

Lookup table that shows the name of the resource for each of the IDs in the table. The IDs are used to reference resource names in other tables.

LOOK

SYSTEM_PRIVILEGE_MAP

Shows the system privileges that can be granted to roles or directly to Oracle users.

LOOK

TABLE_PRIVILEGE_
MAP

Shows the different types of table level privileges that can be granted.

LOOK

V$FIXED_TABLE

Names of all the dynamic performance tables.

LOOK

V$FIXED_VIEW_
DEFINITION

Definitions of the V$ views. It shows which X$ tables are accessed by the V$ views.

LOOK

V$INDEXED_FIXED_
COLUMN

Shows which columns of the X$ tables are indexed. It is useful in tuning SQL queries on the V$ views.

LOOK

V$LATCHNAME

Names of latches. It is used in conjunction with v$latch.

LOOK

V$NLS_VALID_
VALUES

Valid values that can be set up for the national language support parameters. It is set up depending on which national language software has been installed.

LOOK

V$OPTION

Lists the options installed with the server.

LOOK

V$STATNAME

Lookup table for the names of the statistics reported in the v$sysstat table.

LOOK

V$TYPE_SIZE

Sizes of the database components. It is useful in sizing formulas used to determine the optimum initial and next extent sizes for tables and indexes.

LOOK

V$VERSION

Information about the version numbers of the components for the Oracle server software.

MISC

ALL, USER, DBA

_COL_COMMENTS

Shows the column-level comments on tables or views created using the COMMENT command.

MISC

ALL, USER, DBA

_ERRORS

Errors during the compilation of procedures, functions, packages. The SQL*Plus command SHOW ERRORS formats the contents of this table, which is cleared out automatically when the pl/sql unit has been compiled successfully.

MISC

ALL, USER, DBA

_JOBS

If the DBMS_JOB server package has been used to schedule jobs, this view lists the jobs in the job queue, including who submitted the job, when it was last executed, the next execution date, the pl/sql for the job.

MISC

ALL, USER, DBA

_TAB_COMMENTS

Lists the comments on tables created using the COMMENT command.

MISC

DEPTREE

Shows dependence between database objects. It is created by the Oracle supplied script utldtree.sql.

MISC

DUAL

The dummy table used in some SQL SELECT statements

MISC

V$LOADCSTAT

Statistics on SQL*Loader direct load jobs, which bypass some of the architectural overheads of Oracle by doing more on SQL*Loader. It is not useful because the table cannot be accessed during load or afterward—the data disappears.

MISC

V$LOADTSTAT

Used by SQL*Loader direct load jobs. No useful access can be made. See V$LOADCSTAT.

MISC

V$TIMER

The current time in hundredths of a second since midnight.

MON

DBA

_2PC_NEIGHBORS

Lists the transactions that are involved in a distributed transaction if the distributed option is installed for the database serve. It is useful for the DBA when he must do a manual recovery of a failed distributed transaction.

MON

DBA

_2PC_PENDING

Lists the transactions that failed during a distributed update operation if the distributed database option is installed. It includes an column that advises the DBA whether a commit or rollback should be forced.

MON

DBA

_EXP_FILES

Information about full, cumulative or incremental exports that have been performed against the database, including the name of the export file, the user doing the export, and the time of the export.

MON

DBA

_EXP_OBJECTS

Lists the objects taken by an incremental export.

MON

DBA

_EXP_VERSION

Version number of the latest export.

MON

DBA

_JOBS_RUNNING

Information about the jobs currently running that use the DBMS_JOB package, including the date when the job last run and the time when it started to run for the current execution.

MON

USER, DBA

_OBJECT_SIZE

Sizes, in bytes, of various pl/sql objects, including the source and the parsed version.

MON

V$ACCESS

Objects that are currently locked and by which sessions.

MON

V$ARCHIVE

Information about the archived redo log files for each thread of the database.

MON

V$BACKUP

Information about which database files have been backed up using online tablespace backup commands to do hot backups. It includes the time and the SCN when the backup started.

MON

V$BGPROCESS

Describes the background processes and the latest error to have been encountered with the background process.

MON

V$CIRCUIT

Information about which server and dispatcher processes used by the user server processes when the multi-threaded server configuration is used.

MON

V$COMPATIBILITY

Information about whether a downgrade to a previous release is possible for this instance. It might change when the instance is shut down and started up again.

MON

V$COMPATSEG

Information about which release of the software introduced the new features of the database. It is used in conjunction with v$compatibility.

MON

V$CONTROLFILE

Lists the names and statuses of the control files.

MON

V$DATABASE

The name, creation date, database mode, and information about the last SCN archived and checkpointed. This information is gathered from the control file during instance startup.

MON

V$DATAFILE

Information about the database files, including each data file's current status, the size and name of the data file, and the SCN at the last checkpoint.

MON

V$DB_PIPES

Information on the database pipes currently in use on this database, including the owner.

MON

V$DBLINK

Shows all the database links that are currently open.

MON

V$DISPATCHER

Information on the status of the dispatcher processes and the amount of use when running the instance with the multi-threaded server setup. It also includes information useful in deciding whether to increase or reduce the number of dispatcher processes.

MON

V$INSTANCE

Status of the current instance, including whether it was started with RESTRICTED SESSION and whether it is currently being shut down.

MON

V$LATCHHOLDER

Lists which processes and which latches are currently being held.

MON

V$LICENSE

Used to determine whether the number of concurrent user limits for your license is being breached. It also shows the maximum number of simultaneous user connections since this instance started.

MON

V$LOCK

Information about the locks currently taken out on this instance, including locks taken out by user processes and locks taken out by the system processes.

MON

V$LOG

Information about the online redo log groups that are currently defined, including the log sequence number and archive status. This information is obtained from the control file.

MON

V$LOG_HISTORY

Information about when the redo log files were archived to the archive destination and the name of the archived file. It is used to ensure that archived files are not created too frequently or infrequently. It is useful for determining which redo log files to apply during a recovery procedure.

MON

V$LOGFILE

Information about log files, including the status of the redo log file and the group to which the log file belongs.

MON

V$MTS

Information about the performance of the multi-threaded server, including how many shared server processes were started automatically.

MON

V$NLS_PARAMETERS

Values set for the national language support for the whole instance.

MON

V$OBJECT_
DEPENDENCY

Shows dependencies on objects currently loaded in the shared pool area.

MON

V$PROCESS

Shows which processes are currently connected to the instance, including the operating system process identifier and the background processes.

MON

V$PWFILE_USERS

Lists users who have been given the SYSDBA and SYSOPER privileges.

MON

V$RECOVER_FILE

Information about files that must go through a media recovery process.

MON

V$RECOVERY_LOG

Information about the number and names of the archive logs needed to get the database files back into synchronization. It is used while a media recovery is in progress. It is a subset of the information in the v$log_history view.

MON

V$RESOURCE

Information about database resources.

MON

V$ROLLNAME

Names of the currently active rollback segments.

MON

V$SESSION

Information about each of the sessions connected to the instance.

MON

V$SQLTEXT

The SQL text of SQL statements in the shared cursors in the SGA.

MON

V$SQLTEXT_WITH_
NEWLINES

Similar to V$SQLTEXT but with newline and tab characters to improve legibility.

MON

V$THREAD

Information about the threads that are using the database if it is running in parallel server mode with many instances.

OBJ STRUCT

USER, DBA

_CLU_COLUMNS

Shows which columns in tables owned by a user are part of a cluster.

OBJ STRUCT

USER, DBA

_CLUSTERS

Clusters that have been created. If the cluster has been analyzed, this table includes information used by the cost-based optimizer.

OBJ STRUCT

ALL, USER, DBA

_CONS_COLUMNS

Column-level constraint information on tables, including information about which columns make up the primary key, foreign key, and unique key constraints.

OBJ STRUCT

ALL, USER, DBA

_CONSTRAINTS

Primary key, foreign key, unique key, check, and not null constraints on tables.

OBJ STRUCT

USER, DBA

_CROSS_REFS

Cross references for user views and synonyms.

OBJ STRUCT

ALL, USER, DBA

_DEPENDENCIES

Information on dependencies between database objects, such as tables, views, and procedures.

OBJ STRUCT

USER, DBA

_EXTENTS

Extents taken up by the segments. It shows the size and location of each extent.

OBJ STRUCT

ALL, USER, DBA

_IND_COLUMNS

Columns that make up the indexes on tables and the order in which the columns are included in the index.

OBJ STRUCT

ALL, USER, DBA

_INDEXES

Indexes that have been set up, including the current storage parameters in effect for the indexes. If the index has been analyzed, the information used by the cost-based optimizer is also available in this view.

OBJ STRUCT

ALL, USER, DBA

_OBJECTS

Objects—tables, views, synonyms, and procedures. Refer to this table first when you are not sure whether an object is a table, view, synonym, or procedure. It shows when the object structure was last modified.

OBJ STRUCT

USER, DBA

_SEGMENTS

Storage allocated for all the database segments, including the tablespace in which the storage is allocated and the number of extents taken up.

OBJ STRUCT

ALL, USER, DBA

_SEQUENCES

Sequences used to generate unique numbers using NEXTVAL and CURRVAL. It includes the last sequence number written to disk.

OBJ STRUCT

USER, DBA

_SNAPSHOT_LOGS

Snapshot logs that are used to refresh snapshots. It includes information about the table used to provide the snapshot log and the database trigger on the master table that is used to write to the log table.

OBJ STRUCT

ALL, USER, DBA

_SNAPSHOTS

Snapshots that are currently accessible to the user. It includes information about the master object the snapshot has been set up on, when the snapshot was last refreshed, the date of the next refresh, and the database trigger that is used to refresh the snapshot.

OBJ STRUCT

ALL, USER, DBA

_SOURCE

Source for procedures, functions, and packages.

OBJ STRUCT

ALL, USER, DBA

_SYNONYMS

Synonyms for tables, views, procedures, and so on.

OBJ STRUCT

ALL, USER, DBA

_TAB_COLUMNS

Column descriptions of tables, views, and clusters. If the table or cluster has been analyzed, information used by the cost-based optimizer is included in this data dictionary view.

OBJ STRUCT

ALL, USER, DBA

_TABLES

Tables, including the current storage parameters. If the table has been analyzed, it includes information used by the cost-based optimizer and information about the number of chained rows. The rowids of rows that have been chained can be obtained if the LIST CHAINED ROWS parameter is provided as part of the ANALYZE command.

OBJ STRUCT

ALL, USER, DBA

_TRIGGER_COLS

Lists columns used in triggers.

OBJ STRUCT

ALL, USER, DBA

_TRIGGERS

Database triggers that have been created on tables, including what event the trigger fires on and the source code for the trigger.

OBJ STRUCT

ALL, USER, DBA

_VIEWS

View definitions, including the underlying SQL statement for the view.

PREV

SYSFILES

Oracle5 view that shows the operating system files used by the database.

PREV

SYSSEGOBJ

Oracle5 view that shows information about the different segments on the database.

PREV

TAB

Oracle5 view that shows table information.

PREV

TABQUOTAS

Oracle5 view that shows information about the amount of storage that users can use.

PREV

V$DBFILE

Redundant. Use v$datafile instead.

PREV

V$LOGHIST

Redundant. Use v$log_history instead.

SEC

ALL, USER, DBA

_COL_PRIVS

Shows which column level privileges have been granted or received if access to tables have been taken down to the column level.

SEC

ALL, USER

_COL_PRIVS_MADE

Shows which column-level privileges have granted if security has been taken down to the column level.

SEC

ALL, USER

_COL_PRIVS_RECD

Shows which column-level privileges have been received by the current user if security has been taken down to the column level.

SEC

DBA

_PROFILES

Lists the profiles created on the database when Oracle profiles are used to limit resource usage.

SEC

USER, DBA

_ROLE_PRIVS

Roles granted to the user. Indicates whether the role is a default, whether the user is allowed to administer the role, and whether the roles requires operating system privileges to be enabled.

SEC

DBA

_ROLES

Lists the names of roles and whether a password is required to enable them.

SEC

USER, DBA

_SYS_PRIVS

System privileges that have been given directly to the user.

SEC

ALL, USER, DBA

_TAB_PRIVS

Superset view that lists the grants received or given on objects for which the user made the grant, received the grant, or is the owner of the object.

SEC

ALL, USER

_TAB_PRIVS_MADE

Subset of TAB_PRIVS that shows grants made on objects owned by the current user.

SEC

ALL, USER

_TAB_PRIVS_RECD

Subset of TAB_PRIVS that shows grants on objects for which the user has received the grant.

SEC

RESOURCE_COST

The cost of each resource. It is used to apply weighting factors for resources that can be limited for users with Oracle profiles. The user can change the weighting factors for each resource by using the ALTER SYSTEM SET RESOURCES COST statement.

SEC

ROLE_ROLE_PRIVS

Roles granted to roles. It indicates whether the role has been granted with the ADMINISTRATOR option.

SEC

ROLE_SYS_PRIVS

System privileges granted to roles. It indicates whether the role can grant the system privilege to other roles or users.

SEC

ROLE_TAB_PRIVS

Table privileges granted to roles. It indicates whether the role can grant the table-level privilege to other roles or users.

SEC

SESSION_PRIVS

System-level privileges available in this session.

SEC

SESSION_ROLES

Roles enabled in this session.

SEC

V$ENABLEDPRIVS

Privileges in effect for the session.

TUN

DBA

_ANALYZE_OBJECTS

List the tables, indexes, and clusters that have been analyzed using the ANALYZE SQL statement.

TUN

USER

_RESOURCE_LIMITS

Resource limits—such as CPU time and logical and physical I/O—that have been set up using Oracle profiles.

TUN

CHAINED_ROWS

The default table name used to report the rowids of rows where a chaining problem has been detected by using the ANALYZE TABLE LIST CHAINED ROWS statement. It is created by the utlchain.sql Oracle-supplied script.

TUN

EXCEPTIONS

Used to provide further information about why an exception could not be enabled. It is created by the utlexcpt.sql Oracle supplied script.

TUN

INDEX_HISTOGRAM

Used with the VALIDATE INDEX command to determine how many times key values have been repeated. Instead, use the ANALYZE INDEX command and query the user_indexes table.

TUN

INDEX_STATS

Used after THE VALIDATE INDEX command has been run. It shows lower-level information about how the index is currently constructed. Instead, use the ANALYZE INDEX command and query the user_indexes table.

TUN

PLAN_TABLE

The default table that stores the results of the EXPLAIN PLAN statement, which shows the execution plan for the statement. It is created by the utlxplan.sql Oracle-supplied script.

TUN

V$DB_OBJECT_CACHE

Shows which object definitions are currently being cached in the shared pool area of the SGA, including what users are currently using the object and whether the object has been kept in the SGA.

TUN

V$FILESTAT

Information useful in tuning the database files, including number of physical reads from each database file.

TUN

V$LATCH

Usage and wait information on latches.

TUN

V$LIBRARYCACHE

Information about how efficiently the library cache area of the shared pool is being used in the SGA.

TUN

V$MLS_PARAMETERS

Parameters specified in the INIT.ORA file used to set up Trusted Oracle7.

TUN

V$MYSTAT

Tuning information for this session. It is a subset of the information available in v$sysstat. Use v$statname to get the name of the statistic whose ID is reported in this table.

TUN

V$OPEN_CURSOR

Information about the cursors that are currently open for each user session, including the first part of the SQL statement.

TUN

V$PARAMETER

The INIT.ORA parameters currently in effect. It indicates whether the parameter is the default or whether the default has been overridden in the INIT.ORA file.

TUN

V$QUEUE

Queues for the multi-threaded server setup, including information about items in queue.

TUN

V$REQDIST

Information about the time taken to process requests.

TUN

V$ROLLSTAT

Information about rollback segments, including how often the rollback segments have shrunk and wrapped back to the first extent, as well as the maximum size they have reached.

TUN

V$ROWCACHE

Provides information that can be used to monitor each row within the data dictionary cache in the shared pool area. The data dictionary caches can be tuned only by increasing the total size of the shared pool area that is shared between the data dictionary caches and the cursor area.

TUN

V$SESS_IO

The amount of logical and physical I/O performed by the currently connected session.

TUN

V$SESSION_CURSOR_CACHE

Tuning information that shows how many cursors have been opened during the session and how often they have been used.

TUN

V$SESSION_WAIT

Shows resources or events that currently connected sessions to the database are waiting for.

TUN

V$SESSTAT

A subset of the information shown in the v$sysstat table.

TUN

V$SGA

The amount of memory used by the SGA.

TUN

V$SGASTAT

More detailed information about the SGA in memory.

TUN

V$SHARED_SERVER

Statistical information about the shared server processes that form part of the multi-threaded server architecture. It includes information about how much work each shared server performs.

TUN

V$SQL

Similar to the V$SQLAREA view except that identical statements are listed as often as they occur.

TUN

V$SQLAREA

Information about the SQL statements currently in cursors in the shared pool area, including the amount of memory needed for them, the number of users executing the statements, the amount of I/O performed, and the number of times the statements were asked to be parsed.

TUN

V$SYSSTAT

Instance-level statistics that show the number of logins and physical and logical I/O since the instance was started.

TUN

V$SYSTEM_CURSOR_
CACHE

Similar to V$SESSION_CURSOR_CACHE but shows the cursors in the entire system.

TUN

V$SYSTEM_EVENT

Information about the number of waits for system events.

TUN

V$TRANSACTION

Information about current transactions, including which rollback segments are currently being used by the transactions.

TUN

V$WAITSTAT

Information useful in determining whether waiting occurs because of latches conflicts or because of inserts into a table. It is populated if the TIMED_STATISTICS INIT.ORA parameter is set.

Summary

In this chapter, you learned how to create a database, how to set the mode of the database to ARCHIVELOG, and how to bring an instance up and down. You also learned about the initialization parameters for an instance and the information available in the data dictionary. The fundamentals covered in this chapter are the same for whatever platform your Oracle server runs on. Study your installation and user's guide to see whether any specific differences exist for your platform.

Previous Page Main Page Next Page