Data Dictionary in dbms

Data Dictionary

Data dictionary — metadata — system & object privileges — dictionary structure — ``user'' tables - ``all'' tables — ``dba'' tables — ``v$'' tables — frequently used tables — usage examples — exercises — using the dictionary in PL/SQL programs — optional exercise.

Introduction

This document presents Oracle's data dictionary, also called the system catalogue. The data dictionary is the repository of all the meta-data relevant to the objects stored in the database—and also of information concerning the DBMS itself.

Dictionary Content

Defining metadata.

The term metadata is often defined as data about data. That is, data that provides information about the tables, views, constraints, stored procedures, etc. stored within the database. If we take a table as an example, the dictionary will store information such as:

• its name
• when it was created and when it was last accessed
• the names and data types of its attributes (i.e. structural information)
• its owner, who may read and write to it (i.e. security information)
• where the data is stored (i.e. physical information)

Security in Oracle.

Oracle defines two categories of privileges: object privileges and system privileges. Both categories are granted and revoked using the GRANT and REVOKE SQL constructs: GRANT <object_privilege> ON <object> TO <user> and GRANT <system_privilege> TO <user>.
System privileges mainly specify the types of objects a user is allowed to manipulate (tables,...) and what (s)he can do with them. Object privileges define the access rights at the objects level (and even at the attribute level for tables).

Dictionary Structure

The data dictionary is implemented in Oracle as a set of read-only tables and views.

Figure 1: Hierachical structure of the data dictionary.

Figure 1 presents the two-level structure of the dictionary. At the root of the tree is the dictionary table, that features two attributes: table_name and comments. The comment field presents an informal description of the corresponding dictionary table.
For instance, we can request information about the dictionary table:

SQL> SELECT comments 

  2  FROM dictionary WHERE table_name='DICTIONARY'

  3  /

and get:

Description of data dictionary tables and views

As an exercise, write a query to find out how many tables make up the data dictionary.

The second level of the dictionary is divided into four categories of tables. ``User'' tables describe the objects you own. They are only accessible to you. ``All'' tables describe the objects of all the users, and are accessible to all the users. ``DBA'' tables contain information only relevant and accessible to database administrators. And last, ``V$'' tables reflect the internal state of the DBMS and are mainly useful to DBAs for performance audit and optimization.
Refer to Figure 1 for a list of commonly-used dictionary tables. Also, remember that you can obtain the schema of any table with the desc command

Exercises

Note that by convention, all the textual data in the dictionary uses uppercase characters.

1. What information can you gather from the following query?

2.      SQL> SELECT constraint_name, constraint_type,

3.        2  status, table_name 

4.        3  FROM user_constraints

5.        4  /

6. Find which user have access to the books table (see Introduction to Oracle.)
7. How can you find out whether the procedure welcome (see Introduction to PL/SQL) did compile successfully? Hint: the status of the procedure is then VALID.
8. Can you retrieve its source code from the dictionary?
9. Experiment with the tables from the list given in Figure 1 that you have not used yet.

Using the Dictionary in PL/SQL

The following PL/SQL program checks that the user attended the previous Oracle laboratories using the user_tables and user_objects tables. Make sure you understand the program before running it.

CREATE OR REPLACE PROCEDURE stud_snooper

AS

  attend_lab_1 number;

  attend_lab_2 number;

  user_name varchar2(8) := lower(user);

BEGIN

  -- students that attended lab 1 have a 'books' table

  SELECT count(table_name) INTO attend_lab_1

  FROM user_tables WHERE table_name='BOOKS';

  dbms_output.put(user_name 

    || ' attendance to Oracle lab. 1: '); 

  -- attend_lab_1 will be either 0 or 1.

  if attend_lab_1 = 0 

  then

    dbms_output.put_line('NO');

  else

    dbms_output.put_line('YES');

  end if;

  -- students that attended lab 2 have a 'welcome' procedure

  SELECT count(object_name) INTO attend_lab_2

  FROM user_objects WHERE object_type='PROCEDURE' 

  AND object_name='WELCOME';

  dbms_output.put(user_name 

    || ' attendance to Oracle lab. 2: '); 

  if attend_lab_2 = 0

  then

    dbms_output.put_line('NO');

  else

    dbms_output.put_line('YES');

  end if;

END;

/

Optional Exercise

Write a program stud_snooper_2 that uses all_tables and all_objects to report all the students that did not attend at least one Oracle lab.