How to Administer Oracle Database in 2026

ALTER SESSION SET CONTAINER = XEPDB1;

-- Create tablespace with autoextend and local management
CREATE BIGFILE TABLESPACE expert_ts
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/expert_ts.dbf' SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 32G
SEGMENT SPACE MANAGEMENT AUTO
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

-- Create user with quotas and profiles
CREATE USER expert_user IDENTIFIED BY "ExpertPass123#"
DEFAULT TABLESPACE expert_ts
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON expert_ts
PROFILE DEFAULT;

-- Grant minimal privileges for dev/prod
GRANT CREATE SESSION TO expert_user;
GRANT CREATE TABLE TO expert_user;
GRANT CREATE SEQUENCE TO expert_user;
GRANT CREATE PROCEDURE TO expert_user;
GRANT CREATE VIEW TO expert_user;
GRANT CREATE MATERIALIZED VIEW TO expert_user;
GRANT UNLIMITED TABLESPACE TO expert_user;

-- Advanced roles for partitioning and analytics
GRANT CREATE TABLESPACE TO expert_user;
GRANT CREATE ANY INDEX TO expert_user;
ALTER USER expert_user QUOTA UNLIMITED ON SYSTEM;

CONNECT expert_user/"ExpertPass123#"@//localhost:1521/XEPDB1;

-- Partitioned orders table by month (range-list hybrid)
CREATE TABLE orders (
  order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER NOT NULL,
  order_date DATE NOT NULL,
  amount NUMBER(10,2) NOT NULL,
  status VARCHAR2(20) DEFAULT 'PENDING'
)
PARTITION BY RANGE (order_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) (
  PARTITION p0 VALUES LESS THAN (DATE '2026-01-01')
)
TABLESPACE expert_ts
ENABLE ROW MOVEMENT;

-- Customers table with local index
CREATE TABLE customers (
  customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR2(100) NOT NULL,
  email VARCHAR2(255) UNIQUE,
  created_date DATE DEFAULT SYSDATE
)
TABLESPACE expert_ts;

-- Local index on partition (efficient for date queries)
CREATE INDEX idx_orders_date_status ON orders(order_date, status)
LOCAL
TABLESPACE expert_ts;

-- Foreign key with deferrable for batch loads
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
DEFERRABLE INITIALLY DEFERRED;

CREATE OR REPLACE PACKAGE pkg_order_processor AS
  PROCEDURE upsert_order(p_customer_id IN NUMBER, p_amount IN NUMBER, p_status IN VARCHAR2);
  PROCEDURE generate_monthly_report(p_year IN NUMBER, p_month IN NUMBER);
  FUNCTION get_order_count(p_status IN VARCHAR2) RETURN NUMBER;
END pkg_order_processor;
/

CREATE OR REPLACE PACKAGE BODY pkg_order_processor AS
  -- Autonomous log procedure
  PROCEDURE log_action(p_action VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO audit_log (action, log_date) VALUES (p_action, SYSDATE);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN ROLLBACK;
  END log_action;

  PROCEDURE upsert_order(p_customer_id IN NUMBER, p_amount IN NUMBER, p_status IN VARCHAR2) IS
    v_order_id NUMBER;
  BEGIN
    log_action('Upsert order for cust ' || p_customer_id);
    SELECT order_id INTO v_order_id FROM orders WHERE customer_id = p_customer_id AND ROWNUM=1;
    UPDATE orders SET amount = p_amount, status = p_status WHERE order_id = v_order_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      INSERT INTO orders (customer_id, amount, status, order_date)
      VALUES (p_customer_id, p_amount, p_status, SYSDATE)
      RETURNING order_id INTO v_order_id;
  END;

  PROCEDURE generate_monthly_report(p_year IN NUMBER, p_month IN NUMBER) IS
  BEGIN
    -- Example: aggregate and insert report
    INSERT INTO monthly_reports (year, month, total_amount)
    SELECT p_year, p_month, SUM(amount)
    FROM orders WHERE EXTRACT(YEAR FROM order_date)=p_year AND EXTRACT(MONTH FROM order_date)=p_month;
    COMMIT;
  END;

  FUNCTION get_order_count(p_status IN VARCHAR2) RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM orders WHERE status = p_status;
    RETURN v_count;
  END;
END pkg_order_processor;
/

-- Create support tables
CREATE TABLE audit_log (action VARCHAR2(4000), log_date DATE) TABLESPACE expert_ts;
CREATE TABLE monthly_reports (year NUMBER, month NUMBER, total_amount NUMBER) TABLESPACE expert_ts;

CONNECT sys/"YourSysPass"@//localhost:1521/XEPDB1 as sysdba;

-- Generate AWR snapshot for analysis
EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

-- Expert monitoring views
SELECT * FROM v$sql WHERE executions > 100 ORDER BY elapsed_time DESC;

-- Bitmap index for low-cardinality status
CONNECT expert_user/"ExpertPass123#"@//localhost:1521/XEPDB1;
CREATE BITMAP INDEX idx_orders_status_bitmap ON orders(status) LOCAL TABLESPACE expert_ts;

-- Composite function-based index (for UPPER(email))
CREATE INDEX idx_customers_email_upper ON customers(UPPER(email)) TABLESPACE expert_ts;

-- Gather optimized stats
EXEC DBMS_STATS.gather_table_stats('EXPERT_USER', 'ORDERS', method_opt=>'FOR ALL COLUMNS SIZE AUTO', cascade=>TRUE, estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

-- Example optimized query
SELECT /*+ INDEX(orders idx_orders_date_status) */ COUNT(*) FROM orders WHERE order_date > SYSDATE - 30 AND status = 'SHIPPED';

CONNECT sys/"YourSysPass"@//localhost:1521/XEPDB1 as sysdba;

-- Configure RMAN (run in RMAN>)
-- RMAN
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/%%U';

-- Full level 0 backup
BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;

-- Incremental level 1 backup (daily)
BACKUP INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG;

-- Validate (check for corruption)
VALIDATE DATABASE;

-- Report
REPORT NEED BACKUP;
LIST BACKUP SUMMARY;