##########################################################
## WHAT IS GOING ON AT THIS MOMENT????
##########################################################
--show all cpu and how much they are being used.
mpstat -P ALL 2 100
--memory usage/advice
select pga_target_for_estimate, pga_target_factor, estd_time
from v$pga_target_advice;
select * from v$memory_target_advice;
SELECT
low_optimal_size/1024 "Low(K)",
(high_optimal_size+1)/1024 "High(K)",
estd_optimal_executions "Optimal",
estd_onepass_executions "One Pass",
estd_multipasses_executions "Multi-Pass"
FROM v$pga_target_advice_histogram
WHERE pga_target_factor = 2
AND estd_total_executions != 0
ORDER By 1
SELECT name, sum(value/1024/1024/1024) gb
FROM GV$PGASTAT
group by name
order by 2 desc
SELECT pool, sum(bytes/1024/1024/1024) gb
FROM GV$SGASTAT
group by pool
order by 2 desc
--resource usage
select resource_name, current_utilization, max_utilization
from v$resource_limit
where resource_name in ('processes','sessions');
--ash sql historical
SELECT
h.sample_time,
u.username,
h.program,
h.module,
s.sql_text
FROM
DBA_HIST_ACTIVE_SESS_HISTORY h,
DBA_USERS u,
DBA_HIST_SQLTEXT s
WHERE sample_time >= SYSDATE - 30
AND h.user_id=u.user_id
AND h.sql_id = s.sql_iD
and s.sql_id = '9922zf6qrbfx7'
ORDER BY h.sample_time;
—waiting sessions and events
set linesize 200
col event for a80
select event, state, count(*)
from v$session_wait
group by event, state
order by 3 desc;
—user i/o per username/session
set linesize 400
col osuser for a20
col username for a30
col program for a80
select osuser,username, b.*, a.program
from v$session a, v$sess_io b
where a.sid = b.sid
and a.sid=1626;
# purge HASH_PLAN FROM SHARED POOL
select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='11wvjs603rw79'
exec DBMS_SHARED_POOL.PURGE ('00000009A52148F0, 2151411945', 'C');
# sessions locking objects
select s.sid, s.serial#, machine, p.spid
from
v$session s,
v$process p
where
s.paddr = p.addr
and
s.sid in (select SESSION_ID from v$locked_object);
COLUMN username FORMAT A15
SELECT
NVL(s.username, '(oracle-BG)') AS username,
s.osuser,
s.program,
s.sid,
s.serial#,
si.block_gets,
si.consistent_gets,
si.physical_reads,
si.block_changes,
si.consistent_changes
FROM v$session s,
v$sess_io si
WHERE s.sid = si.sid
and s.status='ACTIVE'
ORDER BY s.username, s.osuser;
—active sql
col username for a20
set linesize 200
select username, sql_hash_value, count(*)
from v$session
where status = 'ACTIVE'
group by username, sql_hash_value
order by 1 desc;
—how many users are executing it.
select sql_text,users_executing
from v$sql
where hash_value = ‘%sql_hash_value’
—any buffer chains in db cache?
select P1
from v$session_wait
where event = 'cache buffer chains';
## parallel operations ##
#########################
col username for a12
col "QC SID" for A6
col "SID" for A6
col "QC/Slave" for A8
col "Req. DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set pages 300 lines 300
col wait_event format a30
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(sw.state,'WAITING', 'WAIT', 'NOT WAIT' ) as STATE,
case sw.state WHEN 'WAITING' THEN substr(sw.event,1,30) ELSE NULL end as wait_event ,
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp,
gv$session_wait sw
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
and sw.sid = s.sid
and sw.inst_id = s.inst_id
order by
decode(px.QCINST_ID, NULL, px.INST_ID, px.QCINST_ID),
px.QCSID,
decode(px.SERVER_GROUP, NULL, 0, px.SERVER_GROUP),
px.SERVER_SET,
px.INST_ID ;
set linesize 200
col name for a80
select
name,
value
from
v$sysstat
where
upper(NAME) like '%PARALLEL OPERATIONS%'
OR
upper(NAME) like '%PARALLELIZED%'
OR
upper(NAME) like '%PX%';
# display most recent parallel query ops.
select
tq_id,
server_type,
process,
num_rows
from
v$pq_tqstat
where
dfo_number =
(select max(dfo_number)
from
v$pq_tqstat)
order by
tq_id,
decode (substr(server_type,1,4),
'Prod', 0, 'Cons', 1, 3)
;
#######
## FRA Pressure
select file_type, percent_space_used, percent_space_reclaimable, number_of_files
from v$recovery_area_usage;
########################################
## sid, user, machine by unix process id
########################################
select s.username, s.sid, serial#, p.spid unix_id, s.machine
FROM v$sesstat v, v$session s, v$process p
WHERE v.statistic# = 3
and v.sid = s.sid
and p.addr = s.paddr
and p.spid = 12300 --unix id
############################################
## tracing oracle process with unix id
## get sid and serial# from above statement
############################################
—enable trace events to trc file.
exec dbms_monitor.session_trace_enable(167,1);
—verify it is tracing:
select s.sid, s.serial#, s.sql_trace
from v$session s, v$process p
where s.paddr=p.addr
and p.spid=4101;
—tail sid_process_pid.trc in trace dir.
—disable trace events to trc file.
exec dbms_monitor.session_trace_disable(167,1);
—re-verify tracing is turned off.
########################################
## sid, user, machine by sid
########################################
select s.username, s.sid, p.spid unix_id, s.machine
FROM v$sesstat v, v$session s, v$process p
WHERE v.statistic# = 3
and v.sid = s.sid
and p.addr = s.paddr
and v.sid = 254
# username, sid, serial by sql_id
SELECT a.username, a.sid, a.serial#, a.status
FROM v$session a
INNER JOIN v$sqlarea b
ON a.sql_id = b.sql_id
where a.sql_id='37a22867xdm2t'
###################################
## MEMORY HOG FINDER
###################################
SELECT ssn.program, to_char(ssn.sid, '9999') || ' - ' || nvl(ssn.username, nvl(bgp.name, 'background')) || ' :' ||
nvl(lower(ssn.machine), ins.host_name) "SESSION",
to_char(prc.spid, '999999999') "PID/THREAD",
to_char((se1.value/1024)/1024, '999G999G990D00') || ' MB' " CURRENT SIZE",
to_char((se2.value/1024)/1024, '999G999G990D00') || ' MB' " MAXIMUM SIZE"
FROM v$sesstat se1, v$sesstat se2, v$session ssn, v$bgprocess bgp, v$process prc,
v$instance ins, v$statname stat1, v$statname stat2
WHERE se1.statistic# = stat1.statistic# and stat1.name = 'session pga memory'
AND se2.statistic# = stat2.statistic# and stat2.name = 'session pga memory max'
AND se1.sid = ssn.sid
AND se2.sid = ssn.sid
AND ssn.paddr = bgp.paddr (+)
AND ssn.paddr = prc.addr (+)
--and ssn.sid=434
order by se2.value desc
###################################
## WHO IS LOCKING ACCOUNTS
###################################
select count(*) || '|' || os_username || '|' || username || '|' || userhost || '|' ||
to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS')
from dba_audit_session
where timestamp >= sysdate-20 and returncode > 0
GROUP BY to_char(timestamp, 'DD-MON-YYYY HH24:MI:SS'), os_username, username, userhost, returncode
ORDER BY COUNT(*) desc;
##########################################################
# LOCKING LOCKING LOCKING# TM lock ID1 is the object ID.
##########################################################
# lock object info giving process and user info.
select s.username,
o.owner,
o.object_name,
s.sid,
p.spid unix_id,
p.program,
p.pga_used_mem/1024/1024 mb
from dba_objects o, v$session s,
v$locked_object l, v$process p
where s.paddr = p.addr
and s.sid = l.session_id
and l.object_id = o.object_id
# who is blocking who?
select l1.sid, ' IS BLOCKING ',
l2.sid
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;
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and OBJECT_NAME='EMP';
# open transactions or long running tx as reported by GGS.
select s.*, t.*
from v$transaction t, v$session s
where s.SADDR = t.ses_addr
select inst_id, sid, type, id1, lmode
from gv$lock
where type in ('TM', 'TX', 'UL');
--release lock
exec dbms_output.put_line(DBMS_LOCK.RELEASE(1073741968));
select * from v$locked_object
where session_id in (select sid from v$session)
# find open long running tx and kill them.
select t.inst_id, sid||','||serial# sid,xidusn||'.'||xidslot||'.'||xidsqn xid, (sysdate - start_date )* 1440
runlength ,terminal,program from gv$transaction t, gv$session s where t.addr=s.taddr
and (sysdate - start_date) * 1440 > 20;
select 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';'
from gv$transaction t, gv$session s where t.addr=s.taddr
and (sysdate - start_date) * 1440 > 20;
# tx running longer than 4 hours
select distinct *
from
(select to_date(t.START_DATE,'DD.MM.YYYY HH24:MI:SS') Started_Date ,
s.sid, s.username,t.status tr_status, s.status sess_status,
nvl(s.program,s.module) Program,sq.sql_text from v$transaction t,
v$session s, v$sql sq where t.addr = s.taddr and s.sql_address = sq.address(+)
and t.start_date < (sysdate-4/24) order by 1);
########################################
## REDO LOG ACTIVITY
########################################
set linesize 200
col day for a12
col 00 for a4
col 01 for a4
col 02 for a4
col 03 for a4
col 04 for a4
col 05 for a4
col 06 for a4
col 07 for a4
col 08 for a4
col 09 for a4
col 10 for a4
col 11 for a4
col 12 for a4
col 13 for a4
col 14 for a4
col 15 for a4
col 16 for a4
col 17 for a4
col 18 for a4
col 19 for a4
col 20 for a4
col 21 for a4
col 22 for a4
col 23 for a4
SELECT
to_char(first_time,'YYYY-MON-DD') day,
to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from
v$log_history
GROUP by
to_char(first_time,'YYYY-MON-DD')
order by to_char(first_time,'YYYY-MON-DD');
------------------------------------
--redo by sesssion
------------------------------------
set lines 120
col osuser format a10
col username format 10
select
osuser,
username,
process pid,
ses.sid sid,
serial#,
physical_reads,
block_changes
from
v$session ses,
v$sess_io sio
where
ses.sid = sio.sid
order
by block_changes, physical_reads desc;
# GB Redo per day
select trunc(completion_time) rundate ,
count(*) logswitch,
round((sum(blocks*block_size)/1024/1024/1024)) "REDO PER DAY (GB)"
from v$archived_log
group by trunc(completion_time) order by 1;
# MB REDO per hour
SELECT Start_Date, Start_Time, Num_Logs,
Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2) AS Mbytes, Vdb.NAME AS Dbname
FROM (SELECT To_Char(Vlh.First_Time, 'YYYY-MM-DD') AS Start_Date,
To_Char(Vlh.First_Time, 'HH24') || ':00' AS Start_Time, COUNT(Vlh.Thread#) Num_Logs
FROM V$log_History Vlh
GROUP BY To_Char(Vlh.First_Time, 'YYYY-MM-DD'),
To_Char(Vlh.First_Time, 'HH24') || ':00') Log_Hist,
V$log Vl , V$database Vdb
WHERE Vl.Group# = 1
ORDER BY Log_Hist.Start_Date, Log_Hist.Start_Time;
------------------------------------
--TEMP SPACE, WHO IS TAKING IT------
------------------------------------
SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocks
FROM v$session s, v$tempseg_usage u
WHERE s.saddr=u.session_addr
order by u.blocks;
col hash_value for 999999999999
select hash_value, sorts, rows_processed/executions
from v$sql
where hash_value in (select hash_value from v$open_cursor where sid=$SID)
and sorts > 0
and PARSING_SCHEMA_NAME='$SCHEMA_NAME'
order by rows_processed/executions;
------------------------------------
-- time remaining long ops by sid --
------------------------------------
set linesize 200
col username for a10
col SUBSTR((SELECTSQL_TEXTFROMV$SQLAREAWHEREADDRESS=V$SESSION_LONGOPS.SQL_ADDRESS),1,50) for a80
select username, sid, time_remaining/60 min,
substr((select sql_text from v$sqlarea where address = v$session_longops.sql_address), 1, 50)
from v$session_longops
where time_remaining > 0;
select username, sid, time_remaining/60 min,
substr((select sql_text from gv$sqlarea
where address = gv$session_longops.sql_address), 1, 50)
from gv$session_longops
where time_remaining > 0;
select ops.OPNAME, ops.TIME_REMAINING,ops.start_time
from v$px_session par, v$session_longops ops
where ops.sid=par.sid
and ops.serial#=par.serial#
and ops.time_remaining>0;
# session_waits
col event for a80
set linesize 200
select event, state, seconds_in_wait, count(*) from v$session_wait group by event, state, seconds_in_wait order by 3 desc
set linesize 200
col event for a40
col state for a20
select event, state, seconds_in_wait from v$session_wait
where event =
col username for a20
col machine for a30
select a.username, a.machine, count(*) from v$session a, v$session_wait b
where a.sid = b.sid
and b.event = 'SQL*Net message from client'
group by a.username, a.machine;
-----------------------------
--view active transactions
-----------------------------
select start_time, sid, used_ublk, used_urec, phy_io, log_io
from v$transaction, v$session
where ses_addr = saddr
and username <> 'SYS'
-----------------------------
--view rbs/UNDO usage
-----------------------------
select s.sql_text from v$sql s, v$undostat u
where u.maxqueryid=s.sql_id;
SELECT a.sid, a.username, b.xidusn, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr;
select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic#=t.statistic#
and t.name='undo change vector size'
order by s.value desc;
select sql.sql_text, t.used_urec records, t.used_ublk blocks,
(t.used_ublk*8192/1024) kb from v$transaction t,
v$session s, v$sql sql
where t.addr=s.taddr
and s.sql_id = sql.sql_id
and s.username ='APP_DSAPI';
-------------------------------------
--all sessions by sql_id
-------------------------------------
select s.username, s.sid, s.serial#, sql.sql_id
from v$session s, v$sqlarea sql
where s.sql_address = sql.address
and sql.sql_id='12s4146sk5tr2';
select 'alter session kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';'
from v$session s, v$sqlarea sql
where s.sql_address = sql.address
and sql.sql_id='9xv1yf7vsn4tx';
----------------------------------------
-- sid, unix process id, machine by username
----------------------------------------
select s.username, s.sid, p.spid unix_id, s.machine
FROM v$sesstat v, v$session s, v$process p
WHERE v.statistic# = 3
and v.sid = s.sid
and p.addr = s.paddr
and s.username = 'IPINTEL' --username
SELECT s.sid, s.serial#, s.process, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND action ='STREAMS Capture'
--------------------------------
--current query by user sid.
--------------------------------
select s.program, sql.sql_text, s.logon_time
from gv$session s, gv$sqlarea sql
where s.sql_address = sql.address
and s.sid in (407) --s.sid
select s.sid, s.program, sql.sql_text, sql.last_load_time
from gv$session s, gv$sqlarea sql
where s.sql_address = sql.address
and s.username = 'APPWMS'
order by sql.last_load_time
--all waiting sessions
select count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session_wait
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY 1 DESC, 2 DESC;
--waiting sessions non-background
select count(*),
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END AS state,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END AS sw_event
FROM
v$session
WHERE
type = 'USER'
AND status = 'ACTIVE'
GROUP BY
CASE WHEN state != 'WAITING' THEN 'WORKING'
ELSE 'WAITING'
END,
CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
ELSE event
END
ORDER BY 1 DESC, 2 DESC;
--active sql and counts
select sql_hash_value, count(*)
from v$session
where status = 'ACTIVE'
and type='APP_DSAPI'
group by sql_hash_value
order by 2 desc;
--troubleshoot specific user performance problems
break on sid skip 2
select a.sid, c.name, b.value, a.inst_id
from gv$session a, gv$sesstat b, gv$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and b.value != 0
and a.sid=2548
and c.name like '%wait%'
group by a.sid, c.name, b.value, a.inst_id
ORDER BY a.sid, c.name, b.value
2=INSERT
3=SELECT
column sql format a60
column execs format 999,999,999,999
select substr (sql_text, 1, 60) sql, sum (executions) execs
from v$sql
where command_type in (3,2)
and sql_text like '%epan_token%'
group by substr (sql_text, 1, 60)
order by sum (executions) desc
/
----------------------------------------
-- long operations
----------------------------------------
select username, sid, time_remaining,
substr((select sql_text from gv$sqlarea
where address = gv$session_longops.sql_address), 1, 60)
from gv$session_longops
where time_remaining>0
and username ='SYS';
----------------------
--session cursor count
----------------------
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.sid=391 --sid
and b.name = 'session cursor cache count'
order by 1 desc;
-----------------------------
--sql by user
-----------------------------
select u.sid, sysdate, last_load_time, s.sql_text
from v$sql s, v$session u
where s.hash_value = u.sql_hash_value
and u.username = 'APP_DSAPI'
order by u.sid
select a.username, c.name, b.value, a.inst_id
from gv$session a, gv$sesstat b, gv$statname c
where a.sid = b.sid
and b.statistic# = c.statistic#
and b.value != 0
and a.sid = 471
group by a.username, c.name, b.value, a.inst_id
ORDER BY b.value, c.name
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
---------------------------
--show user activity.
---------------------------
SELECT se.username, DECODE(sa.command_type,
2, 'INSERT',
3, 'SELECT',
6, 'UPDATE',
7, 'DELETE',
26, 'LOCK',
42, 'DDL',
44, 'COMMIT',
47, 'PL/SQL BLOCK',
sa.command_type) AS command_type,
SUBSTR(s.sql_text, 1, 50) AS sql_text
FROM v$sqlarea sa, v$sql s, v$session se
WHERE s.address = sa.address
AND s.hash_value = sa.hash_value
and se.sql_hash_value = s.hash_value
and se.username = 'APP_DSAPI'
--------------------------------------
--statements by command_type
--------------------------------------
col sql for a100
select substr(sql_text, 1, 80) sql, sum (executions) execs
from gv$sql
where command_type in (3,2) - insert, select
group by substr (sql_text, 1, 80)
order by sum (executions) asc
------------------------
--segment info
------------------------
select sum(bytes/1024/1024) mb
from dba_segments
where owner = 'CMS_STAGE'
select name from v$event_name where wait_class = 'Concurrency';
—-STREAMS ARCH LOGS NEEDED.
SET LINESIZE 200
COLUMN CONSUMER_NAME HEADING 'Capture|Process|Name' FORMAT A15
COLUMN SOURCE_DATABASE HEADING 'Source|Database' FORMAT A10
COLUMN SEQUENCE# HEADING 'Sequence|Number' FORMAT 999999
COLUMN NAME HEADING 'Required|Archived Redo Log|File Name' FORMAT A60
SELECT r.CONSUMER_NAME,
r.SOURCE_DATABASE,
r.SEQUENCE#,
r.NAME
FROM DBA_REGISTERED_ARCHIVED_LOG r, DBA_CAPTURE c
WHERE r.CONSUMER_NAME = c.CAPTURE_NAME AND
r.NEXT_SCN >= c.REQUIRED_CHECKPOINT_SCN;
--FRA sizing
select
name,
to_char(space_limit/1024/1024, '999,999,999,999') as space_limit,
to_char(space_limit/1024/1024 - space_used/1024/1024 + space_reclaimable/1024/1024,'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from v$recovery_file_dest;
select 'FRA Utilization = ' || round(100*round(space_used/1024/1024/1024)/round(space_limit/1024/1024/1024),0) || '% ' ||
round(space_used/1024/1024/1024,0) || '/' || ROUND(space_limit/1024/1024/1024,0) || ' GB' "FRA USAGE"
from v$recovery_file_dest;
select rpad(file_type,30) || ' pct_used:' || trunc(percent_space_used) || '%' || ' num_files:' || number_of_files from v$flash_recovery_area_usage where trunc(PERCENT_SPACE_USED) <>0;
col name for a20
col space_limit for a5
col space_available for a5
col pct_full for a3
select 'FLASH RECOVERY AREA USAGE:' from dual;
set heading off
select '' from dual;
select name,
to_char(space_limit/1024/1024, '999,999,999,999') as space_limit,
to_char(space_limit/1024/1024 - space_used/1024/1024 + space_reclaimable/1024/1024,'999,999,999,999') as space_available,
round((space_used - space_reclaimable)/space_limit * 100, 1) as pct_full
from v$recovery_file_dest;
select rpad(file_type,30) || ' pct_used:' || trunc(percent_space_used) || '%' || ' num_files:' || number_of_files from v$flash_recovery_area_usage where trunc(PERCENT_SPACE_USED) <>0;
