COMMON USED DICTIONARY VIEWS-ORACLE DBA-APPSDBA

leave a comment

Here I list the Dictionary Views that can one oracle dba & appsdba use most of the

times.

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