Oracle 19c DBA Features Guide - New Features for Database Administrators

Oracle 19c DBA Features Guide

Key Oracle 19c features every DBA should know, with practical examples.

Automatic Indexing

Oracle 19c introduces fully automatic index management.

Enable Automatic Indexing

-- Enable automatic indexing
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE', 'IMPLEMENT');

-- Options:
-- 'OFF' - Disabled
-- 'REPORT ONLY' - Creates invisible indexes, generates reports
-- 'IMPLEMENT' - Creates and drops indexes automatically

-- Configure schemas
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', TRUE);

-- Set retention period (days)
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', 373);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', NULL);

Monitor Automatic Indexes

-- View auto index configuration
SELECT * FROM DBA_AUTO_INDEX_CONFIG;

-- View automatic indexes
SELECT INDEX_NAME, TABLE_NAME, AUTO, CONSTRAINT_INDEX, VISIBILITY
FROM DBA_INDEXES
WHERE AUTO = 'YES';

-- Auto index activity report
SELECT DBMS_AUTO_INDEX.REPORT_ACTIVITY() FROM DUAL;

-- Last auto index report
SELECT DBMS_AUTO_INDEX.REPORT_LAST_ACTIVITY() FROM DUAL;

SQL Quarantine

Prevent resource-intensive SQL from running.

Create SQL Quarantine

-- Quarantine by SQL_ID
BEGIN
  DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(
    SQL_ID => 'abc123xyz'
  );
END;
/

-- Quarantine by SQL text
BEGIN
  DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_TEXT(
    SQL_TEXT => 'SELECT * FROM large_table WHERE col = :1'
  );
END;
/

-- Set resource limits
DECLARE
  l_quarantine VARCHAR2(30);
BEGIN
  l_quarantine := DBMS_SQLQ.CREATE_QUARANTINE_BY_SQL_ID(SQL_ID => 'abc123xyz');

  DBMS_SQLQ.ALTER_QUARANTINE(
    QUARANTINE_NAME => l_quarantine,
    PARAMETER_NAME => 'ELAPSED_TIME',
    PARAMETER_VALUE => '60'  -- 60 seconds
  );
END;
/

Manage Quarantines

-- View quarantined SQL
SELECT * FROM DBA_SQL_QUARANTINE;

-- Drop quarantine
EXEC DBMS_SQLQ.DROP_QUARANTINE('quarantine_name');

-- Temporarily disable
ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_QUARANTINE = FALSE;

Real-Time Statistics

Statistics gathered during DML operations.

Check Real-Time Statistics

-- View real-time stats status
SELECT TABLE_NAME, NOTES FROM DBA_TAB_STATISTICS
WHERE OWNER = 'HR'
ORDER BY TABLE_NAME;

-- Stats gathered during bulk load
SELECT * FROM DBA_TAB_COL_STATISTICS
WHERE OWNER = 'HR' AND TABLE_NAME = 'EMPLOYEES'
AND NOTES LIKE '%STATS_ON_LOAD%';

Configure Real-Time Statistics

-- Enable at table level
EXEC DBMS_STATS.SET_TABLE_PREFS('HR', 'EMPLOYEES', 'APPROXIMATE_NDV_ALGORITHM', 'HYPERLOGLOG');

-- Disable real-time stats if needed
ALTER SESSION SET OPTIMIZER_REAL_TIME_STATISTICS = FALSE;

High-Frequency Statistics Collection

-- Collect statistics for volatile tables more frequently
EXEC DBMS_STATS.SET_TABLE_PREFS('HR', 'VOLATILE_TABLE', 'STALE_PERCENT', '5');

-- Auto task for high-frequency collection
BEGIN
  DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'AUTOMATIC');
  DBMS_STATS.SET_GLOBAL_PREFS('INCREMENTAL', 'TRUE');
END;
/

JSON Improvements

JSON Data Type (19c+)

-- Create table with JSON data type
CREATE TABLE products (
  id NUMBER,
  data JSON
);

-- Insert JSON
INSERT INTO products VALUES (1, JSON('{"name": "Widget", "price": 19.99}'));

-- Query with dot notation
SELECT p.data.name, p.data.price
FROM products p;

-- JSON path expressions
SELECT JSON_VALUE(data, '$.name') FROM products;

Simplified JSON Syntax

-- JSON_OBJECT and JSON_ARRAY
SELECT JSON_OBJECT(
  'employee_id' VALUE employee_id,
  'name' VALUE first_name || ' ' || last_name,
  'salary' VALUE salary
) AS emp_json
FROM employees;

Private Temporary Tables

-- Create private temp table (visible only in session)
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_temp_data (
  id NUMBER,
  value VARCHAR2(100)
) ON COMMIT DROP DEFINITION;

-- Options:
-- ON COMMIT DROP DEFINITION - Drops table and data on commit
-- ON COMMIT PRESERVE DEFINITION - Drops data, keeps structure until session end

-- Insert data
INSERT INTO ORA$PTT_temp_data VALUES (1, 'Test');

-- Query
SELECT * FROM ORA$PTT_temp_data;

Schema-Only Accounts

-- Create account that cannot log in but owns objects
CREATE USER app_schema NO AUTHENTICATION;

-- Grant privileges
GRANT CREATE TABLE, CREATE VIEW TO app_schema;

-- Application user connects and uses schema
ALTER SESSION SET CURRENT_SCHEMA = app_schema;

Hybrid Partitioned Tables

-- Table with both internal and external partitions
CREATE TABLE sales_hybrid (
  sale_id NUMBER,
  sale_date DATE,
  amount NUMBER
)
PARTITION BY RANGE (sale_date) (
  PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
  PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
  PARTITION p_external VALUES LESS THAN (MAXVALUE)
    EXTERNAL LOCATION ('/data/external_sales/')
);

Active Data Guard DML Redirect

-- On Standby: Enable DML redirect
ALTER SYSTEM SET ADG_REDIRECT_DML = TRUE;

-- DML on standby is automatically redirected to primary
INSERT INTO hr.employees VALUES (...);  -- Redirected to primary

In-Memory Expressions

-- Populate expressions in memory
ALTER TABLE employees INMEMORY INMEMORY_EXPRESSIONS (
  (salary * 12) AS annual_salary
);

-- Check in-memory expressions
SELECT COLUMN_NAME, INMEMORY_COMPRESSION
FROM V$IM_COLUMN_LEVEL
WHERE TABLE_NAME = 'EMPLOYEES';

Easy Connect Plus

-- Enhanced connection string syntax
sqlplus hr/password@//hostname:1521/service_name?connect_timeout=10&retry_count=3

-- With wallet
sqlplus /@alias?wallet_location=/path/to/wallet

-- With SSL
sqlplus hr/password@tcps://hostname:2484/service_name

Memoptimized Rowstore

-- Fast lookup for key-value access
ALTER TABLE fast_lookup MEMOPTIMIZE FOR READ;

-- Fast ingest
ALTER TABLE staging MEMOPTIMIZE FOR WRITE;

-- Check status
SELECT TABLE_NAME, MEMOPTIMIZE_READ, MEMOPTIMIZE_WRITE
FROM DBA_TABLES
WHERE OWNER = 'HR';

Query Performance Improvements

SQL Macros (21c backported to 19c RU)

CREATE OR REPLACE FUNCTION mask_ssn RETURN VARCHAR2 SQL_MACRO IS
BEGIN
  RETURN q'[CONCAT('XXX-XX-', SUBSTR(ssn, -4))]';
END;
/

-- Use in query
SELECT employee_id, mask_ssn(ssn) FROM employees;

Approximate Count Distinct

-- Faster count distinct
SELECT APPROX_COUNT_DISTINCT(customer_id) FROM orders;

-- With precision
SELECT APPROX_COUNT_DISTINCT(customer_id, 'MAX_ERROR', 0.01) FROM orders;

Useful 19c DBA Queries

-- Check 19c specific features
SELECT * FROM V$OPTION WHERE PARAMETER LIKE '%19%';

-- Database version details
SELECT * FROM V$VERSION;

-- Patch information
SELECT * FROM DBA_REGISTRY_SQLPATCH;

-- Check component status
SELECT COMP_NAME, VERSION, STATUS FROM DBA_REGISTRY;