Direct SQL queries for troubleshooting Oracle LIMS database issues when you have database access but limited/no server access.
-- Current database status and basic info
SELECT
NAME AS DATABASE_NAME,
DBID,
TO_CHAR(CREATED, 'DD-MON-YYYY HH24:MI:SS') AS CREATED,
LOG_MODE,
OPEN_MODE,
DATABASE_ROLE,
TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS CURRENT_TIME
FROM V$DATABASE;
-- Instance information
SELECT
INSTANCE_NAME,
HOST_NAME,
VERSION,
STATUS,
TO_CHAR(STARTUP_TIME, 'DD-MON-YYYY HH24:MI:SS') AS STARTUP_TIME,
DATABASE_STATUS
FROM V$INSTANCE;
-- Check your current connection details
SELECT
USER AS CURRENT_USER,
SYS_CONTEXT('USERENV','SESSION_USER') AS SESSION_USER,
SYS_CONTEXT('USERENV','SERVER_HOST') AS SERVER_HOST,
SYS_CONTEXT('USERENV','DB_NAME') AS DB_NAME,
SYS_CONTEXT('USERENV','INSTANCE_NAME') AS INSTANCE_NAME,
SYS_CONTEXT('USERENV','IP_ADDRESS') AS CLIENT_IP
FROM DUAL;
-- Check your privileges
SELECT * FROM USER_SYS_PRIVS WHERE ROWNUM <= 20;
SELECT * FROM USER_TAB_PRIVS WHERE ROWNUM <= 20;
---
-- See all active sessions and what they're doing
SELECT
SID,
SERIAL#,
USERNAME,
PROGRAM,
MACHINE,
STATUS,
COMMAND,
LAST_CALL_ET AS SECONDS_SINCE_LAST_CALL,
LOGON_TIME,
SQL_ID
FROM V$SESSION
WHERE USERNAME IS NOT NULL
AND STATUS = 'ACTIVE'
ORDER BY LAST_CALL_ET DESC;
-- Find queries running longer than 5 minutes
SELECT
s.SID,
s.SERIAL#,
s.USERNAME,
s.MACHINE,
s.PROGRAM,
ROUND(s.LAST_CALL_ET/60, 2) AS MINUTES_RUNNING,
sql.SQL_TEXT
FROM V$SESSION s
LEFT JOIN V$SQLAREA sql ON s.SQL_ID = sql.SQL_ID
WHERE s.USERNAME IS NOT NULL
AND s.STATUS = 'ACTIVE'
AND s.LAST_CALL_ET > 300 -- More than 5 minutes
ORDER BY s.LAST_CALL_ET DESC;
-- Critical: Find blocking sessions (locks)
SELECT
'BLOCKING SESSIONS DETECTED' AS ALERT_TYPE,
COUNT(*) AS BLOCKING_COUNT
FROM V$LOCK
WHERE BLOCK = 1;
-- Detailed blocking information
SELECT
l1.SID AS WAITING_SESSION,
l2.SID AS BLOCKING_SESSION,
s1.USERNAME AS WAITING_USER,
s2.USERNAME AS BLOCKING_USER,
s1.MACHINE AS WAITING_MACHINE,
s2.MACHINE AS BLOCKING_MACHINE,
s1.PROGRAM AS WAITING_PROGRAM,
s2.PROGRAM AS BLOCKING_PROGRAM,
o.OBJECT_NAME AS LOCKED_OBJECT,
l1.TYPE AS LOCK_TYPE,
l1.LMODE AS LOCK_MODE_HELD,
l1.REQUEST AS LOCK_MODE_REQUESTED
FROM V$LOCK l1
JOIN V$LOCK l2 ON l1.ID1 = l2.ID1 AND l1.ID2 = l2.ID2
JOIN V$SESSION s1 ON l1.SID = s1.SID
JOIN V$SESSION s2 ON l2.SID = s2.SID
LEFT JOIN DBA_OBJECTS o ON l1.ID1 = o.OBJECT_ID
WHERE l1.BLOCK = 0 AND l2.BLOCK = 1
AND l1.TYPE = l2.TYPE;
-- See what SQL is currently running
SELECT
s.SID,
s.USERNAME,
s.MACHINE,
s.PROGRAM,
sa.SQL_TEXT,
s.LAST_CALL_ET AS SECONDS_RUNNING
FROM V$SESSION s
JOIN V$SQLAREA sa ON s.SQL_ID = sa.SQL_ID
WHERE s.USERNAME IS NOT NULL
AND s.STATUS = 'ACTIVE'
AND s.SQL_ID IS NOT NULL
ORDER BY s.LAST_CALL_ET DESC;
---
-- Check tablespace usage - critical for LIMS performance
SELECT
ts.TABLESPACE_NAME,
ROUND(ts.TOTAL_MB, 2) AS TOTAL_MB,
ROUND(ts.TOTAL_MB - NVL(fs.FREE_MB, 0), 2) AS USED_MB,
ROUND(NVL(fs.FREE_MB, 0), 2) AS FREE_MB,
ROUND(((ts.TOTAL_MB - NVL(fs.FREE_MB, 0)) / ts.TOTAL_MB) * 100, 2) AS PCT_USED,
CASE
WHEN ((ts.TOTAL_MB - NVL(fs.FREE_MB, 0)) / ts.TOTAL_MB) * 100 > 90 THEN '⚠️ CRITICAL'
WHEN ((ts.TOTAL_MB - NVL(fs.FREE_MB, 0)) / ts.TOTAL_MB) * 100 > 80 THEN '⚠️ WARNING'
ELSE '✅ OK'
END AS STATUS
FROM
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS TOTAL_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) ts
LEFT JOIN
(SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS FREE_MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) fs
ON ts.TABLESPACE_NAME = fs.TABLESPACE_NAME
ORDER BY PCT_USED DESC;
-- Check temporary tablespace usage
SELECT
t.TABLESPACE_NAME,
ROUND(SUM(t.BYTES_USED)/1024/1024, 2) AS TEMP_USED_MB,
ROUND(SUM(t.BYTES_FREE)/1024/1024, 2) AS TEMP_FREE_MB,
s.USERNAME,
s.SID,
s.MACHINE,
s.PROGRAM
FROM V$TEMP_SPACE_HEADER t
LEFT JOIN V$SESSION s ON t.SESSION_ID = s.SID
GROUP BY t.TABLESPACE_NAME, s.USERNAME, s.SID, s.MACHINE, s.PROGRAM
HAVING SUM(t.BYTES_USED) > 0
ORDER BY SUM(t.BYTES_USED) DESC;
---
-- Check LIMS-related tables (adjust table names for your LIMS)
SELECT
TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN,
ROUND((NUM_ROWS * AVG_ROW_LEN)/1024/1024, 2) AS ESTIMATED_SIZE_MB,
LAST_ANALYZED,
TRUNC(SYSDATE - LAST_ANALYZED) AS DAYS_SINCE_ANALYZED
FROM USER_TABLES
WHERE TABLE_NAME LIKE '%SAMPLE%'
OR TABLE_NAME LIKE '%TEST%'
OR TABLE_NAME LIKE '%RESULT%'
OR TABLE_NAME LIKE '%SPECIMEN%'
OR TABLE_NAME LIKE '%PATIENT%'
OR TABLE_NAME LIKE '%ORDER%'
ORDER BY NUM_ROWS DESC NULLS LAST;
-- Check recent database alerts and errors
SELECT
TO_CHAR(ORIGINATING_TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') AS ERROR_TIME,
MESSAGE_TYPE,
MESSAGE_LEVEL,
SUBSTR(MESSAGE, 1, 100) AS ERROR_MESSAGE
FROM V$ALERT_HISTORY
WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1 -- Last 24 hours
ORDER BY ORIGINATING_TIMESTAMP DESC;
-- Find invalid objects in your schema
SELECT
OBJECT_TYPE,
OBJECT_NAME,
STATUS,
LAST_DDL_TIME
FROM USER_OBJECTS
WHERE STATUS != 'VALID'
ORDER BY OBJECT_TYPE, OBJECT_NAME;
-- If you have DBA privileges, check all schemas
SELECT
OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS,
LAST_DDL_TIME
FROM DBA_OBJECTS
WHERE STATUS != 'VALID'
AND OWNER NOT IN ('SYS', 'SYSTEM', 'DBSNMP', 'SYSMAN', 'OUTLN')
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
---
-- See what the database is waiting for
SELECT
EVENT,
TOTAL_WAITS,
ROUND(TIME_WAITED_MICRO/1000000, 2) AS TIME_WAITED_SECONDS,
ROUND(AVERAGE_WAIT, 2) AS AVERAGE_WAIT_MS,
ROUND((TIME_WAITED_MICRO/1000000) /
(SELECT SUM(TIME_WAITED_MICRO/1000000) FROM V$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL*Net%' AND EVENT NOT LIKE '%idle%') * 100, 2) AS PCT_TOTAL_WAIT_TIME
FROM V$SYSTEM_EVENT
WHERE EVENT NOT LIKE 'SQL*Net%'
AND EVENT NOT LIKE '%idle%'
AND TIME_WAITED_MICRO > 0
ORDER BY TIME_WAITED_MICRO DESC
FETCH FIRST 10 ROWS ONLY;
-- Find slowest SQL statements
SELECT
SQL_ID,
EXECUTIONS,
ROUND(ELAPSED_TIME/1000000, 2) AS ELAPSED_SECONDS,
ROUND(ELAPSED_TIME/EXECUTIONS/1000000, 4) AS AVG_ELAPSED_SECONDS,
ROUND(CPU_TIME/1000000, 2) AS CPU_SECONDS,
BUFFER_GETS,
DISK_READS,
SUBSTR(SQL_TEXT, 1, 80) AS SQL_PREVIEW
FROM V$SQLSTATS
WHERE EXECUTIONS > 0
AND SQL_TEXT NOT LIKE '%V$%'
AND SQL_TEXT NOT LIKE '%DBA_%'
ORDER BY ELAPSED_TIME DESC
FETCH FIRST 15 ROWS ONLY;
-- Check database size growth
SELECT
'DATA_FILES' 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;
---
-- Current session counts by program
SELECT
PROGRAM,
STATUS,
COUNT(*) AS SESSION_COUNT
FROM V$SESSION
WHERE USERNAME IS NOT NULL
GROUP BY PROGRAM, STATUS
ORDER BY SESSION_COUNT DESC;
-- Check if hitting resource limits
SELECT
RESOURCE_NAME,
CURRENT_UTILIZATION,
MAX_UTILIZATION,
INITIAL_ALLOCATION,
LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE LIMIT_VALUE != 'UNLIMITED'
AND MAX_UTILIZATION > 0
ORDER BY (CURRENT_UTILIZATION/DECODE(LIMIT_VALUE,'UNLIMITED',99999999,TO_NUMBER(LIMIT_VALUE))) DESC;
---
-- Look for specific LIMS-related activity
SELECT
s.SID,
s.USERNAME,
s.MACHINE,
s.PROGRAM,
s.STATUS,
s.LAST_CALL_ET,
sa.SQL_TEXT
FROM V$SESSION s
LEFT JOIN V$SQLAREA sa ON s.SQL_ID = sa.SQL_ID
WHERE (s.PROGRAM LIKE '%LIMS%'
OR s.PROGRAM LIKE '%LAB%'
OR s.MACHINE LIKE '%LIMS%'
OR UPPER(sa.SQL_TEXT) LIKE '%SAMPLE%'
OR UPPER(sa.SQL_TEXT) LIKE '%RESULT%'
OR UPPER(sa.SQL_TEXT) LIKE '%TEST%')
AND s.USERNAME IS NOT NULL
ORDER BY s.LAST_CALL_ET DESC;
-- Find locks on specific objects
SELECT
o.OBJECT_NAME,
l.SID,
s.USERNAME,
s.MACHINE,
s.PROGRAM,
l.TYPE AS LOCK_TYPE,
DECODE(l.LMODE,
0, 'None',
1, 'Null',
2, 'Row-S',
3, 'Row-X',
4, 'Share',
5, 'S/Row-X',
6, 'Exclusive') AS LOCK_MODE
FROM V$LOCK l
JOIN V$SESSION s ON l.SID = s.SID
LEFT JOIN DBA_OBJECTS o ON l.ID1 = o.OBJECT_ID
WHERE o.OBJECT_NAME LIKE '%SAMPLE%'
OR o.OBJECT_NAME LIKE '%TEST%'
OR o.OBJECT_NAME LIKE '%RESULT%'
OR o.OBJECT_NAME LIKE '%SPECIMEN%'
ORDER BY o.OBJECT_NAME, l.SID;
---
-- Find the session first
SELECT SID, SERIAL#, USERNAME, MACHINE, PROGRAM, STATUS
FROM V$SESSION
WHERE USERNAME = 'PROBLEMATIC_USER';
-- Then kill it (requires privileges)
-- ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
-- Example: ALTER SYSTEM KILL SESSION '123,45678';
-- One-query LIMS health check
SELECT
'DATABASE_STATUS' AS METRIC,
OPEN_MODE AS VALUE
FROM V$DATABASE
UNION ALL
SELECT
'INVALID_OBJECTS' AS METRIC,
TO_CHAR(COUNT(*)) AS VALUE
FROM USER_OBJECTS
WHERE STATUS != 'VALID'
UNION ALL
SELECT
'ACTIVE_SESSIONS' AS METRIC,
TO_CHAR(COUNT(*)) AS VALUE
FROM V$SESSION
WHERE USERNAME IS NOT NULL AND STATUS = 'ACTIVE'
UNION ALL
SELECT
'BLOCKING_SESSIONS' AS METRIC,
TO_CHAR(COUNT(*)) AS VALUE
FROM V$LOCK
WHERE BLOCK = 1
UNION ALL
SELECT
'LONG_RUNNING_QUERIES' AS METRIC,
TO_CHAR(COUNT(*)) AS VALUE
FROM V$SESSION
WHERE USERNAME IS NOT NULL AND STATUS = 'ACTIVE' AND LAST_CALL_ET > 300;
---
1. *SQLPlus**: Copy/paste directly
2. SQL Developer: Run in worksheet
3. Toad: Execute in editor
4. Any Oracle client: Should work with standard Oracle syntax
1. Start with Quick Health Check (#1, #20)
2. Check for Blocking (#5) - Critical first step
3. Look for Long Runners (#4) - Common LIMS issue
4. Check Space (#7) - Prevents crashes
5. Investigate Specific Issues based on findings
1. Blocking sessions - Immediate resolution needed
2. Tablespace full - Database will stop
3. Long-running queries - May indicate hung processes
4. Invalid objects - Can break LIMS functionality
Key Features:
🎯 Immediate Issue Detection:
Blocking sessions (query #5) - Run this first in emergencies
Long-running queries (#4) - Common LIMS performance killer
Tablespace usage (#7) - Prevents database crashes
Invalid objects (#11) - Can break LIMS functionality
📊 LIMS-Specific Monitoring:
LIMS table health (#9) - Checks SAMPLE, TEST, RESULT, SPECIMEN tables
LIMS processes (#17) - Find running LIMS-related sessions
Lock contention (#18) - Locks on critical LIMS tables
🔧 No Special Privileges Needed:
Most queries use standard V$ views available to regular users
USER_ views for your schema objects
Clearly marked when DBA privileges required
⚡ Emergency-Ready:
Quick health summary (#20) - One query to rule them all
Session kill commands (#19) - When you need to stop runaway processes
Real-time monitoring - See what's happening right now
Typical Troubleshooting Workflow:
Quick Health Check → Query #20 (overall status)
Check for Blocking → Query #5 (critical first step)
Find Long Runners → Query #4 (common LIMS issue)
Check Space Usage → Query #7 (prevent crashes)
LIMS-Specific Issues → Queries #9, #17, #18