The Data Dictionary Views | Guerrilla Oracle: The Succinct Windows Perspective

There are four major groups of data dictionary views. The easiest way to identify them is by the prefix: USER , ALL , DBA, or V$ . In general, the USER, ALL, and DBA views have pretty much the same information. USER views can be seen as a subset of ALL views, and ALL views as a subset of DBA views. V$ views contain dynamically changing information, such as system usage, and they are usually of interest only to DBAs.

To be perfectly clear, the DBA views provide information about the entire database: all users, all tables. ALL views return information about all the objects that a particular user can access in all schemas in the entire database ”but they are still user-oriented. And USER views provide information on only the particular schema that each user owns.

For example, if you requested a list of all the objects for a typical user ” SQL> SELECT OBJECT_NAME FROM USER_OBJECTS ”maybe only a couple or a dozen or two dozen objects would be returned. If the user were a developer, however, you might get hundreds or thousands. Make the same request for ALL_OBJECTS , and Oracle will return thousands of objects, reflecting all the data dictionary views, system tables and indexes, and so forth that a user automatically can access. Log on as a DBA, and even more objects will be returned if you make the same request for DBA_OBJECTS . Is this clear? Good. It's just a hierarchical way of looking at the data.

Table 10.1 summarizes the different groups of data dictionary views.

Table 10.1. The Four Major Groups of Data Dictionary Views

Prefix	Explanation
USER	These are the user's views ”only what is in the user's schema.
ALL	Again, these are the user's views, but they show whatever the user has rights to in all schemas.
DBA	These are the high-level, DBA views that show everything about a database.
V$	These are dynamic "performance tables" maintained by Oracle that record what is currently going on with the database. Usually only DBAs access V$ views.

Although thousands of views are available, you will use just a small set of them most frequently. Let's cover these commonly used views first. What follows is a collection of the most frequently asked questions, along with answers that reveal the views in this category. You may want to consider starting your own list of such questions as the beginning of your own data dictionary cheat sheet:

Q	How do I find all the tables and views in the data dictionary?
A	`SQL>SELECT * FROM DICTIONARY;`

Note

A nickname for dictionary is dict ”for example: select * from DICT;

Q	How do I show all tables in a database?
A	`SQL>SELECT * FROM DBA_TABLES;`
Q	How do I show all the tables that a user owns ?
A	`SQL>SELECT * FROM USER_TABLES;`
Q	How do I show all tables in a database that a user can access ?
A	`SQL>SELECT * FROM ALL_TABLES;`

Note

There is a nickname (actually, it's a public synonym) for USER tables. Just use TABS , as in

 SQL>SELECT * FROM TABS

(Another nickname, TAB , returns only three columns of information about a table, unlike the 26 columns that TABS returns.)

Q	How do I check whether an account is locked?
A	`SQL>SELECT * FROM USER_USERS WHERE USER = <` `username` `>;`

What's in USER_USERS? There are eight columns of information:

Username . Self-explanatory
User_ID . A system-generated number
Default_Tablespace . What you gave the user when you created the account
Temporary_Tablespace . Same as Default_Tablespace
Created . Date the account was created
Account_Status . Locked, unlocked, or expired
Lock_Date . Date the account was locked
Expiry_Date . Date the account will expire

Note

Let's review what synonyms, or public synonyms, are. (We talked about these in Chapter 8, but the idea is worth repeating.) We can create alternate names for objects, either to hide the underlying name , especially for a table, or to make the name easier for users to handle. Such a name is called a synonym . It just makes life easier to use the nicknames. If you're a purist, go ahead and use the full, original names. The SELECT commands will work no matter which name you use! (As long as you have the security rights. That is, if you have rights to the synonym but not the real table name, you cannot do a SELECT using the real table name. You'll get the "Object xxx DOES NOT EXIST" error message.)

Q	How do I show all columns in all tables?
A	`SQL>SELECT * FROM ALL_TAB_COLUMNS;` or `SQL>SELECT * FROM USER_TAB_COLUMNS;`
Q	How do I find the columns in the data dictionary views and tables?
A	`SQL>SELECT * FROM DICT_COLUMNS;`

Tip

Given that the data dictionary will have the data for well over 10,000 tables and views ”more likely close to 20,000 ”it is strongly advised that you qualify any SELECT * statement. Otherwise, you should have spooling turned on and be prepared to spend hours searching a large file to find what you need.

Instead, use qualifiers or exact names to reduce the number of rows returned ”for example,

 SQL>SELECT * FROM ALL_TABLES WHERE TABLE_NAME LIKE " WAIVER%";

Just be careful. The table names are case-sensitive , so put them in UPPERCASE or you won't get anything back.

Q	How do I find out which privileges the different roles have?
A	`SQL>SELECT * FROM ROLE_TAB_PRIVS;`
Q	How do I find out which roles a particular user has?
A	`SQL> SELECT * FROM USER_ROLE_PRIVS WHERE USERNAME = <` `username` `>;`
Q	How do I find out all the tables and views that a user owns?
A	`SQL> SELCT * FROM DBA_TABLES WHERE OWNER = <` `ownername` `>;`

Note

DBA tables can be seen only by users who have the "SELECT ANY TABLE" privilege.

Q	How do I find out which constraints a table has?
A	`SQL>SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME = <` `tablename` `>;`
Q	What's a quick way to list every user in the database?
A	`SQL>SELECT * FROM ALL_USERS;` (You will get only three columns ”Username, User_ID, and Created ”but if all you want is a list, this is the best command. You can then use USER_USERS to get more details.)
Q	How do I find out what the default tablespaces are for a user?
A	`SQL>SELECT * FROM USER_USERS WHERE USERNAME = <username>;` (Yes, I showed you USER_USERS a little earlier. It is one of the most widely used data dictionary views when one is working with a user account.)
Q	How do I find all the profiles in the database?
A	`SQL>SELECT * FROM DBA_PROFILES;`

In the preceding Q&A section, I have given you a quick list of the data dictionary views that you will use the most. As mentioned already, there are many, many more. Let's take a look at some of them. My approach to showing you more of the data dictionary is to follow what we have already done. We have:

Created tables
Added constraints
Created primary keys
Created profiles
Created roles
Created users
Added limits to users
Created tablespaces

The next steps will be to show you the the power of the data dictionary with respect to these tasks . When you need to see what you have done, or when a user complains about a security or space error, how can you track it down quickly? In other words, how do you find this information after the fact?

But wait, we're not done! We're also going to take a look at:

A little bit more on auditing
Dynamic tables ”the V$ environment

1 Tables

Start with the basic view, ALL_TABLES. Quite a bit of information is returned from the command SQL>DESC ALL_TABLES :

ALL_TABLES View
Name	Type	Null?
Owner	VARCHAR2(30)	NOT NULL
Table_Name	VARCHAR2(30)	NOT NULL
Tablespace_Name	VARCHAR2(30)	NOT NULL
Cluster_Name	VARCHAR2(30)
Pct_Free	NUMBER
Pct_Used	NUMBER	NOT NULL
Ini_Trans	NUMBER	NOT NULL
Max_Trans	NUMBER	NOT NULL
Initial_Extent	NUMBER
Next_Extent	NUMBER
Min_Extents	NUMBER
Max_Extents	NUMBER
Pct_Increase	NUMBER
Freelists	NUMBER
Freelist_Groups	NUMBER
Backed_up	VARCHAR2(1)
Num_Rows	NUMBER
Blocks	NUMBER
Empty_Blocks	NUMBER
Avg_Space	NUMBER
Chain_Cnt	NUMBER
Avg_Row_Len	NUMBER
Degree	VARCHAR2(10)
Instances	VARCHAR2(10)
Cache	VARCHAR2(5)
Table_Lock	VARCHAR2(8)

2 Constraints

To find the constraints for a table, use the command SQL>DESC_ALL_CONSTRAINTS :

ALL_CONSTRAINTS View
Name	Type	Null?
Owner	VARCHAR2(30)	NOT NULL
Constraint_Name	VARCHAR2(30)	NOT NULL
Constraint_Type	VARCHAR2(1)
Table_Name	VARCHAR2(30)	NOT NULL
Search_Condition	LONG
R_Owner	VARCHAR2(30)
R_Constraint_Name	VARCHAR2(30)
Delete_Rule	VARCHAR2(9)
Status	VARCHAR2(8)

To look at any constraints on a particular column in a table, use the ALL_CONS_COLUMNS view:

ALL_CONS_COLUMNS View
Name	Type	Null?
Owner	VARCHAR2(30)	NOT NULL
Constraint_Name	VARCHAR2(30)	NOT NULL
Table_Name	VARCHAR2(30)	NOT NULL
Column_Name	VARCHAR2(30)	NOT NULL
Position	NUMBER

3 Primary Keys

To find the primary key for a table, use the command SQL>DESC ALL_CONSTRAINTS to get the ALL_CONSTRAINTS view, and look at Constraint_Type. Here's an example:

ALL_CONSTRAINTS View
Table_Name	Constraint_Type	Constraint_Name
TEST_TABLE	P	SYS_C0021715

Notice that the type is P, for primary key . If this were a foreign key, which we have in our waiver tables, there would be something in the columns R_Owner and R_Constraint_Name (see the complete list of columns in the ALL_CONSTRAINTS view in the preceding section).

Now how would you find all the columns involved with a primary key if the primary key were made up of several columns? There are two steps: (1) run the ALL_CONSTRAINTS view (you've already done this); (2) take the information for Constraint_Name and run the view ALL_CONS_COLUMNS, using Constraint_Name:

 Select COLUMN_NAME from USER_CONS_COLUMNS Where CONSTRAINT_NAME = 'SYS_C0021715';

This command will return any columns that are part of the primary key.

4 Profiles

To show the user's profile, you have to use a DBA view ”DBA_USERS:

DBA_USERS View
Name	Type	Null?
Username	VARCHAR2(30)	NOT NULL
User_ID	NUMBER	NOT NULL
Password	VARCHAR2(30)
Default_Tablespace	VARCHAR2(30)	NOT NULL
Temporary_Tablespace	VARCHAR2(30)	NOT NULL
Created	DATE	NOT NULL
Profile	VARCHAR2(30)	NOT NULL

Notice that this view gives you the profile name only. To see what the profile actually is, use the DBA_PROFILES view. The command is:

 SQL> select resource_name, Limit from dba_profiles   2  where profile = 'WAIVER_DEV_PROFILE';

DBA_PROFILES View
Resource_Name	Limit
Composite_Limit	DEFAULT
Sessions_Per_User	UNLIMITED
CPU_Per_Session	DEFAULT
CPU_Per_Call	3000
Logical_Reads_Per_Session	DEFAULT
Logical_Reads_Per_Call	DEFAULT
Idle_Time	45
Connect_Time	UNLIMITED
Private_SGA	DEFAULT

See how this view will show you all the limits for the particular profile?

Note

Remember that to see the DBA views, you must have the "SELECT ALL TABLES" privilege!

5 Roles

To find out what roles exist, use the DBA_ROLES view:

DBA_ROLES View
Role	Password
Connect	NO
Resource	NO
DBA	NO
Exp_Full_Database	NO
Imp_Full_Database	NO
Waiver_Dev_Role	NO
Waiver_Bus_Role	NO
Waiver_Student_Role	NO

Then use the view DBA_ROLE_PRIVS to get some more details about a particular role:

DBA_ROLE_PRIVS View
Name	Type	Null?
Grantee	VARCHAR2(30)
Granted_Role	VARCHAR2(30)	NOT NULL
Admin_Option	VARCHAR2(3)
Default_Role	VARCHAR2(3)

Notice that this view tells you who granted the role, and whether it was granted with the powerful Admin_Option.

Well and good, you may be thinking, but the roles we created have lots of other information about things like number of sessions and so forth. Where do we find that information?

It's not easy. Unfortunately, no single view shows you every privilege for a role. But since you've gotten this far in the book, you're obviously stubborn enough to stick with me as I show you how to get the final details.

First understand that there are really three kinds of grants:

Grants given about tables and sometimes columns
Grants that give the role system privileges
Grants that give roles to other roles

Each of these grants has its own view, as you might have suspected. I told you this would be a little complicated!

Let's go. For grants about tables and columns, use the ROLE_TAB_PRIVS view:

ROLE_TAB_PRIVS View
Name	Type	Null?
Role	VARCHAR2(30)	NOT NULL
Owner	VARCHAR2(30)	NOT NULL
Table_Name	VARCHAR2(30)	NOT NULL
Column_Name	VARCHAR2(30)
Privilege	VARCHAR2(40)	NOT NULL
Grantable	VARCHAR2(3)

For grants about any system privileges, use the ROLE_SYS_PRIVS view:

ROLE_SYS_PRIVS View
Name	Type	Null?
Role	VARCHAR2(30)	NOT NULL
Privilege	VARCHAR2(40)	NOT NULL
Admin_Option	VARCHAR2(3)

And for information about grants that give roles to other roles, use the ROLE_ROLE_PRIVS view:

ROLE_ROLE_PRIVS View
Name	Type	Null?
Role	VARCHAR2(30)	NOT NULL
Granted_Role	VARCHAR2(30)	NOT NULL
Admin_Option	VARCHAR2(3)

Finally, to find out which roles a particular user has, use the USER_ROLE_PRIVS view:

USER_ROLE_PRIVS View
Name	Type	Null?
Username	VARCHAR2(30)
Granted_Role	VARCHAR2(30)
Admin_Option	VARCHAR2(3)
Default_Role	VARCHAR2(3)
OS_Granted	VARCHAR2(3)

6 Users

Let's start with basic user information. The most basic data dictionary view for users is USER_USERS:

USER_USERS View
Name	Type	Null?
Username	VARCHAR2(30)	NOT NULL
User_ID	NUMBER	NOT NULL
Default_Tablespace	VARCHAR2(3)	NOT NULL
Temporary_Tablespace	VARCHAR2(3)	NOT NULL
Created	DATE	NOT NULL

Although this data dictionary view doesn't give much information, you can use it to:

Quickly find a user
Get the user's default tablespaces

To see which, if any, system privileges a user may have, use the USER_SYS_ PRIVS view:

USER_SYS_PRIVS View
Name	Type	Null?
Username	VARCHAR2(30)
Privilege	VARCHAR2(40)	NOT NULL
Admin_Option	VARCHAR2(3)

And to find all the tables, views, and so forth that the user owns, use the USER_CATALOG view:

USER_CATALOG View
Name	Type	Null?
Table_Name	VARCHAR2(30)	NOT NULL
Table_Type	VARCHAR2(11)

For user information, Table 10.2 gives a good synopsis of the available data dictionary views.

Table 10.2. USER Data Dictionary Views

View	Contents
USER_CATALOG	Tables, views, synonyms, and sequences that the user owns. There are only two columns of data: Table_Name and Table_Type.
USER_CONS_COLUMNS	The columns that are involved with the constraints. Use this with USER_CONSTRAINTS.
USER_CONSTRAINTS	Any constraints on any table that the user owns. Use ALL_CONSTRAINTS to see the constraints on all the tables that the user can access.
USER_OBJECTS	Information for all types of objects for the user.
USER_RESOURCE_LIMITS	Any limits, such as logons , that were given to the user's profile.
USER_SEQUENCES	All the sequences that the user owns. Use ALL_SEQUENCES to see all the sequences that the user can access.
USER_SYNONYMS	Synonyms that the user owns. Use ALL_SYNONYMS to see the entire list of synonyms that the user can access.
USER_TAB_COLS	All the columns for all the tables that the user owns. Use ALL_TAB_COLS to see all the tables and columns that the user can access.
USER_TABS	26 columns of information on tables that the user owns. Use the ALL_TABS view to see all the tables that the user can access.
USER_VIEWS	Views that the user owns. Use ALL_VIEWS to see the views that the user can access.

7 Limits

This one is also not simple. You have to look at various views to get all the limits that have been set for the user. Some of the more common data dictionary views to check the limits are SESSION_PRIVS and SESSION_ROLES. These views describe all the privileges and roles that are available to the current session.

8 Tablespaces

Finding the tablespaces is easy. Just use the USER_TABLESPACES view:

USER_TABLESPACES View
Name	Type	Null?
Tablespace_Name	VARCHAR2(30)	NOT NULL
Initial_Extent	NUMBER
Next_Extent	NUMBER
Min_Extents	NUMBER	NOT NULL
Max_Extents	NUMBER	NOT NULL
Pct_Increase	NUMBER	NOT NULL
Status	VARCHAR2(9)
Contents	VARCHAR2(9)

To find the quotas for a user's tablespaces, use the USER_TS_QUOTAS view:

USER_TS_QUOTAS View
Name	Type	Null?
Tablespace_Name	VARCHAR2(30)	NOT NULL
Bytes	NUMBER
Max_Bytes	NUMBER
Blocks	NUMBER	NOT NULL
Max_Blocks	NUMBER

Note

Don't forget: Most of these views can be run as USER, ALL, or DBA views.

9 Auditing

Remember the short paragraph on auditing in Chapter 8? Well, as you have probably guessed, there is a plain, standard audit trail that anyone can look at, and three very complete views are available. Each of these views can be run as USER or DBA views.

To follow the audit trail, you must have the auditing feature turned on for your database. Normally auditing is done for three types of things, through three different views:

Objects (through the USER_AUDIT_OBJECT view)
Session information (through USER_AUDIT_SESSION)
Certain commands that the user might use, such as audit, no audit, grant, revoke, and others (through USER_AUDIT_STATEMENT)

Anyone can look at the USER_AUDIT_TRAIL view. This view has 30 columns that include information about all three types of auditing. Confused? I suggest that you consult the Oracle documentation when you need to use the auditing feature.

10 V$ Tables

The last stop on the Data Dictionary Express is to give you some information on the tables that are used to show how the system is performing and to provide a sundry list of statistics. As I mentioned earlier, V$ views are normally used by DBAs. Here are the most common V$ views:

V$SESSION shows all current sessions.
V$SYSSTAT gives statistics about the entire database.
V$BGPROCESS shows any background processes that are active.
V$DATAFILE shows data on the files in the database.

You get the idea. Along with information on users, tables, rights, roles, and so forth, Oracle keeps real-time information on system performance. Again I encourage you to search the Oracle documentation for more information. This short discussion on the V$ tables is meant simply to make you aware that they exist.

There are many more tables in the data dictionary. Some, such as USER_SOURCE, list any functions, packages, or procedures that you may create. Others show details on space usage, extents, and so forth. Don't be afraid to take a look at the "DICT" view and see what's out there.

My goal in covering the basics of the data dictionary was to make you aware of this very powerful reference tool. In the Q&A section of this chapter I tried to start you on your way to your own data dictionary "cheat sheet" by showing you some of the most common commands. You can use the Q&A section as a starting point and add to it as you need to.

You will find that your use of the data dictionary will change depending on what you're doing ”working with user accounts, creating roles, programming, and so forth. Just remember that many of the commands come with a USER_ , ALL_ , or DBA_ prefix, and you'll be in good shape. If you try one that doesn't seem to work, then run a DESCRIBE operation on it, and if that doesn't work, look for it in the data dictionary by using the following command:

 SQL>SELECT * FROM DICT WHERE TABLE_NAME LIKE '%  what you're looking for%  ';

Good hunting!

One Last Thought

Now that you have worked a bit with the data dictionary, don't you think it makes sense to develop naming standards ? For example, in our waiver database, our tables have plain English names ”AUTHORS, QUESTIONS, and so forth. However, when you start developing systems with hundreds or thousands of tables, you will quickly run out of simple names. Hence I suggest that you develop a seven-character naming schema that will fit all the applications you develop. The first letter would stand for the general area ”F for finance, E for education, P for payroll, and so forth. Then the remaining characters would be used to refine the name.

What does this gain for you? You immediately have a shortcut for the data dictionary. If you wanted to find all the tables associated with education, you could run your data dictionary query to find all tables that begin with E .

Here's an example of a fairly standard naming convention:

Position	Explanation
1	The system that owns the object (form, report, table)
2	The module within the system that owns the object
3	The type of object ”form, query, report, view
4 “7	Unique identifiers for the object

Here's how it would work. Let's take finance as an example because that's an easy area to understand. The database object FAQBUDG translates into:

F	=	Finance, the system that owns this object
A	=	Accounts Payable, the module within Finance that owns this object
Q	=	Query
BUDG	=	Identifies this object as a budget query

If you wanted all the objects that had to do with the Accounts Payable area, you could query the data dictionary by looking for tables beginning with FA% .

See how easy it becomes to navigate through the data dictionary when you have a solid naming convention? It will take time to develop, but it is well worth it in the long run.

OK, One More

I don't want to leave you with the idea that the data dictionary contains magic wands that will easily answer all your questions. Actually, quite the opposite . In many cases you will have to work to get all the answers you need. As I have discussed, it is somewhat difficult to get all the information you need for something as common as roles. And one real shortcoming is that Oracle has no way to provide you with your original statements creating the roles.

So let me leave you with a question: What is Oracle Designer, and what does repository mean? (Hint: Oracle Designer, one of the Oracle development tools, is a general-purpose modeling tool used to design and document databases. Although there is work to be done to maintain the information, the payback is having complete and accurate reference materials ”more complete and organized than in the data dictionary. When you decide to create a database or are assigned to a project that must create a database, I strongly advise that you use either Oracle Designer or another such tool.)