Data Dictionary

Data Dictionary
During the database creation, oracle server creates additional object structures within the data files. These include
• Data dictionary tables (includes base table and data dictionary views)
• Dynamic performance tables
Data dictionary is a set of read only tables and views that record, verify, and provide information about its associated database.
Includes two types of objects
• Base tables
• Data dictionary views

Base tables
Created during the creation of database
Created using sql.bsq e.g ind$ stores index related information
Data dictionary views
Created using catalog.sql
Data dictionary views categories

Data dictionary consists of three main sets of static views
DBA_
What is in all the schemas. Generally these views are accessed by DBA. Any user granted ‘SELECT ANY TABLE’ system privilege can also access DBA prefixed views
ALL_
What the user can access
USER_
What is in the user’s schema

Dynamic Performance Views
Oracle server records the current database activity in the virtual tables called dynamic performance views. These virtual tables exist only when the database is running and is represented by V$.
Dynamic Performance Views record current database activity. These get information from memory and control files. These are dynamic because the information in these views is updated when the data is open and running.
Dynamic performance views are owned by ‘SYS’ user.

To get an overview of data dictionary tables DICTIONARY or its synonym DICT synonym can be used. To get columns data we can use DICT_COLUMNS

--The following gives the database name
select name "database_name" from v$database


--The following gives the instance name
select instance "Instance_name" from v$thread

--The following gives the used space in bytes
select sum(bytes) "used space in bytes" from dba_segments

--The following gives the used space giga bytes
select sum(bytes)/(1024*1024*1024) "used space in Giga Bytes" from dba_segments

--Free space in giga bytes
select sum(bytes)/(1024*1024*1024) "Free space in Giga Bytes" from dba_free_space

--Users and when were they got created
select username, created from dba_users

--Gives the data file and corresponding table space names
select * from v$datafile