Oracle system views v$

The names of many tables look similar. For example, dba_users, all_users, user_users. The thing is, the prefix indicates different levels of data accessibility:

• dba - pertains to the entire database and is available to a user with DBA privileges;
• all - pertains to the user's schema and other schemas for which the user has permissions;
• user - pertains only to the user's schema;

Thus, the system user executing the query "select * from dba_sys_privs;" will receive information about all privileges of all database users, while the user scott will encounter an error. However, the query "select * from user_sys_privs;" executed by the user scott will return information about all of their privileges.

source	available information	example query	note
dictionary (dict)	dictionary of tables and views	select * from dict where table_name like '%PUMP%' ;	list of all tables and views whose names contain "PUMP"
v$instance	information about the database instance	select instance_name from v$instance;	instance name
v$session	information about established sessions	select sid||','||serial# sess ,username ,program ,machine ,status from v$session;	who is connected from where, and the connection status. sess - ready-to-use value for substitution, for example, in alter system disconnect session '' immediate;
dba_objects	information about all database objects	select * from dba_objects where status='INVALID';	list of database objects that have become invalid for some reason
dba_all_tables	information about all database tables	select * from dba_all_tables where tablespace_name not like 'sys%';	displays all tables located in tablespaces whose names do not start with sys
dba_data_files	information about all database files	select * from dba_data_files;	displays a list of all database files and their information
		select distinct aat.owner , aat.table_name , aat.tablespace_name from all_all_tables aat , dba_data_files ddf where aat.tablespace_name=ddf.tablespace_name and ddf.file_name like '%gbs_ecap_data%';	displays the names of all tables (with their owners) and tablespaces included in the data-files gbs_ecap_data
		select file_id , file_name , tablespace_name , bytes/1024/1024 as mbytes , status from dba_data_files;	displays the names of all data files, their associated tablespaces, sizes, and statuses
v$parameter	information about database parameters	select name , value , display_value , isses_modifiable , issys_modifiable from v$parameter where name like 'sga%';	parameter name, its value, displayed value, and indication of whether modification is possible/impossible for the session and system
v$version	information about the database version	select banner from v$version;
v$sql	information about SQL queries	select s.sql_fulltext , ses.username , ses.osuser from v$sql s , v$session ses where ses.sql_address=s.address;	displays SQL queries and the users who executed them (both Oracle users and OS users)
v$process	information about database processes and the resources they use	select * from v$process;
user$	list of users	select * from user$;	display all users
dba_users		select * from dba_users;
dba_tab_privs	all users and their privileges, as well as who granted these privileges	select * from dba_tab_privs
dba_tab_privs_made	who made which privilege changes	select * from dba_tab_privs_made where grantee='ctxsys';	who made what changes to privileges for the user ctxsys
dba_tab_privs_recd	all users and their privileges, as well as who granted these privileges	select * from dba_tab_privs_recd
dba_sys_privs	list of privilege recipients and their privileges	select * from dba_sys_privs where grantee='sys';	displays the privileges of the privilege recipient sys
dba_role_privs	roles granted to users and other roles	select * from dba_role_privs where grantee='sys';	displays the roles of the privilege recipient sys
dba_roles	list of roles	select * from dba_roles;
dba_ts_quotas	tablespace privileges	select * from dba_ts_quotas;	list of tablespaces and users who have privileges on these tablespaces
role_role_privs	list of roles granted to other roles	select * from role_role_privs;
role_sys_privs	all assignments of system privileges to tables	select * from role_sys_privs;	all assignments of system privileges to tables (owner, privileges, who granted them)
role_tab_privs	list of tables, their owners, and privileges granted to specific roles	select * from role_tab_privs;
session_privs	all privileges of the current user	select * from session_privs;
session_roles	all roles of the current user	select * from session_roles;
user_sys_privs	list of privileges of the current user	connect user/password@sid; select * from user_sys_privs;
user_role_privs	list of roles of the current user	connect user/password@sid; select * from user_role_privs;
v$nls_parameters	NLS settings of the connected user's database	select * from v$nls_parameters;
nls_database_parameters	current NLS settings of the database	select * from nls_database_parameters;
nls_instance_parameters	current NLS settings of the instance	select * from nls_instance_parameters;
nls_session_parameters	current NLS settings of the session	select * from nls_session_parameters;
v$nls_valid_values	view available valid NLS values	select * from v$nls_valid_values where parameter='CHARACTERSET' and VALUE like '%WIN%';	select all CHARACTERSET values containing "WIN" in their name
dba_errors	view errors	select * from dba_errors;
dba_scheduler_jobs	view information about scheduled tasks	select * from dba_scheduler_jobs;
dba_scheduler_job_run_details	view detailed information about executed tasks	select log_date , job_name , status , req_start_date , actual_start_date , run_duration from dba_scheduler_job_run_details;
dba_scheduler_job_log	view history of executed scheduled tasks	select log_date , job_name , status from dba_scheduler_job_log;	display the date, name, and status of executed tasks
dba_scheduler_running_jobs	view information about current tasks	select job_name , session_id , running_instance , elapsed_time , cpu_used from dba_scheduler_running_jobs;
dba_scheduler_programs	display all programs and their attributes	select * from dba_scheduler_programs;
dba_scheduler_program_args	display all programs and their arguments	select * from dba_scheduler_program_args;
dba_jobs	display all jobs	select * from dba_jobs;
dba_jobs_running	display all running jobs	select * from dba_jobs_running;
v$sga	display information about SGA	select * from v$sga;
v$sgastat	display SGA statistics	select * from v$sgastat;