Structure of Data Dictionary
Since the data dictionary has data about the database objects, it forms the base for the entire database. Hence, the information in the data dictionary are stored in cryptic form and is not accessible any user for modification. When a query is fired to database, DBMS software fetches in the data dictionary for the objects and their details. Then it searches in the core memory for the stored data and performs the requested transaction. So it is the underlying DBMS software which accesses the data dictionary tables. Still sometimes, DBA or the users/developers wants to see the information about some of data objects. In such cases, views on these tables are created and given read-only (SELECT Grant) access to the users.
All the information about the database is stored in the base tables and is owned by SYS schema. Only the DBMS would read and write to the base tables. No user is given access to these tables. Altering any of these tables will crash the whole database. Hence user is not given access. Any changes to any of the objects will be automatically made to reflect in base tables by DBMS. This is also known as internal level of data dictionary. At this level, most of the information would be in cryptic form.
Views on these Base tables are created in the SYSTEM schema and the users are given read-only access on these views. These views will provide decoded summary about the base tables. This level is also known as external level or user-accessible views.
In the SYSTEM schema, DICTIONARY (DICT is the Synonym of DICTIONARY) view gives a list of all the tables and views present in the database along with comments of each table. Below is the small snapshot of DICTIONARY view.
SELECT * FROM DICTIONARY;
Data Dictionary Views/Architecture
Based on the accessibility rights, Data Dictionary provides three views – USER, ALL and DBA.
The user view lists only those tables and views which are created by the current user/schema. It does not list the tables and views of other schemas nor the ones to which it has access. It displays only those objects that are created by it.
The ALL view lists all the tables and views that are owned by the current user as well as those tables and views to which it has access. That means it lists the tables and views that are created by it, and those tables and views of other user/schema to which it has access.
DBA view will have access to all the tables and views of all the users/schema. But these views will be accessible by only those who have DBA privileges.
Tables and views in these views are prefixed by the view name. For example, USER_TABLES will have all tables with their name, number of columns, storage information, statistical information etc. in the USER view, whereas ALL_TABLES will have all tables with their name, number of columns, storage information, statistical information etc. in ALL view and DBA_TABLES will have all the tables and description in the database.
To be clearer, let us consider one example. In a STUDENT database, say we have STUDENT, LECTURER, COURSE, SUBJECT, STAFF, CLASSROOMS tables. Here STUDENT table is created by the user James, LECTURER, COURSE and SUBJECT are created by the user Antony, and STAFF and CLASSROOMS are created by the user Jacob. Admin is another user with DBA privilege. Tables created by James are given read only access to Antony. All the tables of Jacob are accessible by James and Antony. Below diagram depicts the same.
Now let us see what each user can see at 3 views.In this example, if we fire a query SELECT * FROM %_TABLES for 3 views, we will see different set of results as shown below. Please note that select query will give all other fields of the table, below table just shows which tables are visible at which views.
Since ADMIN user does not have any tables created by its own and have no access to any tables created by other users, they are not displayed. It has access to all the tables of all users as a DBA.
Query for each view would be
SELECT * FROM USER_TABLES; -- this query will not list any owner column as it itself is the owner of tables SELECT * FROM ALL_TABLES; SELECT * FROM DBA_TABLES;
This is how we see the views when we fire queries on views.
Above rule for tables holds good for views too in all the data dictionary views.