Web based School
- Chapter 16 -
Using Views to Retrieve Useful Information from the Data Dictionary
Objectives
ToChapter 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.
Chapter 16 extends what you learned yesterChapter (Chapter 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 Chapter 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 Chapter 19, "Transact-SQL: An Introduction," and Chapter 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 Chapter 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 Chapter 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.
Popular Tutorials
-
MS Access
1109
-
C++
1222
-
HTML
584
-
JavaScript
616
-
Vbscript
873
-
Oracle
473
-
VC++
875
-
SQL
2959
-
XML
514
-
Java
814
-
Perl
455
-
Linux
451
