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 nameYOUR_COLUMN_NAME
→ Replace with actual column nameYOUR_VIEW_NAME
→ Replace with actual view nameYOUR_PROCEDURE_NAME
→ Replace with actual procedure nameYOUR_DATE_COLUMN
→ Replace with actual date column nameYOUR_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)