Static data dictionary views

select * from dict;

USER_ / ALL_ / DBA_

Most static dictionary views come with three prefixes: USER_*, ALL_* and DBA_*. For example, there's a user_tables, all_tables and a dba_tables view. For brevity, I only give the names of the views that start with dba_.

Generally, the user_ views show database objects owned by the user who is querying the user_ view.

The all_ views show the database objects that are accessible to the user who is querying the all_view.

The dba_ views show all database objects.

Table related data dictionary views

Registry related data dictionary views

XML DB related views

Audit related views

Other static data dictionary views

dba_advisor_findings

Exposes the Oracle 10g's advisors tips.
See also dbms_sqltune

dba_advisor_recommendations

Exposes the Oracle 10g's advisors tips.
See also dbms_sqltune

dba_advisor_rationale

Exposes the Oracle 10g's advisors tips.
See also dbms_sqltune

dba_advisor_tasks

Exposes the Oracle 10g's advisors tips.
See also dbms_sqltune

dba_arguments

This view can be used to find out what arguments a procedure's or object type's function/procedure has.

See this page for an example of a select statement, or this pagea.

dba_col_privs

dba_constraints

Derives from con$. Records the constraints.

See also On identifiying parent and child tables.

dba_datapump_jobs

This view monitors data pump jobs.

dba_data_files

If a datafile has autoextend on and unlimited maxsize, the maxsize is reported with a ridiciculous high number such as 1.7180E+10.

file_id vs relative_fno: file_id is unique in a database while relative_fno is 'only' unique for datafiles within a tablespace.

dba_db_links

dba_directories

Displays all directories that were created with create directory.

Note: There's no user_directories, only dba_directories and all_directories exist!

defcall

This is a view that belongs to the replication catalog.

defdefaultdest

This is a view that belongs to the replication catalog.

deferror

This is a view that belongs to the replication catalog.

It records entries in the error queue.

defpropagator

This is a view that belongs to the replication catalog.

deftran

This is a view that belongs to the replication catalog.

defcalldest

This is a view that belongs to the replication catalog.

deferrcount

This is a view that belongs to the replication catalog.

deflob

This is a view that belongs to the replication catalog.

defschedule

This is a view that belongs to the replication catalog.

deftrandest

This is a view that belongs to the replication catalog.

Use dbms_defer_sys.delete_tran to get rid of entries in deftrandest.

This view records entries in the deferred transaction queue.

flashback_transaction_query

Used for Flashback transaction queries. (See flashback transaction query example)

global_name

dba_indexes

dba_jobs

dba_jobs_running

dba_lobs

dba_logstdby_log

Can be used to verirfy that archived redo log are being applied to standby databases.

dba_logstdby_not_unique

dba_logstdby_parameters

dba_logstdby_progress

dba_logstdby_skip

dba_logstdby_skip_transaction

dba_mviews

dba_objects

Displays information about objects.

all_olap2_aw_cubes

Shows all cubes in all analytic workspaces.

all_olap2_aw_dimensions

dba_plsql_object_settings

Stores the values of the following initialization parameters as of compilation time:

dba_procedures

The column procedure_name is null for procedures and functions, it is only set for procedures and functions in pl/sql packages. The procedures' and functions' names are found in the column object_name, however, using dba_procedures, it is not possible to find out if it is a procedure or function. This is possible with dba_objects.

dba_profiles

Allows to see the profiles and their settings.

dba_queues

dba_queue_tables

dba_recyclebin

Displays the object in the recycle bin for the currently logged on user.

recyclebin is a synonym for user_recyclebin.

dba_refresh

dba_registered_mview_groups

This is a view that belongs to the replication catalog.

See also materialized view group.

dba_registry

dba_repcat_refresh_templates

This is a view that belongs to the replication catalog.

dba_repcat_template_objects

This is a view that belongs to the replication catalog.

It keeps track of deployent templates.

dba_repcat_template_parms

This is a view that belongs to the replication catalog.

dba_repcat_template_sites

This is a view that belongs to the replication catalog.

dba_repcat_user_authorizations

This is a view that belongs to the replication catalog.

dba_repcat_user_parm_values

This is a view that belongs to the replication catalog.

dba_repcatlog

This is a view that belongs to the replication catalog.

It can be used to track administrative requests.

dba_repcolumn

This is a view that belongs to the replication catalog.

dba_repcolumn_group

This is a view that belongs to the replication catalog.

dba_repconflict

This is a view that belongs to the replication catalog.

dba_repddl

This is a view that belongs to the replication catalog.

dba_repextensions

This is a view that belongs to the replication catalog.

dba_repgenobjects

This is a view that belongs to the replication catalog.

dba_repgroup

This is a view that belongs to the replication catalog.

dba_repgroup_privileges

This is a view that belongs to the replication catalog.

dba_repgrouped_column

This is a view that belongs to the replication catalog.

dba_repkey_columns

This is a view that belongs to the replication catalog.

dba_repobject

This is a view that belongs to the replication catalog.

dba_repparameter_column

This is a view that belongs to the replication catalog.

dba_reppriority

This is a view that belongs to the replication catalog.

dba_reppriority_group

This is a view that belongs to the replication catalog.

dba_repprop

This is a view that belongs to the replication catalog.

dba_represol_stats_control

This is a view that belongs to the replication catalog.

dba_represolution

This is a view that belongs to the replication catalog.

dba_represolution_method

This is a view that belongs to the replication catalog.

dba_represolution_statistics

This is a view that belongs to the replication catalog.

dba_repsites

This is a view that belongs to the replication catalog.

dba_repsites_new

This is a view that belongs to the replication catalog.

dba_rewrite_equivalences

This view can be used to show the equivalences that were established using dbms_advanced_rewrite.declare_rewrite_equivalence.

dba_roles

This view lists all roles except the special role public.

select name, ##A(decode/ora/sql/decode.html)(password, null, 'NO', 'EXTERNAL', 'EXTERNAL',
                      'GLOBAL', 'GLOBAL', 'YES')
from  user$
where type# = 0 and name not in ('PUBLIC', '_NEXT_USER')

dba_role_privs

Lists roles that are assigned to a either another role or a user.

Here is a script that uses dba_role_privs to recursively list privileges assigned to users and roles.

dba_segments

This view shows information about segments.

dba_sequences

dba_sequences is a bit special: Unlike dba_tables, dba_indexes, dba_triggers, dba_constraints, dba_db_links, dba_jobs, dba_queue_tables and dba_queues, there is no column owner but sequence_owner.

dba_source

dba_sqltune_binds

This view can be used to get the SQL tuning advisors recommondations.

dba_sqltune_plans

This view can be used to get the SQL tuning advisors recommondations.

dba_sqltune_statistics

This view can be used to get the SQL tuning advisors recommondations.

dba_synonyms

dba_sys_privs

Lists system privileges that are assigned to a either another role or a user.

dba_scheduler_job_run_details

system_privilege_map

Lists all system privileges.

These privileges can be audited.

all_sumdelta

Lists direct path load entries accessible to the current user.

dba_tab_privs

all_tab_privs_made

There is no dba_tab_privs_made, only user_tab_privs_made and all_tab_privs_made.

All_tab_privs_made view lists all object privileges that the current either has granted or for for which he owns the underlying object.

User_tab_privs_made displays only grants for which the current user is the object owner.

These views are not role-recursive. That is to say, if I grant an object privilege to a role, and then grant that role to a user, this view doesn't show me that the user has that object's privilege.

A related view is all_tab_privs_recd.

all_tab_privs_recd

There is no dba_tab_privs_recd, only user_tab_privs_recd and all_tab_privs_recd.

A related view is all_tab_privs_made.

dba_triggers

dba_ts_quotas

Can be used to find out how big the quota of a user is on a tablespace and how much thereof he has already occupied.

dba_users

Has a record for each user in the database.

The related view user_users has only one row: the one that belongs to the user selecting from user_users. See also Who am I.


	René Nyffenegger's collection of things on the web
	René Nyffenegger on Oracle - Most wanted - Feedback - Follow @renenyffenegger