How to Use Oracle 12c’s Data Dictionary - dummies

How to Use Oracle 12c’s Data Dictionary

By Chris Ruel, Michael Wessler

Learning the in and out of the data dictionary is one of the most important things you can do to become a top-notch Oracle 12c Database Administrator (DBA). The data dictionary is a collection of tables and views inside the database that hold all the information about the current and past state of the database.

Data in the data dictionary is modified only by Oracle itself through the running processes of the database. Oracle records millions of bits of information for which you can use to tune, secure, and troubleshoot the database. It may take years for you to master. Understanding the Oracle data dictionary is one of the measuring sticks between a junior or senior DBA.

The data dictionary can be broken into the categories noted below.

Prefix Type Description
USER_ View Objects owned by current user
ALL_ View All objects to which current user has access
DBA_ View All objects in the database
V$ View Dynamic performance view. Populated from memory and control
files
GV$ View Like V$, but, for multiple instances in a cluster
environment
X$ Table Internal tables containing cryptic but often useful data

Of the dictionary view types listed, as a DBA, you will spend most of your time reading the DBA_ and V$ views. These views provide the most useful and most easily interpreted data. In fact, users who aren’t DBAs often will not have access to the DBA_ and V$ views.

On the other hand, all users have ACCESS to the USER_ and ALL_ views. Keeping the DBA_, V$, and X$ views hidden is an important part of database security. Some information in those views could be used by people to gain access to data in which they are not allowed.

Oracle 12c has thousands of data dictionary views in the database. In the database used for demonstration purposes, there are approximately three thousand.

Most, but not all, DBA_ and V$ views have names that are somewhat intuitive. For example, here is a sampling of useful views in the database.

Name Contents
DBA_TABLES Information about all tables
DBA_USERS Information about all users
DBA_AUDIT_TRAIL Information about captured audits
V$DATABASE Information about the current database configuration
V$CONTROLFILE Information about the current database control files

Despite the vast amount of data dictionary objects available to you, getting information about them is relatively easy with a little bit of practice. The Oracle documentation is going to be the definitive source of all information, listing the different views and describing the contents of the various columns. You can get the documentation for the view at Oracle docs.

With a little bit of know-how and common sense, you can also get a lot of the information yourself. Many of the views have names that are self-explanatory. With that information, you can look inside the database to see what views are available. There is actually a view of the views:

SQL> describe dictionary
 Name             Null?  Type
 ----------------------------- -------- --------------------
 TABLE_NAME               VARCHAR2(128)
 COMMENTS                VARCHAR2(4000)

You can sometimes find what you are looking for with a little bit of common sense and cleverness. Say you’re looking for information about indexes:

SQL> SELECT table_name
 2 FROM dictionary
 3 WHERE table_name like 'DBA%INDEX%';
TABLE_NAME
------------------------------------------------------------
DBA_INDEXES
DBA_INDEXTYPES
DBA_INDEXTYPE_ARRAYTYPES
DBA_INDEXTYPE_COMMENTS
DBA_INDEXTYPE_OPERATORS
DBA_PART_INDEXES
DBA_XML_INDEXES
7 rows selected.

Getting familiar with the data dictionary is paramount. You might hear there’s no need to worry about the data dictionary because all the GUI tools give you the information that you need. The fact is those GUI tools do read from the data dictionary views.

However, don’t let that give you a false sense of security. More than once, we’ve seen where the GUI tools have failed or gone offline. If you’re not comfortable navigating the data dictionary by SQL, it could be the end of your DBA job in an emergency.

We would also advise you that despite the availability of the GUI tools, a DBA who is efficient at querying the data dictionary with SQL can often get accurate answers more quickly than someone using a tool like Database Express.

Last, if you want very specific reports generated on a schedule, there is no better way than to write your own reports and schedule to run as a script through a scheduler, such as Windows Task Scheduler or UNIX/Linux crontab. Then, after generating the report, the script can send the results out via e-mail.

You may like to have reports that are not canned in Enterprise Manager, such as Users with Failed Login’s due to Wrong Password in the Last 24 Hours:

SQL> SELECT USERNAME, USERHOST, TIMESTAMP, ACTION_NAME, RETURNCODE
 2 FROM dba_audit_trail
 3 WHERE username = 'HR'
 4 ORDER BY timestamp;
USERNAME USERHOST    TIMESTAMP ACTION_NAME RETURNCODE
-------- --------------- --------- ------------ ----------
HR    orasvr01    06-JUN-13 LOGON         0
HR    orasvr01    08-JUN-13 LOGON       1017
HR    orasvr01    09-JUN-13 LOGOFF        0
HR    orasvr01    16-JUN-13 LOGON         0
HR    orasvr01    17-JUN-13 LOGON         0
HR    orasvr01    17-JUN-13 LOGOFF        0
HR    orasvr01    18-JUN-13 LOGOFF        0
HR    orasvr01    28-JUN-13 LOGON         0
HR    orasvr01    28-JUN-13 LOGON       1017
HR    orasvr01    28-JUN-13 LOGOFF        0
10 rows selected.

Or, Tables Created by User HR in the Last 100 Days:

SQL> SELECT object_name, created
 2 FROM dba_objects
 3 WHERE created > sysdate - 100
 4 AND object_type = 'TABLE'
 5 AND owner = 'HR';
OBJECT_NAME   CREATED
--------------- ---------
REGIONS     09-MAY-13
LOCATIONS    09-MAY-13
JOB_HISTORY   09-MAY-13
JOBS      09-MAY-13
EMPLOYEES    09-MAY-13
DEPARTMENTS   09-MAY-13
COUNTRIES    09-MAY-13
7 rows selected.