Architecting Oracle Database Solutions

For any request, immediately determine three things before writing code:

1. Target platform — Oracle 19c/23ai on-prem, Exadata, OCI Autonomous DB, Oracle Database@Azure/AWS, or RAC cluster.
2. Deliverable scope — schema only? Full migration repo? Tuning diagnostic? HA architecture?
3. Non-functional requirements — availability tier (single instance vs MAA Gold/Platinum), security posture (regulated data → TDE + VPD + FGAC), performance SLA.

Then produce the full blueprint — never a fragment. Example minimal response shape for "design a schema for orders":

SQL
-- Target: Oracle 19c Enterprise Edition
CREATE TABLESPACE ts_orders_data
  DATAFILE '/u01/app/oracle/oradata/ORCL/ts_orders_data01.dbf'
  SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
  LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLE orders (
  order_id      NUMBER(19) GENERATED ALWAYS AS IDENTITY,
  customer_id   NUMBER(19)      NOT NULL,
  order_status  VARCHAR2(20)    NOT NULL,
  order_json    JSON,
  created_at    TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT pk_orders PRIMARY KEY (order_id),
  CONSTRAINT chk_orders_status CHECK (order_status IN ('NEW','PAID','SHIPPED','CANCELLED'))
) TABLESPACE ts_orders_data
  PARTITION BY RANGE (created_at) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (
    PARTITION p_init VALUES LESS THAN (TIMESTAMP '2024-01-01 00:00:00 +00:00')
  );

CREATE INDEX ix_orders_customer ON orders (customer_id) TABLESPACE ts_orders_data LOCAL;

Never stop mid-artifact. If the request implies a repo, emit the full tree + every file.

Progress:
- [ ] Clarify target engine/edition and availability tier (infer sensibly if unstated, state assumption)
- [ ] Produce directory tree for the deployment/migration repository
- [ ] Write DDL & data model (tablespaces, partitioned tables, constraints, indexes)
- [ ] Write PL/SQL programmability (packages, procedures, triggers, error handling/logging)
- [ ] Write security layer (TDE, VPD policies, FGAC, unified auditing, least-privilege grants)
- [ ] Write IaC (Terraform/OCI Resource Manager) for infrastructure provisioning
- [ ] Write HA/DR layer if in scope (Data Guard, RAC, RMAN backup strategy)
- [ ] Write diagnostics/tuning artifacts (AWR/SQLT scripts, SQL Profiles, execution plan baselines)
- [ ] Write README.md playbook: execution order, verification SQL, backout plan
- [ ] Self-check against Evaluation Metric before finalizing

Standard repository structure to emit for any multi-file deliverable

db-deployment/
├── README.md
├── 01_ddl/
│   ├── 01_tablespaces.sql
│   ├── 02_tables.sql
│   ├── 03_indexes.sql
│   └── 04_constraints.sql
├── 02_plsql/
│   ├── pkg_order_processing.pks
│   ├── pkg_order_processing.pkb
│   └── trg_orders_audit.sql
├── 03_security/
│   ├── tde_config.sql
│   ├── vpd_policies.sql
│   └── roles_and_grants.sql
├── 04_iac/
│   ├── main.tf
│   ├── variables.tf
│   └── network.tf
├── 05_ha_dr/
│   ├── dataguard_setup.sql
│   └── rman_backup_strategy.rman
└── 06_diagnostics/
    ├── awr_report.sql
    └── sql_profile_baseline.sql