DevOps, Testing and Best Practices

66.1 Database DevOps

Version control everything: DDL, PL/SQL, reference data, grants.

Schema migration tools: Liquibase (built into SQLcl: lb generate-schema, lb update), Flyway, the SQLcl Projects feature (23ai+), Redgate Change Automation for Oracle.

Build in disposable containers (gvenzl/oracle-free images) Run unit tests Run static analysis

Deploy through environments, with approvals Unit testing PL/SQL with utPLSQL:

Code quality: SQL Developer code analysis, Trivadis/dbLinter PL/SQL & SQL Coding Guidelines, PL/SQL Cop, SonarQube plugins.

Test data: Data Masking, DBMS_RANDOM, the SQL Developer data generator, Data Pump subsetting.

CREATE OR REPLACE PACKAGE test_order_pkg AS
  --%suite(Order package)
  --%test(Order total equals sum of lines)
  PROCEDURE order_total_ok;
END;
/
CREATE OR REPLACE PACKAGE BODY test_order_pkg AS
  PROCEDURE order_total_ok IS
  BEGIN
    ut.expect(order_pkg.order_total(1)).to_equal(1500);
  END;
END;
/
EXEC ut.run('test_order_pkg');

66.2 SQL and PL/SQL Coding Standards

✅ Use bind variables and set-based SQL. Use BULK COLLECT/FORALL when you need PL/SQL loops.

✅ Anchor types with %TYPE / %ROWTYPE.

✅ Name constraints and indexes explicitly (pk_, fk_, uq_, ck_, ix_).

✅ Index foreign keys.

✅ Use ANSI joins, explicit column lists, and table aliases.

✅ Handle exceptions deliberately: log them and re-raise. Never swallow WHEN OTHERS.

✅ Use packages to organize code. Keep subprograms small.

✅ Instrument code (DBMS_APPLICATION_INFO, Logger).

✅ Commit at business transaction boundaries, not inside loops.

✅ Use DATE/TIMESTAMP literals (DATE '2025-01-01') rather than implicit conversions.

❌ String concatenation in dynamic SQL with user input ❌ SELECT * in application code

❌ Functions on indexed columns in WHERE (without function-based indexes)

❌ COMMIT inside triggers (except autonomous logging) ❌ Hard-coded schema names and literals

❌ Row-by-row processing ("slow-by-slow") ❌ Relying on implicit sorting (always use ORDER BY)

❌ Ignoring ORA-01555 and ORA-04031 warnings

66.3 DBA Production Checklist

[ ] ARCHIVELOG mode, FORCE LOGGING (if using Data Guard), multiplexed control files and redo logs

[ ] RMAN backups scheduled, validated, restore-tested. Controlfile autobackup ON. Wallet backed up.

[ ] Data Guard / RAC configured and failover tested [ ] Monitoring and alerting (OEM / scripts / cloud tools)

[ ] Statistics gathering strategy, AWR retention sized [ ] Security: default passwords changed, least privilege, unified auditing, TDE, network encryption, patching cadence

[ ] Capacity planning: tablespace growth, FRA, archive generation

[ ] Documented runbooks: startup/shutdown, failover, restore, patching

[ ] License compliance (options and packs usage: DBA_FEATURE_USAGE_STATISTICS)