Data Dictionary Views

Depending on which features are installed and configured, an Oracle 10g database can contain more than 1,300 data dictionary views. Data dictionary views have names that begin with DBA_, ALL_, and USER_.

The difference between the DBA_, ALL_, and USER_ views can be illustrated using the DBA_TABLES data dictionary view as an example. The DBA_TABLES view shows information on all the tables in the database. The corresponding ALL_TABLES view, despite its name, shows only the tables that a particular database user owns or has access to.

For example, if you were logged into the database as a user named SCOTT, the ALL_TABLES view would show all the tables owned by the user SCOTT and the tables to which SCOTT has been granted access by other users. The USER_TABLES view shows only those objects owned by a user. If the user SCOTT were to examine the USER_TABLES view, only those tables he owns would be displayed.

eg:
DBA_TABLES
All tables in the entire database. Reserved for user accounts that have DBA privileges.

ALL_TABLES
All tables owned by a particular database user plus all tables to which the user has been granted access.

USER_TABLES
All tables owned by a particular database user.

---------------
Dynamic Performance Views

Depending on which features are installed and configured, an Oracle 10g database can contain approximately 350 dynamic performance views. Most of these views have names that begin with V$.

------------
A Comparison of Data Dictionary and Dynamic Performance Views

Dictionary Views                            Dynamic Performance Views

The DBA_ views usually have plural names (for example, DBA_DATA_FILES).

The names of the V$ views are generally singular(for example, V$DATAFILE).

++++
The DBA_ views are available only when the database is open and running.

Some V$ views are available even when the database is not fully open and running.

++++
The data contained in the DBA_ views is generally uppercase.

The data contained in the V$ views is usually lowercase.

++++
The data contained in the DBA_ views is static and is not cleared when the database is shutdown.

The V$ views contain dynamic statistical data that is lost each time the database is shutdown.

-----------------

Oracle namespace

Tables share a namespace with views, sequences, private synonyms, procedures, functions, packages, materialized views, and user-defined types. Objects sharing a namespace cannot have the same name.

------------------
   Oracle uses a search hierarchy when a startup command is issued without specifying either a PFILE or an SPFILE. Oracle first looks for a parameter file called spfile$ORACLE_SID.ora. If it doesn’t find that, it searches for spfile.ora. Finally it searches for a traditional text PFILE with the default name of init$ORACLE_SID.ora.