Oracle در عمق: PL/SQL، بهینه‌سازی و معماریOracle In Depth: PL/SQL, Tuning & Architecture

DO $$
DECLARE
  v_total numeric := 0;
BEGIN
  SELECT COALESCE(SUM(amount), 0) INTO v_total
  FROM payments WHERE status = 'PAID';
  RAISE NOTICE 'Total = %', v_total;
END $$;

DECLARE
  v_total NUMBER := 0;
BEGIN
  SELECT NVL(SUM(amount), 0) INTO v_total
  FROM payments WHERE status = 'PAID';
  DBMS_OUTPUT.PUT_LINE('Total = ' || v_total);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No rows');
END;
/

-- PostgreSQL has no packages: a schema is the closest grouping unit
CREATE SCHEMA IF NOT EXISTS billing;

CREATE OR REPLACE FUNCTION billing.invoice_total(p_invoice_id bigint)
RETURNS numeric LANGUAGE plpgsql STABLE AS $$
DECLARE v_sum numeric;
BEGIN
  SELECT COALESCE(SUM(line_amount), 0) INTO v_sum
  FROM invoice_lines WHERE invoice_id = p_invoice_id;
  RETURN v_sum;
END $$;

CREATE OR REPLACE PROCEDURE billing.mark_paid(p_invoice_id bigint)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE invoices SET status = 'PAID', paid_at = now()
   WHERE invoice_id = p_invoice_id;
END $$;

CALL billing.mark_paid(42);

CREATE OR REPLACE PACKAGE billing_pkg AS
  FUNCTION invoice_total(p_invoice_id NUMBER) RETURN NUMBER;
  PROCEDURE mark_paid(p_invoice_id NUMBER);
END billing_pkg;
/
CREATE OR REPLACE PACKAGE BODY billing_pkg AS
  FUNCTION invoice_total(p_invoice_id NUMBER) RETURN NUMBER IS
    v_sum NUMBER;
  BEGIN
    SELECT NVL(SUM(line_amount), 0) INTO v_sum
    FROM invoice_lines WHERE invoice_id = p_invoice_id;
    RETURN v_sum;
  END;

  PROCEDURE mark_paid(p_invoice_id NUMBER) IS
  BEGIN
    UPDATE invoices SET status = 'PAID', paid_at = SYSTIMESTAMP
     WHERE invoice_id = p_invoice_id;
  END;
END billing_pkg;
/
BEGIN billing_pkg.mark_paid(42); END;
/

DO $$
DECLARE rec RECORD;
BEGIN
  FOR rec IN
    SELECT invoice_id, customer_id FROM invoices WHERE status = 'DUE'
  LOOP
    UPDATE customers SET dunning_flag = true WHERE id = rec.customer_id;
  END LOOP;
END $$;

BEGIN
  FOR rec IN (SELECT invoice_id, customer_id
              FROM invoices WHERE status = 'DUE') LOOP
    UPDATE customers SET dunning_flag = 'Y' WHERE id = rec.customer_id;
  END LOOP;
END;
/

-- One set-based statement beats any loop
UPDATE invoices SET status = 'DUNNED' WHERE status = 'DUE';

-- From Java, N rows in one round-trip via an array parameter:
--   UPDATE invoices SET status='DUNNED' WHERE invoice_id = ANY($1::bigint[]);

-- Chunked variant, safe for concurrent workers
WITH batch AS (
  SELECT invoice_id FROM invoices WHERE status = 'DUE'
   ORDER BY invoice_id LIMIT 1000 FOR UPDATE SKIP LOCKED
)
UPDATE invoices i SET status = 'DUNNED'
  FROM batch b WHERE i.invoice_id = b.invoice_id
RETURNING i.invoice_id;

DECLARE
  TYPE t_ids IS TABLE OF invoices.invoice_id%TYPE;
  v_ids t_ids;
  CURSOR c IS SELECT invoice_id FROM invoices WHERE status = 'DUE';
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_ids LIMIT 1000;   -- LIMIT is critical
    EXIT WHEN v_ids.COUNT = 0;

    FORALL i IN 1 .. v_ids.COUNT
      UPDATE invoices SET status = 'DUNNED' WHERE invoice_id = v_ids(i);

    COMMIT;                                       -- batched commit
    EXIT WHEN v_ids.COUNT < 1000;
  END LOOP;
  CLOSE c;
END;
/

CREATE OR REPLACE PROCEDURE purge_old_events(p_batch int DEFAULT 5000)
LANGUAGE plpgsql AS $$
DECLARE v_deleted int;
BEGIN
  LOOP
    DELETE FROM events WHERE ctid IN (
      SELECT ctid FROM events
       WHERE created_at < now() - interval '90 days' LIMIT p_batch);
    GET DIAGNOSTICS v_deleted = ROW_COUNT;
    EXIT WHEN v_deleted = 0;
    COMMIT;              -- allowed in a PROCEDURE, never in a FUNCTION
  END LOOP;
END $$;

CALL purge_old_events(5000);

CREATE OR REPLACE PROCEDURE purge_old_events(p_batch NUMBER DEFAULT 5000) IS
BEGIN
  LOOP
    DELETE FROM events
     WHERE created_at < SYSDATE - 90 AND ROWNUM <= p_batch;
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;
  END LOOP;
END;
/

BEGIN purge_old_events(5000); END;
/

CREATE OR REPLACE FUNCTION billing.charge(p_id bigint, p_amount numeric)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'amount must be positive: %', p_amount
      USING ERRCODE = 'P0001';
  END IF;
  INSERT INTO charges(invoice_id, amount) VALUES (p_id, p_amount);
EXCEPTION
  WHEN unique_violation THEN          -- SQLSTATE 23505
    RAISE NOTICE 'already charged: %', p_id;
  WHEN no_data_found THEN
    RAISE;                            -- re-raise, never swallow
END $$;

CREATE OR REPLACE PROCEDURE charge(p_id NUMBER, p_amount NUMBER) IS
BEGIN
  IF p_amount <= 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'amount must be positive: ' || p_amount);
  END IF;
  INSERT INTO charges(invoice_id, amount) VALUES (p_id, p_amount);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN          -- ORA-00001
    DBMS_OUTPUT.PUT_LINE('already charged: ' || p_id);
  WHEN NO_DATA_FOUND THEN
    RAISE;                            -- re-raise, never swallow
END;
/

CREATE OR REPLACE FUNCTION audit_salary() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO salary_audit(emp_id, old_val, new_val, changed_at)
  VALUES (OLD.id, OLD.salary, NEW.salary, now());
  RETURN NEW;                 -- BEFORE triggers must return a row
END $$;

CREATE TRIGGER trg_audit_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
EXECUTE FUNCTION audit_salary();

CREATE OR REPLACE TRIGGER trg_audit_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary <> NEW.salary)
BEGIN
  INSERT INTO salary_audit(emp_id, old_val, new_val, changed_at)
  VALUES (:OLD.id, :OLD.salary, :NEW.salary, SYSTIMESTAMP);
END;
/

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
  FROM employees e
) t WHERE rn BETWEEN 1 AND 5;

SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;      -- LIMIT 5 is the classic spelling

SELECT * FROM (
  SELECT e.*, ROW_NUMBER() OVER (ORDER BY salary DESC) rn
  FROM employees e
) WHERE rn BETWEEN 1 AND 5;

SELECT * FROM employees ORDER BY salary DESC
FETCH FIRST 5 ROWS ONLY;      -- Oracle 12c+

SELECT invoice_id, created_at, amount
  FROM invoices
 WHERE (created_at, invoice_id) < ($1, $2)   -- last row of previous page
 ORDER BY created_at DESC, invoice_id DESC
 FETCH FIRST 20 ROWS ONLY;

SELECT invoice_id, created_at, amount
  FROM invoices
 WHERE (created_at < :last_created
        OR (created_at = :last_created AND invoice_id < :last_id))
 ORDER BY created_at DESC, invoice_id DESC
 FETCH FIRST 20 ROWS ONLY;

ANALYZE VERBOSE orders;                    -- autovacuum usually does this

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;  -- histogram size

CREATE STATISTICS orders_cust_status (dependencies, ndistinct)
  ON customer_id, status FROM orders;
ANALYZE orders;

SELECT attname, n_distinct, correlation
  FROM pg_stats WHERE tablename = 'orders';

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS',
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    cascade          => TRUE);
END;
/

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'ORDERS',
         '(CUSTOMER_ID, STATUS)') FROM dual;

SELECT column_name, num_distinct, histogram
  FROM user_tab_col_statistics WHERE table_name = 'ORDERS';

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT * FROM orders WHERE customer_id = 42;

-- Plan a parameterized statement without inventing a literal (v16+)
EXPLAIN (GENERIC_PLAN)
SELECT * FROM orders WHERE customer_id = $1;

SELECT /*+ GATHER_PLAN_STATISTICS */ * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- Estimated plan only
EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = :c;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- needs preload + restart

SELECT calls, round(total_exec_time) AS total_ms,
       round(mean_exec_time, 2) AS mean_ms, rows,
       shared_blks_hit, shared_blks_read, left(query, 60) AS sql
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC FETCH FIRST 10 ROWS ONLY;

SELECT pid, state, wait_event_type, wait_event,
       now() - query_start AS running_for
  FROM pg_stat_activity WHERE state <> 'idle';

-- AWR report: @?/rdbms/admin/awrrpt.sql   (Diagnostics Pack licence!)
SELECT sql_id, SUM(executions_delta) AS execs,
       ROUND(SUM(elapsed_time_delta)/1e6) AS total_s
  FROM dba_hist_sqlstat
 WHERE snap_id BETWEEN :b_snap AND :e_snap
 GROUP BY sql_id ORDER BY 3 DESC FETCH FIRST 10 ROWS ONLY;

SELECT session_id, event, sql_id, COUNT(*) AS samples
  FROM v$active_session_history
 WHERE sample_time > SYSTIMESTAMP - INTERVAL '5' MINUTE
 GROUP BY session_id, event, sql_id ORDER BY samples DESC;

-- Bad: a different text per user → planner CPU burned on every call
SELECT * FROM orders WHERE customer_id = 12345;

-- Good: one text, parameter placeholder
PREPARE ord(bigint) AS SELECT * FROM orders WHERE customer_id = $1;
EXECUTE ord(12345);

-- Bad: each value is a new text → shared pool fills, CPU burns
SELECT * FROM orders WHERE customer_id = 12345;

-- Good: one text, shared plan → soft parse
VARIABLE cust_id NUMBER;
EXEC :cust_id := 12345;
SELECT * FROM orders WHERE customer_id = :cust_id;

CREATE INDEX idx_sales_status ON sales (status);
CREATE INDEX idx_sales_region ON sales (region);
-- plan: Bitmap Index Scan + BitmapAnd + Bitmap Heap Scan

-- BRIN: kilobytes for a billion-row time-ordered table
CREATE INDEX idx_sales_date_brin ON sales
  USING brin (sale_date) WITH (pages_per_range = 64);

CREATE BITMAP INDEX idx_sales_status ON sales (status);
CREATE BITMAP INDEX idx_sales_region ON sales (region);
-- optimizer can do BITMAP AND / BITMAP OR across them

-- Oracle's "tiny index for huge tables" analogue is a zone map
-- (Exadata/engineered systems) or simply partitioning.

CREATE INDEX idx_emp_upper_email ON employees (upper(email));
SELECT * FROM employees WHERE upper(email) = 'ANA@EXAMPLE.COM';

-- Often better on PostgreSQL: case-insensitive by type
CREATE EXTENSION IF NOT EXISTS citext;

CREATE INDEX idx_emp_upper_email ON employees (UPPER(email));
SELECT * FROM employees WHERE UPPER(email) = 'ANA@EXAMPLE.COM';

-- Oracle 12.2+: a case-insensitive collation does the same
-- ALTER TABLE employees MODIFY (email VARCHAR2(200) COLLATE BINARY_CI);

-- INCLUDE keeps payload columns out of the search key (v11+)
CREATE INDEX idx_orders_cust_incl
  ON orders (customer_id) INCLUDE (status, amount);

VACUUM (ANALYZE) orders;    -- index-only scans need the visibility map
EXPLAIN (ANALYZE, BUFFERS)
SELECT customer_id, status, amount FROM orders WHERE customer_id = $1;

-- No INCLUDE clause: append the payload columns to the key
CREATE INDEX idx_orders_cust_incl
  ON orders (customer_id, status, amount);

EXPLAIN PLAN FOR
SELECT customer_id, status, amount FROM orders WHERE customer_id = :c;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);   -- no TABLE ACCESS BY ROWID

CREATE INDEX idx_orders_pending ON orders (created_at)
  WHERE status = 'PENDING';

-- Conditional uniqueness, for free
CREATE UNIQUE INDEX uq_active_email ON users (email) WHERE deleted_at IS NULL;

-- No WHERE clause on an index. The idiom: a function-based index that
-- returns NULL for uninteresting rows (all-NULL keys are not stored).
CREATE INDEX idx_orders_pending ON orders (
  CASE WHEN status = 'PENDING' THEN created_at END);

SELECT * FROM orders
 WHERE CASE WHEN status = 'PENDING' THEN created_at END > :since;

CREATE INDEX CONCURRENTLY idx_orders_created ON orders (created_at);
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_old;
REINDEX INDEX CONCURRENTLY idx_orders_created;

SELECT relname, indexrelname, idx_scan
  FROM pg_stat_user_indexes WHERE idx_scan = 0;

CREATE INDEX idx_orders_created ON orders (created_at) ONLINE;
ALTER INDEX idx_orders_created REBUILD ONLINE;

-- Prove a drop is safe before you do it
ALTER INDEX idx_orders_old INVISIBLE;
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

ALTER INDEX idx_orders_old MONITORING USAGE;
SELECT * FROM v$object_usage;

CREATE TABLE orders (
  order_id   bigint GENERATED ALWAYS AS IDENTITY,
  order_date date NOT NULL,
  amount     numeric(12,2)
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');
CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

-- A unique key MUST contain the partition key:
ALTER TABLE orders ADD PRIMARY KEY (order_id, order_date);

CREATE TABLE orders (
  order_id   NUMBER GENERATED ALWAYS AS IDENTITY,
  order_date DATE NOT NULL,
  amount     NUMBER(12,2),
  CONSTRAINT pk_orders PRIMARY KEY (order_id)   -- global index: fine
)
PARTITION BY RANGE (order_date) (
  PARTITION p2025 VALUES LESS THAN (DATE '2026-01-01'),
  PARTITION p2026 VALUES LESS THAN (DATE '2027-01-01'),
  PARTITION pmax  VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE orders DETACH PARTITION orders_2025 CONCURRENTLY;
DROP TABLE orders_2025;

-- No INTERVAL clause in core PostgreSQL: script it, or use pg_partman
CREATE TABLE orders_2027 PARTITION OF orders
  FOR VALUES FROM ('2027-01-01') TO ('2028-01-01');

ALTER TABLE orders DROP PARTITION p2025 UPDATE INDEXES;

-- Interval partitioning: Oracle creates the next partition automatically
ALTER TABLE orders SET INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'));

-- Move cold data to cheap storage instead of deleting it
ALTER TABLE orders MOVE PARTITION p2026 TABLESPACE archive_ts ONLINE;

-- Idiomatic PostgreSQL upsert (requires a unique constraint on id)
INSERT INTO target (id, amount) VALUES ($1, $2)
ON CONFLICT (id) DO UPDATE SET amount = EXCLUDED.amount
RETURNING id, amount;

-- Standard MERGE (v15+) with RETURNING and merge_action() (v17+)
MERGE INTO target t
USING (SELECT $1::bigint AS id, $2::numeric AS amount) s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount)
RETURNING merge_action(), t.id, t.amount;

MERGE INTO target t
USING (SELECT :id AS id, :amount AS amount FROM dual) s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN INSERT (id, amount) VALUES (s.id, s.amount);

-- Oracle has no ON CONFLICT; the single-row equivalent is MERGE above,
-- or catching the unique violation:
BEGIN
  INSERT INTO target (id, amount) VALUES (:id, :amount);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    UPDATE target SET amount = :amount WHERE id = :id;
END;
/

CREATE SEQUENCE seq_order START WITH 1 INCREMENT BY 1 CACHE 20;
INSERT INTO orders (id, amount) VALUES (nextval('seq_order'), 100);

-- Preferred: standard identity column
CREATE TABLE orders (
  id     bigint GENERATED ALWAYS AS IDENTITY,
  amount numeric(12,2));
INSERT INTO orders (amount) VALUES (100) RETURNING id;

CREATE SEQUENCE seq_order START WITH 1 INCREMENT BY 1 CACHE 100;
INSERT INTO orders (id, amount) VALUES (seq_order.NEXTVAL, 100);

-- Preferred since 12c: standard identity column
CREATE TABLE orders (
  id     NUMBER GENERATED ALWAYS AS IDENTITY,
  amount NUMBER(12,2));
INSERT INTO orders (amount) VALUES (100) RETURNING id INTO :out_id;

CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', order_date) AS d, SUM(amount) AS total
  FROM orders GROUP BY 1
WITH DATA;

-- CONCURRENTLY needs a unique index and avoids blocking readers
CREATE UNIQUE INDEX ON mv_daily_sales (d);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

CREATE MATERIALIZED VIEW LOG ON orders
  WITH ROWID, SEQUENCE (order_date, amount) INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW mv_daily_sales
BUILD IMMEDIATE
REFRESH FAST ON COMMIT          -- incremental, at commit time
ENABLE QUERY REWRITE
AS SELECT TRUNC(order_date) d, SUM(amount) total, COUNT(*) cnt
     FROM orders GROUP BY TRUNC(order_date);

BEGIN ISOLATION LEVEL REPEATABLE READ;   -- snapshot fixed at first statement
SELECT SUM(amount) FROM orders;
SELECT SUM(amount) FROM orders;          -- identical, guaranteed
COMMIT;

-- No flashback query. Raw visibility info is all you get:
SELECT xmin, xmax, * FROM orders WHERE order_id = 42;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;  -- transaction-level snapshot
SELECT SUM(amount) FROM orders;
SELECT SUM(amount) FROM orders;                -- identical, guaranteed
COMMIT;

-- Flashback Query: read the table as it was 15 minutes ago
SELECT * FROM orders AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '15' MINUTE)
 WHERE order_id = 42;
SELECT * FROM orders AS OF SCN 12345678 WHERE order_id = 42;

-- Classic queue consumer: take work nobody else holds
SELECT * FROM job_queue
 WHERE status = 'READY' ORDER BY created_at
 FETCH FIRST 10 ROWS ONLY
 FOR UPDATE SKIP LOCKED;

SET lock_timeout = '3s';                       -- fail fast, don't hang
SELECT * FROM accounts WHERE id = $1 FOR UPDATE NOWAIT;

SELECT pid, wait_event, pg_blocking_pids(pid), query
  FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT * FROM job_queue
 WHERE status = 'READY' ORDER BY created_at
 FETCH FIRST 10 ROWS ONLY
 FOR UPDATE SKIP LOCKED;

SELECT * FROM accounts WHERE id = :id FOR UPDATE NOWAIT;
SELECT * FROM accounts WHERE id = :id FOR UPDATE WAIT 3;

SELECT sid, blocking_session, event, sql_id
  FROM v$session WHERE blocking_session IS NOT NULL;

DO $$
DECLARE attempt int := 0;
BEGIN
  LOOP
    BEGIN
      PERFORM transfer_funds(1, 2, 100);
      EXIT;
    EXCEPTION
      WHEN serialization_failure OR deadlock_detected THEN   -- 40001 / 40P01
        attempt := attempt + 1;
        EXIT WHEN attempt > 3;
    END;
  END LOOP;
END $$;

DECLARE
  attempt PLS_INTEGER := 0;
  cannot_serialize EXCEPTION;
  PRAGMA EXCEPTION_INIT(cannot_serialize, -8177);   -- ORA-08177
  deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(deadlock, -60);             -- ORA-00060
BEGIN
  LOOP
    BEGIN
      transfer_funds(1, 2, 100);
      EXIT;
    EXCEPTION
      WHEN cannot_serialize OR deadlock THEN
        ROLLBACK;
        attempt := attempt + 1;
        EXIT WHEN attempt > 3;
    END;
  END LOOP;
END;
/

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
  FROM pg_stat_user_tables ORDER BY n_dead_tup DESC FETCH FIRST 5 ROWS ONLY;

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02,
                        fillfactor = 85);
-- Rebuild a bloated table online with the pg_repack extension

SELECT tuned_undoretention, maxquerylen
  FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 5 ROWS ONLY;

ALTER TABLE orders ENABLE ROW MOVEMENT;
ALTER TABLE orders SHRINK SPACE COMPACT;   -- reclaim space online

ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby1, standby2)';
ALTER SYSTEM SET synchronous_commit = 'remote_apply';  -- or remote_write/on
SELECT pg_reload_conf();

SELECT application_name, state, sync_state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes
  FROM pg_stat_replication;

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
ALTER SYSTEM SET log_archive_dest_2 =
  'SERVICE=stby SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
   DB_UNIQUE_NAME=stby';

SELECT name, value, time_computed FROM v$dataguard_stats
 WHERE name IN ('transport lag', 'apply lag');

CREATE DATABASE tenant_a;
\c tenant_a
CREATE SCHEMA billing;

-- No cross-database queries: you need an extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
SELECT * FROM dblink('dbname=tenant_b', 'SELECT id FROM invoices')
       AS t(id bigint);

CREATE PLUGGABLE DATABASE tenant_a
  ADMIN USER ta_admin IDENTIFIED BY "***"
  FILE_NAME_CONVERT = ('/pdbseed/', '/tenant_a/');
ALTER PLUGGABLE DATABASE tenant_a OPEN;
ALTER SESSION SET CONTAINER = tenant_a;

-- Cross-PDB access also needs a link
CREATE DATABASE LINK tb CONNECT TO app IDENTIFIED BY "***" USING 'tenant_b';
SELECT id FROM invoices@tb;

-- 1) Empty string is NOT null
SELECT '' IS NULL AS empty_is_null;               -- false

-- 2) NULL handling and string aggregation
SELECT COALESCE(bonus, 0), STRING_AGG(name, ',' ORDER BY name) FROM emp;

-- 3) now() is a timestamptz; date arithmetic uses intervals
SELECT now(), CURRENT_DATE + INTERVAL '30 days',
       to_char(now(), 'YYYY-MM-DD');

-- 4) No DUAL needed
SELECT 1 + 1 AS result;

-- 5) Unquoted identifiers fold to LOWER case
CREATE TABLE Foo (Id int);     -- becomes foo(id)
SELECT * FROM FOO;             -- works; SELECT * FROM "Foo" fails

-- 1) Empty string IS null — the single most dangerous Oracle behaviour
SELECT CASE WHEN '' IS NULL THEN 'true' ELSE 'false' END FROM dual;  -- true

-- 2) NULL handling and string aggregation
SELECT NVL(bonus, 0), LISTAGG(name, ',') WITHIN GROUP (ORDER BY name) FROM emp;

-- 3) SYSDATE is a DATE (with time); arithmetic is in days
SELECT SYSDATE, TRUNC(SYSDATE) + 30,
       TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;

-- 4) DUAL required before 23ai
SELECT 1 + 1 AS result FROM dual;

-- 5) Unquoted identifiers fold to UPPER case
CREATE TABLE Foo (Id NUMBER);  -- becomes FOO(ID)
SELECT * FROM foo;             -- works; SELECT * FROM "Foo" fails