1. Scripts to find SYSAUX Tablespace growth .
COLUMN "Item" FORMAT A25
COLUMN "Space Used (GB)" FORMAT 999.99
COLUMN "Schema" FORMAT A25
COLUMN "Move Procedure" FORMAT A40
SELECT occupant_name "Item",
space_usage_kbytes/1048576 "Space Used (GB)",
schema_name "Schema",
move_procedure "Move Procedure"
FROM v$sysaux_occupants
ORDER BY 2 desc
/
2. Scripts for redo log generation per hour basis
set lines 120;
set pages 999;
column day format a15
column 00 format a3
column 01 format a3
column 02 format a3
column 03 format a3
column 04 format a3
column 05 format a3
column 06 format a3
column 07 format a3
column 08 format a3
column 09 format a3
column 10 format a3
column 11 format a3
column 12 format a3
column 13 format a3
column 14 format a3
column 15 format a3
column 16 format a3
column 17 format a3
column 18 format a3
column 19 format a3
column 20 format a3
column 21 format a3
column 22 format a3
column 23 format a3
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
from
v$log_history
where
first_time > sysdate-8
GROUP by
to_char(first_time,'YYYY-MON-DD')
order by
to_char(first_time,'YYYY-MON-DD');
EXAMPLE:
DAY 00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
--------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2012-MAR-12 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 4 2 4 2
2012-MAR-13 2 2 2 4 2 4 4 4 3 3 4 4 3 3 5 3 6 10 11 5 4 2 4 2
2012-MAR-14 2 2 2 2 2 4 5 6 3 4 4 3 3 3 3 4 4 10 11 6 4 2 6 2
2012-MAR-15 2 2 2 2 3 6 4 5 4 4 4 5 5 3 5 5 4 10 11 4 4 4 2 2
2012-MAR-16 5 3 2 2 2 5 5 5 3 3 5 3 3 4 5 5 3 10 9 4 4 2 2 2
2012-MAR-17 4 2 2 1 2 6 6 3 2 2 3 4 3 3 3 3 3 8 4 4 3 2 5 2
2012-MAR-18 2 2 2 2 2 6 5 2 3 3 2 3 2 2 3 2 2 7 4 3 3 5 2 2
2012-MAR-19 2 2 2 3 2 4 6 3 2 3 3 3 3 6 5 5 5 10 10 3 4 2 5 3
2012-MAR-20 2 2 0 1 2 7 4 5 3 3 3 3 4 3 3 6 3 9 9 4 0 0 0 0
3. Scripts for UNDO Usage
SELECT sum(used_ublk * (SELECT block_size
FROM dba_tablespaces
WHERE contents = 'UNDO'))/1024/1024 MB
FROM v$transaction;
SELECT ROUND(((ur * (ups * dbs)) + (dbs * 24))/ut*100,0) AS "%"
FROM (SELECT VALUE AS ur
FROM v$parameter
WHERE NAME = 'undo_retention'),
(SELECT (SUM (undoblks) / SUM (((end_time - begin_time) * 25200))
) AS ups
FROM v$undostat),
(SELECT block_size AS dbs
FROM dba_tablespaces
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace')),
(SELECT sum(bytes) as ut
FROM dba_data_files
WHERE tablespace_name = (SELECT VALUE
FROM v$parameter
WHERE NAME = 'undo_tablespace'));
%
----------
14
4. Find session consuming more undo.
select ses.username , substr(ses.program, 1, 19) command , tra.used_ublk from v$session ses , v$transaction tra where ses.saddr = tra.ses_addr;
SELECT r.name rbs,
NVL(s.username, 'None') oracle_user,
s.osuser client_user,
p.username unix_user,
TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) as sid_serial,
p.spid unix_pid,
t.used_ublk * TO_NUMBER(x.value)/1024 as undo_kb
FROM v$process p,
v$rollname r,
v$session s,
v$transaction t,
v$parameter x
WHERE s.taddr = t.addr
AND s.paddr = p.addr(+)
AND r.usn = t.xidusn(+)
AND x.name = 'db_block_size'
ORDER
BY r.name ;
RBS ORACLE_USER CLIENT_USER UNIX_USER SID_SERIAL UNIX_PID UNDO_KB
--------------------------------- ---------------------- ------------------------------ --------------- ---------------------- --------------------
select l.sid, s.segment_name from dba_rollback_segs s, v$transaction t, v$lock l
where t.xidusn=s.segment_id and t.addr=l.addr ;
select to_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss')
, maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks ;
set lines 160 pages 40
col machine format A20
col username format A15
select xidusn, xidslot, trans.status, start_time, ses.sid, ses.username, ses.machine ,proc.spid, used_ublk
from v$transaction trans, v$session ses , v$process proc
where trans.ses_addr =ses.saddr and ses.paddr=proc.addr
order by start_time ;
TO_CHAR( TO_CHAR( MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT UNDOBLKS TXNCOUNT
-------- -------- ----------- ----------- ------------- ---------- ----------
18:09:33 18:19:33 1219 0 0 111193 619
18:09:33 18:19:33 694 0 0 115625 362
18:59:33 19:09:33 1071 0 0 115761 323
17:59:33 18:09:33 616 0 0 117666 588
18:59:33 19:09:33 994 0 0 118582 377
17:59:33 18:09:33 913 0 0 119148 581
18:29:33 18:39:33 1220 0 0 128037 432
4. Find size of schema
select owner, sum(bytes/1024/1024) "Used Size in MB"
from dba_segments
group by owner
order by 2
/
5. Find tablespace usage history by using snap id.
select a.TABLESPACE_SIZE/1024/1024,a.TABLESPACE_USEDSIZE/1024/1024 from dba_hist_tbspc_space_usage a where a.TABLESPACE_ID=3 and a.SNAP_ID > 177314 and a.SNAP_ID < 1177324;
--- show as no of blocks
set pages 100
set lines 200
select * from dba_tablespace_usage_metrics order by tablespace_name;
SELECT Total.name "Tablespace Name",
round(Free_space,0) Free_space,
round((total_space-Free_space),0) Used_space,
round(total_space,0) Total_Space,
round(100-((Free_space/total_space)*100),0) Pct_used
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v\$datafile a, sys.v\$tablespace B
where a.ts# = b.ts#
group by b.name) Total
WHERE Free.Tablespace_name = Total.name
order by 5 desc;
********************************
set pages 100
set lines 200
select * from dba_tablespace_usage_metrics order by tablespace_name;
SELECT Total.name "Tablespace Name",
round(Free_space,0) Free_space,
round((total_space-Free_space),0) Used_space,
round(total_space,0) Total_Space,
round(100-((Free_space/total_space)*100),0) Pct_used
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v\$datafile a, sys.v\$tablespace B
where a.ts# = b.ts#
group by b.name) Total
WHERE Free.Tablespace_name = Total.name
order by 5 desc;
SELECT ts.tablespace_name, "File Count",
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc;
8. Find Flash Recovery Area Usage
TRUNC("SIZE(MB)", 2) "Size(MB)",
TRUNC(fr."FREE(MB)", 2) "Free(MB)",
TRUNC("SIZE(MB)" - "FREE(MB)", 2) "Used(MB)",
df."MAX_EXT" "Max Ext(MB)",
(fr."FREE(MB)" / df."SIZE(MB)") * 100 "% Free"
FROM (SELECT tablespace_name,
SUM (bytes) / (1024 * 1024) "FREE(MB)"
FROM dba_free_space
GROUP BY tablespace_name) fr,
(SELECT tablespace_name, SUM(bytes) / (1024 * 1024) "SIZE(MB)", COUNT(*)
"File Count", SUM(maxbytes) / (1024 * 1024) "MAX_EXT"
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name
FROM dba_tablespaces) ts
WHERE fr.tablespace_name = df.tablespace_name (+)
AND fr.tablespace_name = ts.tablespace_name (+)
ORDER BY "% Free" desc;
8. Find Flash Recovery Area Usage
#!/bin/ksh
echo ":::::::::::::::Flash usage details:::::::::::::::::::: "
sqlplus -s '/as sysdba'<<EOF
set lines 200
set pagesize 100;
sho parameter db_unique_name
set heading on
sho parameter db_flashback_retention_target
sho parameter db_recovery_file_dest
set heading on
select SPACE_LIMIT/1024/1024/1024 ALLCO_GB,SPACE_USED/1024/1024/1024 USED_GB
from v\$recovery_file_dest
/
select sum(round(PERCENT_SPACE_USED)) USED_PERCENT,SUM(PERCENT_SPACE_RECLAIMABLE) RECLAIM_PERCENT
from v\$flash_recovery_area_usage
/
select name, TOTAL_MB/1024 TOT_GB, free_mb/1024 FREE_GB from v\$asm_diskgroup
/
select sum(estimated_flashback_size)/1024/1024/1024 from v\$flashback_database_log
/
col name for a33
col RESTORE_POINT_TIME for a22
col TIME for a22
echo restore point details
select NAME,TIME,RESTORE_POINT_TIME,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/1024/1024/1024 SPACE_GB from v\$restore_point
/
9. Find datafiles size and details for a specific tablespace.
set lines 200
col file_name for a55
select FILE_NAME,TABLESPACE_NAME,bytes/1024/1024 Mb ,AUTOEXTENSIBLE,MAXBYTES/1024/1024 MAXBYTEMB from dba_data_files where TABLESPACE_NAME='&tsname';
10. Moitoring dataguard lag on standby end.
#!/bin/ksh
sqlplus -s '/as sysdba'<<EOF
set lines 200
set time on
set verify off
set pages 100
col value for a33
col name for a24
col database_role for a20
col process for a11
col status for a15
col open_mode for a20
col group# for a10
select database_role,DB_UNIQUE_NAME,open_mode from v\$database;
select name,value,unit,time_computed,datum_time from v\$dataguard_stats;
select PROCESS,PID,GROUP#,THREAD#,STATUS,ACTIVE_AGENTS,SEQUENCE#,block# from v\$MANAGED_STANDBY where process in ('MRP0','RFS') order by process desc;
archive log list;
exit;
EOF
11. Database ASM space usage
#!/bin/ksh
sqlplus -s '/as sysdba'<<EOF
column pct_free format 99.99
select name, total_mb/1024 total_gb, free_mb/1024 free_gb, (total_mb - free_mb)/1024 used_gb,
free_mb/total_mb *100 pct_free
from v\$asm_diskgroup;
col path for a40
set lines 200
select * from dba_tablespace_usage_metrics;
select path,header_status,os_mb,total_mb from v\$asm_disk order by header_status desc
/
EOF
********************************
Undo stealing session *
********************************
select BEGIN_TIME,MAXQUERYLEN,MAXQUERYID,MAXCONCURRENCY,EXPSTEALCNT,EXPBLKRELCNT,EXPBLKREUCNT,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS,TUNED_UNDORETENTION from v$undostat;
********************************
Redo Generation per user *
********************************
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 <= 30
;
********************************
Backup progress Monitor : *
********************************
SELECT
i.instance_name instance_name
, sid sid
, serial# serial_num
, b.opname opname
, TO_CHAR(b.start_time, 'mm/dd/yy HH24:MI:SS') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork
, 0
, 0.001
, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
, DECODE( b.time_remaining
, 0
, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'mm/dd/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'mm/dd/yy HH24:MI:SS')
) done_at
FROM
gv$session a
JOIN gv$session_longops b USING (sid,serial#)
JOIN gv$instance i ON ( i.inst_id = a.inst_id
AND i.inst_id = b.inst_id)
WHERE
a.program LIKE 'rman%'
AND b.opname LIKE 'RMAN%'
AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY
i.instance_name
, b.start_time
/
********************************
DBA JOBS MONITORING *
********************************
********************************
Index info by table *
********************************
select i.index_name
********************************
select BEGIN_TIME,MAXQUERYLEN,MAXQUERYID,MAXCONCURRENCY,EXPSTEALCNT,EXPBLKRELCNT,EXPBLKREUCNT,ACTIVEBLKS,UNEXPIREDBLKS,EXPIREDBLKS,TUNED_UNDORETENTION from v$undostat;
********************************
Redo Generation per user *
********************************
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 <= 30
;
********************************
Backup progress Monitor : *
********************************
SELECT
i.instance_name instance_name
, sid sid
, serial# serial_num
, b.opname opname
, TO_CHAR(b.start_time, 'mm/dd/yy HH24:MI:SS') start_time
, b.totalwork totalwork
, b.sofar sofar
, ROUND( (b.sofar/DECODE( b.totalwork
, 0
, 0.001
, b.totalwork)*100),2) pct_done
, b.elapsed_seconds elapsed_seconds
, b.time_remaining time_remaining
, DECODE( b.time_remaining
, 0
, TO_CHAR((b.start_time + b.elapsed_seconds/3600/24), 'mm/dd/yy HH24:MI:SS')
, TO_CHAR((SYSDATE + b.time_remaining/3600/24), 'mm/dd/yy HH24:MI:SS')
) done_at
FROM
gv$session a
JOIN gv$session_longops b USING (sid,serial#)
JOIN gv$instance i ON ( i.inst_id = a.inst_id
AND i.inst_id = b.inst_id)
WHERE
a.program LIKE 'rman%'
AND b.opname LIKE 'RMAN%'
AND b.opname NOT LIKE '%aggregate%'
AND b.totalwork > 0
ORDER BY
i.instance_name
, b.start_time
/
********************************
DBA JOBS MONITORING *
********************************
select substr(job,1,4) "job",
substr(schema_user,1,10) "user",
substr(to_char(last_date,'dd.mm.yyyy hh24:mi'),1,16) "last date",
substr(broken,1,2) "b",
substr(failures,1,6) "failed",
substr(what,1,32) "command"
from dba_jobs;
********************************
Index info by table *
********************************
select i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from dba_indexes i
, dba_segments s
where i.index_name = s.segment_name
and table_name like '&table'
order by 2, 1
/
select i.index_name
, i.tablespace_name
, ceil(s.bytes / 1048576) "Size MB"
from dba_indexes i
, dba_segments s
where i.index_name = s.segment_name
and table_name like '&table'
order by 2, 1
/
Show indexed columns
select column_name
from dba_ind_columns
where index_name = '&index'
order by column_position
set echo off
set feedback off
set verify off
set wrap on
set pagesize 60
set linesize 132
set newpage 0
set serverout on
set trimspool on
ttitle off
btitle off
clear columns
clear breaks
clear compute
-- spool show_logins.txt
col stat# format 9999
select statistic# stat#,
substr(name,1,20) name,
value,
class
from v$sysstat
where name like '%logon%';
select status, count(*)
from v$session
where type <> 'BACKGROUND'
group by status;
select username,count(*) from v$session where type <> 'BACKGROUND' group by username;
break on inst_id skip 1
col inst_id format 99 heading IN
col program format a7 trunc
col sid format 999
col serial# format 9999999 heading serl#
col sid_serl# format a16 trunc
col child format a6 trunc
col logon_time format a9 trunc
col cpu_time format 9999999
col event format a37 trunc
col typ format a3 trunc
col username format a8 trunc
col osuser format a7 trunc
col status format a1 trunc
col sql_text format a43 trunc
col p1 format a12 trunc
col p2 format 99999999
select p.spid child,
to_char(a.logon_time,'MMDD/hh24mi') logon_time,
lpad(a.sid || ',' || a.serial#,9,' ') sid_serl#,
decode(a.username, NULL, substr(a.program,instr(a.program,'(',1,1)), a.username) username,
a.osuser,
a.machine,
a.sql_id sql_id,
a.sql_hash_value hash_value,
decode(a.status, 'ACTIVE', '*', 'INACTIVE', ' ', 'KILLED', 'K', 'SNIPED', 'S', a.sql_id,substr(a.status,1,1)) || b.sql_text sql_text,
c.event,
c.wait_time,
decode(c.event,'enqueue',chr(bitand(c.p1,-16777216)/16777215)||chr(bitand(c.p1,16711680)/65536),c.p1) p1,
decode(c.event,'enqueue',mod(c.P1,16),c.p2) p2
from gv$session a, gv$sqltext b, gv$process p, gv$session_wait c
where a.paddr = p.addr
and a.inst_id = p.inst_id
and b.piece(+) = 0
and a.sql_address = b.address(+)
and a.inst_id = b.inst_id(+)
and a.inst_id = c.inst_id
and a.sid = c.sid
and a.status = 'ACTIVE'
--and a.status = 'INACTIVE'
--and c.event like '%enq: TX - row lock contention%'
and a.type <> 'BACKGROUND'
--and a.username = 'FLEXAPP'
--and a.osuser = 'GLMSAD'
--and a.program like 'rman%'
order by a.inst_id, a.type, a.status, a.sid;
-- spool off
set feedback 6
set verify on
set wrap on
set heading on
set pagesize 66
set linesize 132
ttitle off
btitle off
-- clear columns
-- clear breaks
clear compute
set echo on
Archive generation Size
set linesize 200
break on Date_Archived
prompt
prompt
prompt **********************************************************************************
prompt ** Logs Trends Analysis for Last Week (Day Wise Aggredates) **********************
prompt **********************************************************************************
col Date_Archived for a15
col Total_Bytes_MB format 99999999.99
break on Date_Archived
select trunc(COMPLETION_TIME) Date_Archived,thread#,
sum(BLOCKS*512/1024/1024) Total_Bytes_MB
from V$ARCHIVED_LOG
where trunc(completion_time) between sysdate-7 and sysdate
group by thread#, trunc(COMPLETION_TIME)
order by 1 desc,2
/
select to_char(FIRST_TIME,'dd-MON-rr hh24') , count(1), sum(BLOCKS*BLOCK_SIZE)/(1024*1024*1024) " in GB" from
v$archived_log group by to_char(FIRST_TIME,'dd-MON-rr hh24') order by 1
/
CPU USAGE:
SELECT se.username, ss.sid,se.program, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;
set feedback off
set verify off
set wrap on
set pagesize 60
set linesize 132
set newpage 0
set serverout on
set trimspool on
ttitle off
btitle off
clear columns
clear breaks
clear compute
-- spool show_logins.txt
col stat# format 9999
select statistic# stat#,
substr(name,1,20) name,
value,
class
from v$sysstat
where name like '%logon%';
select status, count(*)
from v$session
where type <> 'BACKGROUND'
group by status;
select username,count(*) from v$session where type <> 'BACKGROUND' group by username;
break on inst_id skip 1
col inst_id format 99 heading IN
col program format a7 trunc
col sid format 999
col serial# format 9999999 heading serl#
col sid_serl# format a16 trunc
col child format a6 trunc
col logon_time format a9 trunc
col cpu_time format 9999999
col event format a37 trunc
col typ format a3 trunc
col username format a8 trunc
col osuser format a7 trunc
col status format a1 trunc
col sql_text format a43 trunc
col p1 format a12 trunc
col p2 format 99999999
select p.spid child,
to_char(a.logon_time,'MMDD/hh24mi') logon_time,
lpad(a.sid || ',' || a.serial#,9,' ') sid_serl#,
decode(a.username, NULL, substr(a.program,instr(a.program,'(',1,1)), a.username) username,
a.osuser,
a.machine,
a.sql_id sql_id,
a.sql_hash_value hash_value,
decode(a.status, 'ACTIVE', '*', 'INACTIVE', ' ', 'KILLED', 'K', 'SNIPED', 'S', a.sql_id,substr(a.status,1,1)) || b.sql_text sql_text,
c.event,
c.wait_time,
decode(c.event,'enqueue',chr(bitand(c.p1,-16777216)/16777215)||chr(bitand(c.p1,16711680)/65536),c.p1) p1,
decode(c.event,'enqueue',mod(c.P1,16),c.p2) p2
from gv$session a, gv$sqltext b, gv$process p, gv$session_wait c
where a.paddr = p.addr
and a.inst_id = p.inst_id
and b.piece(+) = 0
and a.sql_address = b.address(+)
and a.inst_id = b.inst_id(+)
and a.inst_id = c.inst_id
and a.sid = c.sid
and a.status = 'ACTIVE'
--and a.status = 'INACTIVE'
--and c.event like '%enq: TX - row lock contention%'
and a.type <> 'BACKGROUND'
--and a.username = 'FLEXAPP'
--and a.osuser = 'GLMSAD'
--and a.program like 'rman%'
order by a.inst_id, a.type, a.status, a.sid;
-- spool off
set feedback 6
set verify on
set wrap on
set heading on
set pagesize 66
set linesize 132
ttitle off
btitle off
-- clear columns
-- clear breaks
clear compute
set echo on
Archive generation Size
set linesize 200
break on Date_Archived
prompt
prompt
prompt **********************************************************************************
prompt ** Logs Trends Analysis for Last Week (Day Wise Aggredates) **********************
prompt **********************************************************************************
col Date_Archived for a15
col Total_Bytes_MB format 99999999.99
break on Date_Archived
select trunc(COMPLETION_TIME) Date_Archived,thread#,
sum(BLOCKS*512/1024/1024) Total_Bytes_MB
from V$ARCHIVED_LOG
where trunc(completion_time) between sysdate-7 and sysdate
group by thread#, trunc(COMPLETION_TIME)
order by 1 desc,2
/
select to_char(FIRST_TIME,'dd-MON-rr hh24') , count(1), sum(BLOCKS*BLOCK_SIZE)/(1024*1024*1024) " in GB" from
v$archived_log group by to_char(FIRST_TIME,'dd-MON-rr hh24') order by 1
/
CPU USAGE:
SELECT se.username, ss.sid,se.program, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE '%CPU used by this session%'
AND se.sid = ss.SID
AND se.username IS NOT NULL
ORDER BY value DESC;
SCHEMA STAT
set pagesize 0
select table_name, to_char(last_analyzed, 'DD-MON-YY HH:MM:SS') last_analyzed, num_rows , sample_size
from dba_tab_statistics
where
owner = '&owner'
/