Page 114
v$bgprocess can be used to quickly list the running background processes. The following query will help:
SELECT name FROM v$bgprocess WHERE paddr <> `00';
The dba_jobs table holds information on pending jobs scheduled through the DBMS_JOBS package. You can list the scheduled database jobs with the following query:
SELECT log_user, job, what, to_char(last_date, `DD-MON-YY') l1, last_sec l2, to_char(next_date, `DD-MON-YY') n1, next_sec n2, failures FROM dba_jobs ORDER BY next_date DESC, next_sec DESC;
User information is stored in dba_users. Information on granted privileges is stored in the dba_tab_privs, dba_sys_privs, and dba_role_privs tables. You can also query role_tab_privs, role_sys_privs, and role_role_privs to display information on privileges granted to roles. More information on monitoring security information can be found in Chapter 23, "Security Management."
Now that we know where database configuration information is stored, let's look at the user data objects information. Database segments we are interested in include tables, indexes, views, clusters, and other data storing objects.
The DBA_TABLES, DBA_INDEXES, DBA_SEGMENTS, DBA_SEQUENCES, and DBA_OBJECTS data dictionary views listed in Table 7.6 provide information on the various database segments stored in the database.
The format and information in the views are fairly self explanatory. Dba_objects is interesting because it stores creation and last modification (timestamp) information on all objects stored in the database, and DBA_SEGMENTS is useful because it stores the size and number of extents used by each database segment.
Our exploration of the database is not complete without looking at several other structures, including the stored PL/SQL objects, triggers, synonyms, and database links.
Sources for the packages, procedures, and functions are stored in the sys.source$ table. When you create the object, all of the blank lines are stripped and each line is stored as a separate record in this table. You can query dba_source to easily retrieve the text of stored packages, procedures, and functions. The following query will produce the source code for an indicated stored object:
SELECT text FROM dba_source WHERE name = upper(`&Object_Name') ORDER BY line;
Page 115
Information on triggers is stored in the dba_triggers table. Because the trigger body is stored as a long value, you'll need to set your longsize to an adequate value when querying on this column from SQL*Plus. In this view, the TRIGGER_TYPE column is either row or statement, and TRIGGERING_EVENT stores the type of triggerbefore insert, after update, and so on.
DBA_synonyms lists information on the synonyms in the database. Note that public synonyms will have PUBLIC as their owner. Query dba_db_links for information on the defined database links. One word of caution on dba_db_linksif the link is defined with a user and password to connect to, the password is stored in clear text.
Let's put some of the information covered in this chapter into a familiar frame of reference. Suppose you are a contract DBA and have just arrived at a new site. The previous DBA has already departed the scene and, amazingly enough, has left no supporting documentation of his system configuration. What can you do?
The following two sections explore possible courses of actionone on a UNIX server, and one on a Windows NT server. They should give you ideas and reinforce the information presented earlier in the chapter.
Your primary interest is to determine how the Oracle software has been installed and configured, and what databases are on the system. The first step is obtaining access to the OS account used to install the Oracle software. Next, you'll need to locate possible ORACLE_HOME directories. If there is only one ORACLE_HOME on the system, this should already be set by your login sequence. Check the ORACLE_HOME environment variable for this value. Also, find and check the oratab file to determine if database and ORACLE_HOME information is contained within. This is not a guaranteed comprehensive list. However, the oratab file is manually updated, and up-to-date information is not a requirement to operate the database. Another alternative is to search for the Oracle server executable (oracle), or the rdbms and dbs directories. These are standard in all Oracle server installations. Make a careful list of all possible Oracle directories.
Next, you'll want to determine what databases are on the server. In each of the Oracle home directories you found, check the dbs directory. All of the configured databases should have an initSID.ora file in this directory. The file may be linked to a different administrative directory, but the listing of these files will show you all of the databases that are configured. Again, this does not necessarily mean that all of these databases exist. The creation as well as the deletion of the init.ora files is, for the most part, manually done by the DBA. It is also no guarantee that the init.ora files will be in the dbs directory. As this is where these files are looked for by default by Oracle programs, it is likely you'll find them here.
Check your user and system login script and determine whether custom modifications have been made to the Oracle environment. Check to see whether coraenv or oraenv are being
Page 116
called from the login script and whether these files have been modified in any way. If coraenv or oraenv is not being called, determine where the Oracle environment variables are being set. Check the system startup scripts (rc files and the like) to see what programs or daemons are automatically started upon system boot. Don't forget to check the crontab file or system scheduler for the Oracle owner and administrator to determine what automated jobs are scheduled and running. Finally, examine your UNIX kernel parameters to see how the memory and kernel has been configured for your Oracle install. The location and method of setting these parameters vary between UNIX vendors. Check your OS-specific documentation for more details.
On a Windows NT server, figuring out the specific configuration of the Oracle environment is a little more straightforward than on a UNIX server. This is because fewer aspects of the configuration are left completely to the DBA's whimsy. Your first step in deciphering a Windows NT environment is to look at the Oracle Registry values. Using regedit, look at the HKEY_LOCAL_MACHINE\Software\Oracle key. The string values will tell you the ORACLE_HOME location, among other useful information. Next, open up the Services applet in the Control Panel and look for any Oracle services. There will be one Oracle service for each database on the system. There may be an Oracle Start for each database, as well. Also, look for any other Oracle services that may be installedthe TNS Listener, Web Server, and so on.
All configuration values of interest will be stored in the Oracle Registry key. The init.ora parameter files for the database are in $ORACLE_HOME/database. This is also the default directory to store database datafiles. You may or may not find them here. Be sure to check the Windows NT job scheduler to see what jobs may be or have been running.