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.
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.
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.
SQL> SELECT * 2 FROM USER_USERS;
USERNAME USER_ID DEFAULT_TABLESPACE TEMPORARY TABLESPACE CREATED ---------- ------ -------------------- -------------------- -------- JSMITH 29 USERS TEMP 14-MAR-97 1 row selected.
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-97As 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.
You can use the USER_SYS_PRIVS view to examine your system privileges. Remember, system privileges are privileges that allow you to do certain things within the database as a whole. These privileges are not specific to any one object or set of objects.
SQL> SELECT * 2 FROM USER_SYS_PRIVS;
USERNAME PRIVILEGE ADM -------- -------------------- --- JSMITH UNLIMITED TABLESPACE NO JSMITH CREATE SESSION NO 2 rows selected.
You can use the USER_ROLE_PRIVS view to view information about roles you have been granted within the database. Database roles are very similar to system-level privileges. A role is created much like a user and then granted privileges. After the role has been granted privileges, the role can be granted to a user. Remember that object-level privileges may also be contained within a role.
SQL> SELECT * 2 FROM USER_ROLE_PRIVS; USERNAME GRANTED_ROLE ADM DEF OS_ ------------ ---------------- --- --- -- JSMITH CONNECT NO YES NO JSMITH RESOURCE NO YES NO 2 rows selected.
Probably the most basic user view is USER_CATALOG, which is simply a catalog of the tables, views, synonyms, and sequences owned by the current user.
SQL> SELECT * 2 FROM USER_CATALOG;
TABLE_NAME TABLE_TYPE ---------------------------- ---------- MAGAZINE_TBL TABLE MAG_COUNTER SEQUENCE MAG_VIEW VIEW SPORTS TABLE 4 rows selected.
Another useful view is ALL_CATALOG, which enables you to see tables owned by other individuals.
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.
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;
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.
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
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.
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."
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
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
SQL> SELECT * 2 FROM USER_ROLE_PRIVS;
USERNAME GRANTED_ROLE ADM DEF OS_ ------------------ -------------------- --- --- -- JSMITH CONNECT NO YES NO JSMITH RESOURCE NO YES NO
SQL> SELECT * 2 FROM SYS.DBA_ROLES; FROM SYS.DBA_ROLES; * ERROR at line 2: ORA-00942: table or view does not exist
SQL> SELECT * 2 FROM SYS.DBA_USERS;
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.
SQL> SELECT * 2 FROM SYS.DBA_ROLES;
ROLE PASSWORD ------------------------------ -------- CONNECT NO RESOURCE NO DBA NO EXP_FULL_DATABASE NO IMP_FULL_DATABASE NO END_USER_ROLE NO 6 rows selected.
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.
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.
SQL> SELECT * 2 FROM SYS.DBA_CATALOG 3 WHERE ROWNUM < 5;
OWNER TABLE_NAME TABLE_TYPE ------------------------------ ------------------------------ ---------- SYS CDEF$ TABLE SYS TAB$ TABLE SYS IND$ TABLE SYS CLU$ TABLE 4 rows selected.
The following query shows you what type of objects exist in a particular database.
TIP: You can use ROWNUM to narrow down the results of your query to a specified number of rows for testing purposes. Oracle calls ROWNUM a pseudocolumn. ROWNUM, like ROWID, can be used on any database table or view.
SQL> SELECT DISTINCT(OBJECT_TYPE) 2 FROM SYS.DBA_OBJECTS; OBJECT_TYPE ------------ CLUSTER DATABASE LINK FUNCTION INDEX PACKAGE PACKAGE BODY PROCEDURE SEQUENCE SYNONYM TABLE TRIGGER VIEW 12 rows selected.
The DBA_TABLES view gives specific information about database tables, mostly concerning storage.
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.
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.
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.
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.
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.
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.
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.
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?
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.
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;
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.
Next you can take a look at each extent of one of the tables. You can start with INVOICE_TBL.
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.
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.
SQL> SELECT TABLESPACE_NAME, SUM(BYTES) 2 FROM SYS.DBA_FREE_SPACE 3 GROUP BY TABLESPACE_NAME;
TABLESPACE_NAME SUM(BYTES) ------------------------------ ---------- SYSTEM 23543040 RBS 524288000 TEMP 524288000 TOOLS 12871520 USERS 971520 DATA_TS 568000 INDEX_TS 1288000 7 rows selected.
SQL> SELECT OWNER, 2 SEGMENT_NAME 3 FROM SYS.DBA_ROLLBACK_SEGS;
OWNER SEGMENT_NAME ------ ------------ SYS SYSTEM SYS R0 SYS R01 SYS R02 SYS R03 SYS R04 SYS R05 7 rows selected.
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.
SQL> DESCRIBE V$SESSION
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 NUMBERTo get information about current database sessions, you could write a SELECT statement similar to the one that follows from V$SESSION.
SQL> SELECT USERNAME, COMMAND, STATUS 2 FROM V$SESSION 3 WHERE USERNAME IS NOT NULL; USERNAME COMMAND STATUS ------------------------------ ---------- -------- TWILLIAMS 3 ACTIVE JSMITH 0 INACTIVE 2 rows selected.
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 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.
NOTE: Exploring the data dictionary is an adventure, and you will need to explore in order to learn to use it effectively.
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.
2. What types of information are stored in the data dictionary?
3. How can you use performance statistics?
4. What are some database objects?