Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Administration’ Category

« Previous Entries

Script to get Tablespace total, free and used space

Posted by Pavan DBA on February 21, 2012

set lines 300

SELECT /* + RULE */  df.tablespace_name “Tablespace”,
       df.bytes / (1024 * 1024) “Size (MB)”,
       round(SUM(fs.bytes) / (1024 * 1024)) “Free (MB)”,
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
       Round((df.bytes – SUM(fs.bytes)) / 1024/1024) “Used space”,
       Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
 order by  “% Used” desc;

Posted in Administration, Scripts | Tagged: , , , , , , | Leave a Comment »

Script to know which SQL’s are generating redo

Posted by Pavan DBA on February 7, 2012

SELECT when, sql, SUM(sx) executions, sum (sd) rows_processed 
FROM ( 
      SELECT to_char(begin_interval_time,’YYYY_MM_DD HH24′) when, 
             dbms_lob.substr(sql_text,4000,1) sql, 
             dhss.instance_number inst_id, 
             dhss.sql_id, 
             sum(executions_delta) exec_delta, 
             sum(rows_processed_delta) rows_proc_delta 
        FROM dba_hist_sqlstat dhss, 
             dba_hist_snapshot dhs, 
             dba_hist_sqltext dhst 
        WHERE upper(dhst.sql_text) LIKE ‘%Z_PLACENO%’ 
          AND ltrim(upper(dhst.sql_text)) NOT LIKE ‘SELECT%’
          AND dhss.snap_id=dhs.snap_id 
          AND dhss.instance_Number=dhs.instance_number 
          AND dhss.sql_id = dhst.sql_id  
          AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                      AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
        GROUP BY to_char(begin_interval_time,’YYYY_MM_DD HH24′), 
            dbms_lob.substr(sql_text,4000,1), 
              dhss.instance_number, 
             dhss.sql_id 
) 
group by when, sql;

Posted in Administration, Scripts | Tagged: , , , , | Leave a Comment »

Script to identify segments generating redologs

Posted by Pavan DBA on February 7, 2012

SELECT to_char(begin_interval_time,’YY-MM-DD HH24′) snap_time, 
        dhso.object_name, 
        sum(db_block_changes_delta) BLOCK_CHANGED 
  FROM dba_hist_seg_stat dhss, 
       dba_hist_seg_stat_obj dhso, 
       dba_hist_snapshot dhs 
  WHERE dhs.snap_id = dhss.snap_id 
    AND dhs.instance_number = dhss.instance_number 
    AND dhss.obj# = dhso.obj# 
    AND dhss.dataobj# = dhso.dataobj# 
    AND begin_interval_time BETWEEN to_date(’12-02-07 12:00′,’YY-MM-DD HH24:MI’)  
                                AND to_date(’12-02-07 16:00′,’YY-MM-DD HH24:MI’) 
  GROUP BY to_char(begin_interval_time,’YY-MM-DD HH24′), 
           dhso.object_name 
  HAVING sum(db_block_changes_delta) > 0 
ORDER BY sum(db_block_changes_delta) desc ;

Posted in Administration, Scripts | Tagged: , , , , | Leave a Comment »

Script to find archivelog generation per hour

Posted by Pavan DBA on February 7, 2012

set pagesize 120; 
set linesize 200; 
col day for a8; 
spool archivelog.lst

PROMPT Archive log distribution per hours on each day … 
  

select 
  to_char(first_time,’YY-MM-DD’) day, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’00′,1,0)),’999′) “00″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’01′,1,0)),’999′) “01″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’02′,1,0)),’999′) “02″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’03′,1,0)),’999′) “03″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’04′,1,0)),’999′) “04″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’05′,1,0)),’999′) “05″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’06′,1,0)),’999′) “06″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’07′,1,0)),’999′) “07″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’08′,1,0)),’999′) “08″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’09′,1,0)),’999′) “09″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’10′,1,0)),’999′) “10″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’11′,1,0)),’999′) “11″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’12′,1,0)),’999′) “12″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’13′,1,0)),’999′) “13″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’14′,1,0)),’999′) “14″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’15′,1,0)),’999′) “15″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’16′,1,0)),’999′) “16″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’17′,1,0)),’999′) “17″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’18′,1,0)),’999′) “18″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’19′,1,0)),’999′) “19″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’20′,1,0)),’999′) “20″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’21′,1,0)),’999′) “21″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’22′,1,0)),’999′) “22″, 
  to_char(sum(decode(substr(to_char(first_time,’HH24′),1,2),’23′,1,0)),’999′) “23″, 
  COUNT(*) TOT 
from v$log_history 
group by to_char(first_time,’YY-MM-DD’) 
order by day ;

Posted in Administration, Scripts | Tagged: , , , , , | Leave a Comment »

Script to identify users generating more redo

Posted by Pavan DBA on February 7, 2012

Many a times we would need to know the users who are causing more redo generation. Hope the following script will help us from now onwards

col machine for a15 
col username for a10 
col redo_MB for 999G990 heading “Redo |Size MB”
column sid_serial for a13; 
  

select b.inst_id,  
       lpad((b.SID || ‘,’ || lpad(b.serial#,5)),11) sid_serial,  
       b.username,  
       machine,  
       b.osuser,  
       b.status,  
       a.redo_mb   
from (select n.inst_id, sid,  
             round(value/1024/1024) redo_mb 
        from gv$statname n, gv$sesstat s 
        where n.inst_id=s.inst_id 
              and n.name = ‘redo size’
              and s.statistic# = n.statistic# 
        order by value desc
     ) a, 
     gv$session b 
where b.inst_id=a.inst_id  and a.sid = b.sid  and   rownum <= 10  and b.username is not null;

Posted in Administration, Scripts | Tagged: , , , , , , , | Leave a Comment »

« Previous Entries
 
Follow

Get every new post delivered to your Inbox.

Join 1,272 other followers