Using Views to Retrieve Useful Information from the
• The definition of the data dictionary
• The type of information the data dictionary contains
• Different types of tables within the data dictionary
• Effective ways to retrieve useful information from the data dictionary
Introduction to the Data Dictionary
Every relational database has some form of data dictionary, or system catalog. (We use both terms in today's presentation.) A data dictionary is a system area within a database environment that contains information about the ingredients of a database. Data dictionaries include information such as database design, stored SQL code, user statistics, database processes, database growth, and database performance statistics.
The data dictionary has tables that contain database design information, which are populated upon the creation of the database and the execution of Data Definition Language (DDL) commands such as CREATE TABLE. This part of the system catalog stores information about a table's columns and attributes, table-sizing information, table privileges, and table growth. Other objects that are stored within the data dictionary include indexes, triggers, procedures, packages, and views.
User statistics tables report the status of items such as database connectivity information and privileges for individual users. These privileges are divided into two major components: system-level privileges and object-level privileges. The authority to create another user is a system-level privilege, whereas the capability to access a table is an object-level privilege. Roles are also used to enforce security within a database. This information is stored as well.
Data retrieved from the system catalog can be used to monitor database performance and to modify database parameters that will improve database and SQL statement performance.
The data dictionary is one of the most useful tools available with a database. It is a way of keeping a database organized, much like an inventory file in a retail store. It is a mechanism that ensures the integrity of the database. For instance, when you create a table, how does the database server know whether a table with the same name exists? When you create a query to select data from a table, how can it be verified that you have been given the proper privileges to access the table? The data dictionary is the heart of a database, so you need to know how to use it.
Users of the Data Dictionary
End users, system engineers, and database administrators all use the data dictionary, whether they realize it or not. Their access can be either direct or indirect.
End users, often the customers for whom the database was created, access the system catalog indirectly. When a user attempts to log on to the database, the data dictionary is referenced to verify that user's username, password, and privileges to connect to the database. The database is also referenced to see whether the user has the appropriate privileges to access certain data. The most common method for an end user to access the data dictionary is through a front-end application. Many graphical user interface (GUI) tools, which allow a user to easily construct an SQL statement, have been developed. When logging on to the database, the front-end application may immediately perform a select against the data dictionary to define the tables to which the user has access. The front-end application may then build a "local" system catalog for the individual user based on the data retrieved from the data dictionary. The customer can use the local catalog to select the specific tables he or she wishes to query.
System engineers are database users who are responsible for tasks such as database modeling and design, application development, and application management. (Some companies use other titles, such as programmers, programmer analysts, and data modelers, to refer to their system engineers.) System engineers use the data dictionary directly to manage the development process, as well as to maintain existing projects. Access may also be achieved through front-end applications, development tools, and computer assisted software engineering (CASE) tools. Common areas of the system catalog for these users are queries against objects under groups of schemas, queries against application roles and privileges, and queries to gather statistics on schema growth. System engineers may also use the data dictionary to reverse-engineer database objects in a specified schema.
Database administrators (DBAs) are most definitely the largest percentage of direct users of the data dictionary. Unlike the other two groups of users, who occasionally use the system catalog directly, DBAs must explicitly include the use of the data dictionary as part of their daily routine. Access is usually through an SQL query but can also be through administration tools such as Oracle's Server Manager. A DBA uses data dictionary information to manage users and resources and ultimately to achieve a well-tuned database.
As you can see, all database users need to use the data dictionary. Even more important, a relational database cannot exist without some form of a data dictionary.
Contents of the Data Dictionary
This section examines the system catalogs of two RDBMS vendors, Oracle and Sybase. Although both implementations have unique specifications for their data dictionaries, they serve the same function. Don't concern yourself with the different names for the system tables; simply understand the concept of a data dictionary and the data it contains.
Oracle's Data Dictionary
Because every table must have an owner, the owner of the system tables in an Oracle data dictionary is SYS. Oracle's data dictionary tables are divided into three basic categories: user accessible views, DBA views, and dynamic performance tables, which also appear as views. Views that are accessible to a user allow the user to query the data dictionary for information about the individual database account, such as privileges, or a catalog of tables created. The DBA views aid in the everyday duties of a database administrator, allowing the DBA to manage users and objects within the database. The dynamic performance tables in Oracle are also used by the DBA and provide a more in-depth look for monitoring performance of a database. These views provide information such as statistics on processes, the dynamic usage of rollback segments, memory usage, and so on. The dynamic performance tables are all prefixed V$.
A Look Inside Oracle's Data Dictionary
The examples in this section show you how to retrieve information from the data dictionary and are applicable to most relational database users, that is, system engineer, end user, or DBA. Oracle's data dictionary has a vast array of system tables and views for all types of database users, which is why we have chosen to explore Oracle's data dictionary in more depth.
User views are data dictionary views that are common to all database users. The only privilege a user needs to query against a user view is the CREATE SESSION system privilege, which should be common to all users.
Who Are You?
Before venturing into the seemingly endless knowledge contained within a database, you should know exactly who you are (in terms of the database) and what you can do. The following two examples show SELECT statements from two tables: one to find out who you are and the other to see who else shares the database.
SQL> SELECT * FROM USER-USERS;
The USER-USERS view allows you to view how your Oracle ID was set up, when it was set up, and it also shows other user-specific, vital statistics. The default tablespace and the temporary tablespace are also shown. The default tablespace, USERS, is the tablespace that objects will be created under as that user. The temporary tablespace is the designated tablespace to be used during large sorts and group functions for JSMITH.
What Are Your Privileges?
Now that you know who you are, it would be nice to know what you can do. Several views are collectively able to give you that information. The USER-SYS-PRIVS view and the USER-ROLE-PRIVS view will give you (the user) a good idea of what authority you have.
You can use the USER-SYS-PRIVS view to examine your system privileges. Remember, system privileges are privileges that allow you to do certain things within the database as a whole. These privileges are not specific to any one object or set of objects.
SQL> SELECT * FROM USER-SYS-PRIVS;
JSMITH has been granted two system-level privileges, outside of any granted roles. Notice the second, CREATE SESSION. CREATE SESSION is also contained within an Oracle standard role, CONNECT.
You can use the USER-ROLE-PRIVS view to view information about roles you have been granted within the database. Database roles are very similar to system-level privileges. A role is created much like a user and then granted privileges. After the role has been granted privileges, the role can be granted to a user. Remember that object-level privileges may also be contained within a role.
SQL> SELECT * FROM USER-ROLE-PRIVS;
The USER-ROLE-PRIVS view enables you to see the roles that have been granted to you. As mentioned earlier, CONNECT contains the system privilege CREATE SESSION, as well as other privileges. RESOURCE has a few privileges of its own. You can see that both roles have been granted as the user's default role; the user cannot grant these roles to other users, as noted by the Admin option (ADM); and the roles have not been granted by the operating system.
What Do You Have Access To?
Now you might ask, What do I have access to? I know who I am, I know my privileges, but where can I get my data? You can answer that question by looking at various available user views in the data dictionary. This section identifies a few helpful views.
Probably the most basic user view is USER-CATALOG, which is simply a catalog of the tables, views, synonyms, and sequences owned by the current user.
SQL> SELECT * FROM USER-CATALOG;
This example provides a quick list of tables and related objects that you own. You can also use a public synonym for USER-CATALOG for simplicity's sake: CAT. That is, try select * from cat;.
Another useful view is ALL-CATALOG, which enables you to see tables owned by other individuals.
SQL> SELECT * FROM ALL-CATALOG;
More objects than appear in the preceding list will be accessible to you as a user. (The SYSTEM tables alone will add many tables.) We have simply shortened the list. The ALL-CATALOG view is the same as the USER-CATALOG view, but it shows you all tables, views, sequences, and synonyms to which you have access (not just the ones you own).
SQL> SELECT TABLE-NAME, INITIAL-EXTENT, NEXT-EXTENT FROM USER-TABLES;
Much more data is available when selecting from the USER-TABLES view, depending upon what you want to see. Most data consists of storage information.
NOTE:Notice in the output that the values for initial and next extent are in bytes. In some implementations you can use column formatting to make your output more readable by adding commas.
The ALL-TABLES view is to USER-TABLES as the ALL-CATALOG view is to USER-CATALOG. In other words, ALL-TABLES allows you to see all the tables to which you have access, instead of just the tables you own. The ALL-TABLES view may include tables that exist in another user's catalog.
SQL> SELECT SUBSTR(OWNER,1,15) OWNER, SUBSTR(TABLE-NAME,1,25) TABLE-NAME,
SUBSTR(TABLESPACE-NAME,1,13) TABLESPACE FROM ALL-TABLES;
Again, you have selected only the desired information. Many additional columns in ALL-TABLES may also contain useful information.
As a database user, you can monitor the growth of tables and indexes in your catalog by querying the USER-SEGMENTS view. As the name suggests, USER-SEGMENTS gives you information about each segment, such as storage information and extents taken. A segment may consist of a table, index, cluster rollback, temporary, or cache. The following example shows how you might retrieve selected information from the USER-SEGMENTS view.
NOTE: The SUBSTR function appears in many of the preceding queries of data dictionary views. You can use many of the functions that you learned about earlier to improve the readablility of the data you retrieve. The use of consistent naming standards in your database may allow you to limit the size of data in your output.
System DBA Views
The DBA views that reside within an Oracle data dictionary are usually the primary, or most common, views that a DBA would access. These views are invaluable to the productivity of any DBA. Taking these tables away from a DBA would be like depriving a carpenter of a hammer.
As you may expect, you must have the SELECT-ANY-TABLE system privilege, which is contained in the DBA role, to access the DBA tables. For example, suppose you are JSMITH, who does not have the required privilege to select from the DBA tables.
Database User Information
The USER-USERS and ALL-USERS views give you minimum information about the users. The DBA view called DBA-USERS (owned by SYS) gives you the information on all users if you have the DBA role or SELECT-ANY-TABLE privilege, as shown in the next example.
SQL> SELECT * FROM SYS.DBA-USERS;
When you select all from the DBA-USERS view, you are able to see the vital information on each user. Notice that the password is encrypted. DBA-USERS is the primary view used by a DBA to manage users.
Three basic data dictionary views deal with security, although these views can be tied to-gether with other related views for more complete information. These three views deal with database roles, roles granted to users, and system privileges granted to users. The three views introduced in this section are DBA-ROLES, DBA-ROLE-PRIVS, and DBA-SYS-PRIVS. The following sample queries show how to obtain information pertinent to database security.
SQL> SELECT * FROM SYS.DBA-ROLES;
The view DBA-ROLES lists all the roles that have been created within the database. It gives the role name and whether or not the role has a password.
SQL> SELECT * FROM SYS.DBA-ROLE-PRIVS WHERE GRANTEE = 'RJENNINGS';
The DBA-ROLE-PRIVS view provides information about database roles that have been granted to users. The first column is the grantee, or user. The second column displays the granted role. Notice that every role granted to the user corresponds to a record in the table. ADM identifies whether the role was granted with the Admin option, meaning that the user is able to grant the matching role to other users. The last column is DEFAULT, stating whether the matching role is a default role for the user.
Database objects are another major focus for a DBA. Several views within the data dictionary provide information about objects, such as tables and indexes. These views can contain general information or they can contain detailed information about the objects that reside within the database.
SQL> SELECT * FROM SYS.DBA-CATALOG WHERE ROWNUM <5;
The DBA-CATALOG is the same thing as the USER-CATALOG, only the owner of the table is included. In contrast, the USER-CATALOG view deals solely with tables that belonged to the current user. DBA-CATALOG is a view that the DBA can use to take a quick look at all tables.
The following query shows you what type of objects exist in a particular database.
TIP:You can use ROWNUM to narrow down the results of your query to a specified number of rows for testing purposes. Oracle calls ROWNUM a pseudocolumn. ROWNUM, like ROWID, can be used on any database table or view.
SQL> SELECT DISTINCT(OBJECT-TYPE) FROM SYS.DBA-OBJECTS;
The DISTINCT function in the preceding query lists all unique object types that exist in the database. This query is a good way to find out what types of objects the database designers and developers are using.
The DBA-TABLES view gives specific information about database tables, mostly concerning storage.
This section looks at two views that aid in the measurement of database growth: DBA-SEGMENTS and DBA-EXTENTS. DBA-SEGMENTS provides information about each segment, or object in the database such as storage allocation, space used, and extents. Each time a table or index grows and must grab more space as identified by the NEXT-EXTENT, the table takes another extent. A table usually becomes fragmented when it grows this way. DBA-EXTENTS provides information about each extent of a segment.
SQL> SELECT SUBSTR(SEGMENT-NAME,1,30) SEGMENT-NAME,
SUBSTR(SEGMENT-TYPE,1,12) SEGMENT-TYPE, BYTES, EXTENTS,
FROM SYS.DBA-SEGMENTS WHERE OWNER = 'TWILLIAMS' AND ROWNUM < 5;
By looking at the output from DBA-SEGMENTS, you can easily identify which tables are experiencing the most growth by referring to the number of extents. Both HISTORY-TBL and HISTORY-INX have grown much more than the other two tables.
Next you can take a look at each extent of one of the tables. You can start with INVOICE-TBL.
Oracle allocates space to the database by using "data files." Space logically exists within a tablespace, but data files are the physical entities of tablespaces. In other implementations, data is also ultimately contained in data files, though these data files may be referenced by another name. The view called DBA-DATA-FILES enables you to see what is actually allocated to a tablespace.
SQL> SELECT SUBSTR(TABLESPACE-NAME,1,25) TABLESPACE-NAME,
SUBSTR(FILE-NAME,1,40) FILE-NAME, BYTES FROM SYS.DBA-DATA-FILES;
You are now able to see how much space has been allocated for each tablespace that exists in the database. Notice the names of the data files correspond to the tablespace to which they belong.
As the following example shows, the DBA-FREE-SPACE view tells you how much free space is available in each tablespace.
SQL> SELECT TABLESPACE-NAME, SUM(BYTES) FROM SYS.DBA-FREE-SPACE
GROUP BY TABLESPACE-NAME;
The preceding example lists the total free space for each tablespace. You can also view each segment of free space by simply selecting bytes from DBA-FREE-SPACE instead of SUM(bytes).
As areas for rolling back transactions are a crucial part to database performance, you need to know what rollback segments are available. DBA-ROLLBACK-SEGS provides this information.
SQL> SELECT OWNER, SEGMENT-NAME FROM SYS.DBA-ROLLBACK-SEGS;
This example performs a simple select to list all rollback segments by name. Much more data is available for your evaluation as well.
Dynamic Performance Views
Oracle DBAs frequently access dynamic performance views because they provide greater detail about the internal performance measures than many of the other data dictionary views. (The DBA views contain some of the same information.)
These views involve extensive details, which is implementation-specific. This section simply provides an overview of the type of information a given data dictionary contains.
A DESCRIBE command of the V$SESSION views follows. You can see the detail that is contained in the view.
SQL> DESCRIBE V$SESSION
To get information about current database sessions, you could write a SELECT statement similar to the one that follows from V$SESSION.
SQL> SELECT USERNAME, COMMAND, STATUS FROM V$SESSION
WHERE USERNAME IS NOT NULL;
TWILLIAMS is logged on to the database and performing a select from the database, which is represented by command 3.
JSMITH is merely logged on to the database. His session is inactive, and he is not performing any type of commands. Refer to your database documentation to find out how the commands are identified in the data dictionary. Commands include SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, and DROP TABLE.
Data concerning performance statistics outside the realm of user sessions is also available in the data dictionary. This type of data is much more implementation specific than the other views discussed today.
Performance statistics include data such as read/write rates, successful hits on tables, use of the system global area, use of memory cache, detailed rollback segment information, detailed transaction log information, and table locks and waits. The well of knowledge is almost bottomless.
The Plan Table
The Plan table is the default table used with Oracle's SQL statement tool, EXPLAIN PLAN. This table is created by an Oracle script called UTLXPLAN.SQL, which is copied on to the server when the software is installed. Data is generated by the EXPLAIN PLAN tool, which populates the PLAN table with information about the object being accessed and the steps in the execution plan of an SQL statement.
Although the details of the data dictionary vary from one implementation to another, the content remains conceptually the same in all relational databases. You must follow the syntax and rules of your database management system, but today's examples should give you the confidence to query your data dictionary and to be creative when doing so.
NOTE: Exploring the data dictionary is an adventure, and you will need to explore in order to learn to use it effectively.