Most useful Dictionary and Views for oracle dba

Most useful Dictionary and Views for oracle dba .

Data Dictionary
Which users are in the database password file:
V$PWFILE_USERS

Where values set in the init.ora file can be viewed – all parameters:
V$PARAMETER

Script used to create the objects that comprise the data dictionary:
catalog.sql

To grant a special role to users so they can look at DBA views:
SELECT_CATALOG_ROLE

Information about all database objects in the database:
DBA_OBJECTS

Information about all tables in the database:
DBA_TABLES

Information about all indexes in the database:
DBA_INDEXES

Information about all views (including dictionary views) in the database:
DBA_VIEWS

Information about all sequences in the database:
DBA_SEQUENCES

Information about all users in the database:
DBA_USERS

Information about all constraints in the database:
DBA_CONSTRAINTS

Information about all table columns that have constraints on them:
DBA_CONS_COLUMNS

Information about all columns that have indexes on them in the database:
DBA_IND_COLUMNS

Information about all columns in all the tables in the database:
DBA_TAB_COLUMNS


Information about all the roles in the database:
DBA_ROLES

Information about all object privileges in the database:
DBA_TAB_PRIVS

Information about all system privileges granted to all users in the database:
DBA_SYS_PRIVS

Displays all PL/SQL source code in the database:
DBA_SOURCE

Information about all triggers in the database:
DBA_TRIGGERS

Information about object privileges granted to roles
ROLE_TAB_PRIVS

Information about system privileges granted to roles
ROLE_SYS_PRIVS

Information about roles granted to roles
ROLE_ROLE_PRIVS

Information about all tablespaces in the database:
DBA_TABLESPACES

Information about all profiles in the database:
DBA_PROFILES


For all parameters?
V$PARAMETER

General information about the database mounted to your instance:
V$DATABASE

Most information about the performance of the database is kept here:
V$SYSSTAT

Most information about the performance for individual user sessions is stored here:
V$SESSION , V$SESSTAT

Information about online redo logs (2)
V$LOG, V$LOGFILE

Information about datafiles
V$DATAFILE

Basic information about control files, and the two columns it has:
V$CONTROLFILE. STATUS / NAME

An object you can query to obtain a listing of all data dictionary objects (4)
CATALOG, CAT, DICTIONARY, DICT.

When the control file was created, Sequence Number, most recent SCN:
V$DATABASE

Information stored in different sections of the control file, Sequence Number:
V$CONTROLFILE_RECORD_SECTION

To see the names and locations of all control files in the db? (2)
V$PARAMETER. V$CONTROLFILE


Tablespace and Datafiles
Temporary Segments:
Name, tablespace location, and owner of temporary segments:
DBA_SEGMENTS

Size of temporary tablespaces, current number of extents allocated to sort segments, and sort segment high-water mark information. Space usage allocation for temporary segments:
V$SORT_SEGMENT

Types of sorts that are happening currently on the database
V$SORT_USAGE

To see the username corresponding with the session:
V$SESSION

Information about every datafile in the database associated with a temporary tablespace:
DBA_TEMP_FILES

Similar to DBA_TEMP_FILES, this performance view gives Information about every datafile in the database associated with a temporary tablespace:
V$TEMPFILE

Storage Structures
A summary view, contains all types of segments and their storage parameters, space utilization settings:
DBA_SEGMENTS

Tablespace quotas assigned to users:
DBA_TS_QUOTAS

Segment name, type, owner, total bytes of extent, name of tablespace storing the extent:
DBA_EXTENTS

The location and amount of free space by tablespace name:
DBA_FREE_SPACE

The location of free space in the tablespace that has been coalesced:
DBA_FREE_SPACE_COALESCED

Information about datafiles for every tablespace
DBA_DATAFILES

Performance view for information for datafiles for every tablespace
V$DATAFILE

To see the total amount of space allocated to a table?
DBA_EXTENTS

Table creation timestamp, information about the object ID:
DBA_OBJECTS

High water mark, all storage settings for a table, and statistics collected as part of the analyze (for row migration) operation on that table
DBA_TABLES

Information about every column in every table:
DBA_TAB_COLUMNS

To determine how many columns are marked unused for later removal?
DBA_UNUSED_COL_TABS

To find the number of deleted index entries ?
INDEX_STATS

To determine the columns on a table that have been indexed:
DBA_ID_COLUMNS

The dynamic view to show whether the index is being used in a meaningful way?
V$OBJECT_USAGE

To see whether a constraint exists on a particular column?
DBA_CONS_COLUMNS
To see the constraints associated with a particular table:
DBA_CONSTRAINTS

To find the username, ID number, (encrypted) password, default and temporary tablespace information, user profile of a user, password expiry date:
DBA_USERS

To all objects, which objects belong to which users, how many objects a user has created?
DBA_OBJECTS

Resource-usage parameters for a particular profile:
DBA_PROFILES

Identifies all resources in the database and their corresponding cost:
RESOURCE_COST

Identifies system resource limits for individual users:
USER_RESOURCE_LIMITS

Shows all system privileges:
DBA_SYS_PRIVS

Show all object privileges:
DBA_TAB_PRIVS

Shows all privileges in this session available to you as the current user:
SESSION_PRIVS

Views for audits currently taking place are created by this script:
cataudit.sql

a list of audit entries generated by the exists option of the audit command:
DBA_AUDIT_EXISTS

A list of audit entries generated for object audits:
DBA_AUDIT_OBJECT

A list of audit entries generated by session connects and disconnects:
DBA_AUDIT_SESSION

A list of audit entries generated by statement options of the audit command:
DBA_AUDIT_STATEMENT

A list of all entries in the AUD$ table collected by the audit command:
DBA_AUDIT_TRAIL

To determine the roles available in the database, the names of all the roles on the database and if a password is required to use each role:
DBA_ROLES

Names of all users and the roles granted to them:
DBA_ROLE_PRIVS

All the roles and the roles that are granted to them:
ROLE_ROLE_PRIVS

Which system privileges have been granted to a role:
DBA_SYS_PRIVS

All the system privileges granted only to roles:
ROLE_SYS_PRIVS

All the object privileges granted only to roles:
ROLE_TAB_PRIVS

All the roles available in the current session:
SESSION_ROLES

Which object privilege has been granted to a role:
DBA_TAB_PRIVS

To display the value of the NLS_CHARACTERSET parameter:
NLS_DATABASE_PARAMETERS







Looking forward for your suggestions.. please feel free to write to me.. Your help would definitely help to improve our blog. Chetan Yadav