Data Dictionary Tutorial, Part 2

Data dictionary — meta-data — 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.

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?

   SQL> SELECT constraint_name, constraint_type,
     2  status, table_name 
     3  FROM user_constraints
     4  /
   

2. Find which user have access to the books table (see Introduction to Oracle.)
3. 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.
4. Can you retrieve its source code from the dictionary?
5. 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.

Footnotes

... command¹

This command (obviously) gets its data from the dictionary.

Add to del.icio.us
Contents
Examples
The Dictionary in PL/SQL
Optional Exercise
See Also
Oracle Basics
PL/SQL
SQL Built-In Functions
Oracle & Java
Advanced Oracle & Java
Tell Your Friends

Your name

Your friend's email