Daily Usefull Oracle Database scripts

************Db  status script *****************************

SQL>select 'Hostname      : ' || host_name

      ,'Instance Name : ' || instance_name

      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime

      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||

       trunc( 24*((sysdate-startup_time) -

       trunc(sysdate-startup_time))) || ' hour(s) ' ||

       mod(trunc(1440*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||

       mod(trunc(86400*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' seconds' uptime

from sys.v_$instance;

********For RAC server use the below query************************

select 'Hostname      : ' || host_name

      ,'Instance Name : ' || instance_name

      ,'Started At    : ' || to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') stime

      ,'Uptime        : ' || floor(sysdate - startup_time) || ' days(s) ' ||

       trunc( 24*((sysdate-startup_time) -

       trunc(sysdate-startup_time))) || ' hour(s) ' ||

       mod(trunc(1440*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' minute(s) ' ||

       mod(trunc(86400*((sysdate-startup_time) -

       trunc(sysdate-startup_time))), 60) ||' seconds' uptime

from sys.gv_$instance;

                                      (or)

SQL> select to_char(start_time, ‘dd-mon-yy hh24:mi:ss’) start_time,

 to_char(end_time,‘dd-mon-yy hh24:mi:ss’) end_time

 from stats$dates;

************Which schemas  taking up all of the free space************

set pages 999

col "size MB" format 999,999,999

col "Objects" format 999,999,999

select      obj.owner "Owner"

,              obj_cnt "Objects"

,              decode(seg_size, NULL, 0, seg_size) "size MB"

from        (select owner, count(*) obj_cnt from dba_objects group by owner) obj

,              (select owner, ceil(sum(bytes)/1024/1024) seg_size

               from dba_segments group by owner) seg

where      obj.owner  = seg.owner(+)

order       by 3 desc ,2 desc, 1;

To check which tables are accessing frequently on the database********

 set lines 80;

set pages 999;

column avg_touches           format 999

column myname heading 'Name' format a30

column mytype heading 'Type' format a10

column buffers               format 999,999

SELECT  object_type mytype,object_name myname,blocks,COUNT(1) buffers,AVG(tch) avg_touches

FROM sys.x$bh a,dba_objects b,dba_segments s WHERE a.obj = b.data_object_id

and b.object_name = s.segment_name and b.owner not in ('SYS','SYSTEM')

GROUP BY   object_name,object_type,blocks,obj HAVING AVG(tch) > 5 AND  COUNT(1) > 20;

*****Rman backup job status************************************

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 V$RMAN_BACKUP_JOB_DETAILS

order by session_key;

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,   

       ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"  

FROM V$SESSION_LONGOPS   

WHERE OPNAME LIKE 'RMAN%'  

  AND OPNAME NOT LIKE '%aggregate%'  

  AND TOTALWORK != 0   

  AND SOFAR  != TOTALWORK;  

**To find out the table size****************************************

SQL> select segment_type, sum(bytes/1024/1024) size_mb from dba_segments  where OWNER ='V500' and SEGMENT_NAME='CR_REPORT_STYLE_PROFILE' group by segment_type;

*****Checked and found no segment could not extend****************

 SELECT s.owner, s.segment_type, s.segment_name,s.tablespace_name ,s.next_extent

       FROM dba_segments s

      WHERE s.next_extent * ( 1 + s.pct_increase / 100 ) >

      (SELECT MAX( f.bytes ) FROM dba_free_space f

         WHERE f.tablespace_name = s.tablespace_name );

no rows selected

***Displaying Segment Information********************************

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, BLOCKS, EXTENTS FROM DBA_SEGMENTS WHERE SEGMENT_TYPE = 'INDEX'     AND OWNER='ARADMIN' AND SEGMENT_NAME='T171_PERF_2';

***Query to check the tablespace usage metrics*******

SQL> select * from dba_tablespace_usage_metrics order by used_percent desc;

TABLESPACE_NAME                USED_SPACE TABLESPACE_SIZE USED_PERCENT

------------------------------ ---------- --------------- ------------

D_R_MEDIUM                         251784          286336   87.9330577

D_R_SMALL                          375408          438016   85.7064582

L_R_SMALL                          788560          933760   84.4499657

*****Here is a simple way to calculate the average number of transactions per second since database startup

select round(sum(s.value / (86400 * (SYSDATE - startup_time))),3) "TPS" from v$sysstat s ,v$instance i where s.NAME in ('user commits','transaction rollbacks');

*****script gives me the database growth as follows**************

select to_char(creation_time, 'RRRR Month') "MONTH",

sum(bytes)/1024/1024 "GROWTH IN MB"

from sys.v_$datafile

where creation_time >= SYSDATE-365

group by to_char(creation_time, 'RRRR Month');

MONTH GROWTH IN MB;

SQL>select to_char(CREATION_TIME,'RRRR') year, to_char(CREATION_TIME,'MM') month, round(sum(bytes)/1024/1024/1024) GB

 from   v$datafile group by to_char(CREATION_TIME,'RRRR'), to_char(CREATION_TIME,'MM') order by   1, 2;

how many  datafiles were added in month********

select 

  to_char(CREATION_TIME,'RRRR') year,

  to_char(CREATION_TIME,'MM') month,

  sum(bytes/1024/1024)  size_mb

 from    v$datafile

 group by

 to_char(CREATION_TIME,'RRRR'),

 to_char(CREATION_TIME,'MM')

 order by 

   1, 2;

This script will show "spaced used total" for a specific Oracle table, essentiall computing the Oracle table size over time************************

 col c1 format a15 heading 'snapshot|date' 

col c2 format a25 heading 'table|name' 

col c3 format 999,999,999 heading 'space|used|total'

select 

   to_char(begin_interval_time,'yy/mm/dd hh24:mm')     c1, 

   object_name c2,space_used_total c3 from 

   dba_hist_seg_stat       s, 

   dba_hist_seg_stat_obj   o, 

   dba_hist_snapshot       sn 

where    o.owner = 'V500' and    s.obj# = o.obj# and    sn.snap_id = s.snap_id and    object_name='CR_REPORT_STYLE_PROFILE' 

order by begin_interval_time;

****User quotas on tablespaces*********************************

select username

,      tablespace_name

,      decode(max_bytes, -1, 'unlimited'

       , ceil(max_bytes / 1024 / 1024) || 'M' ) "QUOTA"

from   dba_ts_quotas

where  tablespace_name not in ('TEMP');

************ Checking Active and Inactive session script **************

SQL>select sid,serial#,LOCKWAIT,STATUS,username from v$session;

SQL>select USERNAME,OSUSER,PROGRAM,LOGON_TIME from  v$session where status in ('ACTIVE');

SQL>select SID,SERIAL#,USERNAME,LOCKWAIT,STATUS,PROGRAM,LOGON_TIME from  v$session where USERNAME='XXXX' and STATUS='ACTIVE';

SQL>select USERNAME,OSUSER,PROGRAM,LOGON_TIME from  v$session where status in ('ACTIVE');

1

************To Chk is sorting is going on **************************

SQL>Select tablespace_name,USED_BLOCKS,FREE_BLOCKS,MAX_SORT_BLOCKS from   v$sort_segment;

SQL> Select   EXTENT_SIZE,CURRENT_USERS,TOTAL_EXTENTS,USED_EXTENTS,FREE_EXTENTS  from v$sort_segment;

SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free) FROM   V$temp_space_header    GROUP  BY tablespace_name;

SQL>select FILE_NAME,BYTES/1024/1024 from dba_temp_files where 

TABLESPACE_NAME='TEMP';

SQL> ALTER TABLESPACE <tablespace_name>   ADD  TEMPFILE < tempfile name > SIZE <Est_mb>m                              

                                                      OR 

SQL>ALTER DATABAS TEMPFILE <tempfile name>    RESIZE  < Current_size + Est_mb>m;

**** dead locks script***************

SQL>select 'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking

from v$lock l1, v$lock l2

where l1.block =1 and l2.request > 0

and l1.id1=l2.id1

and l1.id2=l2.id2;

************* List locks script************************************

column lock_type format a12

column mode_held format a10

column mode_requested format a10

column blocking_others format a20

column username format a10

SQL>SELECT session_id

, lock_type

, mode_held

, mode_requested

, blocking_others

, lock_id1

FROM dba_lock l

WHERE lock_type NOT IN ('Media Recovery', 'Redo Thread');

************ Show locked objects script***************************

set lines 100 pages 999

col username format a20

col sess_id format a10

col object format a25

col mode_held format a10

SQL>select oracle_username || ' (' || s.osuser || ')' username

, s.sid || ',' || s.serial# sess_id

, owner || '.' || object_name object

, object_type

, decode( l.block

, 0, 'Not Blocking'

, 1, 'Blocking'

, 2, 'Global') status

, decode(v.locked_mode

, 0, 'None'

, 1, 'Null'

, 2, 'Row-S (SS)'

, 3, 'Row-X (SX)'

, 4, 'Share'

, 5, 'S/Row-X (SSX)'

, 6, 'Exclusive', TO_CHAR(lmode)) mode_held

from v$locked_object v

, dba_objects d

, v$lock l

,v$session s

where v.object_id = d.object_id

and v.object_id = l.id1

and v.session_id = s.sid

order by oracle_username

, session_id;

************Show which row is locked script***********************

SQL>select do.object_name

, row_wait_obj#

, row_wait_file#

, row_wait_block#

, row_wait_row#

, dbms_rowid.rowid_create (1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, 

ROW_WAIT_BLOCK#, ROW_WAIT_ROW#)

from v$session s

, dba_objects do

where sid=&sid

and s.ROW_WAIT_OBJ# = do.OBJECT_ID;

*********Check Running jobs status script***************************

SQL>SELECT job,schema_user, to_char(last_date, 'mm/dd/yyyy hh:mi pm') lastdate, to_char(next_date, 'mm/dd/yyyy hh:mi pm') nextdate, failures, broken, what  FROM dba_jobs  ORDER BY next_date;

                                Or

SELECT 

 'Job:'|| job,

 WHAT,

 'Next:'|| to_char(NEXT_DATE,'dd-Mon-yyyy HH24:MI'),

 ' Last:'|| to_char(LAST_DATE,'dd-Mon-yyyy HH24:MI'),

 ' Broken:'|| BROKEN

 FROM dba_jobs;

                               Or

SQL>select JOB,LOG_USER,PRIV_USER,SCHEMA_USER,BROKEN,FAILURES,TOTAL_TIME from user_jobs;

Create a DBMS_STATS.gather_schema_stats job that runs daily at 11pm (23:00) ******************

 Set SERVEROUT ON

 Set pagesize 200

 

 DECLARE

 jobno NUMBER;

 BEGIN

 DBMS_JOB.submit

 (job => jobno,

 what => 'DBMS_STATS.gather_schema_stats(ownname => ''scott'', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);',

 next_date => trunc(sysdate)+23/24,

 interval => 'SYSDATE + 1',

 no_parse => TRUE );

 

 DBMS_OUTPUT.put_line ('Created Job - the job number is:' || TO_CHAR (jobno));

 COMMIT;

 END;

 /

******To detect Oracle long running queries script*******************

SET PAGESIZE 80
SET LINESIZE 100
COL SQL_TEXT WORD_WRAP
SELECT SID, SERIAL#, OPNAME, TARGET, SOFAR, TOTALWORK, UNITS,
TO_CHAR(START_TIME,'DD/MON/YYYY HH24:MI:SS') START_TIME,
TO_CHAR(LAST_UPDATE_TIME,'DD/MON/YYYY HH24:MI:SS') LAST_UPDATE_TIME,
TIME_REMAINING, ELAPSED_SECONDS, MESSAGE, USERNAME
FROM V$SESSION_LONGOPS
WHERE TIME_REMAINING != 0;

***To detect long operations**************************************

select sid,username,opname,sql_address,

        to_char(start_time ,'mm/dd/yyyy hh24:mi:ss') as sttime,

        to_char(last_update_time,'mm/dd/yyyy hh24:mi:ss') as luptime,

        to_char(time_remaining,'9999999999') as timeremain,

        to_char(elapsed_seconds,'999999999') as elapstime

from v$session_longops a

join v$sqltext_with_newlines b on b.address=a.sql_address

where  (last_update_time - start_time) >= (1/24/4)

order by sid,opname,a.sql_address,last_update_time desc;

**************To Show all running SQLs in the Db**************

SQL>select distinct spid, s.sid, s.serial#,to_char(sysdate -

last_call_et/(24*3600),'mm/dd/yyhh24:mi:ss') "LAST_ACTIVITY",

       logon_time,

       osuser,

       s.program,

       schemaname,

       sql_text

  from v$session s,

       v$process p,

       v$sql t

  where s.paddr=p.addr

    and t.hash_value(+)=s.sql_hash_value

    and s.type !='BACKGROUND';

Query to check which user process has occupied high memory usage*****

SQL> SELECT spid, program,

            pga_max_mem      max,

            pga_alloc_mem    alloc,

            pga_used_mem     used,

            pga_freeable_mem free

       FROM V$PROCESS

      WHERE spid = 471102;

***what the session is doing and how much CPU they have consumed******

SQL>select ss.sid,se.command,ss.value CPU ,se.username,se.program 

from v$sesstat ss, v$session se

where ss.statistic# in 

(select statistic# 

from v$statname 

where name = 'CPU used by this session')

and se.sid=ss.sid 

and ss.sid>6

order by ss.sid;

*****Users and Sessions CPU consumption can be obtained by below query*****

Set lines 1000

select ss.username, se.SID,VALUE/100 cpu_usage_seconds

from v$session ss, v$sesstat se, v$statname sn

where se.STATISTIC# = sn.STATISTIC#

and NAME like '%CPU used by this session%'

and se.SID = ss.SID and ss.status='ACTIVE'

and ss.username is not null

order by VALUE desc;

 shows Day wise,User wise,Process id of server wise- CPU and I/O consumption****

set linesize 140

col spid for a6

col program for a35 trunc

select p.spid SPID,to_char(s.LOGON_TIME,'DDMonYY HH24:MI') date_login,s.username,decode(nvl(p.background,0),1,bg.description, s.program ) program,

ss.value/100 CPU,physical_reads disk_io,(trunc(sysdate,'J')-trunc(logon_time,'J')) days,

round((ss.value/100)/(decode((trunc(sysdate,'J')-trunc(logon_time,'J')),0,1,(trunc(sysdate,'J')-trunc(logon_time,'J')))),2) cpu_per_day

from V$PROCESS p,V$SESSION s,V$SESSTAT ss,V$SESS_IO si,V$BGPROCESS bg

where s.paddr=p.addr and ss.sid=s.sid

and ss.statistic#=12 and si.sid=s.sid

and bg.paddr(+)=p.addr

and round((ss.value/100),0) > 10

order by 8;

*****To find out what sql  session(s) are executing******

SQL>select s.sid, event, wait_time, w.seq#, q.sql_text

from v$session_wait w, v$session s, v$process p, v$sqlarea q

where s.paddr=p.addr and

s.sid=&p and s.sql_address=q.address;

                       or

SELECT SQL_TEXT

FROM V$SQLAREA

WHERE (ADDRESS, HASH_VALUE) IN

      (SELECT SQL_ADDRESS, SQL_HASH_VALUE

       FROM V$SESSION

       WHERE SID = &sid_number);

               or

SQL>SELECT sql_id, child_number, sql_text FROM v$sql WHERE sql_text LIKE '%online discount%' AND sql_text NOT LIKE '%v$sql%';

****To check username,osuser,terminal,sid,serial# connected to the Database*****

SELECT USERNAME, OSUSER, TERMINAL, SID, SERIAL#,

       PROCESS, PROGRAM FROM V$SESSION ORDER BY OSUSER;

               

*****Show all connected users*********************

set lines 100 pages 999

col ID format a15

select username

,      sid || ',' || serial# "ID"

,      status

,      last_call_et "Last Activity"

from   v$session

where  username is not null

order by status desc

,        last_call_et desc;

*****Time since last user activity*********************

set lines 100 pages 999

select username

,      floor(last_call_et / 60) "Minutes"

,      status

from   v$session

where  username is not null

order by last_call_et;

******Show tables that have analyze dates older than today**********

select     count(last_analyzed) left_to_do

from      dba_tables

where    owner = '&schema'

and       trunc(last_analyzed) < trunc(sysdate)

order     by 1;

                                   OR

SELECT OWNER,TABLE_NAME, NUM_ROWS,LAST_ANALYZED,DURATION FROM DBA_TABLES WHERE OWNER='ARADMIN' AND LAST_ANALYZED < (SYSDATE -7) AND NUM_ROWS >100

                                  OR

select OWNER,TABLE_NAME,LAST_ANALYZED,NUM_ROWS,STATUS from dba_tables where OWNER=’ARADMIN’ order by LAST_ANALYZED;

Enjoy the work and do like me if you like this article!!!