Databases Reference
In-Depth Information
For example, say you're troubleshooting an issue with materialized views, and you want to determine the names
of data dictionary views related to the materialized view feature. You can run a query such as this:
select table_name, comments
from dictionary
where table_name like '%MV%';
Here is a snippet of the output:
TABLE_NAME COMMENTS
------------------------- -------------------------------------------------------
DBA_MVIEW_LOGS All materialized view logs in the database
DBA_MVIEWS All materialized views in the database
DBA_MVIEW_ANALYSIS Description of the materialized views accessible to dba
DBA_MVIEW_COMMENTS Comments on all materialized views in the database
In this manner, you can quickly determine which view you need to access. If you want further information about
the view, you can describe it; for example,
SQL>desc dba_mviews
If that doesn't give you enough information regarding the column names, you can query the
DICT_COLUMNS
view.
This view provides comments about the columns of a data dictionary view; for example,
select column_name, comments
from dict_columns
where table_name = 'DBA_MVIEWS';
Here is a fraction of the output:
COLUMN_NAME COMMENTS
----------------------- ---------------------------------------------
OWNER Owner of the materialized view
MVIEW_NAME Name of the materialized view
CONTAINER_NAME Name of the materialized view container table
QUERY The defining query that the materialized view instantiates
In this way, you can generate and view documentation regarding most data dictionary objects. The technique
allows you to quickly identify appropriate views and the columns that may help you in a troubleshooting situation.
Displaying User Information
You may find yourself in an environment that contains hundreds of databases located on dozens of different servers.
In such a scenario, you want to ensure that you don't run the wrong commands or connect to the wrong database,
or both. When performing DBA tasks, it's prudent to verify that you're connected as the appropriate account and to
the correct database. You can run the following types of SQL commands to verify the currently connected user and
database information:
SQL>show user;
SQL>select * from user_users;
SQL>select name from v$database;
SQL>select instance_name, host_name from v$instance;
