DBA Views

DBA VIEW: Top 10 to keep Handy

DBA View is the key role player for getting database related information. You want to know anything about database, DBA VIEW is the final solution for this.Though, Each DBA VIEW has it's own significance and purpose.

DBA VIEWS doesn't have any impact of Single instance or RAC instance because they are directly
related to Database not an Instance. So, a DBA need not to worry about which instance to look at or putting any extra  G" like GV$ before these views.

Here, I am listing few most commonly used DBA VIEWS.

1. Dictionary view

This DBA View is most important view, the reason is very simple because this view has detail of each and every possible view in the database. There are five types of view in the database.

A. DBA Views: Starts with "DBA_" like DBA_SEGMENTS, DBA_USERS, DBA_INDEXES

B. User Views: Starts with "USER_" like USER_SEGMENTS, USER_TABLES,    USER_INDEXES

C. All Views: Starts with "ALL_" like ALL_OBJECT_TABLES, ALL_SEQUENCES, ALL_IND_COLUMNS

D. V$ Views: There views are for single instance database parameters or values. starts with "v$" like V$TEMPFILE, V$UNDOSTAT, V$VERSION etc.

E. GV$ Views: These views are for Real Application cluster environment. "G" is placed in front
    of V$views to make it useful for cluster wide stats.

SQL> SELECT * FROM DICT;

This query will list all the view into the database.

SQL> SELECT * FROM DICT WHERE TABLE_NAME LIKE 'DBA_%' ORDER BY TABLE_NAME;

This query will list all the view starting with "DBA_" into the database.

2. Display List of Users

In every database, first thing remote DBA wants to know is who and how many Database users are available.DBA_USERS is the view to list this.

SQL> desc dba_users;

SQL> select USERNAME, ACCOUNT_STATUS, LOCK_DATE, DEFAULT_TABLESPACE , CREATED from dba_users;

USERNAME	ACCOUNT_STATUS	LOCK_DATE	DEFAULT_TA	CREATED
SYS	OPEN		SYSTEM	12-MAR-13
TC	OPEN		USERS	29-APR-13
SCOTT	OPEN		USERS	03-APR-13
SYSTEM	LOCKED(TIMED)	09-APR-13	SYSTEM	12-MAR-13
WMSYS	EXPIRED & LOCKED	12-MAR-13	SYSAUX	12-MAR-13
XDB	EXPIRED & LOCKED	12-MAR-13	SYSAUX	12-MAR-13
APPQOSSYS	EXPIRED & LOCKED	12-MAR-13	SYSAUX	12-MAR-13
OUTLN	EXPIRED & LOCKED	12-MAR-13	USERS	12-MAR-13
ORACLE_OCM	EXPIRED & LOCKED	12-MAR-13	USERS	12-MAR-13

9 rows selected.

From above list, Important things to look at are "ACCOUNT_STATUS". This must be open to connect to the user. if this is lock user can't connect to database.

DEFAULT_TABLESPACE, For application users system and sysaux should not be the default tablespaces because these are Database related tablespaces. You can also cross check about existence of a user from this list.

3. Find Size of Database Objects

A common error message faced by database administrator is "ORA-1653 unable to extend table %s.%s by # in tablespace %s" . This means table space has reached it's size limit.

Now, first things remote DBA needs to check is what are the objects taking maximum size into tablespace. Do we really need them or we can reduce their size to get some free space. Here DBA_SEGMENTS is the view to help.

SQL> desc dba_segments
SQL> set pagesize 50000
SQL> set linesize 120
SQL> COLUMN SEGMENT_NAME FORMAT A20;

SQL>SELECTOWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/(1024*1024) "SIZE IN MB" FROM DBA_SEGMENTS WHERE TABLESPACE_NAME='USERS';

OWNER	SEGMENT_NAME	SEGMENT_TYPE	TABESPACE_NAME	SIZE IN MB
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	10625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	.0625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	.0625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	.0625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	.0625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	.0625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	.0625
REMOTE_DBA	MUH_FILS	TABLE PARTITION	USERS	543625

Above output clearly shows, which user object has taken how much space in "USERS" table space, so DBA can act accordingly.

4. Display List of Data Files

Data files are files having actual data stored at OS level, There are situations like corruption, high disk utilization, movement of datafiles when DBA needs to check exact location of datafile. DBA_DATA_FILES is the answer of all there questions.

SQL> desc dba_data_files;
SQL> COLUMN FILE_NAME FORMAT A50;
SQL> COLUMN AUTOEXTENSIBLE FORMAT A20;
SQL> SELECT FILE_NAME, BYTES, STATUS, AUTOEXTENSIBLE FROM DBA_DATA_FILES;

FILE_NAME	BYTES	STATUS	AUTO EXTENSIBLE
+DATA1/dba4/datafile/system.262.809881673	1.7180E+10	AVAILABLE	YES
+DATA1/dba4/datafile/sysaux.263.809881683	1.7180E+10	AVAILABLE	YES
+DATA1/dba4/datafile/undotbs1.264.809881689	1.7180E+10	AVAILABLE	YES
+DATA1/dba4/datafile/undotbs2.266.809881703	1.7180E+10	AVAILABLE	YES
+DATA1/dba4/datafile/users.267.809881709	1073741824	AVAILABLE	YES

Thare are few very important fields to look at "file_name" Define actual physical location of data file. If you find "+" prfix with file name, this means file system is using ASM.

"BYTES" defines the size of file in byets, "STATUS" AVAILABLE or INVALID (INVALID means that the file number is not in use. "AUTOEXTENSIBLE" options "YES" or "NO".
Yes says file size will increase as an when requires and "NO" says size of the file is fixed unti change manually by DBA.

5. Tablespace Free Space Repot

To check free space available in each tablespaces is another day to day job of DBA. To achieve this DBA_FREE_SPACE is the view.

SQL> desc DBA_FREE_SPACE;

To get meaning full report, we have done some manipulation in query,

SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/(1024*1024) "FREE SPACE IN MB" FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

TABLESPACE_NAME	FREE SPACE IN MB
SYSAUX	15930.875
UNDOTBS1	16349.375
USERS	702.125
SYSTEM	5919.625
UNDOTBS2	16380.8125

This result shows, which tablespace has how much free space available, so DBA can re size accordingly.

6. Location of Temp Files

Temp files are the files, which does all sorting operations in the database and these files are not displayed in dba_data_files views. Since, there is a separate view for temp files.

SQL> DESC DBA_TEMP_FILES;

SQL> SELECT FILE_NAME,TABLESPACE_NAME,BYTES/(1024*1024) "SIZE IN MB" FROM DBA_TEMP_FILES;

FILE_NAME	TABLESPACE_NAME	SIZE IN MB
+DATA1/dba4/tempfile/temp.265.809882231	TEMP	32768

Above view shows, location, tablespace_name and size of temporary tablespace. Sometimes there are situations when temporary tablespace is full and you have to drop and recreate it.

7. List of Scheduled and Running Jobs in Database

In data warehousing and batch processing kind of environment, huge data is processed, in that case most of the task are done by scheduled jobs. So dba_jobs and dba_jobs_running are very important dba views to look at.

SQL> DESC DBA_JOBS;

SQL> SELECT JOB, SCHEMA_USER, LAST_DATE, THIS_DATE, NEXT_DATE , NEXT_SEC,   WHAT 

FROM DBA_JOBS;

The above query will list job details scheduled into the database. To check current running jobs in database use below query.

SQL> DESC DBA_JOBS_RUNNING;

SQL> SELECT SID,JOB,THIS_DATE,THIS_SEC,INSTANCE 

FROM DBA_JOBS_RUNNING;

This query can tell you, "SID" session ID of job, "THIS_DATE,THIS_SEC" current running time and "INSTANCE" number of instance i.e on which instance this job is running. This is helpful in case of RAC environment.

8. Database Directories

As we know, To upload some data into database like external files, Data pump data etc. files must be placed in Database Directories which are created and maintained by DBA's. Below is the command to list database directories

SQL> DESC DBA_DIRECTORIES;

SQL> COLUMN OWNER FORMAT A10;
SQL>  COLUMN DIRECTORY_PATH FORMAT A60;

SQL> SELECT * FROM DBA_DIRECTORIES;

OWNER	DIRECTORY_NAME	DIRECTORY_PATH
SYS	DATA_PUMP_DIR	/u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/log/

Here DATA_PUMP_DIR is the default directory created at time of database creation.

9. Show user Profile Details

Each DB user has a profile assigned to it by default, Each profile has resource limitations and some other policies related to password defined. There has been situations when DBA face "ORA-28001: the password has expired" which is mainly because the password resource limit defined in user profile has finished.

So remote DBA has to keep an eye on profiles assigned to users.

SQL> DESC DBA_PROFILES;

SQL> SELECT * FROM DBA_PROFILES;

PROFILE	RESOURCE_NAME	RESOURCE	LIMIT
DEFAULT	COMPOSITE_LIMIT	KERNEL	UNLIMITED
DEFAULT	SESSIONS_PER_USER	KERNEL	UNLIMITED
DEFAULT	CPU_PER_SESSION	KERNEL	UNLIMITED
DEFAULT	CPU_PER_CALL	KERNEL	UNLIMITED
DEFAULT	LOGICAL_READS_PER_SESSION	KERNEL	UNLIMITED
DEFAULT	LOGICAL_READS_PER_CALL	KERNEL	UNLIMITED
DEFAULT	IDLE_TIME	KERNEL	UNLIMITED
DEFAULT	CONNECT_TIME	KERNEL	UNLIMITED
DEFAULT	PRIVATE_SGA	KERNEL	UNLIMITED
DEFAULT	FAILED_LOGIN_ATTEMPTS	PASSWORD	3
DEFAULT	PASSWORD_LIFE_TIME	PASSWORD	90
DEFAULT	PASSWORD_REUSE_TIME	PASSWORD	365
DEFAULT	PASSWORD_REUSE_MAX	PASSWORD	20
DEFAULT	PASSWORD_VERIFY_FUNCTION	PASSWORD	NULL
DEFAULT	PASSWORD_LOCK_TIME	PASSWORD	1
DEFAULT	PASSWORD_GRACE_TIME	PASSWORD	3

16 rows selected.

Here, DEFAULT is the profile name created at time of instance creation. This profile is assigned to each Database user created into DB. You can manipulate this profile or you can also make you own profie as per need.

10. List of Historical and Current SQL Commands Running

A remote DBA working on performance related issue, looks into v$session views many times to find out currently what is running on the system. This view has information about SQL_ID running into the system but not the sql text. So, to find out exact sql commands running currently in the sessions.

DBA can take help from v$sqltext or gv$sqltext in case of Real Application Cluster, which has sql_Id and SQL_text for this id.

SQL> desc v$sqltext;

SQL> SELECT SQL_ID,SQL_TEXT FROM V$SQLTEXT WHERE ROWNUM < 3;

SQL_ID	SQL_TEXT
7q1qmwv82w006	;
7q1qmwv82w006	35,0,0,nv,nv,TO_DATE('2013-03-23 14:49:02',df),nv,nv,nv,2,nv;EN

If, DBA need historical data from this view, He has to user dba_hist_sqltext view.

SQL> SELECT SQL_ID,SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE ROWNUM < 3;

SQL_ID	SQL_TEXT
f3223cb4ng6hq	select next_run_date, obj#, run_job, sch_job from (select decode(bitand(a.flags
fd4aspkbkqs4u	SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESU