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

These views can help you to find out exact query running into the system.

Please share your views about this article.

4 comments:

  1. excellent. very useful. and straight to the point .

    ReplyDelete
  2. Useful entry, thanks.

    ReplyDelete
  3. Very useful Bro,, Lot of Thanks

    ReplyDelete