How to Use Oracle 12c’s Data Dictionary
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.
|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
|GV$||View||Like V$, but, for multiple instances in a cluster
|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.
|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.