Teach Yourself SQL in 21 Days, Second Edition
  
- 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:
- 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.
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.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
