0441-0443

Previous Table of Contents Next

Page 441

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:

 SHUTDOWN ABORT 

Similar to the IMMEDIATE option, ABORT disconnects all the users currently connected, but the rollback operation is not 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 cruder way of bringing down the database on some platforms is to kill the Oracle background processes currently running. If the background processes no longer are running, the instance is not up. This method is not recommended, though.

Regardless of how you bring down the instance, 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 process is invisible to the user and the DBA. The only noticeable effect after SHUTDOWN IMMEDIATE or SHUTDOWN ABORT is an increase in the time taken during the opening stage of bringing up the instance.

Suppose that you start an instance in the 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. Similarly, you could use the ALTER DATABASE command, which takes the database from one stage of startup to another (refer to the Oracle8 Server SQL Reference Manual for more information on this command).

The Data Dictionary

The Oracle data dictionary is a set of tables 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 rarely is used, even by DBAs, for maintenance or inquiry work. Instead, another Oracle user with high-level system privileges is used. Typically, only one other DBA account is created for the user of the DBA.

Page 442

The DBA does not usually use the SYSTEM user, who also is 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$, and ind$. These core system tables rarely are referenced directly, because the information is available in more readily digestible forms 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. These views make information available in a readable format. The 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 Instance Manager utility does not. Therefore, you use SQL*Plus for running queries on these views.

TIP

If you are not sure which data dictionary view contains the information you want, write a query on the DICT view. Suppose 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 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 on to the Oracle account LINEKER and query the USER_TABLES view, the only table you will see is the FOOTBALL table. If you disconnect from that user, connect to the Oracle user BRUNO, and then run the same query, the only table you 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 that BRUNO gives SELECT access on the BOXING table to LINEKER. When you log on to the Oracle database

Page 443

using the LINEKER account and run a query on the ALL_TABLES view, you see information on both the FOOTBALL and BOXING tables. One is owned by the account, and the other is a table 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 typically are used only by the DBA. These views show information for all 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 on as SYS and query the DBA_TABLES view, you 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 is 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 these tables 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. The v$parameter table shows the settings for the initialization parameters that have been left at their defaults or set in the INIT.ORA file, for example.

The dynamic tables are updated continuously when activity occurs, and they provide data that is useful for tuning. The v$ sysstat table, for example, 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 views are 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 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.

Previous Table of Contents Next