|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
Static data dictionary views
To be finished...
The following views are part of the data dictionary.
Find all views along with a comment in dict:
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_.
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
This view can be used to find out what arguments a procedure's or object type's function/procedure has.
Derives from con$. Records the constraints.
See also On identifiying parent and child tables.
This view monitors data pump jobs.
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.
Displays all directories that were created with create directory.
Note: There's no user_directories, only dba_directories and all_directories exist!
It records entries in the error queue.
Use dbms_defer_sys.delete_tran to get rid of entries in deftrandest.
This view records entries in the deferred transaction queue.
Can be used to verirfy that archived redo log are being applied to standby databases.
Displays information about objects.
Shows all cubes in all analytic workspaces.
Stores the values of the following initialization parameters as of compilation time:
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.
Allows to see the profiles and their settings.
See drop table.
recyclebin is a synonym for user_recyclebin.
See also materialized view group.
It keeps track of deployent templates.
It can be used to track administrative requests.
This view can be used to show the equivalences that were established using dbms_advanced_rewrite.declare_rewrite_equivalence.
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')
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.
This view shows information about segments.
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.
Show all synonyms.
Lists all system privileges.
These privileges can be audited.
Lists direct path load entries accessible to the current user.
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.
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.
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.
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.
Displays the tablespaces of a database.