ALL instances of the following are PLACEHOLDERS you must replace:
SCHEMA_NAME = Your actual LIMS schema name SAMPLE_TABLE, ERROR_TABLE, BATCH_TABLE, etc. = Your actual table namessample_status, error_message, batch_id, etc. = Your actual column namesREAL Oracle system tables (never change these):
all_tables, all_tab_columns, all_constraints, v$session, v$lock, dba_usersExamples:
SELECT * FROM all_tables WHERE owner = 'STARLIMS_PROD' ← STARLIMS_PROD is placeholderSELECT * FROM STARLIMS_PROD.SAMPLE ← Both STARLIMS_PROD and SAMPLE are placeholders SELECT * FROM v$session ← v$session is real Oracle, never change⚠️ CRITICAL: In every query below, YOU MUST replace schema names, table names, and column names with your actual LIMS values. Only Oracle system objects (all_tables, v$session, etc.) are real.
-- Find your LIMS schema
SELECT DISTINCT owner FROM all_tables WHERE table_name LIKE '%SAMPLE%';
-- Get all table names (replace SCHEMA_NAME with result above)
SELECT table_name, num_rows FROM all_tables WHERE owner = 'SCHEMA_NAME' ORDER BY num_rows DESC; -- <-- PLACEHOLDER: Use your actual schema
-- Map table relationships
SELECT a.table_name child, a.column_name child_col, c_pk.table_name parent, c_pk.column_name parent_col
FROM all_cons_columns a
JOIN all_constraints c ON a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name
JOIN all_cons_columns c_pk ON c_pk.constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R' AND a.owner = 'SCHEMA_NAME'; -- <-- PLACEHOLDER: Use your actual schema
-- Find blocking sessions
SELECT s1.sid blocking_sid, s1.username blocking_user, s1.machine blocking_machine,
s2.sid blocked_sid, s2.username blocked_user, s2.machine blocked_machine,
o.object_name locked_object, l1.type lock_type
FROM v$lock l1, v$session s1, v$lock l2, v$session s2, dba_objects o
WHERE s1.sid = l1.sid AND s2.sid = l2.sid AND l1.id1 = l2.id1
AND l1.block = 1 AND l2.request > 0 AND l1.id2 = o.object_id;
-- Long running sessions (> 30 minutes)
SELECT sid, serial#, username, status, last_call_et/60 minutes_running, machine, program, sql_id
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL AND last_call_et > 1800
ORDER BY last_call_et DESC;
-- Current SQL being executed
SELECT s.sid, s.username, s.machine, t.sql_text
FROM v$session s, v$sqltext t
WHERE s.sql_address = t.address AND s.status = 'ACTIVE' AND s.username IS NOT NULL
ORDER BY s.sid, t.piece;
-- Tablespace usage critical check
SELECT tablespace_name,
ROUND(used_space * 8192 / 1024 / 1024, 2) used_mb,
ROUND(tablespace_size * 8192 / 1024 / 1024, 2) total_mb,
ROUND((used_space / tablespace_size) * 100, 2) pct_used
FROM dba_tablespace_usage_metrics
WHERE ROUND((used_space / tablespace_size) * 100, 2) > 85
ORDER BY pct_used DESC;
-- Find largest tables consuming space
SELECT owner, table_name, num_rows,
ROUND(avg_row_len * num_rows / 1024 / 1024, 2) est_size_mb
FROM all_tables
WHERE owner = 'SCHEMA_NAME' AND num_rows > 0 -- <-- PLACEHOLDER: Use your actual schema
ORDER BY est_size_mb DESC;
-- Archive log space issues
SELECT dest_name, status, destination, error FROM v$archive_dest WHERE status != 'INACTIVE';
-- Disabled/broken constraints
SELECT owner, table_name, constraint_name, constraint_type, status, validated
FROM all_constraints
WHERE owner = 'SCHEMA_NAME' AND (status = 'DISABLED' OR validated = 'NOT VALIDATED') -- <-- PLACEHOLDER: Use your actual schema
ORDER BY table_name;
-- Foreign key constraint violations (orphaned records)
SELECT 'Table: ' || a.table_name || ' Column: ' || a.column_name ||
' References: ' || c_pk.table_name || '.' || c_pk.column_name as violation_check,
'SELECT COUNT(*) FROM ' || a.owner || '.' || a.table_name || ' t1 WHERE NOT EXISTS (SELECT 1 FROM ' ||
c_pk.owner || '.' || c_pk.table_name || ' t2 WHERE t2.' || c_pk.column_name || ' = t1.' || a.column_name || ');' as check_query
FROM all_cons_columns a
JOIN all_constraints c ON a.constraint_name = c.constraint_name
JOIN all_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name
JOIN all_cons_columns c_pk ON c_pk.constraint_name = c_pk.constraint_name
WHERE c.constraint_type = 'R' AND a.owner = 'SCHEMA_NAME';
-- Check constraint violations
SELECT constraint_name, table_name, search_condition
FROM all_constraints
WHERE owner = 'SCHEMA_NAME' AND constraint_type = 'C'
AND search_condition IS NOT NULL AND constraint_name NOT LIKE 'SYS_%';
-- Find duplicate records in key tables
SELECT 'Duplicates in ' || table_name as issue,
'SELECT column_list, COUNT(*) FROM ' || owner || '.' || table_name ||
' GROUP BY column_list HAVING COUNT(*) > 1;' as check_query
FROM all_tables WHERE owner = 'SCHEMA_NAME' AND table_name LIKE '%SAMPLE%';
-- NULL values where they shouldn't be
SELECT table_name, column_name, nullable
FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME' AND nullable = 'Y'
AND column_name IN ('SAMPLE_ID', 'BATCH_ID', 'TEST_ID', 'RESULT_ID')
ORDER BY table_name;
-- Find all status columns across the system
SELECT table_name, column_name, data_type
FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME'
AND (column_name LIKE '%STATUS%' OR column_name LIKE '%STATE%' OR column_name LIKE '%STAGE%')
ORDER BY table_name;
-- Get all distinct status values with counts (run for each status column found)
SELECT 'Table: SAMPLE_TABLE Column: STATUS_COLUMN' as source,
'SELECT status_column, COUNT(*), MIN(created_date), MAX(last_modified) FROM schema.table GROUP BY status_column ORDER BY COUNT(*) DESC;' as query_to_run;
-- Find samples stuck in workflow (adapt table/column names)
SELECT sample_id, sample_status, created_date, last_modified, -- <-- PLACEHOLDER: Use your actual column names
ROUND((SYSDATE - last_modified), 2) days_stuck
FROM SCHEMA_NAME.SAMPLE_TABLE -- <-- PLACEHOLDER: Use your actual schema.table
WHERE sample_status IN ('PENDING', 'IN_PROGRESS', 'AWAITING_APPROVAL') -- <-- PLACEHOLDER: Use your actual status values
AND last_modified < SYSDATE - 2
ORDER BY days_stuck DESC;
-- Find incomplete batches
SELECT batch_id, batch_status, created_date,
COUNT(*) total_samples,
SUM(CASE WHEN sample_status = 'COMPLETE' THEN 1 ELSE 0 END) completed_samples
FROM SCHEMA_NAME.BATCH_SAMPLES_VIEW -- Adapt to your batch/sample relationship
GROUP BY batch_id, batch_status, created_date
HAVING COUNT(*) != SUM(CASE WHEN sample_status = 'COMPLETE' THEN 1 ELSE 0 END)
ORDER BY created_date DESC;
-- Find batches with mixed statuses (potential workflow issues)
SELECT batch_id, COUNT(DISTINCT sample_status) status_count,
LISTAGG(sample_status, ', ') WITHIN GROUP (ORDER BY sample_status) all_statuses
FROM SCHEMA_NAME.BATCH_SAMPLES_VIEW
GROUP BY batch_id
HAVING COUNT(DISTINCT sample_status) > 3
ORDER BY status_count DESC;
-- Find instrument interface tables
SELECT table_name FROM all_tables
WHERE owner = 'SCHEMA_NAME'
AND (table_name LIKE '%IMPORT%' OR table_name LIKE '%INTERFACE%'
OR table_name LIKE '%INSTRUMENT%' OR table_name LIKE '%FILE%')
ORDER BY table_name;
-- Failed imports in last 24 hours (adapt table/column names)
SELECT instrument_name, file_name, import_status, error_message, import_date
FROM SCHEMA_NAME.INSTRUMENT_IMPORTS
WHERE import_status != 'SUCCESS' AND import_date > SYSDATE - 1
ORDER BY import_date DESC;
-- Find parsing errors in raw data
SELECT file_id, line_number, raw_data, error_description
FROM SCHEMA_NAME.IMPORT_ERRORS
WHERE created_date > SYSDATE - 1
ORDER BY created_date DESC;
-- Find results without proper validation
SELECT r.sample_id, r.test_name, r.result_value, r.result_status, r.created_date
FROM SCHEMA_NAME.RESULTS r
WHERE r.result_status IS NULL OR r.result_value IS NULL
AND r.created_date > SYSDATE - 7
ORDER BY r.created_date DESC;
-- Out-of-specification results
SELECT r.sample_id, r.test_name, r.result_value, s.spec_min, s.spec_max, r.created_date
FROM SCHEMA_NAME.RESULTS r
JOIN SCHEMA_NAME.SPECIFICATIONS s ON r.test_name = s.test_name
WHERE (r.result_value < s.spec_min OR r.result_value > s.spec_max)
AND r.created_date > SYSDATE - 7
ORDER BY r.created_date DESC;
-- Find locked or inactive users
SELECT username, account_status, lock_date, expiry_date, created
FROM dba_users
WHERE username LIKE '%LIMS%' OR username IN (
SELECT DISTINCT created_by FROM SCHEMA_NAME.SAMPLE_TABLE WHERE created_date > SYSDATE - 30
)
ORDER BY account_status;
-- Recent failed login attempts
SELECT username, timestamp, returncode, client_id
FROM dba_audit_session
WHERE returncode != 0 AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;
-- Users with excessive privileges
SELECT grantee, privilege, admin_option
FROM dba_sys_privs
WHERE grantee IN (SELECT username FROM dba_users WHERE username LIKE '%LIMS%')
AND privilege IN ('DELETE ANY TABLE', 'UPDATE ANY TABLE', 'DROP ANY TABLE')
ORDER BY grantee, privilege;
-- Find audit tables
SELECT table_name FROM all_tables
WHERE owner = 'SCHEMA_NAME'
AND (table_name LIKE '%AUDIT%' OR table_name LIKE '%HISTORY%' OR table_name LIKE '%LOG%')
ORDER BY table_name;
-- Recent data modifications (adapt table/column names)
SELECT table_name, operation_type, record_id, old_value, new_value,
changed_by, change_date, change_reason
FROM SCHEMA_NAME.AUDIT_TRAIL
WHERE change_date > SYSDATE - 1
ORDER BY change_date DESC;
-- Unauthorized data changes
SELECT table_name, record_id, changed_by, change_date, change_reason
FROM SCHEMA_NAME.AUDIT_TRAIL
WHERE changed_by NOT IN (SELECT username FROM SCHEMA_NAME.AUTHORIZED_USERS)
AND change_date > SYSDATE - 7
ORDER BY change_date DESC;
-- Find configuration tables
SELECT table_name FROM all_tables
WHERE owner = 'SCHEMA_NAME'
AND (table_name LIKE '%CONFIG%' OR table_name LIKE '%PARAM%'
OR table_name LIKE '%SETTING%' OR table_name LIKE '%PROPERTY%')
ORDER BY table_name;
-- Check critical configuration values
SELECT config_name, config_value, last_modified, modified_by
FROM SCHEMA_NAME.SYSTEM_CONFIG
WHERE config_name IN ('DATABASE_VERSION', 'INTERFACE_ENABLED', 'BACKUP_LOCATION', 'EMAIL_SERVER')
ORDER BY config_name;
-- Find misconfigured methods/tests
SELECT method_name, method_status, instrument_id, last_calibration
FROM SCHEMA_NAME.METHODS
WHERE method_status != 'ACTIVE' OR last_calibration < SYSDATE - 90
ORDER BY method_name;
-- Critical system metrics in one query
SELECT
'Active Sessions' metric, COUNT(*) value
FROM v$session WHERE status = 'ACTIVE' AND username IS NOT NULL
UNION ALL
SELECT 'Blocked Sessions', COUNT(*)
FROM v$session WHERE blocking_session IS NOT NULL
UNION ALL
SELECT 'Failed Logins (24h)', COUNT(*)
FROM dba_audit_session WHERE returncode != 0 AND timestamp > SYSDATE - 1
UNION ALL
SELECT 'Samples Pending >48h', COUNT(*)
FROM SCHEMA_NAME.SAMPLE_TABLE -- <-- PLACEHOLDER: Use your actual schema.table
WHERE sample_status = 'PENDING' AND created_date < SYSDATE - 2 -- <-- PLACEHOLDER: Use your actual status column/value
ORDER BY metric;
-- Quick error summary
SELECT
SUBSTR(error_message, 1, 50) error_type, -- <-- PLACEHOLDER: Use your actual error column name
COUNT(*) occurrences,
MAX(error_date) last_occurrence -- <-- PLACEHOLDER: Use your actual date column name
FROM SCHEMA_NAME.ERROR_LOG -- <-- PLACEHOLDER: Use your actual schema.error_table
WHERE error_date > SYSDATE - 1 -- <-- PLACEHOLDER: Use your actual date column name
GROUP BY SUBSTR(error_message, 1, 50) -- <-- PLACEHOLDER: Use your actual error column name
ORDER BY COUNT(*) DESC;
Sample/Specimen tables:
-- Find sample tables
SELECT table_name, num_rows FROM all_tables
WHERE owner = 'SCHEMA_NAME' AND table_name LIKE '%SAMPLE%' ORDER BY num_rows DESC;
-- Sample table structure analysis
SELECT column_name, data_type, nullable FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME' AND table_name = 'SAMPLE_TABLE_NAME' ORDER BY column_id;
-- Sample status distribution
SELECT sample_status, COUNT(*), MIN(created_date), MAX(created_date)
FROM SCHEMA_NAME.SAMPLE_TABLE GROUP BY sample_status ORDER BY COUNT(*) DESC;
Result/Test tables:
-- Find result tables
SELECT table_name, num_rows FROM all_tables
WHERE owner = 'SCHEMA_NAME' AND (table_name LIKE '%RESULT%' OR table_name LIKE '%TEST%')
ORDER BY num_rows DESC;
-- Result data integrity check
SELECT test_name, COUNT(*) total_results,
COUNT(result_value) results_with_values,
COUNT(*) - COUNT(result_value) missing_values
FROM SCHEMA_NAME.RESULT_TABLE
GROUP BY test_name ORDER BY missing_values DESC;
Batch/Worksheet tables:
-- Find batch tables
SELECT table_name, num_rows FROM all_tables
WHERE owner = 'SCHEMA_NAME' AND (table_name LIKE '%BATCH%' OR table_name LIKE '%WORKSHEET%')
ORDER BY num_rows DESC;
-- Batch completion analysis
SELECT batch_status, COUNT(*) batches, AVG(SYSDATE - created_date) avg_age_days
FROM SCHEMA_NAME.BATCH_TABLE GROUP BY batch_status ORDER BY COUNT(*) DESC;
Status/Workflow tables:
-- Find all status/lookup tables
SELECT table_name FROM all_tables WHERE owner = 'SCHEMA_NAME'
AND (table_name LIKE '%STATUS%' OR table_name LIKE '%LOOKUP%' OR table_name LIKE '%CODE%');
-- Map all status values across system
SELECT t.table_name, c.column_name, 'SELECT DISTINCT ' || c.column_name || ', COUNT(*) FROM ' || t.owner || '.' || t.table_name || ' GROUP BY ' || c.column_name || ';' as query
FROM all_tables t JOIN all_tab_columns c ON t.table_name = c.table_name AND t.owner = c.owner
WHERE t.owner = 'SCHEMA_NAME' AND c.column_name LIKE '%STATUS%';
Error/Log tables:
-- Find error/log tables
SELECT table_name, num_rows FROM all_tables
WHERE owner = 'SCHEMA_NAME' AND (table_name LIKE '%ERROR%' OR table_name LIKE '%LOG%' OR table_name LIKE '%AUDIT%')
ORDER BY num_rows DESC;
-- Recent error analysis
SELECT SUBSTR(error_message,1,100) error_type, COUNT(*) occurrences,
MIN(error_date) first_seen, MAX(error_date) last_seen
FROM SCHEMA_NAME.ERROR_TABLE WHERE error_date > SYSDATE - 7
GROUP BY SUBSTR(error_message,1,100) ORDER BY COUNT(*) DESC;
Instrument file imports:
-- Import status breakdown
SELECT import_status, COUNT(*), MIN(import_date), MAX(import_date)
FROM SCHEMA_NAME.INSTRUMENT_IMPORTS WHERE import_date > SYSDATE - 7
GROUP BY import_status ORDER BY COUNT(*) DESC;
-- File parsing errors
SELECT file_name, error_type, COUNT(*) errors
FROM SCHEMA_NAME.IMPORT_ERRORS WHERE error_date > SYSDATE - 1
GROUP BY file_name, error_type ORDER BY COUNT(*) DESC;
Workflow transitions (approval bottlenecks):
-- Find approval bottlenecks
SELECT approver_role, approval_status, COUNT(*) pending_count,
AVG(SYSDATE - submitted_date) avg_days_pending
FROM SCHEMA_NAME.APPROVALS WHERE approval_status = 'PENDING'
GROUP BY approver_role, approval_status ORDER BY avg_days_pending DESC;
-- Validation rule failures
SELECT validation_rule, COUNT(*) failures, LISTAGG(sample_id, ', ') WITHIN GROUP (ORDER BY sample_id) failed_samples
FROM SCHEMA_NAME.VALIDATION_FAILURES WHERE failure_date > SYSDATE - 1
GROUP BY validation_rule ORDER BY COUNT(*) DESC;
Follow the data flow:
-- Complete sample journey tracking
SELECT s.sample_id, s.sample_status, s.created_date,
t.test_name, t.test_status, t.assigned_date,
r.result_value, r.result_status, r.result_date,
a.approval_status, a.approved_date
FROM SCHEMA_NAME.SAMPLES s
LEFT JOIN SCHEMA_NAME.TESTS t ON s.sample_id = t.sample_id
LEFT JOIN SCHEMA_NAME.RESULTS r ON t.test_id = r.test_id
LEFT JOIN SCHEMA_NAME.APPROVALS a ON s.sample_id = a.sample_id
WHERE s.sample_id = 'SPECIFIC_SAMPLE_ID'
ORDER BY t.assigned_date, r.result_date;
Look for patterns in errors:
-- Error patterns by user
SELECT error_user, COUNT(*) error_count,
LISTAGG(DISTINCT error_type, ', ') WITHIN GROUP (ORDER BY error_type) error_types
FROM SCHEMA_NAME.ERROR_TABLE WHERE error_date > SYSDATE - 7
GROUP BY error_user ORDER BY COUNT(*) DESC;
-- Error patterns by time
SELECT TRUNC(error_date, 'HH24') error_hour, COUNT(*) error_count
FROM SCHEMA_NAME.ERROR_TABLE WHERE error_date > SYSDATE - 1
GROUP BY TRUNC(error_date, 'HH24') ORDER BY error_hour;
-- Error patterns by instrument
SELECT instrument_id, error_type, COUNT(*) occurrences
FROM SCHEMA_NAME.INSTRUMENT_ERRORS WHERE error_date > SYSDATE - 7
GROUP BY instrument_id, error_type ORDER BY COUNT(*) DESC;
Check recent configuration changes:
-- Recent configuration changes
SELECT config_name, old_value, new_value, changed_by, change_date
FROM SCHEMA_NAME.CONFIG_AUDIT WHERE change_date > SYSDATE - 7
ORDER BY change_date DESC;
-- Method/test configuration changes
SELECT method_name, change_type, changed_by, change_date, change_reason
FROM SCHEMA_NAME.METHOD_AUDIT WHERE change_date > SYSDATE - 30
ORDER BY change_date DESC;
-- User permission changes
SELECT username, permission_change, changed_by, change_date
FROM SCHEMA_NAME.USER_AUDIT WHERE change_date > SYSDATE - 7
ORDER BY change_date DESC;
Error log pattern analysis:
-- Recurring error patterns
SELECT error_signature, COUNT(*) occurrences,
ROUND(COUNT(*) / (SYSDATE - MIN(error_date)), 2) errors_per_day,
MIN(error_date) first_occurrence, MAX(error_date) last_occurrence
FROM (
SELECT REGEXP_REPLACE(error_message, '[0-9]+', 'N') error_signature, error_date
FROM SCHEMA_NAME.ERROR_TABLE WHERE error_date > SYSDATE - 30
)
GROUP BY error_signature HAVING COUNT(*) > 5
ORDER BY errors_per_day DESC;
-- Error correlation analysis
SELECT e1.error_type primary_error, e2.error_type secondary_error, COUNT(*) correlation_count
FROM SCHEMA_NAME.ERROR_TABLE e1 JOIN SCHEMA_NAME.ERROR_TABLE e2 -- <-- PLACEHOLDER: Use your actual schema.error_table
ON e1.session_id = e2.session_id AND e1.error_date < e2.error_date
AND e2.error_date - e1.error_date < 1/24 -- Within 1 hour
WHERE e1.error_date > SYSDATE - 7
GROUP BY e1.error_type, e2.error_type ORDER BY COUNT(*) DESC;
The key insight: Every item in the 80-20 list now has specific queries. LIMS problems are usually workflow interruptions, data integrity issues, or system resource problems. Master these queries and you can diagnose 80% of LIMS issues.