PL/SQL 9-3: Review of the Data Dictionary

 Database Programming with PL/SQL

9-3: Review of the Data Dictionary

Practice Activities

Vocabulary

Contain information about everything in the database, no matter who owns them. - DBA_* tables

Contain information about objects which you have privileges to use. - ALL_* tables

A catalog of all database objects contained in an Oracle database. - Data Dictionary

Contain information about objects you own. - USER_* tables

Try It / Solve It

1. Which of the following statements are true:

A) The Data Dictionary is a list of hard coded table names in all Oracle databases.

False

B) The Data Dictionary can be updated by all users with Select statements.

False

C) All users of an Oracle Database can see details of all tables in that database.

True

D) The Data Dictionary is owned by the user called SYS.

False

2. List the three different classes of Data Dictionary views, and state what kind of information is

shown by each class.

DBA - everything in the database

ALL - objects which you have privileges to use

USER - objects you own

3. Write and execute a SELECT statement that lists all the stored objects you have created in your

account so far. The query should return the object name, its type, and its status. Order the output

by type of object.

SELECT object_name, object_type, status

FROM USER_OBJECTS

ORDER BY object_type;

4. Modify the query from question 3 to show only functions and procedures to which you have

access. Add the owner of the object to display in the results.

SELECT object_name, object_type, status, owner

FROM ALL_OBJECTS

WHERE object_type = 'FUNCTION' OR object_type = 'PROCEDURE'

ORDER BY object_type;

5. What is DICT and why is it useful?

DICT is a super-view that lists all the dictionary tables.

It is useful to search a table when you forgot the name, for example user_ind_columns, you search in DICT the tables with the table_name LIKE 'USER%IND%'

6. Write and execute a suitable SELECT…FROM DICT… statement to list dictionary views which

contain information about all views which you own. 

SELECT * FROM DICT WHERE table_name LIKE '%VIEW%';