Oracle Basic SQL Queries - Database Exploration

Essential Oracle SQL queries for exploring database structure, tables, columns, and metadata. Perfect for LIMS environments where you need to understand the database schema.

Database and Schema Information

1. Current Database Information

-- Basic database information
SELECT 
    NAME AS DATABASE_NAME,
    DBID,
    TO_CHAR(CREATED, 'DD-MON-YYYY') AS CREATED_DATE,
    LOG_MODE,
    OPEN_MODE,
    DATABASE_ROLE
FROM V$DATABASE;

-- Current user and schema
SELECT 
    USER AS CURRENT_USER,
    SYS_CONTEXT('USERENV','CURRENT_SCHEMA') AS CURRENT_SCHEMA,
    SYS_CONTEXT('USERENV','SESSION_USER') AS SESSION_USER,
    SYS_CONTEXT('USERENV','DB_NAME') AS DATABASE_NAME
FROM DUAL;

2. All Schemas/Users in Database

-- List all schemas (users) in the database
SELECT 
    USERNAME,
    USER_ID,
    TO_CHAR(CREATED, 'DD-MON-YYYY') AS CREATED_DATE,
    ACCOUNT_STATUS,
    DEFAULT_TABLESPACE,
    PROFILE
FROM DBA_USERS
WHERE USERNAME NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN')
ORDER BY USERNAME;

-- If you don't have DBA privileges, see accessible schemas
SELECT DISTINCT OWNER
FROM ALL_TABLES
ORDER BY OWNER;

---

Tables and Objects

3. List All Tables

-- Tables in your current schema
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    AVG_ROW_LEN,
    TABLESPACE_NAME,
    TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI') AS LAST_ANALYZED,
    STATUS
FROM USER_TABLES
ORDER BY TABLE_NAME;

-- Tables you have access to (all schemas)
SELECT 
    OWNER,
    TABLE_NAME,
    NUM_ROWS,
    TABLESPACE_NAME,
    TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY') AS LAST_ANALYZED
FROM ALL_TABLES
WHERE OWNER NOT IN ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN')
ORDER BY OWNER, TABLE_NAME;

4. Search for Tables by Name

-- Find tables with specific names (useful for LIMS)
SELECT 
    OWNER,
    TABLE_NAME,
    NUM_ROWS,
    TABLESPACE_NAME
FROM ALL_TABLES
WHERE TABLE_NAME LIKE '%SAMPLE%'
   OR TABLE_NAME LIKE '%TEST%'
   OR TABLE_NAME LIKE '%RESULT%'
   OR TABLE_NAME LIKE '%PATIENT%'
   OR TABLE_NAME LIKE '%SPECIMEN%'
ORDER BY OWNER, TABLE_NAME;

-- Case insensitive search
SELECT 
    OWNER,
    TABLE_NAME,
    NUM_ROWS
FROM ALL_TABLES
WHERE UPPER(TABLE_NAME) LIKE UPPER('%YourSearchTerm%')
ORDER BY OWNER, TABLE_NAME;

5. Table Details and Size Information

-- Detailed table information
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    AVG_ROW_LEN,
    ROUND((NUM_ROWS * AVG_ROW_LEN)/1024/1024, 2) AS ESTIMATED_SIZE_MB,
    TABLESPACE_NAME,
    COMPRESSION,
    PARTITIONED,
    TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI') AS LAST_ANALYZED
FROM USER_TABLES
WHERE NUM_ROWS IS NOT NULL
ORDER BY NUM_ROWS DESC;

---

Columns and Data Types

6. List All Columns for a Table

-- Columns for a specific table
SELECT 
    COLUMN_NAME,
    DATA_TYPE,
    DATA_LENGTH,
    DATA_PRECISION,
    DATA_SCALE,
    NULLABLE,
    DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
ORDER BY COLUMN_ID;

-- More detailed column information
SELECT 
    COLUMN_ID,
    COLUMN_NAME,
    DATA_TYPE || 
    CASE 
        WHEN DATA_TYPE IN ('VARCHAR2','CHAR','NVARCHAR2','NCHAR') THEN '(' || DATA_LENGTH || ')'
        WHEN DATA_TYPE = 'NUMBER' AND DATA_PRECISION IS NOT NULL THEN 
            '(' || DATA_PRECISION || 
            CASE WHEN DATA_SCALE > 0 THEN ',' || DATA_SCALE END || ')'
        ELSE ''
    END AS FULL_DATA_TYPE,
    NULLABLE,
    DATA_DEFAULT,
    NUM_DISTINCT,
    DENSITY
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
ORDER BY COLUMN_ID;

7. Search for Columns by Name

-- Find columns with specific names across all tables
SELECT 
    OWNER,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE,
    NULLABLE
FROM ALL_TAB_COLUMNS
WHERE COLUMN_NAME LIKE '%PATIENT%'
   OR COLUMN_NAME LIKE '%SAMPLE%'
   OR COLUMN_NAME LIKE '%RESULT%'
ORDER BY OWNER, TABLE_NAME, COLUMN_NAME;

-- Find all date/timestamp columns
SELECT 
    OWNER,
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE DATA_TYPE IN ('DATE','TIMESTAMP','TIMESTAMP(6)')
AND OWNER NOT IN ('SYS','SYSTEM')
ORDER BY OWNER, TABLE_NAME, COLUMN_NAME;

8. Data Type Summary

-- Summary of data types in your schema
SELECT 
    DATA_TYPE,
    COUNT(*) AS COLUMN_COUNT,
    COUNT(DISTINCT TABLE_NAME) AS TABLE_COUNT
FROM USER_TAB_COLUMNS
GROUP BY DATA_TYPE
ORDER BY COLUMN_COUNT DESC;

---

Indexes and Performance

9. List All Indexes

-- Indexes in your schema
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    INDEX_TYPE,
    UNIQUENESS,
    STATUS,
    NUM_ROWS,
    DISTINCT_KEYS,
    CLUSTERING_FACTOR
FROM USER_INDEXES
ORDER BY TABLE_NAME, INDEX_NAME;

-- Index columns details
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_POSITION,
    DESCEND
FROM USER_IND_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
ORDER BY INDEX_NAME, COLUMN_POSITION;

10. Find Missing Indexes (Foreign Keys without Indexes)

-- Foreign key columns that might need indexes
SELECT 
    fk.TABLE_NAME,
    fk.COLUMN_NAME,
    fk.CONSTRAINT_NAME,
    'Missing Index?' AS RECOMMENDATION
FROM USER_CONS_COLUMNS fk
JOIN USER_CONSTRAINTS c ON fk.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'R'  -- Foreign Key
AND NOT EXISTS (
    SELECT 1 
    FROM USER_IND_COLUMNS ic 
    WHERE ic.TABLE_NAME = fk.TABLE_NAME 
    AND ic.COLUMN_NAME = fk.COLUMN_NAME
    AND ic.COLUMN_POSITION = 1
)
ORDER BY fk.TABLE_NAME, fk.COLUMN_NAME;

---

Constraints and Relationships

11. Primary Keys and Unique Constraints

-- Primary keys in your schema
SELECT 
    c.TABLE_NAME,
    c.CONSTRAINT_NAME,
    cc.COLUMN_NAME,
    cc.POSITION
FROM USER_CONSTRAINTS c
JOIN USER_CONS_COLUMNS cc ON c.CONSTRAINT_NAME = cc.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'P'  -- Primary Key
ORDER BY c.TABLE_NAME, cc.POSITION;

-- All constraints by table
SELECT 
    TABLE_NAME,
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE,
    CASE CONSTRAINT_TYPE
        WHEN 'P' THEN 'Primary Key'
        WHEN 'R' THEN 'Foreign Key'
        WHEN 'U' THEN 'Unique'
        WHEN 'C' THEN 'Check'
        WHEN 'N' THEN 'Not Null'
    END AS CONSTRAINT_DESCRIPTION,
    STATUS,
    R_CONSTRAINT_NAME AS REFERENCES_CONSTRAINT
FROM USER_CONSTRAINTS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

12. Foreign Key Relationships

-- Foreign key relationships (what this table references)
SELECT 
    fk.TABLE_NAME AS CHILD_TABLE,
    fk.COLUMN_NAME AS CHILD_COLUMN,
    fk.CONSTRAINT_NAME AS FK_NAME,
    pk.TABLE_NAME AS PARENT_TABLE,
    pk.COLUMN_NAME AS PARENT_COLUMN
FROM USER_CONS_COLUMNS fk
JOIN USER_CONSTRAINTS c ON fk.CONSTRAINT_NAME = c.CONSTRAINT_NAME
JOIN USER_CONS_COLUMNS pk ON c.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE c.CONSTRAINT_TYPE = 'R'
ORDER BY fk.TABLE_NAME, fk.POSITION;

-- Tables that reference this table (reverse foreign keys)
SELECT DISTINCT
    fk.TABLE_NAME AS REFERENCING_TABLE,
    pk.TABLE_NAME AS REFERENCED_TABLE
FROM USER_CONS_COLUMNS fk
JOIN USER_CONSTRAINTS c ON fk.CONSTRAINT_NAME = c.CONSTRAINT_NAME
JOIN USER_CONS_COLUMNS pk ON c.R_CONSTRAINT_NAME = pk.CONSTRAINT_NAME
WHERE pk.TABLE_NAME = 'YOUR_TABLE_NAME'
AND c.CONSTRAINT_TYPE = 'R'
ORDER BY fk.TABLE_NAME;

---

Views and Stored Objects

13. Views

-- List all views in your schema
SELECT 
    VIEW_NAME,
    TEXT_LENGTH,
    TYPE_TEXT
FROM USER_VIEWS
ORDER BY VIEW_NAME;

-- View definition (first 4000 characters)
SELECT 
    VIEW_NAME,
    SUBSTR(TEXT, 1, 4000) AS VIEW_DEFINITION
FROM USER_VIEWS
WHERE VIEW_NAME = 'YOUR_VIEW_NAME';

14. Stored Procedures and Functions

-- List all stored procedures, functions, packages
SELECT 
    OBJECT_NAME,
    OBJECT_TYPE,
    STATUS,
    TO_CHAR(CREATED, 'DD-MON-YYYY') AS CREATED_DATE,
    TO_CHAR(LAST_DDL_TIME, 'DD-MON-YYYY HH24:MI') AS LAST_MODIFIED
FROM USER_OBJECTS
WHERE OBJECT_TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
ORDER BY OBJECT_TYPE, OBJECT_NAME;

-- Procedure/Function parameters
SELECT 
    OBJECT_NAME,
    PACKAGE_NAME,
    ARGUMENT_NAME,
    POSITION,
    IN_OUT,
    DATA_TYPE
FROM USER_ARGUMENTS
WHERE OBJECT_NAME = 'YOUR_PROCEDURE_NAME'
ORDER BY POSITION;

---

Data Exploration

15. Quick Data Sampling

-- Sample data from a table
SELECT * 
FROM YOUR_TABLE_NAME 
WHERE ROWNUM <= 10;

-- Random sample (Oracle 12c+)
SELECT * 
FROM YOUR_TABLE_NAME 
SAMPLE(10)  -- 10% sample
WHERE ROWNUM <= 20;

-- Count rows in all tables
SELECT 
    TABLE_NAME,
    TO_CHAR(NUM_ROWS, '999,999,999') AS ROW_COUNT
FROM USER_TABLES
WHERE NUM_ROWS IS NOT NULL
ORDER BY NUM_ROWS DESC;

16. Data Profiling Queries

-- Check for NULL values in all columns of a table
SELECT 
    'YOUR_TABLE_NAME' AS TABLE_NAME,
    COLUMN_NAME,
    COUNT(*) AS TOTAL_ROWS,
    COUNT(COLUMN_NAME) AS NON_NULL_ROWS,
    COUNT(*) - COUNT(COLUMN_NAME) AS NULL_ROWS,
    ROUND((COUNT(*) - COUNT(COLUMN_NAME)) / COUNT(*) * 100, 2) AS NULL_PERCENTAGE
FROM YOUR_TABLE_NAME, USER_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
GROUP BY COLUMN_NAME
ORDER BY NULL_PERCENTAGE DESC;

-- Find duplicate values
SELECT 
    COLUMN_NAME,
    COUNT(*) AS DUPLICATE_COUNT
FROM YOUR_TABLE_NAME
GROUP BY COLUMN_NAME
HAVING COUNT(*) > 1
ORDER BY DUPLICATE_COUNT DESC;

17. Value Distribution Analysis

-- Most common values in a column
SELECT 
    YOUR_COLUMN_NAME,
    COUNT(*) AS FREQUENCY,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM YOUR_TABLE_NAME), 2) AS PERCENTAGE
FROM YOUR_TABLE_NAME
GROUP BY YOUR_COLUMN_NAME
ORDER BY FREQUENCY DESC
FETCH FIRST 20 ROWS ONLY;

-- Min/Max values for all numeric columns
SELECT 
    'YOUR_TABLE_NAME' AS TABLE_NAME,
    COLUMN_NAME,
    'MIN: ' || MIN(YOUR_NUMERIC_COLUMN) || ' MAX: ' || MAX(YOUR_NUMERIC_COLUMN) AS MIN_MAX_VALUES
FROM YOUR_TABLE_NAME, USER_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
AND DATA_TYPE IN ('NUMBER', 'INTEGER');

---

System and Metadata

18. Database Size and Statistics

-- Database size information
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;

-- Table statistics freshness
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    TO_CHAR(LAST_ANALYZED, 'DD-MON-YYYY HH24:MI') AS LAST_ANALYZED,
    TRUNC(SYSDATE - LAST_ANALYZED) AS DAYS_OLD
FROM USER_TABLES
WHERE LAST_ANALYZED IS NOT NULL
ORDER BY LAST_ANALYZED DESC;

19. Privileges and Security

-- Your system privileges
SELECT PRIVILEGE
FROM USER_SYS_PRIVS
ORDER BY PRIVILEGE;

-- Your object privileges
SELECT 
    TABLE_NAME,
    PRIVILEGE,
    GRANTABLE
FROM USER_TAB_PRIVS
ORDER BY TABLE_NAME, PRIVILEGE;

-- Roles assigned to you
SELECT GRANTED_ROLE
FROM USER_ROLE_PRIVS
ORDER BY GRANTED_ROLE;

20. Quick Schema Summary

-- Complete schema summary
SELECT 
    OBJECT_TYPE,
    COUNT(*) AS OBJECT_COUNT,
    COUNT(CASE WHEN STATUS = 'VALID' THEN 1 END) AS VALID_COUNT,
    COUNT(CASE WHEN STATUS != 'VALID' THEN 1 END) AS INVALID_COUNT
FROM USER_OBJECTS
GROUP BY OBJECT_TYPE
ORDER BY OBJECT_COUNT DESC;

---

Common LIMS-Specific Queries

21. LIMS Table Discovery

-- Find typical LIMS tables
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    ROUND((NUM_ROWS * AVG_ROW_LEN)/1024/1024, 2) AS SIZE_MB
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%'
   OR TABLE_NAME LIKE '%ANALYTE%'
   OR TABLE_NAME LIKE '%INSTRUMENT%'
   OR TABLE_NAME LIKE '%BATCH%'
   OR TABLE_NAME LIKE '%QC%'
ORDER BY NUM_ROWS DESC NULLS LAST;

22. Date Range Analysis

-- Find date columns and their ranges
SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    DATA_TYPE
FROM USER_TAB_COLUMNS
WHERE DATA_TYPE IN ('DATE', 'TIMESTAMP', 'TIMESTAMP(6)')
ORDER BY TABLE_NAME, COLUMN_NAME;

-- Check date ranges in a specific table
SELECT 
    MIN(YOUR_DATE_COLUMN) AS EARLIEST_DATE,
    MAX(YOUR_DATE_COLUMN) AS LATEST_DATE,
    COUNT(*) AS TOTAL_RECORDS,
    COUNT(DISTINCT TRUNC(YOUR_DATE_COLUMN)) AS DISTINCT_DAYS
FROM YOUR_TABLE_NAME;

---

Useful Templates

Replace These Placeholders:

• YOUR_TABLE_NAME → Replace with actual table name
• YOUR_COLUMN_NAME → Replace with actual column name
• YOUR_VIEW_NAME → Replace with actual view name
• YOUR_PROCEDURE_NAME → Replace with actual procedure name
• YOUR_DATE_COLUMN → Replace with actual date column name
• YOUR_NUMERIC_COLUMN → Replace with actual numeric column name

Quick Find & Replace Tips:

-- Template for exploring a new table
SELECT * FROM YOUR_TABLE_NAME WHERE ROWNUM <= 5;
SELECT COUNT(*) FROM YOUR_TABLE_NAME;
SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'YOUR_TABLE_NAME';

Performance Notes:

• Add WHERE ROWNUM <= 100 to limit large result sets
• Use FETCH FIRST n ROWS ONLY in Oracle 12c+
• Be careful with SELECT * on large tables
• Consider using SAMPLE(n) for large data exploration

Key Sections:

🗃️ Database Structure:

Schema/user information

Table listings and details

Column definitions and data types

Size and statistics

🔗 Relationships:

Primary/foreign keys

Constraints and indexes

Table relationships mapping

📊 Data Exploration:

Quick data sampling

Value distribution analysis

NULL value detection

Duplicate finding

🔬 LIMS-Specific:

Common LIMS table patterns (SAMPLE, TEST, RESULT, SPECIMEN, etc.)

Date range analysis

Medical/lab data exploration

⚡ Ready-to-Use:

All queries are copy-paste ready

Clear placeholder system (YOUR_TABLE_NAME, etc.)

Performance tips included

Works with any Oracle client

Typical Workflow for New LIMS Database:

Get oriented → Query #1, #2 (what database, what schemas)

Find LIMS tables → Query #21 (discover key tables)

Explore table structure → Query #6 (understand columns)

Check relationships → Query #12 (find foreign keys)

Sample the data → Query #15 (see actual data)