SQL QUERIES FOR DAILY DBA ROUTINE TASK

 

SQL QUERIES:

---------DATABASE NAME------

select name,open_mode,log_mode,database_role from v$database;

SELECT COUNT(NAME) FROM V$TABLESPACE;

SELECT COUNT(NAME) FROM V$DATAFILE_HEADER;

-----INSTANCE------

select status from v$instance;

----------------db size--------------

select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) d;

-----TOTAL DATABASE USAGE-----:

select (select sum(bytes/1048576) from dba_data_files) "Data Mb",
(select NVL(sum(bytes/1048576),0) from dba_temp_files) "Temp Mb",
(select sum(bytes/1048576)*max(members) from v$log) "Redo Mb",
(select sum(bytes/1048576) from dba_data_files) +
(select NVL(sum(bytes/1048576),0) from dba_temp_files) +
(select sum(bytes/1048576)*max(members) from v$log) "Total Mb"
from dual;

----DB PHYSICAL SIZE----:

select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;

-----DB ACUTAL SIZE-----:

select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;

----------TABLESPACE---------

select TABLESPACE_NAME,STATUS,AUTOEXTENSIBLE, ONLINE_STATUS from dba_data_files;

set pagesize 100

set lines 130

COLUMN free_space_mb format 99999990.00

COLUMN allocated_mb format 99999990.00

COLUMN used_mb format 99999990.00

COLUMN percent_used format 00.00

col tablespace_name format a25

SELECT  SUBSTR(df.tablespace_name,1,30) tablespace_name,
(df.bytes) / 1024 / 1024 allocated_mb,
((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,
NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,
 round(  ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) percent_used
FROM    ( select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) df,
( select  sum(bytes) bytes , tablespace_name from dba_free_space group by tablespace_name) dfs
WHERE   df.tablespace_name = dfs.tablespace_name
ORDER BY percent_used ;

-------------TABLESPACE>75----------------------------------------

#alter session set "_hash_join_enabled"=true;

set pagesize 100

set lines 130

COLUMN free_space_mb format 99999990.00

COLUMN allocated_mb format 99999990.00

COLUMN used_mb format 99999990.00

COLUMN percent_used format 00.00

col tablespace_name format a25

SELECT  SUBSTR(df.tablespace_name,1,30) tablespace_name,
(df.bytes) / 1024 / 1024 allocated_mb,
((df.bytes)-nvl(dfs.bytes,0))/1024/1024 used_mb,
NVL ((dfs.bytes) / 1024 / 1024, 0) free_space_mb,
 round(  ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) percent_used
FROM    ( select sum(bytes) bytes,tablespace_name from dba_data_files group by tablespace_name) df,
( select  sum(bytes) bytes , tablespace_name from dba_free_space group by tablespace_name) dfs
WHERE   df.tablespace_name = dfs.tablespace_name
and round(  ((df.bytes-nvl(dfs.bytes,0))/df.bytes * 100),2) > 75
ORDER BY percent_used ;

 -------------ADDING TABLESPACE---------

col FILE_NAME format a70
set lines 1000
set pages 1000

Select file_name,bytes/1024/1024,autoextensible from dba_data_files where tablespace_name='&TBLSPC_NAME' order by file_id;

col FILE_NAME format a70
set lines 1000
set pages 1000

Select file_name,bytes/1024/1024,autoextensible from dba_temp_files where tablespace_name='&TBLSPC_NAME';

Alter tablespace tablespace_name add datafile ‘path and name+1.dbf’ size 4096m;

------------Archivegap-------------------

select max(sequence#)"Last Sequence" from v$loghist;

select  max(SEQUENCE#)"Applied Sequence" from v$archived_log where APPLIED='YES' and dest_id=2;

----------Temp Tablespace-----------------

SET LINESIZE 200
COL TABLESPACE_NAME FORMAT A20
SELECT TABLESPACE_NAME,SUM(BYTES_USED/1024/1024) "USED_MB",SUM(BYTES_FREE/1024/1024) "FREE_MB",
SUM(BLOCKS_FREE) "FREE_BLOCKS"
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME;

------------------Log sequence-----------------

select max(sequence#) from v$loghist;

show parameter log_archive_dest_state_2

------------------Resource_Limit-----------------

select * from v$resource_limit;

-------COUNT OF INVALID OBJECTS---------:

select count(*) from dba_objects where status='INVALID';

------DB HIT Ratio-------:

select (1-(pr.value/

(dbg.value+cg.value)))*100

FROM v$sysstat pr,v$sysstat dbg,v$sysstat cg

WHERE Pr.name='physical reads'

AND dbg.name='db block gets'

AND cg.name='consistent gets';

 =============================LOAD======================================

select stat_name,value from v$OSSTAT where STAT_NAME='LOAD';

 =================================EVENT=================================

select event,count(event) from v$session group by event;

 =================================Session==================================

select username,status,count(*) from v$session group by username,status order by 1,3;

select count(*)"Active sessions" from v$session;

==============================Number of Session========================================

SET LINESIZE 145
SET PAGESIZE 9999
 COLUMN max_sess_allowed  FORMAT 999,999       JUSTIFY r HEADING 'Max sessions allowed'
 COLUMN num_sessions      FORMAT 999,999,999   JUSTIFY r HEADING 'Number of sessions'
 COLUMN pct_utl           FORMAT a19           JUSTIFY r HEADING 'Percent Utilization'
 COLUMN username          FORMAT a15           JUSTIFY r HEADING 'Oracle User'
 COLUMN num_user_sess     FORMAT 999,999       JUSTIFY r HEADING 'Number of Logins'
 COLUMN count_a           FORMAT 999,999       JUSTIFY r HEADING 'Active Logins'
 COLUMN count_i           FORMAT 999,999       JUSTIFY r HEADING 'Inactive Logins'

PROMPT =================================Oracle Users Session========================================

SET verify off

SELECT
       TO_NUMBER(a.value)         max_sess_allowed
     , TO_NUMBER(count(*))        num_sessions
     , LPAD(ROUND((count(*)/a.value)*100,0) || '%', 19)  pct_utl
     FROM v$session    b, v$parameter  a
     WHERE a.name = 'sessions'
   GROUP BY a.value;

break on report

 compute sum of num_user_sess count_a count_i on report

 SELECT
       lpad(nvl(sess.username, '[B.G. Process]'), 15) username
     , count(*) num_user_sess
     , nvl(act.count, 0)   count_a
     , nvl(inact.count, 0) count_i
     FROM
         v$session sess
       , (SELECT    count(*) count, nvl(username, '[B.G. Process]') username
          FROM      v$session
         WHERE     status = 'ACTIVE'
         GROUP BY  username)   act
      , (SELECT    count(*) count, nvl(username, '[B.G. Process]') username
         FROM      v$session
         WHERE     status = 'INACTIVE'
         GROUP BY  username) inact
    WHERE
             nvl(sess.username, '[B.G. Process]') = act.username (+)
         and nvl(sess.username, '[B.G. Process]') = inact.username (+)
    GROUP BY
        sess.username
      , act.count
      , inact.count;

Prompt================================ SQL row lock contention========================================

set linesize 1000

set pagesize 1000

col USERNAME format a10

select c.USERNAME, 
       a.SID, 
       c.SQL_ID, 
       a.EVENT, 
       a.STATE, 
       c.STATUS, 
       b.TIME_WAITED
from v$session_wait a,
     v$session_wait_class b, 
     v$session c
where a.SID=b.SID
and   b.SID=c.SID
and   c.SQL_ID is not null
and   b.TIME_WAITED<>0 
and   a.EVENT like '%row lock contention%'
order by 7;

Prompt================================= Table lock======================================================

select s1.username || '@' || s1.machine|| ' ( SID=' || s1.sid || ' )  is blocking by '|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2
and l1.type=l2.type;

Prompt================================ ASM DISK GROUP STATUS===================================

SELECT name,free_mb/1024 "Free in GB",total_mb/1024 as Total_GB from v$asm_diskgroup;

Prompt==================================User Expiry Details===========================

select USERNAME,EXPIRY_DATE,PROFILE from dba_users where account_status='OPEN';

Prompt==================================TABLE MAX SIZE IN MB=================================

set linesize 1000

set pagesize 750

col Table_Name for a20

col owner for a15

col size for a10

select segment_name as "Table_Name",owner, round(bytes/1024/1024) || 'MB' as "Size"

from dba_segments where bytes>2073741824 and owner Not IN ('SYS','SYSTEM') 

order by bytes desc;

Prompt==================================TABLE MAX SIZE IN GB=================================

select segment_name as "Table_Name",owner, round(bytes/1024/1024/1024) || 'GB' as "Size"

from dba_segments where bytes>2073741824 and owner Not IN ('SYS','SYSTEM') 

order by bytes desc;

Prompt==================================ORA ERROR IN LAST 15 DAYS=================================

col message_text for a999

SELECT record_id,to_char(originating_timestamp,'DD.MM.YYYY HH24:MI:SS'),

message_text

from x$dbgalertext where message_text like '%ORA-%'

and ORIGINATING_TIMESTAMP > SYSDATE -15;

---------RMAN BACKUP STATUS--------------:

select
session_recid,
to_char(start_time,'YYYY-MM-DD HH24:MI:SS')  start_time,
to_char(end_time,'YYYY-MM-DD HH24:MI:SS')    end_time,
TRUNC(((end_time - start_time)/(1/365))*3600)   executetimemins,
status,
input_type,
output_device_type  ,
round((input_bytes/1024)/1024,2)       input_mb,
round((output_bytes/1024)/1024,2)      output_mb,
output_bytes_display,
time_taken_display
from SYS.V_$RMAN_BACKUP_JOB_DETAILS
where
start_time >= sysdate - 365
and input_type='DB FULL'
order by end_time desc ;

col STATUS format a9

col hrs format 999.99

select

SESSION_KEY, INPUT_TYPE, STATUS,

to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,

to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,

elapsed_seconds/3600                   hrs

from SYS.V_$RMAN_BACKUP_JOB_DETAILS

where input_type='DB INCR'

order by session_key;

SELECT 

SUBSTR(SS.USERNAME,1,8) USERNAME,

SS.OSUSER "USER",

AR.MODULE || ' @ ' || SS.MACHINE CLIENT,

SS.PROCESS PID,

TO_CHAR(AR.LAST_LOAD_TIME, 'DD-Mon HH24:MM:SS') LOAD_TIME,

AR.DISK_READS DISK_READS,

AR.BUFFER_GETS BUFFER_GETS,

SUBSTR(SS.LOCKWAIT,1,10) LOCKWAIT,

W.EVENT EVENT,

SS.STATUS,

AR.SQL_fullTEXT SQL

FROM V$SESSION_WAIT W,

V$SQLAREA AR,

V$SESSION SS, 

v$TIMER T

WHERE SS.SQL_ADDRESS = AR.ADDRESS

AND SS.SQL_HASH_VALUE = AR.HASH_VALUE

AND SS.SID = W.SID (+)

AND SS.STATUS = 'ACTIVE'

AND W.EVENT != 'client message'

ORDER BY  SS.LOCKWAIT ASC, SS.USERNAME, AR.DISK_READS DESC

<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-1462431493865865" crossorigin="anonymous"></script>