Teach Yourself SQL in 21 Days, Second Edition

Previous chapterNext chapterContents


- Day 16 -
Using Views to Retrieve Useful Information from the Data Dictionary

Objectives

Today we discuss the data dictionary, also known as the system catalog. By the end of the day, you should have a solid understanding of the following:

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.

Day 16 extends what you learned yesterday (Day 15, "Streamlining SQL Statements for Improved Performance"). 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$.

Sybase's Data Dictionary

As in Oracle, the owner of the tables in a Sybase data dictionary is SYS. The tables within the data dictionary are divided into two categories: system tables and database tables.

The system tables are contained with the master database only. These tables define objects (such as tables and indexes) that are common through multiple databases. The second set of tables in a Sybase SQL Server data dictionary are the database tables. These tables are related only to objects within each database.

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

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.

INPUT:
SQL> SELECT *
  2  FROM USER_USERS;
OUTPUT:
USERNAME     USER_ID   DEFAULT_TABLESPACE      TEMPORARY TABLESPACE  CREATED
----------   ------    --------------------    --------------------  --------
JSMITH           29    USERS                   TEMP                  14-MAR-97

1 row selected.
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT *
  2  FROM ALL_USERS;

USERNAME        USER_ID         CREATED
--------------  -------    ------------
SYS                   0       01-JAN-97
SYSTEM                5       01-JAN-97
SCOTT                 8       01-JAN-97
JSMITH               10       14-MAR-97
TJONES               11       15-MAR-97
VJOHNSON             12       15-MAR-97

As you can see in the results of the preceding query, you can view all users that exist in the database by using the ALL_USERS view. However, the ALL_USERS view does not provide the same specific information as the previous view (USER_USERS) provided because there is no need for this information at the user level. More specific information may be required at the system level.

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.

INPUT:
SQL> SELECT *
  2  FROM USER_SYS_PRIVS;
OUTPUT:
USERNAME       PRIVILEGE               ADM
--------       --------------------    ---
JSMITH         UNLIMITED TABLESPACE     NO
JSMITH         CREATE SESSION           NO

2 rows selected.
ANALYSIS:

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, which is covered in the next example.

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.

INPUT/OUTPUT:
SQL> SELECT *
  2  FROM USER_ROLE_PRIVS;

USERNAME         GRANTED_ROLE         ADM   DEF    OS_
------------     ----------------     ---   ---    --
JSMITH           CONNECT              NO    YES    NO
JSMITH           RESOURCE             NO    YES    NO

2 rows selected.
ANALYSIS:

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. (Refer to Day 12, "Database Security.")

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.

INPUT:
SQL> SELECT *
  2  FROM USER_CATALOG;
OUTPUT:
TABLE_NAME                      TABLE_TYPE
----------------------------    ----------
MAGAZINE_TBL                    TABLE
MAG_COUNTER                     SEQUENCE
MAG_VIEW                        VIEW
SPORTS                          TABLE

4 rows selected.
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT *
  2  FROM ALL_CATALOG;
OWNER                    TABLE_NAME          TABLE_TYPE
--------------------     ------------------  ----------
SYS                      DUAL                TABLE
PUBLIC                   DUAL                SYNONYM
JSMITH                   MAGAZINE_TBL        TABLE 
JSMITH                   MAG_COUNTER         SEQUENCE
JSMITH                   MAG_VIEW            VIEW
JSMITH                   SPORTS              TABLE
VJOHNSON                 TEST1               TABLE
VJOHNSON                 HOBBIES             TABLE
VJOHNSON                 CLASSES             TABLE
VJOHNSON                 STUDENTS            VIEW

10 rows selected.
ANALYSIS:

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).

INPUT:
SQL> SELECT SUBSTR(OBJECT_TYPE,1,15) OBJECT_TYPE,
  2         SUBSTR(OBJECT_NAME,1,30) OBJECT_NAME,
  3         CREATED,
  4         STATUS
  5  FROM USER_OBJECTS
  6  ORDER BY 1;
OUTPUT:
OBJECT_TYPE      OBJECT_NAME            CREATED      STATUS
--------------   --------------------   ------------ ------
INDEX            MAGAZINE_INX           14-MAR-97    VALID
INDEX            SPORTS_INX             14-MAR-97    VALID
INDEX            HOBBY_INX              14-MAR-97    VALID
TABLE            MAGAZINE_TBL           01-MAR-97    VALID
TABLE            SPORTS                 14-MAR-97    VALID
TABLE            HOBBY_TBL              16-MAR-97    VALID

6 rows selected.
ANALYSIS:

You can use the USER_OBJECTS view to select general information about a user's owned objects, such as the name, type, date created, date modified, and the status of the object. In the previous query, we are checking the data created and validation of each owned object.

INPUT/OUTPUT:
SQL> SELECT TABLE_NAME, INITIAL_EXTENT, NEXT_EXTENT
  2  FROM USER_TABLES;

TABLE_NAME                       INITIAL_EXTENT     NEXT EXTENT
----------------------------     --------------     -----------
MAGAZINE_TBL                            1048576          540672
SPORTS                                   114688          114688
ANALYSIS:

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. See Day 19, "Transact-SQL: An Introduction," and Day 20, "SQL*Plus."

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.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(OWNER,1,15) OWNER,
  2         SUBSTR(TABLE_NAME,1,25) TABLE_NAME,
  3         SUBSTR(TABLESPACE_NAME,1,13) TABLESPACE
  4  FROM ALL_TABLES;

OWNER                    TABLE_NAME                      TABLESPACE
--------------------     ----------------------------    ----------
SYS                      DUAL                            SYSTEM
JSMITH                   MAGAZINE_TBL                    USERS
SMITH                    SPORTS                          USERS
VJOHNSON                 TEST1                           USERS
VJOHNSON                 HOBBIES                         USERS
VJOHNSON                 CLASSES                         USERS
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  2         SUBSTR(SEGMENT_TYPE,1,8) SEG_TYPE,
  3         SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,
  4         BYTES, EXTENTS
  5  FROM USER_SEGMENTS
  6  ORDER BY EXTENTS DESC;

SEGMENT_NAME          SEG_TYPE        TABLESPACE_NAME         BYTES           EXTENTS
--------------------  ------------    --------------------    ------------    -------
MAGAZINE_TBL          TABLE           USERS                        4292608          7
SPORTS_INX            INDEX           USERS                         573440          4
SPORTS                TABLE           USERS                         344064          2
MAGAZINE_INX          INDEX           USERS                        1589248          1
4 rows selected.
ANALYSIS:

The output in the preceding query was sorted by extents in descending order; the segments with the most growth (extents taken) appear first in the results.

Now that you know which tables you have access to, you will want to find out what you can do to each table. Are you limited to query only, or can you update a table? The ALL_TAB_PRIVS view lists all privileges that you have as a database user on each table available to you.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(TABLE_SCHEMA,1,10) OWNER,
  2         SUBSTR(TABLE_NAME,1,25) TABLE_NAME,
  3         PRIVILEGE
  4  FROM ALL_TAB_PRIVS;

OWNER         TABLE_NAME            PRIVILEGE
------------  --------------------  ---------
SYS           DUAL                  SELECT
JSMITH        MAGAZINE_TBL          SELECT
JSMITH        MAGAZINE_TBL          INSERT
JSMITH        MAGAZINE_TBL          UPDATE
JSMITH        MAGAZINE_TBL          DELETE
JSMITH        SPORTS                SELECT
JSMITH        SPORTS                INSERT
JSMITH        SPORTS                UPDATE
JSMITH        SPORTS                DELETE
VJOHNSON      TEST1                 SELECT
VJOHNSON      TEST1                 INSERT
VJOHNSON      TEST1                 UPDATE
VJOHNSON      TEST1                 DELETE
VJOHNSON      HOBBIES               SELECT
VJOHNSON      CLASSES               SELECT
ANALYSIS:

As you can see, you can manipulate the data in some tables, whereas you have read-only access (SELECT only) to others.

When you create objects, you usually need to know where to place them in the database unless you allow your target destination to take the default. An Oracle database is broken up into tablespaces, each of which are capable of storing objects. Each tablespace is allocated a certain amount of disk space, according to what is available on the system. Disk space is usually acquired through the system administrator (SA).

The following query is from a view called USER_TABLESPACES, which will list the tablespaces that you have access to, the default initial and next sizes of objects created within them, and their status.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         INITIAL_EXTENT,
  3         NEXT_EXTENT,
  4         PCT_INCREASE,
  5         STATUS
  6  FROM USER_TABLESPACES;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE STATUS
------------------------------ -------------- ----------- ------------ ------
SYSTEM                                  32768       16384           1  ONLINE
RBS                                   2097152     2097152           1  ONLINE
TEMP                                   114688      114688           1  ONLINE
TOOLS                                   32768       16384           1  ONLINE
USERS                                   32768       16384           1  ONLINE
5 rows selected.
ANALYSIS:

This type of query is very useful when you are creating objects, such as tables and indexes, which will require storage. When a table or index is created, if the initial and next storage parameters are not specified in the DDL, the table or index will take the tablespace's default values. The same concept applies to PCT INCREASE, which is an Oracle parameter specifying the percentage of allocated space an object should take when it grows. If a value for PCT INCREASE is not specified when the table or index is created, the database server will allocate the default value that is specified for the corresponding tablespace. Seeing the default values enables you to determine whether you need to use a storage clause in the CREATE statement.

Sometimes, however, you need to know more than which tablespaces you may access, that is, build tables under. For example, you might need to know what your limits are within the tablespaces so that you can better manage the creation and sizing of your objects. The USER_TS_QUOTAS view provides the necessary information. The next query displays a user's space limits for creating objects in the database.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME,
  2         BYTES, MAX_BYTES
  3  FROM USER_TS_QUOTAS;

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------ ---------- ----------
SYSTEM                                  0          0
TOOLS                             5242880      16384
USERS                              573440         -1

3 rows selected.
ANALYSIS:

The preceding output is typical of output from an Oracle data dictionary. BYTES identifies the total number of bytes in that tablespace that are associated with the user. MAX BYTES identifies the maximum bytes allotted to the user, or the user's quota, on the tablespace. The first two values in this column are self-explanatory. The -1 in the third row means quota unlimited--that is, no limits are placed on the user for that tablespace.


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, as we have done in these examples.

These examples all show how an ordinary database user can extract information from the data dictionary. These views are just a few of the many that exist in Oracle's data dictionary. It is important to check your database implementation to see what is available to you in your data dictionary. Remember, you should use the data dictionary to manage your database activities. Though system catalogs differ by implementation, you need only to understand the concept and know how to retrieve data that is necessary to supplement your job.

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.

INPUT:
SQL> SELECT *
  2  FROM USER_ROLE_PRIVS;
OUTPUT:
USERNAME               GRANTED_ROLE            ADM    DEF    OS_
------------------     --------------------    ---    ---    --
JSMITH                 CONNECT                 NO     YES    NO
JSMITH                 RESOURCE                NO     YES    NO
INPUT/OUTPUT:
SQL> SELECT *
  2  FROM SYS.DBA_ROLES;
FROM SYS.DBA_ROLES;
     *

ERROR at line 2:
ORA-00942: table or view does not exist
ANALYSIS:

When you try to access a table to which you do not have the appropriate privileges, an error is returned stating that the table does not exist. This message can be a little misleading. Virtually, the table does not exist because the user cannot "see" the table. A solution to the problem above would be to grant the role DBA to JSMITH. This role would have to be granted by a DBA, of course.

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.

INPUT:
SQL> SELECT *
  2  FROM SYS.DBA_USERS;
OUTPUT:
USERNAME                          USER_ID PASSWORD
--------------------------------  ------ -----------------------------
DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED
------------------------------ ------------------------------ --------
PROFILE
------------------------------
SYS                                     0 4012DA490794C16B
SYSTEM                         TEMP                           06-JUN-96
DEFAULT

JSMITH                                  5 A4A94B17405C10B7
USERS                          TEMP                           06-JUN-96
DEFAULT

2 rows selected.
ANALYSIS:

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.

Database Security

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.

INPUT:
SQL> SELECT *
  2  FROM SYS.DBA_ROLES;
OUTPUT:
ROLE                           PASSWORD
------------------------------ --------
CONNECT                        NO
RESOURCE                       NO
DBA                            NO
EXP_FULL_DATABASE              NO
IMP_FULL_DATABASE              NO
END_USER_ROLE                  NO

6 rows selected.
ANALYSIS:

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.

INPUT:
SQL> SELECT *
  2  FROM SYS.DBA_ROLE_PRIVS
  3  WHERE GRANTEE = 'RJENNINGS';

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
RJENNINGS                      CONNECT                        NO  YES
RJENNINGS                      DBA                            NO  YES
RJENNINGS                      RESOURCE                       NO  YES

3 rows selected.
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT *
  2  FROM SYS.DBA_SYS_PRIVS
  3  WHERE GRANTEE = 'RJENNINGS';

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RJENNINGS                      CREATE SESSION                           NO
RJENNINGS                      UNLIMITED TABLESPACE                     NO

2 rows selected.
ANALYSIS:

The DBA_SYS_PRIVS view lists all system-level privileges that have been granted to the user. This view is similar to DBA_ROLE_PRIVS. You can include these system privileges in a role by granting system privileges to a role, as you would to a user.

Database Objects

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.

INPUT:
SQL> SELECT *
  2  FROM SYS.DBA_CATALOG
  3  WHERE ROWNUM < 5;
OUTPUT:
OWNER                          TABLE_NAME                     TABLE_TYPE
------------------------------ ------------------------------ ----------
SYS                            CDEF$                          TABLE
SYS                            TAB$                           TABLE
SYS                            IND$                           TABLE
SYS                            CLU$                           TABLE

4 rows selected.
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT DISTINCT(OBJECT_TYPE)
  2  FROM SYS.DBA_OBJECTS;

OBJECT_TYPE
------------
CLUSTER
DATABASE LINK
FUNCTION
INDEX
PACKAGE
PACKAGE BODY
PROCEDURE
SEQUENCE
SYNONYM
TABLE
TRIGGER
VIEW

12 rows selected.
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(OWNER,1,8) OWNER,
  2         SUBSTR(TABLE_NAME,1,25) TABLE_NAME,
  3         SUBSTR(TABLESPACE_NAME,1,30) TABLESPACE_NAME
  4  FROM SYS.DBA_TABLES
  5  WHERE OWNER = 'JSMITH';

OWNER    TABLE_NAME                TABLESPACE_NAME
-------- ------------------------  --------------------
JSMITH   MAGAZINE_TBL              USERS
JSMITH   HOBBY_TBL                 USERS
JSMITH   ADDRESS_TBL               SYSTEM
JSMITH   CUSTOMER_TBL              USERS

4 rows selected.
ANALYSIS:

All tables are in the USERS tablespace except for ADDRESS_TBL, which is in the SYSTEM tablespace. Because the only table you should ever store in the SYSTEM tablespace is the SYSTEM table, the DBA needs to be aware of this situation. It's a good thing you ran this query!

JSMITH should immediately be asked to move his table into another eligible tablespace.

The DBA_SYNONYMS view provides a list of all synonyms that exist in the database. DBA_SYNONYMS gives a list of synonyms for all database users, unlike USER_SYNONYMS, which lists only the current user's private synonyms.

INPUT/OUTPUT:
SQL> SELECT SYNONYM_NAME,
  2         SUBSTR(TABLE_OWNER,1,10) TAB_OWNER,
  3         SUBSTR(TABLE_NAME,1,30) TABLE_NAME
  4  FROM SYS.DBA_SYNONYMS
  5  WHERE OWNER = 'JSMITH';

SYNONYM_NAME                   TAB_OWNER  TABLE_NAME
------------------------------ ---------- ----------
TRIVIA_SYN                     VJOHNSON   TRIVIA_TBL

1 row selected.
ANALYSIS:

The preceding output shows that JSMITH has a synonym called TRIVIA_SYN on a table called TRIVIA_TBL that is owned by VJOHNSON.

Now suppose that you want to get a list of all tables and their indexes that belong to JSMITH. You would write a query similar to the following, using DBA_INDEXES.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(TABLE_OWNER,1,10) TBL_OWNER,
  2         SUBSTR(TABLE_NAME,1,30) TABLE_NAME,
  3         SUBSTR(INDEX_NAME,1,30) INDEX_NAME
  4  FROM SYS.DBA_INDEXES
  5  WHERE OWNER = 'JSMITH'
  6    AND ROWNUM < 5
  7  ORDER BY TABLE_NAME;

TBL_OWNER  TABLE_NAME                     INDEX_NAME
---------- ------------------------------ ------------
JSMITH     ADDRESS_TBL                    ADDR_INX
JSMITH     CUSTOMER_TBL                   CUST_INX
JSMITH     HOBBY_TBL                      HOBBY_PK
JSMITH     MAGAZINE_TBL                   MAGAZINE_INX

4 rows selected.
ANALYSIS:

A query such as the previous one is an easy method of listing all indexes that belong to a schema and matching them up with their corresponding table.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(TABLE_NAME,1,15) TABLE_NAME,
  2         SUBSTR(INDEX_NAME,1,30) INDEX_NAME,
  3         SUBSTR(COLUMN_NAME,1,15) COLUMN_NAME,
  4         COLUMN_POSITION
  5  FROM SYS.DBA_IND_COLUMNS
  6  WHERE TABLE_OWNER = 'JSMITH'
  7    AND ROWNUM < 10
  8  ORDER BY 1,2,3;

TABLE_NAME      INDEX_NAME                     COLUMN_NAME     COLUMN_POSITION
--------------  ------------------------------ --------------  ---------------
ADDRESS_TBL     ADDR_INX                       PERS_ID                       1
ADDRESS_TBL     ADDR_INX                       NAME                          2
ADDRESS_TBL     ADDR_INX                       CITY                          3
CUSTOMER_TBL    CUST_INX                       CUST_ID                       1
CUSTOMER_TBL    CUST_INX                       CUST_NAME                     2
CUSTOMER_TBL    CUST_INX                       CUST_ZIP                      3
HOBBY_TBL       HOBBY_PK                       SAKEY                         1
MAGAZINE_TBL    MAGAZINE_INX                   ISSUE_NUM                     1
MAGAZINE_TBL    MAGAZINE_INX                   EDITOR                        2

9 rows selected.
ANALYSIS:

Now you have selected each column that is indexed in each table and ordered the results by the order the column appears in the index. You have learned about tables, but what holds tables? Tablespaces are on a higher level than objects such as tables, indexes, and so on. Tablespaces are Oracle's mechanism for allocating space to the database. To allocate space, you must know what tablespaces are currently available. You can perform a select from DBA_TABLESPACES to see a list of all tablespaces and their status, as shown in the next example.

INPUT/OUTPUT:
SQL> SELECT TABLESPACE_NAME, STATUS
  2  FROM SYS.DBA_TABLESPACES

TABLESPACE_NAME                STATUS
------------------------------ ------
SYSTEM                         ONLINE
RBS                            ONLINE
TEMP                           ONLINE
TOOLS                          ONLINE
USERS                          ONLINE
DATA_TS                        ONLINE
INDEX_TS                       ONLINE

7 rows selected.
ANALYSIS:

The preceding output tells you that all tablespaces are online, which means that they are available for use. If a tablespace is offline, then the database objects within it (that is, the tables) are not accessible.

What is JSMITH's quota on all tablespaces to which he has access? In other words, how much room is available for JSMITH's database objects?

INPUT/OUTPUT:
SQL> SELECT TABLESPACE_NAME,
  2         BYTES,
  3         MAX_BYTES
  4  FROM SYS.DBA_TS_QUOTAS
  5  WHERE USERNAME = 'JSMITH'

TABLESPACE_NAME                     BYTES  MAX_BYTES
------------------------------  ---------- ----------
DATA_TS                         134111232         -1
INDEX_TS                        474390528         -1

2 rows selected.
ANALYSIS:

JSMITH has an unlimited quota on both tablespaces to which he has access. In this case the total number of bytes available in the tablespace is available on a first-come first-served basis. For instance, if JSMITH uses all the free space in DATA_TS, then no one else can create objects here.

Database Growth

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.

INPUT:
SQL> SELECT SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  2         SUBSTR(SEGMENT_TYPE,1,12) SEGMENT_TYPE,
  3         BYTES,
  4         EXTENTS,
  5  FROM SYS.DBA_SEGMENTS
  6  WHERE OWNER = 'TWILLIAMS'
  7    AND ROWNUM < 5;
OUTPUT:
SEGMENT_NAME                   SEGMENT_TYPE      BYTES    EXTENTS
------------------------------ ------------ ---------- ----------
INVOICE_TBL                    TABLE            163840         10
COMPLAINT_TBL                  TABLE           4763783          3
HISTORY_TBL                    TABLE         547474996         27
HISTORY_INX                    INDEX         787244534         31

4 rows selected.
ANALYSIS:

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.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(OWNER,1,10) OWNER,
  2         SUBSTR(SEGMENT_NAME,1,30) SEGMENT_NAME,
  3         EXTENT_ID,
  4         BYTES
  5  FROM SYS.DBA_EXTENTS
  6  WHERE OWNER = 'TWILLIAMS'
  7    AND SEGMENT_NAME = 'INVOICE_TBL'
  8  ORDER BY EXTENT_ID;

OWNER      SEGMENT_NAME                    EXTENT_ID     BYTES
---------- ------------------------------ ---------- --------
TWILLIAMS  INVOICE_TBL                            0      16384
TWILLIAMS  INVOICE_TBL                            1      16384
TWILLIAMS  INVOICE_TBL                            2      16384
TWILLIAMS  INVOICE_TBL                            3      16384
TWILLIAMS  INVOICE_TBL                            4      16384
TWILLIAMS  INVOICE_TBL                            5      16384
TWILLIAMS  INVOICE_TBL                            6      16384
TWILLIAMS  INVOICE_TBL                            7      16384
TWILLIAMS  INVOICE_TBL                            8      16384
TWILLIAMS  INVOICE_TBL                            9      16384

10 rows selected.
ANALYSIS:

This example displays each extent of the table, the extent_id, and the size of the extent in bytes. Each extent is only 16K, and because there are 10 extents, you might want to rebuild the table and increase the size of the initial_extent to optimize space usage. Rebuilding the table will allow all the table's data to fit into a single extent, and therefore, not be fragmented.

Space Allocated

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.

INPUT/OUTPUT:
SQL> SELECT SUBSTR(TABLESPACE_NAME,1,25) TABLESPACE_NAME,
  2         SUBSTR(FILE_NAME,1,40) FILE_NAME,
  3         BYTES
  4  FROM SYS.DBA_DATA_FILES;

TABLESPACE_NAME           FILE_NAME                                     BYTES
------------------------- ---------------------------------------- ----------
SYSTEM                    /disk01/system0.dbf                        41943040
RBS                       /disk02/rbs0.dbf                          524288000
TEMP                      /disk03/temp0.dbf                         524288000
TOOLS                     /disk04/tools0.dbf                         20971520
USERS                     /disk05/users0.dbf                         20971520
DATA_TS                   /disk06/data0.dbf                         524288000
INDEX_TS                  /disk07/index0.dbf                        524288000

7 rows selected.
ANALYSIS:

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.

Space Available

As the following example shows, the DBA_FREE_SPACE view tells you how much free space is available in each tablespace.

INPUT:
SQL> SELECT TABLESPACE_NAME, SUM(BYTES)
  2  FROM SYS.DBA_FREE_SPACE
  3  GROUP BY TABLESPACE_NAME;
OUTPUT:
TABLESPACE_NAME                SUM(BYTES)
------------------------------ ----------
SYSTEM                           23543040
RBS                             524288000
TEMP                            524288000
TOOLS                            12871520
USERS                              971520
DATA_TS                            568000
INDEX_TS                          1288000

7 rows selected.
ANALYSIS:

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).

Rollback Segments

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.

INPUT:
SQL> SELECT OWNER,
  2         SEGMENT_NAME
  3  FROM SYS.DBA_ROLLBACK_SEGS;
OUTPUT:
OWNER  SEGMENT_NAME
------ ------------
SYS    SYSTEM
SYS    R0
SYS    R01
SYS    R02
SYS    R03
SYS    R04
SYS    R05

7 rows selected.
ANALYSIS:

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.

Session Information

A DESCRIBE command of the V$SESSION views follows. (DESCRIBE is an SQL*Plus command and will be covered on Day 20.) You can see the detail that is contained in the view.

INPUT:
SQL> DESCRIBE V$SESSION
OUTPUT:
 Name                            Null?    Type
 ------------------------------  -------  ----
 SADDR                                    RAW(4)
 SID                                      NUMBER
 SERIAL#                                  NUMBER
 AUDSID                                   NUMBER
 PADDR                                    RAW(4)
 USER#                                    NUMBER
 USERNAME                                 VARCHAR2(30)
 COMMAND                                  NUMBER
 TADDR                                    VARCHAR2(8)
 LOCKWAIT                                 VARCHAR2(8)
 STATUS                                   VARCHAR2(8)
 SERVER                                   VARCHAR2(9)
 SCHEMA#                                  NUMBER
 SCHEMANAME                               VARCHAR2(30)
 OSUSER                                   VARCHAR2(15)
 PROCESS                                  VARCHAR2(9)
 MACHINE                                  VARCHAR2(64)
 TERMINAL                                 VARCHAR2(10)
 PROGRAM                                  VARCHAR2(48)
 TYPE                                     VARCHAR2(10)
 SQL_ADDRESS                              RAW(4)
 SQL_HASH_VALUE                           NUMBER
 PREV_SQL_ADDR                            RAW(4)
 PREV_HASH_VALUE                          NUMBER
 MODULE                                   VARCHAR2(48)
 MODULE_HASH                              NUMBER
 ACTION                                   VARCHAR2(32)
 ACTION_HASH                              NUMBER
 CLIENT_INFO                              VARCHAR2(64)
 FIXED_TABLE_SEQUENCE                     NUMBER
 ROW_WAIT_OBJ#                            NUMBER
 ROW_WAIT_FILE#                           NUMBER
 ROW_WAIT_BLOCK#                          NUMBER
 ROW_WAIT_ROW#                            NUMBER
 LOGON_TIME                               DATE
 LAST_CALL_ET                             NUMBER

To get information about current database sessions, you could write a SELECT statement similar to the one that follows from V$SESSION.

INPUT/OUTPUT:
SQL> SELECT USERNAME, COMMAND, STATUS
  2  FROM V$SESSION
  3  WHERE USERNAME IS NOT NULL;

USERNAME                          COMMAND STATUS
------------------------------ ---------- --------
TWILLIAMS                               3 ACTIVE
JSMITH                                  0 INACTIVE

2 rows selected.
ANALYSIS:

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.

Performance Statistics

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. (See Day 15.) 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.

Summary

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.

Q&A

Q Why should I use the views and tables in the data dictionary?

A Using the views in the data dictionary is the most accurate way to discover the nature of your database. The tables can tell you what you have access to and what your privileges are. They can also help you monitor various other database events such as user processes and database performance.

Q How is the data dictionary created?

A The data dictionary is created when the database is initialized. Oracle Corporation provides several scripts to run when creating each database. These scripts create all necessary tables and views for that particular database's system catalog.

Q How is the data dictionary updated?

A The data dictionary is updated internally by the RDBMS during daily operations. When you change the structure of a table, the appropriate changes are made to the data dictionary internally. You should never attempt to update any tables in the data dictionary yourself. Doing so may cause a corrupt database.

Q How can I find out who did what in a database?

A Normally, tables or views in a system catalog allow you to audit user activity.

Workshop

The Workshop provides quiz questions to help solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you have learned. Try to answer the quiz and exercise questions before checking the answers in Appendix F, "Answers to Quizzes and Exercises."

Quiz

1. In Oracle, how can you find out what tables and views you own?

2. What types of information are stored in the data dictionary?

3. How can you use performance statistics?

4. What are some database objects?

Exercise

Suppose you are managing a small to medium-size database. Your job responsibilities include developing and managing the database. Another individual is inserting large amounts of data into a table and receives an error indicating a lack of space. You must determine the cause of the problem. Does the user's tablespace quota need to be increased, or do you need to allocate more space to the tablespace? Prepare a step-by-step list that explains how you will gather the necessary information from the data dictionary. You do not need to list specific table or view names.


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.


Casa de Bender