Oracle Quick Reference Queries for LIMS Troubleshooting¶
Copy and paste these directly into SQL*Plus, SQL Developer, or Toad.
Immediate Problem Identification¶
1. Find Blocking Sessions¶
Run first when users report "system is slow":
2. Long-Running Queries (>5 minutes)¶
SELECT sid, serial#, username, machine, ROUND(last_call_et/60,2) as minutes_running, sql_id
FROM v$session
WHERE status = 'ACTIVE' AND type = 'USER' AND last_call_et > 300
ORDER BY last_call_et DESC;
3. Current Active LIMS Sessions¶
SELECT sid, serial#, username, machine, program, status, sql_id
FROM v$session
WHERE username IS NOT NULL
AND (UPPER(program) LIKE '%IIS%' OR UPPER(program) LIKE '%W3WP%' OR UPPER(username) LIKE '%LIMS%')
ORDER BY logon_time DESC;
4. Tablespace Usage Over 80%¶
SELECT tablespace_name,
ROUND(((total_mb - free_mb) / total_mb) * 100, 2) as pct_used,
total_mb, free_mb
FROM (
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024, 2) as total_mb
FROM dba_data_files GROUP BY tablespace_name
) total
JOIN (
SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024, 2) as free_mb
FROM dba_free_space GROUP BY tablespace_name
) free USING (tablespace_name)
WHERE ((total_mb - free_mb) / total_mb) * 100 > 80
ORDER BY pct_used DESC;
5. Top Wait Events Causing Performance Issues¶
SELECT event, total_waits, ROUND(time_waited/100, 2) as time_waited_sec
FROM v$system_event
WHERE wait_class != 'Idle' AND time_waited > 0
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;
Database Performance Analysis¶
6. Most Expensive SQL Statements (Last Hour)¶
SELECT sql_id, executions,
ROUND(elapsed_time/1000000, 2) as elapsed_seconds,
ROUND(cpu_time/1000000, 2) as cpu_seconds,
disk_reads, buffer_gets,
SUBSTR(sql_text, 1, 80) as sql_snippet
FROM v$sql
WHERE last_active_time > SYSDATE - 1/24
AND executions > 0 AND elapsed_time > 1000000
ORDER BY elapsed_time DESC
FETCH FIRST 15 ROWS ONLY;
7. Connection Count by Application¶
SELECT program, machine, username, COUNT(*) as connection_count,
COUNT(CASE WHEN status = 'ACTIVE' THEN 1 END) as active_count
FROM v$session
WHERE type = 'USER' AND username IS NOT NULL
GROUP BY program, machine, username
HAVING COUNT(*) > 3
ORDER BY connection_count DESC;
8. Database Performance Metrics Snapshot¶
SELECT metric_name, value, metric_unit
FROM v$sysmetric
WHERE group_id = 2
AND metric_name IN (
'Database CPU Time Ratio',
'Database Wait Time Ratio',
'Buffer Cache Hit Ratio',
'SQL Service Response Time',
'Physical Reads Per Sec',
'User Transaction Per Sec'
)
ORDER BY metric_name;
Lock and Blocking Analysis¶
9. Detailed Blocking Session Information¶
SELECT
s1.sid as blocked_sid,
s1.username as blocked_user,
s1.machine as blocked_machine,
s1.program as blocked_program,
s2.sid as blocking_sid,
s2.username as blocking_user,
s2.machine as blocking_machine,
s2.program as blocking_program,
o.object_name,
o.object_type
FROM v$session s1
JOIN v$session s2 ON s1.blocking_session = s2.sid
LEFT JOIN v$locked_object lo ON s1.sid = lo.session_id
LEFT JOIN dba_objects o ON lo.object_id = o.object_id
WHERE s1.blocking_session IS NOT NULL;
10. Long-Running Transactions (Potential Blockers)¶
SELECT
s.sid, s.serial#, s.username, s.machine,
t.start_time,
ROUND((SYSDATE - t.start_time) * 24 * 60, 2) as runtime_minutes,
ROUND(t.used_ublk * 8192 / 1024 / 1024, 2) as undo_mb
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE (SYSDATE - t.start_time) * 24 * 60 > 30
ORDER BY runtime_minutes DESC;
Space and Storage Monitoring¶
11. Tablespace Usage with Autoextend Info¶
SELECT
df.tablespace_name,
ROUND(df.total_mb, 2) as total_mb,
ROUND(df.max_mb, 2) as max_mb,
ROUND(NVL(fs.free_mb, 0), 2) as free_mb,
ROUND((df.total_mb - NVL(fs.free_mb, 0)), 2) as used_mb,
ROUND(((df.total_mb - NVL(fs.free_mb, 0)) / df.total_mb) * 100, 2) as pct_used,
CASE WHEN df.max_mb > df.total_mb THEN 'YES' ELSE 'NO' END as can_extend
FROM (
SELECT tablespace_name,
SUM(bytes)/1024/1024 as total_mb,
SUM(maxbytes)/1024/1024 as max_mb
FROM dba_data_files GROUP BY tablespace_name
) df
LEFT JOIN (
SELECT tablespace_name, SUM(bytes)/1024/1024 as free_mb
FROM dba_free_space GROUP BY tablespace_name
) fs ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_used DESC;
12. Large Tables (Potential Growth Issues)¶
SELECT owner, table_name,
ROUND(bytes/1024/1024, 2) as size_mb,
num_rows,
last_analyzed
FROM dba_segments s
JOIN dba_tables t ON s.segment_name = t.table_name AND s.owner = t.owner
WHERE s.segment_type = 'TABLE'
AND s.bytes > 100*1024*1024 -- Larger than 100MB
AND s.owner NOT IN ('SYS', 'SYSTEM')
ORDER BY bytes DESC;
Session and SQL Troubleshooting¶
13. Current SQL Being Executed by Active Sessions¶
SELECT
s.sid, s.serial#, s.username, s.machine, s.status,
s.sql_id, sq.sql_text,
ROUND(s.last_call_et/60, 2) as minutes_active
FROM v$session s
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE s.status = 'ACTIVE'
AND s.type = 'USER'
AND s.username IS NOT NULL
ORDER BY s.last_call_et DESC;
14. Sessions Waiting on Specific Events¶
SELECT
sid, serial#, username, machine,
event, wait_class,
ROUND(wait_time_micro/1000000, 2) as wait_seconds,
blocking_session
FROM v$session
WHERE wait_class NOT IN ('Idle')
AND status = 'ACTIVE'
AND type = 'USER'
ORDER BY wait_time_micro DESC;
15. Kill Session Command Generator (For Problem Sessions)¶
SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' as kill_command,
sid, serial#, username, machine, program, status,
ROUND(last_call_et/60, 2) as minutes_idle
FROM v$session
WHERE status = 'INACTIVE'
AND type = 'USER'
AND last_call_et > 3600 -- Idle for more than 1 hour
AND username IS NOT NULL;
Undo and Temporary Space¶
16. UNDO Tablespace Usage¶
SELECT
tablespace_name,
status,
ROUND(bytes/1024/1024, 2) as size_mb,
ROUND(maxbytes/1024/1024, 2) as max_mb,
autoextensible
FROM dba_data_files
WHERE tablespace_name LIKE '%UNDO%'
ORDER BY tablespace_name;
17. Temporary Tablespace Usage¶
SELECT
s.sid, s.serial#, s.username, s.machine,
ROUND(t.blocks * 8192 / 1024 / 1024, 2) as temp_mb,
t.tablespace as temp_tablespace
FROM v$tempseg_usage t
JOIN v$session s ON t.session_addr = s.saddr
ORDER BY temp_mb DESC;
Recent Errors and Alerts¶
18. Recent Database Errors (Oracle 11g+)¶
SELECT
timestamp,
message_text
FROM v$diag_alert_ext
WHERE component_id = 'rdbms'
AND message_level <= 16 -- Errors and warnings
AND timestamp > SYSDATE - 1 -- Last 24 hours
ORDER BY timestamp DESC
FETCH FIRST 20 ROWS ONLY;
19. Invalid Objects That Might Cause LIMS Errors¶
SELECT owner, object_name, object_type, status, last_ddl_time
FROM dba_objects
WHERE status = 'INVALID'
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, object_type, object_name;
20. Recent Failed Login Attempts¶
SELECT username, timestamp, returncode, client_id, spare1
FROM dba_audit_trail
WHERE action_name = 'LOGON'
AND returncode != 0 -- Failed logins
AND timestamp > SYSDATE - 1 -- Last 24 hours
ORDER BY timestamp DESC;
Quick Troubleshooting Workflow¶
1. Immediate Issues¶
- Run queries 1-5 first when users report problems
- Look for blocking sessions, long queries, space issues
2. Performance Problems¶
- Run queries 6-8 to identify expensive SQL
- Check wait events for bottlenecks
3. Locking Issues¶
- Use queries 9-10 for detailed blocking analysis
- Generate kill commands if needed (query 15)
4. Space Problems¶
- Queries 11-12 for tablespace and table growth
- Monitor UNDO and TEMP usage (16-17)
5. Application Errors¶
- Check recent errors (18) and invalid objects (19)
- Review failed logins (20)
Tips¶
- Save frequently used queries: Create a .sql file with your most common queries for quick access
- Run queries in order: Start with immediate issues, then drill down to specific problems
- Document your findings: Keep notes on what queries revealed which issues