Scripts de Monitoramento
Uso de Tablespaces
Monitora o percentual de espaço utilizado em cada tablespace. Use para prevenir problemas de espaço.
-- Verifica uso de tablespaces
SELECT
df.tablespace_name,
ROUND(df.total_mb, 2) AS total_mb,
ROUND(df.total_mb - fs.free_mb, 2) AS used_mb,
ROUND(fs.free_mb, 2) AS free_mb,
ROUND(((df.total_mb - fs.free_mb) / df.total_mb) * 100, 2) AS pct_used
FROM
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS total_mb
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;Sessões Ativas
Lista todas as sessões ativas no banco, mostrando usuário, programa, SQL sendo executado e status.
-- Lista sessões ativas com SQL atual
SELECT
s.sid,
s.serial#,
s.username,
s.status,
s.machine,
s.program,
s.logon_time,
sq.sql_text
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.username IS NOT NULL
AND s.status = 'ACTIVE'
ORDER BY s.logon_time DESC;Top SQLs por Tempo de Execução
Identifica os comandos SQL que consomem mais tempo total de CPU e elapsed time.
-- Top 10 SQLs por elapsed time
SELECT * FROM (
SELECT
sql_id,
executions,
ROUND(elapsed_time/1000000, 2) AS elapsed_sec,
ROUND(cpu_time/1000000, 2) AS cpu_sec,
ROUND(buffer_gets/NULLIF(executions,0), 2) AS gets_per_exec,
SUBSTR(sql_text, 1, 100) AS sql_text
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
)
WHERE ROWNUM <= 10;Uso de Memória (SGA/PGA)
Exibe o uso atual de SGA e PGA, comparando com limites configurados.
-- Uso de SGA
SELECT
name,
ROUND(value/1024/1024, 2) AS size_mb
FROM v$sga;
-- Uso de PGA
SELECT
name,
ROUND(value/1024/1024, 2) AS size_mb
FROM v$pgastat
WHERE name IN ('total PGA inuse', 'total PGA allocated', 'maximum PGA allocated');Scripts de Diagnóstico
Identificar Locks e Bloqueios
Mostra sessões que estão bloqueando outras e o que está sendo bloqueado.
-- Identifica locks e bloqueios
SELECT
l.session_id AS blocking_session,
s.username AS blocking_user,
s.machine AS blocking_machine,
s.program AS blocking_program,
l2.session_id AS waiting_session,
s2.username AS waiting_user,
l.oracle_username,
l.os_user_name,
o.object_name,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Exclusive',
4, 'Share',
5, 'Share Row Exclusive',
6, 'Exclusive') AS lock_mode
FROM v$locked_object l
JOIN v$session s ON l.session_id = s.sid
JOIN dba_objects o ON l.object_id = o.object_id
LEFT JOIN v$lock l2 ON l.session_id = l2.sid
LEFT JOIN v$session s2 ON l2.id1 = s2.blocking_session
WHERE s.blocking_session IS NOT NULL;Análise de Wait Events
Top eventos de espera no banco, essencial para troubleshooting de performance.
-- Top wait events
SELECT
event,
total_waits,
ROUND(time_waited/100, 2) AS time_waited_sec,
ROUND(average_wait, 2) AS avg_wait_ms,
wait_class
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;Objetos Inválidos
Lista todos os objetos inválidos (procedures, functions, packages, views).
-- Verifica objetos inválidos
SELECT
owner,
object_type,
object_name,
status,
last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
AND owner NOT IN ('SYS', 'SYSTEM', 'XDB', 'MDSYS')
ORDER BY owner, object_type, object_name;Análise de Redo Logs
Verifica a taxa de geração de redo logs e frequência de switches.
-- Switches de redo por hora
SELECT
TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,
COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 7
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')
ORDER BY hour DESC;Scripts de Administração
Informações Gerais do Banco
Exibe informações básicas do banco: versão, nome, modo de abertura, etc.
-- Informações da instância e database
SELECT
instance_name,
version,
status,
database_status,
startup_time,
host_name
FROM v$instance;
SELECT
name,
db_unique_name,
dbid,
platform_name,
log_mode,
open_mode,
created
FROM v$database;Tamanho Total do Banco
Calcula o tamanho total do banco de dados (datafiles + temp + redo).
-- Tamanho total do banco
SELECT
'Datafiles' AS file_type,
ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_data_files
UNION ALL
SELECT
'Temp Files' AS file_type,
ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM dba_temp_files
UNION ALL
SELECT
'Redo Logs' AS file_type,
ROUND(SUM(bytes)/1024/1024/1024, 2) AS size_gb
FROM v$log;Verificar Últimos Backups
Lista os backups mais recentes realizados via RMAN.
-- Últimos backups RMAN
SELECT
input_type,
status,
start_time,
end_time,
ROUND(elapsed_seconds/60, 2) AS elapsed_minutes,
ROUND(input_bytes/1024/1024/1024, 2) AS input_gb
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;Auditoria de Alterações DDL
Rastreia comandos DDL executados recentemente.
-- Auditoria de DDL recentes
SELECT
owner,
operation,
object_type,
object_name,
timestamp,
sql_text
FROM dba_ddl_locks
WHERE owner NOT IN ('SYS', 'SYSTEM')
ORDER BY timestamp DESC;Dicas de Uso
Salve os scripts em arquivos .sql
Mantenha uma biblioteca organizada de scripts para reutilização rápida.
Use aliases e variáveis
Crie scripts parametrizados para maior flexibilidade (ex: aceitar tablespace como parâmetro).
Agende execução automática
Use DBMS_SCHEDULER para executar scripts de monitoramento periodicamente.
Documente suas personalizações
Adicione comentários explicando mudanças e casos de uso específicos.
Teste em ambiente de desenvolvimento
Sempre teste scripts novos antes de executar em produção.