|
Oracle data dictionary is the heart of an oracle database. It comprises of read-only set of tables that provide information about every possible aspect of the oracle database. The data dictionary holds definitions of schema objects, space allocation, database security, audit information etc. Oracle data dictionary is stored in the system tablespace and is structured in tables and views just like other database data. SQL statements are used to access the information contained in the data dictionary. Oracle data dictionary is read only, and only select queries can be run against the tables and views. Do not attempt to alter or manipulate the oracle data dictionary since it can permanently damage the integrity of the database
How is the Oracle Data Dictionary Structured ?
The oracle data dictionary is primarily made up of oracle base tables that are owned by the SYS user. Only oracle should write to these tables and an oracle dba should never try to update or delete the base table data since it can easily compromise the database integrity. It is the primary responsibility of an oracle database administrator to secure this account with a strong password.
During the creation of the database oracle also creates the oracle data dictionary also creates various views that are accessible based on the role granted in the database. These views are built upon the normalized base tables to ease the retrieval and understanding of of data using SQL. Most users should only have access to the views rather than base tables. Oracle data dictionary views are primarily grouped into 3 categories as USER, ALL and DBA.
USER_ : views with a prefix of user limit the oracle dictionary data displayed to whats in the current user schema. For example if jack owns 2 tables, then
"select table_name from user_tables"
would only display 2 rows when queried by jack.
ALL_ : All views expand the User_xxx views by allowing access to all the information in the database that is either in the Users schema or what the user has access to.
For example, if the jack owns 2 tables, but also has select privileges on 2 tables from scott's schema, then
"select table_name from all_tables where owner in ('SCOTT', 'JACK');"
would display 4 rows when queried by jack.
DBA_ : DBA views provide an expanded view to be used by the database administrator. These views are only accessible by users who have a DBA role and display what is contained in all users schema's including SYS. Some times DBA data dictionary views will also contain more information that is useful in oracle database administration
Get a Data Dictionary Pocket Reference @ Amazon
What are Oracle Dynamic Performance Tables
Oracle dynamic performance tables are a set of virtual tables that are maintained through out the operation of the database to record current database activity. These are not real tables and database administrator should exercise caution in granting access to these tables. Oracle dynamic performance tables are sometimes referred to as fixed views and are owned by SYS. These views are referred to as fixed views because they cannot be altered or removed by the oracle database administrator.
Oracle fixed views have a name that starts with a v_$. Regular views are created over these oracle dynamic perfomance tables and then public synonyms created. In an oracle database the views start with a v$ and are often referred to as v-dollar-views. For example, the V$DATAFILE view contains information about the database's datafiles, and the V$FIXED_TABLE view contains information about all of the dynamic performance tables and views in the database.
More information about oracle data dictionary can be found in the Oracle Documentation. Many vendors of oracle products like Quest Software have created posters that contain information about each one of the oracle data dictionary objects and the information they represent. We here at MyDatabaseSupport.com will review the components of the oracle data dictionary in more detail in the coming weeks.
{mos_sb_discuss:4}
|
