Oracle DBA Issues and Solutions - Common Problems & Fixes

Oracle DBA Issues and Solutions

A quick reference guide to the most common Oracle database problems and their solutions. Each issue includes diagnostic queries and step-by-step fixes.

Performance Issues

Database Running Slow

Quick Diagnosis:

-- Check top wait events
SELECT event, total_waits, time_waited_micro/1000000 as seconds
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited_micro DESC
FETCH FIRST 10 ROWS ONLY;

-- Find resource-intensive sessions
SELECT sid, serial#, username, sql_id, event, seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE' AND type = 'USER'
ORDER BY seconds_in_wait DESC;

Solutions:

Generate AWR Report for detailed analysis
Check for blocking locks
Review execution plans

High CPU Usage

-- Sessions consuming most CPU
SELECT s.sid, s.serial#, s.username, s.program,
       st.value/100 as cpu_seconds
FROM v$session s
JOIN v$sesstat st ON s.sid = st.sid
JOIN v$statname sn ON st.statistic# = sn.statistic#
WHERE sn.name = 'CPU used by this session'
ORDER BY st.value DESC
FETCH FIRST 10 ROWS ONLY;

Related: CPU Analysis Scripts

Space Issues

Tablespace Full

Quick Check:

SELECT tablespace_name,
       ROUND(used_percent, 2) as pct_used,
       ROUND((tablespace_size - used_space) * 8192/1024/1024, 2) as free_mb
FROM dba_tablespace_usage_metrics
WHERE used_percent > 85
ORDER BY used_percent DESC;

Solutions:

Add datafile: ALTER TABLESPACE ts ADD DATAFILE '/path/file.dbf' SIZE 10G;
Enable autoextend: ALTER DATABASE DATAFILE '/path/file.dbf' AUTOEXTEND ON;
Identify large segments: dsegbig.sql

Related: Space Analysis Scripts

TEMP Tablespace Full

-- Check TEMP usage
SELECT tablespace_name, used_blocks, free_blocks
FROM v$temp_space_header;

-- Find sessions using TEMP
SELECT s.sid, s.serial#, s.username, u.blocks * 8/1024 as temp_mb
FROM v$session s
JOIN v$tempseg_usage u ON s.saddr = u.session_addr
ORDER BY u.blocks DESC;

Related: ORA-01652

Connection Issues

Cannot Connect to Database

Error	Quick Fix
ORA-12154	Check tnsnames.ora syntax
ORA-12541	Start the listener: `lsnrctl start`
ORA-01034	Start the database: `startup`
ORA-28000	Unlock account: `ALTER USER x ACCOUNT UNLOCK;`

Too Many Connections

-- Check current vs max sessions
SELECT
  (SELECT value FROM v$parameter WHERE name = 'sessions') as max_sessions,
  (SELECT COUNT(*) FROM v$session) as current_sessions;

-- Sessions by program
SELECT program, COUNT(*) as count
FROM v$session
GROUP BY program
ORDER BY COUNT(*) DESC;

Related: ORA-00018 | ORA-00020

Lock and Blocking Issues

Find Blocking Sessions

SELECT
  blocker.sid as blocker_sid,
  blocker.username as blocker_user,
  waiting.sid as waiting_sid,
  waiting.username as waiting_user,
  waiting.event
FROM v$session waiting
JOIN v$session blocker ON waiting.blocking_session = blocker.sid
WHERE waiting.blocking_session IS NOT NULL;

Kill Blocking Session:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Related: gvlockb.sql | ORA-00060

Backup & Recovery Issues

Check Backup Status

SELECT input_type, status, start_time, end_time
FROM v$rman_backup_job_details
WHERE start_time > SYSDATE - 7
ORDER BY start_time DESC;

Flash Recovery Area Full

-- Check FRA usage
SELECT * FROM v$flash_recovery_area_usage;

-- Delete obsolete backups
RMAN> DELETE OBSOLETE;

Related: ORA-19815 | FRA Scripts

Invalid Objects

Fix Invalid Objects

-- Count invalid objects
SELECT object_type, COUNT(*)
FROM dba_objects
WHERE status = 'INVALID'
GROUP BY object_type;

-- Recompile all
EXEC UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile specific schema
EXEC UTL_RECOMP.RECOMP_SERIAL('SCHEMA_NAME');

Related: dinvalid.sql

Common Error Quick Reference

Error	Issue	Quick Fix
ORA-00001	Duplicate key	Check unique constraints
ORA-00904	Invalid column	Check column names
ORA-00942	Table not found	Check schema/grants
ORA-01000	Too many cursors	Increase OPEN_CURSORS
ORA-01403	No data found	Add exception handling
ORA-01555	Snapshot too old	Increase UNDO retention
ORA-04031	Shared pool issue	Flush or resize SGA

Monitoring Checklist

Daily checks for Oracle DBAs:

Alert Log - Check for errors
Tablespace Space - Verify free space > 15%
Backup Status - Confirm successful completion
Performance - Review wait events
Invalid Objects - Recompile if needed

Scripts: Health Check Suite