Oracle Database Reverse Engineering Survival Guide

When You’re Alone With No Documentation

Phase 1: Safety First

Before touching anything:

-- Always work on copies in development/test environment
-- If you must work in production, start with SELECT statements only
-- Never run UPDATE/DELETE/INSERT without understanding exactly what they do

Create a backup mindset:

• Document everything you learn
• Test queries with WHERE ROWNUM <= 5 first
• Keep a log of what you try and what happens

Phase 2: Basic Discovery

Step 1: Inventory Your Tables

-- See all tables you have access to
SELECT table_name FROM user_tables ORDER BY table_name;

-- See tables in other schemas you can access
SELECT owner, table_name FROM all_tables
WHERE owner NOT IN ('SYS', 'SYSTEM', 'SYSMAN', 'CTXSYS', 'MDSYS', 'OLAPSYS', 'ORDSYS', 'OUTLN', 'WKSYS', 'WMSYS', 'XDB')
ORDER BY owner, table_name;

-- Count rows in your tables (be careful with large tables)
SELECT table_name, num_rows FROM user_tables WHERE num_rows IS NOT NULL;

Step 2: Understand Table Structure

-- See column details
DESCRIBE table_name;

-- More detailed column information
SELECT column_name, data_type, data_length, nullable, data_default
FROM user_tab_columns
WHERE table_name = 'YOUR_TABLE_NAME'
ORDER BY column_id;

-- Find primary keys
SELECT constraint_name, column_name
FROM user_cons_columns
WHERE constraint_name IN (
    SELECT constraint_name FROM user_constraints
        WHERE table_name = 'YOUR_TABLE_NAME' AND constraint_type = 'P'
        );

Look for these Oracle naming patterns:

• _ID, _SEQ, _NUM = likely primary keys or identifiers
• CREATED_DATE, CREATION_DATE, CREATE_TS = audit columns
• STATUS, STATE, WIP_STATUS = workflow indicators
• MAP_TO_, LINK_, JUNCTION_ = relationship tables
• HDM_SEQ, HSN, ADDR_SEQ = system-specific identifiers

Step 3: Sample the Data

-- See what's actually in each table
SELECT * FROM table_name WHERE ROWNUM <= 5;

-- Check for common values and patterns
SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC;

-- See date ranges (important for understanding data age)
SELECT
    MIN(date_column) as earliest_date,
        MAX(date_column) as latest_date,
            COUNT(*) as total_records
            FROM table_name;
            ```

### Phase 3: Find the Relationships

#### Method 1: Oracle Foreign Key Discovery

```sql
-- Find all foreign key relationships you own
SELECT
    a.constraint_name,
        a.table_name as child_table,
            a.column_name as child_column,
                c_pk.table_name as parent_table,
                    c_pk.column_name as parent_column
                    FROM user_cons_columns a
                    JOIN user_constraints c ON a.constraint_name = c.constraint_name
                    JOIN user_constraints c_pk ON c.r_constraint_name = c_pk.constraint_name
                    JOIN user_cons_columns c_pk ON c_pk.constraint_name = c_pk.constraint_name
                    WHERE c.constraint_type = 'R'
                    ORDER BY a.table_name, a.column_name;

                    -- Find indexes (often indicate relationships even without formal FKs)
                    SELECT table_name, index_name, column_name, column_position
                    FROM user_ind_columns
                    WHERE table_name = 'YOUR_TABLE_NAME'
                    ORDER BY index_name, column_position;
                    ```

#### Method 2: Pattern Recognition in Oracle

```sql
-- Find columns with similar names across tables (likely relationships)
SELECT table_name, column_name
FROM user_tab_columns
WHERE column_name LIKE '%_ID'
   OR column_name LIKE '%_SEQ'
      OR column_name LIKE '%_NUM'
      ORDER BY column_name, table_name;

      -- Look for common linking patterns
      SELECT DISTINCT column_name, COUNT(*) as table_count
      FROM user_tab_columns
      WHERE column_name IN ('HSN', 'HDM_SEQ', 'ADDR_SEQ', 'DELIVERY_ID', 'SAMPLE_ID')
      GROUP BY column_name
      ORDER BY table_count DESC;
      ```

#### Method 3: Oracle Data Dictionary Exploration

```sql
-- Find comments on tables and columns (if they exist)
SELECT table_name, comments
FROM user_tab_comments
WHERE comments IS NOT NULL;

SELECT table_name, column_name, comments
FROM user_col_comments
WHERE comments IS NOT NULL;

-- Look for triggers (business logic clues)
SELECT trigger_name, table_name, triggering_event, status
FROM user_triggers
WHERE table_name = 'YOUR_TABLE_NAME';

Phase 4: Understanding Oracle-Specific Patterns

Decode Oracle Naming Conventions

-- Common Oracle patterns in your system:
-- HSN = Hospital Sample Number (likely primary key for samples)
-- HDM_SEQ = Hierarchical Document Management Sequence
-- ADDR_SEQ = Address Sequence (links to addresses table)
-- WIP_STATUS = Work In Progress Status
-- LIMS_USE = Laboratory Information Management System Usage

Oracle Date Functions You’ll Encounter

-- Common date patterns in Oracle:
SYSDATE                           -- Current date/time
TO_CHAR(date_col, 'DD-MON-YYYY')  -- Format dates for display
TO_DATE('01-JAN-2024', 'DD-MON-YYYY') -- Convert strings to dates
TRUNC(SYSDATE)                    -- Today at midnight
SYSDATE - 30                      -- 30 days ago

Oracle Sequence Usage

-- Find sequences (often used for primary keys)
SELECT sequence_name, last_number, increment_by
FROM user_sequences;

-- How sequences are typically used:
-- SELECT seq_name.NEXTVAL FROM dual;  -- Get next value
-- SELECT seq_name.CURRVAL FROM dual;  -- Get current value

Phase 5: Oracle-Specific Investigation Tools

Using Oracle System Views

-- Find recently modified objects
SELECT object_name, object_type, last_ddl_time
FROM user_objects
WHERE last_ddl_time > SYSDATE - 30
ORDER BY last_ddl_time DESC;

-- Check for synonyms (aliases to other tables)
SELECT synonym_name, table_owner, table_name
FROM user_synonyms;

-- Find views that might simplify complex relationships
SELECT view_name FROM user_views;

-- See view definitions
SELECT text FROM user_views WHERE view_name = 'YOUR_VIEW_NAME';

Oracle Execution Plans (Advanced)

-- Only when you're ready for this:
EXPLAIN PLAN FOR
SELECT * FROM table1 t1
JOIN table2 t2 ON t1.id = t2.parent_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Phase 6: Oracle Transaction Control

Safe Testing with Oracle

-- Oracle transaction control
SET AUTOCOMMIT OFF;  -- Don't auto-commit changes

-- Test your changes
UPDATE table_name SET column = 'test_value' WHERE id = 12345;

-- Check the results
SELECT * FROM table_name WHERE id = 12345;

-- If it looks wrong:
ROLLBACK;

-- If it looks right:
COMMIT;

Oracle Session Management

-- See your current session info
SELECT SID, SERIAL#, USERNAME, STATUS FROM V$SESSION WHERE USERNAME = USER;

-- Kill your own session if something goes wrong (ask DBA for help)
-- ALTER SYSTEM KILL SESSION 'sid,serial#';

Phase 7: Oracle-Specific Debugging

When Queries Fail

-- Common Oracle error patterns:
-- ORA-00942: table or view does not exist
--   -> Check if table exists in your schema or need schema prefix
-- ORA-00904: invalid identifier
--   -> Column name is wrong or doesn't exist
-- ORA-01722: invalid number
--   -> Trying to convert non-numeric string to number

-- Check Oracle error details:
SELECT * FROM USER_ERRORS;  -- For stored procedures/functions

Oracle Performance Issues

-- Find long-running queries in your session
SELECT sql_text, elapsed_time, executions
FROM v$sql
WHERE parsing_user_id = (SELECT user_id FROM user_users WHERE username = USER)
ORDER BY elapsed_time DESC;

-- Check for locks (if updates seem to hang)
SELECT * FROM user_objects WHERE object_name = 'YOUR_TABLE' AND object_type = 'TABLE';

Phase 8: Oracle Documentation Strategy

Create Oracle-Specific Documentation

## Oracle Schema Map
Schema: YOUR_SCHEMA
- SAMPLES table: HSN (PK), WIP_STATUS, creation details
- REPORT_DELIVERY: delivery_seq (PK), delivery_id (FK->SAMPLES.HSN)
- MAP_TO_HDM: hdm_seq (PK), object_id (FK->SAMPLES.HSN), lims_use
- ADDRESSES: addr_seq (PK), e_mail, contact info

## Oracle Sequences
- DELIVERY_SEQ: Used for REPORT_DELIVERY.delivery_seq
- HDM_SEQ: Used for MAP_TO_HDM.hdm_seq

## Business Process (Oracle Implementation)
1. Sample created -> INSERT into SAMPLES, HSN from sequence
2. Documents attached -> INSERT into MAP_TO_HDM
3. Delivery requested -> INSERT into REPORT_DELIVERY
4. Email job processes -> Oracle job/trigger sends emails

Phase 6: Finding Procedures, Functions, and Code

Oracle Code Objects Discovery

-- Find all stored procedures, functions, packages
SELECT object_name, object_type, status, last_ddl_time
FROM user_objects
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY')
ORDER BY object_type, object_name;

-- Get source code for procedures/functions
SELECT text FROM user_source
WHERE name = 'YOUR_PROCEDURE_NAME'
ORDER BY line;

-- Find triggers on specific tables
SELECT trigger_name, triggering_event, trigger_body
FROM user_triggers
WHERE table_name = 'YOUR_TABLE_NAME';

-- Find all database jobs (scheduled procedures)
SELECT job_name, job_type, enabled, state, last_start_date
FROM user_scheduler_jobs;

Searching for Text Across Database Objects

-- Search for specific text in all your stored code
SELECT name, type, line, text
FROM user_source
WHERE UPPER(text) LIKE '%SEARCH_TEXT%'
ORDER BY name, line;

-- Search for column names across all tables
SELECT table_name, column_name
FROM user_tab_columns
WHERE column_name LIKE '%SEARCH_TERM%'
ORDER BY table_name;

-- Search for text in view definitions
SELECT view_name, text
FROM user_views
WHERE UPPER(text) LIKE '%SEARCH_TEXT%';

-- Search for text in trigger code
SELECT trigger_name, table_name, trigger_body
FROM user_triggers
WHERE UPPER(trigger_body) LIKE '%SEARCH_TEXT%';

-- Search constraint names and conditions
SELECT constraint_name, table_name, search_condition
FROM user_constraints
WHERE UPPER(search_condition) LIKE '%SEARCH_TEXT%'
   OR UPPER(constraint_name) LIKE '%SEARCH_TEXT%';
   ```

#### Find Dependencies and Usage

```sql
-- What objects reference a specific table/view
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE referenced_name = 'YOUR_TABLE_NAME';

-- What does a procedure/function depend on
SELECT referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'YOUR_PROCEDURE_NAME';

-- Find where specific columns are used in code
SELECT name, type, line, text
FROM user_source
WHERE UPPER(text) LIKE '%COLUMN_NAME%'
ORDER BY name, line;

Phase 7: SQL Keywords vs Placeholders

Reserved SQL Keywords (Oracle)

-- These are KEYWORDS (Oracle reserved words):
SELECT, FROM, WHERE, AND, OR, NOT, IN, EXISTS, BETWEEN
INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, GRANT, REVOKE
JOIN, INNER, LEFT, RIGHT, OUTER, ON, USING
GROUP BY, ORDER BY, HAVING, DISTINCT, UNION, INTERSECT
BEGIN, END, IF, THEN, ELSE, LOOP, FOR, WHILE
COMMIT, ROLLBACK, SAVEPOINT
NUMBER, VARCHAR2, DATE, TIMESTAMP, CLOB, BLOB
CONSTRAINT, PRIMARY, FOREIGN, UNIQUE, CHECK, NOT NULL

How to Identify Placeholders vs Real Names

-- PLACEHOLDER EXAMPLES (you replace these):
SELECT column_name FROM table_name WHERE condition;
--     ^^^^^^^^^^^     ^^^^^^^^^^       ^^^^^^^^^
--     Replace with    Replace with     Replace with
--     actual column   actual table     actual WHERE clause

-- REAL EXAMPLE:
SELECT delivery_seq FROM report_delivery WHERE status = 'PENDING';
--     ^^^^^^^^^^^^     ^^^^^^^^^^^^^^^       ^^^^^^^^^^^^^^^^
--     Actual column    Actual table         Actual condition

Common Documentation Conventions

ALL_CAPS_WITH_UNDERSCORES = Usually Oracle system tables/views
lowercase_with_underscores = Usually your application tables
MixedCase = Sometimes Oracle objects
YOUR_*, MY_*, SOME_* = Definitely placeholders in documentation
table_name, column_name = Generic placeholders
user_tables, user_constraints = Actual Oracle system views

Phase 8: Complete Oracle Investigation Arsenal

Oracle System Views Reference

-- CRITICAL Oracle data dictionary views:
USER_OBJECTS      -- All your objects (tables, procedures, etc.)
USER_TABLES       -- Your tables
USER_TAB_COLUMNS  -- Your table columns with details
USER_CONSTRAINTS  -- Your constraints (PK, FK, etc.)
USER_CONS_COLUMNS -- Which columns are in which constraints
USER_INDEXES      -- Your indexes
USER_IND_COLUMNS  -- Which columns are in which indexes
USER_SEQUENCES    -- Your sequences
USER_TRIGGERS     -- Your triggers
USER_VIEWS        -- Your views
USER_SOURCE       -- Source code of procedures/functions
USER_DEPENDENCIES -- What depends on what
USER_SYNONYMS     -- Your synonyms (aliases)
USER_TAB_COMMENTS -- Comments on tables
USER_COL_COMMENTS -- Comments on columns

-- For access across schemas (if you have permissions):
ALL_* -- Same as USER_* but includes other schemas you can access
DBA_* -- Same as USER_* but for entire database (DBA access required)

Advanced Text Searching

-- Search everywhere for a specific value/text:
-- 1. In table data
SELECT 'TABLE: ' || table_name as location, 'DATA' as type
FROM user_tables
WHERE table_name IN (
  -- This gets complex - you'd need dynamic SQL to search all tables
    -- Better to use specific tables you suspect
    );

-- 2. In stored code
SELECT 'CODE: ' || name as location, 'SOURCE' as type
FROM user_source
WHERE UPPER(text) LIKE '%YOUR_SEARCH_TEXT%';

-- 3. In constraints
SELECT 'CONSTRAINT: ' || constraint_name as location, 'CONSTRAINT' as type
FROM user_constraints
WHERE UPPER(search_condition) LIKE '%YOUR_SEARCH_TEXT%';

-- 4. In comments
SELECT 'COMMENT: ' || table_name as location, 'TABLE_COMMENT' as type
FROM user_tab_comments
WHERE UPPER(comments) LIKE '%YOUR_SEARCH_TEXT%';

Oracle Job and Scheduler Investigation

-- Find Oracle jobs that might process your data
SELECT job_name, job_type, job_action, enabled, state
FROM user_scheduler_jobs;

-- See job run history
SELECT job_name, status, actual_start_date, run_duration
FROM user_scheduler_job_run_details
WHERE job_name = 'YOUR_JOB_NAME'
ORDER BY actual_start_date DESC;

-- Old-style Oracle jobs (pre-10g)
SELECT job, what, next_date, interval, broken
FROM user_jobs;

Oracle Tools and Resources

SQL Developer Advanced Tips

• Use the Connections panel to browse table structures
• Right-click tables for “Quick DDL” to see CREATE statements
• Use Find DB Object (Ctrl+Shift+L) to search for objects by name
• Use Explain Plan button (F10) to see execution plans
• Keep multiple SQL worksheets open for testing
• Use Reports tab for predefined data dictionary queries

Oracle SQL*Plus Commands

-- Describe objects
DESC table_name;
DESC procedure_name;

-- Set output formatting
SET LINESIZE 200;
SET PAGESIZE 100;
COLUMN column_name FORMAT A30;

-- Spool output to file
SPOOL output.txt;
-- Your queries here
SPOOL OFF;

Oracle Emergency Procedures

When You Lock Yourself Out

-- If you have a hanging session:
-- 1. Open new SQL Developer connection
-- 2. Find your session: SELECT * FROM V$SESSION WHERE USERNAME = 'YOUR_USER';
-- 3. Ask DBA to kill it or wait for timeout

-- If you corrupt data:
-- 1. ROLLBACK if transaction is still open
-- 2. Check if flashback is available: FLASHBACK TABLE table_name TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
-- 3. Restore from backup (ask DBA)

Oracle-Specific Red Flags

• Queries without ROWNUM limits on large tables
• Hard-coded SCNs or timestamps in flashback queries
• Direct manipulation of Oracle system tables (SYS schema)
• Using TRUNCATE instead of DELETE (can’t be rolled back)
• Dropping constraints without understanding dependencies

Oracle Best Practices for Beginners

1. Always use UPPER CASE for Oracle keywords in shared code
2. Quote identifiers with spaces or lowercase using double quotes
3. Use bind variables (:variable) for values that change
4. Understand the difference between CHAR and VARCHAR2
5. Be careful with NULL handling - Oracle treats empty strings as NULL
6. Use DUAL table for testing expressions: SELECT SYSDATE FROM DUAL;

Remember: Oracle-Specific Gotchas

• Empty strings are treated as NULL
• Date arithmetic is different: SYSDATE + 1 = tomorrow
• ROWNUM is assigned before ORDER BY
• Oracle is case-sensitive for data but not keywords
• Sequences don’t rollback - gaps in sequence numbers are normal
• VARCHAR2 has different size limits depending on Oracle version