In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Sunday, November 15, 2009

Static data dictionary views

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_.
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

* dba_tables
* dba_tab_columns
* dba_all_tables
* dba_tab_comments
* dba_col_comments
* dba_external_tables
* dba_external_locations
* dba_tab_histograms
* dba_tab_statistics
* dba_tab_col_statistics
* dba_tab_modifications
* dba_encrypted_columns
* dba_unused_col_tabs
* dba_partial_drop_tabs

Registry related data dictionary views

* dba_registry,
* dba_registry_hierarchy,
* dba_registry_history,
* dba_registry_log

XML DB related views

* dba_xml_schemas
* dba_xml_tables
* dba_xml_views
* dba_xml_views_cols

Audit related views

* dba_audit_exists
* dba_audit_object
* dba_audit_policies
* dba_audit_policy_columns
* dba_audit_session
* dba_audit_statement
* dba_audit_trail
* dba_common_audit_trail
* dba_fga_audit_trail
* dba_opj_audit_opts
* dba_priv_audit_opts
* dba_repaudit_attribute
* dba_repaudit_column
* dba_stmt_audit_option_opts

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:

* plsql_ccflags
* plsql_code_type
* plsql_debug
* plsql_optimize_level
* plsql_warnings
* nls_length_semantics

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
See drop table.
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
Show all 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
See also Getting the nth source code line of a trigger.
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.
dba_tablespaces
Displays the tablespaces of a database.
dba_views

regards,
rajeshkumar govindarajan

source and reference:
http://www.adp-gmbh.ch/ora/misc/static_dictionary_views.html

No comments:

 
Share/Bookmark