Syronex: sofware, technology.

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

... command1
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