ITSE2309 – DATABASE PROGRAMMING ORACLE) Lab 1 – The Oracle Data Dictionary Solved

Description

The Oracle data dictionary is a comprehensive set of tables and views owned by the DBA user SYS and SYSTEM, which is created when Oracle is initially installed. It is the central source of information for the Oracle RDBMS itself and for all users of Oracle. The tables are automatically maintained by Oracle. The data dictionary contains:

 

• The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)
• How much space has been allocated for, and is currently used by, the schema objects
• Default values for columns
• Integrity constraint information
• The names of Oracle users
• Privileges and roles each user has been granted
• Auditing information, such as who has accessed or updated various schema objects
• Other general database information

 

The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database’s SYSTEM tablespace.

 

Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators.

 

The data dictionary has three primary uses:

 

• Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.
• Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.
• Any Oracle user can use the data dictionary as a read-only reference for information about the database.

 

With some exceptions, the names of the objects in the data dictionary begin with one of three prefixes: “USER”, “ALL”, or “DBA”.  Records in the “USER” views usually record information about objects owned by the account performing the query.

 

In this lab, you will use SQL statements to access the data dictionary. The data dictionary is read-only, you can issue only use SELECT statements against its tables and views.

 

Issue the following SQL statements to learn more about the data dictionary.  We will be querying the data dictionary throughout the semester.  Copy each SQL statement and the results of the statement and past it in a file (one file per SQL statement).  Create a folder named, Fullname_Lab1.  Copy your files to the folder.  Zip the folder and upload it to Blackboard.

 

1. SQL> describe dictionary

 

2. SQL> select * from dictionary; — (Do not copy output to a file)

 

3. SQL> select Table_Name, Comments from dictionary where Table_Name like ‘DBA_%’;   — (Do not copy output to a file)

 

4. SQL> select Table_Name, Comments from dictionary where Table_Name like ‘USER_%’;  — (Do not copy output to a file)

 

5. SQL> describe user_tables;

 

6. SQL> select Table_Name from user_tables;

 

7. SQL> select Object_Name from user_objects where object_type = ‘TABLE’;

 

8. SQL> select Object_Name, object_type from user_objects;

— (Do not copy output to a file)

 

9. SQL> select Table_Name, Column_Name, Comments from dict_columns                where table_name  Like ‘USER_%’;  ;  — (Do not copy output to a file)

 

10. SQL> select Table_Name, Column_Name, Comments from dict_columns                 where Column_Name = “BLOCK” and Table_Name Like ‘USER_%’;

 

11. SQL> describe v$version;

 

12. SQL> select * from v$version;