SET ECHO OFF
ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YY HH24:MI';
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM SWITCH LOGFILE;
SET linesize 300
SELECT inst_id,
instance_number inst_no,
instance_name inst_name,
parallel,
status,
database_status db_status,
active_state state,
host_name host,
startup_time
FROM gv$instance
ORDER BY inst_id;
break on instance_name skip 1
compute SUM of sessions on instance_name
SELECT
i.instance_name,
s.status,
COUNT(*) sessions
FROM
gv$instance i,
gv$session s
WHERE
i.inst_id = s.inst_id
AND s.username IS NOT NULL
GROUP BY
i.instance_name,
s.status
ORDER BY
i.instance_name,
s.status;
CLEAR break
CLEAR compute
CLEAR COLumns Computes Breaks
SET lines 200
SET pages 200
COL file_name for a50
COL tablespace_name for a30
COL status for a21
compute SUM of "Total(Mb)" on report
compute SUM of "Free(Mb)" on report
break on report
SELECT t.tablespace_name,
ts.CONTENTS,
ts.status,
Round(Nvl(t.bytes, 0) / 1024 / 1024, 1) "Total(Mb)",
Round(( Nvl(Nvl(f.free, ft.free), 0) / 1024 / 1024 ), 1) "Free(Mb)",
Round(( Nvl(Nvl(f.free, ft.free), 0) * 100 / t.bytes ), 1) "% Free",
Decode(( CASE
WHEN Round(( Nvl(Nvl(f.free, ft.free), 0) / 1024 / 1024 / 1024 )) >= 5
THEN 'OK'
ELSE 'NOK'
END ), 'OK', 'OK',
Decode(CONTENTS, 'UNDO', 'OK - UNDO TABLESPACE',
Decode(CONTENTS, 'TEMPORARY', 'OK - TEMP TABLESPACE',
Decode( Round( ( Nvl(Nvl(f.free, ft.free), 0) * 100 ) / t.bytes),
'0', 'CRITICAL',
'1', 'CRITICAL',
'2', 'CRITICAL',
'3', 'CRITICAL',
'4', 'CRITICAL',
'5', 'WARNING',
'6', 'WARNING',
'7', 'WARNING',
'8', 'WARNING',
'9', 'WARNING',
'OK')
)
)
) STATUS
FROM (SELECT d.tablespace_name,
SUM(d.bytes) bytes
FROM dba_data_files d
GROUP BY tablespace_name
UNION
SELECT d.tablespace_name,
SUM(d.bytes) bytes
FROM dba_temp_files d
GROUP BY tablespace_name) t,
(SELECT tablespace_name,
SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) f,
(SELECT tablespace_name,
SUM(bytes_free) free
FROM v$temp_space_header
GROUP BY tablespace_name) ft,
dba_tablespaces ts
WHERE t.tablespace_name = f.tablespace_name(+)
AND t.tablespace_name = ft.tablespace_name(+)
AND t.tablespace_name = ts.tablespace_name
ORDER BY 5;
TAMANHO DO BANCO
CLEAR COLumns
COL dados for a10
COL undo for a12
COL redo for a12
COL temp for a12
COL livre for a12
COL total for a12
SELECT to_char(SUM(dados) / 1048576, 'fm99g999g990') dados,
to_char(SUM(undo) / 1048576, 'fm99g999g990') undo,
to_char(SUM(redo) / 1048576, 'fm99g999g990') redo,
to_char(SUM(temp) / 1048576, 'fm99g999g990') temp,
to_char(SUM(free) / 1048576, 'fm99g999g990') livre,
to_char(SUM(dados + undo + redo + temp) / 1048576, 'fm99g999g990') total
FROM (
SELECT SUM(decode(substr(t.contents, 1, 1), 'P', bytes, 0)) dados,
SUM(decode(substr(t.contents, 1, 1), 'U', bytes, 0)) undo,
0 redo,
0 temp,
0 free
FROM dba_data_files f, dba_tablespaces t
WHERE f.tablespace_name = t.tablespace_name
UNION ALL
SELECT 0 dados,
0 undo,
0 redo,
SUM(bytes) temp,
0 free
FROM dba_temp_files f, dba_tablespaces t
WHERE f.tablespace_name = t.tablespace_name(+)
UNION ALL
SELECT 0 dados,
0 undo,
SUM(bytes * members) redo,
0 temp,
0 free
FROM v$log
UNION ALL
SELECT 0 dados,
0 undo,
0 redo,
0 temp,
SUM(bytes) free
FROM dba_free_space f, dba_tablespaces t
WHERE f.tablespace_name = t.tablespace_name AND
substr(t.contents, 1, 1) = 'P'
);
SET lines 1000
SET pages 2000
COL tablespace_name for a15
COL name for a60
COL status1 for a12
COL status2 for a12
COL status3 for a12
COL online_status for a12
COL status_backup for a12
COL recover for a7
COL error for a5
COL "FILE#" for 9999
SELECT df.tablespace_name,
d.FILE
d.NAME,
df.autoextensible,
df.bytes/1024/1024 "Total(Mb)",
d.status status1,
dh.STATUS status2,
df.status status3,
b.STATUS status_backup,
dh.RECOVER,
dh.ERROR
FROM v$datafile d,
dba_data_files df,
v$backup b,
v$datafile_header dh
WHERE d.FILE
AND d.FILE
AND d.FILE
ORDER BY df.tablespace_name,d.NAME;
archive log list;
CLEAR COLumns
SELECT
owner,
object_type,
count(*) "QTD_INVALIDOS"
FROM dba_objects
WHERE status ='INVALID'
GROUP BY owner, object_type;
SELECT
owner,
trunc(last_analyzed) LAST_ANALYZED,
count(*)
FROM DBA_TABLES
GROUP BY owner, trunc(last_analyzed)
ORDER BY OWNER, LAST_ANALYZED;
CLEAR COLumns
SET LINESIZE 200
SET PAGESIZE 1000
COLUMN username FORMAT A20
COLUMN event FORMAT A30
SELECT NVL(s.username, '(oracle)') AS username,
s.sid,
s.serial
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM gv$session_wait sw,
gv$session s
WHERE s.sid = sw.sid AND sw.event not like 'SQL%' AND sw.event not like 'rdbms%'
ORDER BY sw.seconds_in_wait DESC;
CLEAR COLumns
COL sid for 9999
COL username for A16 heading 'Username'
COL osuser for A20 heading 'OS user'
COL logical_reads for 999,999,999,990 heading 'Logical reads'
COL physical_reads for 999,999,999,990 heading 'Physical reads'
COL hr for A6 heading 'HR'
SELECT s.sid,
s.username,
s.osuser,
i.block_gets + i.consistent_gets logical_reads,
i.physical_reads,
to_char(
(i.block_gets + i.consistent_gets - i.physical_reads) /
(i.block_gets + i.consistent_gets) * 100, '9990') || '%' hr
FROM gv$sess_io i, gv$session s
WHERE i.INST_ID = s.INST_ID AND
i.sid = s.sid AND
i.block_gets + i.consistent_gets > 200000
ORDER BY logical_reads;
LISTA DE JOBS AGENDADOS NA DBA_JOBS BROKEN = N
SET lines 100 pages 999
COL schema_user format a15
COL fails format 999
COL job for 999999
SELECT
job,
schema_user,
to_char(last_date, 'hh24:mi dd/mm/yy') last_run,
to_char(next_date, 'hh24:mi dd/mm/yy') next_run,
failures fails,
broken,
substr(what, 1, 15) what
FROM dba_jobs
WHERE BROKEN='N'
ORDER BY 4
/
CLEAR COLUMNS
COL job FORM 999999
COL interval FORM a40
COL schema_user FORM a15
COL failures FORM 9999
ALTER SESSION SET nls_date_format='dd/mm/yyyy hh24:mi:ss';
SELECT sid,
job,
last_date,
last_sec,
this_date,
this_sec,
failures,
instance
FROM dba_jobs_running
ORDER BY job;
LISTA DE JOBS AGENDADOS NA DBA_SCHEDULER_JOBS
SELECT
JOB_NAME,
ENABLED,
TO_CHAR(NEXT_RUN_DATE,'DD-MM-YYHH24:MI:SS') PROXIMA_DATA,
RUN_COUNT,
FAILURE_COUNT
FROM DBA_SCHEDULER_JOBS ;
JOBS EM EXECUCAO NA V$SCHEDULER_RUNNING_JOBS
CLEAR COLUMNS
SET lines 1000 trims on
SELECT * FROM GV$SCHEDULER_RUNNING_JOBS ;
SELECT
substr(OPERATION, 1, 12) operation,
substr(STATUS, 1, 10) status,
MBYTES_PROCESSED,
INPUT_BYTES,
OUTPUT_BYTES,
substr(OPTIMIZED, 1, 10) optimized,
substr(OBJECT_TYPE, 1, 10) object_type,
substr(OUTPUT_DEVICE_TYPE, 1, 10) output_device_type,
START_TIME,
END_TIME,
rpad(round ((end_time - start_time) *1440 , 2), 8, ' ') Minutos,
lpad(round ((end_time - start_time) *1440 / 60 , 2) , 8, ' ') Horas
FROM
V$RMAN_STATUS
WHERE
start_time BETWEEN sysdate - 1 AND sysdate
ORDER BY
START_TIME;
SELECT DISTINCT status FROM v$backup;
SET lines 1000 trims on
SELECT
sid,
serial
sofar,
totalwork,
opname,
Round(sofar / totalwork * 100, 2) " % completado",
Round(time_remaining / 60) REMAINING,
Round(elapsed_seconds / 60) ELAPSED
FROM
gv$session_longops
WHERE
opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;
SET LINESIZE 190
COLUMN spid FORMAT A10
COLUMN username FORMAT A14
COLUMN PROGRAM FORMAT A40
COLUMN PROGRAM MACHINE A40
SELECT s.inst_id,
s.sid,
s.serial
p.spid,
s.username,
s.program, s.status, s.machine, to_char(s.LOGON_TIME,'dd-mm-yyyy HH24:MM') LOGON_TIME
FROM gv$session s
JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' ORDER BY s.STATUS;
SET term on
SET verify off
SET feed on
CLEAR COLumns
COL sid form 999
COL serial
COL sofar form 999,999,999
COL time_remaining form 999,999,999
COL username form a30
SELECT sid,
serial
totalwork,
sofar,
to_char(start_time,'dd/mm/yyyy hh24:mi:ss') start_time,
time_remaining "TEMPO_RESTANTE(S)",
time_remaining/60 "TEMPO_RESTANTE(MIN)",
time_remaining/60/60 "TEMPO_RESTANTE(HS)",
substr(username,1,20) username,
sql_address,
sql_id,
sql_hash_value
FROM gv$session_longops
WHERE time_remaining > 0
ORDER BY 6 desc ;
SET lines 300
SET pages 50000
COL username format a12
COL sid format 999999
COL inst_id format 99999
COL sql_text format a36
COL status format a8
SELECT
f.INST_ID,
f.sid,
f.username,
f.STATUS,
f.BLOCKING_INSTANCE,
f.blocking_session,
f.lockwait,
f.SECONDS_IN_WAIT,
f.LAST_CALL_ET,
s.SQL_TEXT,
s.hash_value
FROM
(
SELECT
INST_ID,
sid,
username,
STATUS,
BLOCKING_INSTANCE,
blocking_session,
lockwait,
SECONDS_IN_WAIT,
LAST_CALL_ET,
SQL_HASH_VALUE
FROM
gv$session
WHERE
blocking_session IS NOT NULL
AND SECONDS_IN_WAIT > 0
UNION ALL
SELECT
A.INST_ID,
A.sid,
A.username,
A.STATUS,
a.BLOCKING_INSTANCE,
A.blocking_session,
A.lockwait,
A.SECONDS_IN_WAIT,
A.LAST_CALL_ET,
A.SQL_HASH_VALUE
FROM
gv$session A,
gv$session B
WHERE
A.sid = B.blocking_session
AND A.INST_ID = b.BLOCKING_INSTANCE
)
f,
gv$sqlarea s
WHERE
f.inst_id = s.inst_id
AND
f.sql_hash_value = s.hash_value;
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A30
COL osuser for a10
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial
a.lockwait,
a.status,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM Gv$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND A.INST_ID=1
AND c.statistic
AND d.name = DECODE(UPPER('c.value'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
AND rownum < 11
ORDER BY c.value DESC;
SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF
COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A30
COL osuser for a10
SELECT NVL(a.username, '(oracle)') AS username,
a.osuser,
a.sid,
a.serial
a.lockwait,
a.status,
a.machine,
a.program,
TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM Gv$session a,
v$sesstat c,
v$statname d
WHERE a.sid = c.sid
AND A.INST_ID=2
AND c.statistic
AND d.name = DECODE(UPPER('c.value'), 'READS', 'session logical reads',
'EXECS', 'execute count',
'CPU', 'CPU used by this session',
'CPU used by this session')
AND rownum < 11
ORDER BY c.value DESC;
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A30
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
A.OSUSER,
a.program,
Trunc(b.value/1024/1024) AS memory_MB
FROM gv$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic
AND c.name = 'session pga memory'
AND a.program IS NOT NULL AND Trunc(b.value/1024/1024) > 0 AND a.inst_id=1
ORDER BY b.value DESC;
SET LINESIZE 200
COLUMN username FORMAT A20
COLUMN module FORMAT A30
SELECT NVL(a.username,'(oracle)') AS username,
a.module,
A.OSUSER,
a.program,
Trunc(b.value/1024/1024) AS memory_MB
FROM gv$session a,
v$sesstat b,
v$statname c
WHERE a.sid = b.sid
AND b.statistic
AND c.name = 'session pga memory'
AND a.program IS NOT NULL AND Trunc(b.value/1024/1024) > 0 AND a.inst_id=2
ORDER BY b.value DESC;
CLEAR COLUMNS
COL member for a60
SET pagesize 5000
SET lines 1000 trims on
SELECT
f.MEMBER,
f.GROUP
l.members,
l.bytes,
l.SEQUENCE
f.status status_1,
l.status status_2,
f.IS_RECOVERY_DEST_FILE
FROM
gv $ logfile f
JOIN
gv $ log l
ON l.GROUP
AND l.inst_id = 1
AND f.inst_id = 1
ORDER BY
GROUP
CLEAR COLUMNS
COL member for a60
SET pagesize 5000
SET lines 1000 trims on
SELECT
f.MEMBER,
f.GROUP
l.members,
l.bytes,
l.SEQUENCE
f.status status_1,
l.status status_2,
f.IS_RECOVERY_DEST_FILE
FROM
gv $ logfile f
JOIN
gv $ log l
ON l.GROUP
AND l.inst_id = 1
AND f.inst_id = 2
ORDER BY
GROUP
