ITSE2309-Lab 1 The Oracle Data Dictionary Solved

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;