Implement Schema, PL/SQL APIs, Partitioning/Indexes, Security, Services, and Change Automation

BEGIN EXECUTE IMMEDIATE 'DROP USER sh29_app CASCADE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -1918 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP PACKAGE servicehub_owner.sh29_work_order_api';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -4043 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_owner.sh29_work_order_events PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_owner.sh29_work_orders PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE servicehub_owner.sh29_customers PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/

CREATE TABLE servicehub_owner.sh29_customers (  customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  tenant_code VARCHAR2(30) NOT NULL,  customer_name VARCHAR2(120) NOT NULL,  status_code VARCHAR2(12) DEFAULT 'ACTIVE' NOT NULL,  created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  CONSTRAINT sh29_customer_tenant_uq UNIQUE(tenant_code,customer_id),  CONSTRAINT sh29_customer_status_ck    CHECK (status_code IN ('ACTIVE','SUSPENDED')));CREATE TABLE servicehub_owner.sh29_work_orders (  work_order_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  customer_id NUMBER NOT NULL,  tenant_code VARCHAR2(30) NOT NULL,  status_code VARCHAR2(12) DEFAULT 'OPEN' NOT NULL,  priority_code VARCHAR2(10) DEFAULT 'NORMAL' NOT NULL,  description VARCHAR2(1000) NOT NULL,  amount NUMBER(12,2) DEFAULT 0 NOT NULL,  created_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  updated_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  CONSTRAINT sh29_wo_customer_fk    FOREIGN KEY(tenant_code,customer_id)    REFERENCES servicehub_owner.sh29_customers(tenant_code,customer_id),  CONSTRAINT sh29_wo_status_ck    CHECK (status_code IN ('OPEN','HOLD','CLOSED')),  CONSTRAINT sh29_wo_priority_ck    CHECK (priority_code IN ('LOW','NORMAL','HIGH')),  CONSTRAINT sh29_wo_amount_ck CHECK (amount >= 0));CREATE TABLE servicehub_owner.sh29_work_order_events (  event_id NUMBER GENERATED ALWAYS AS IDENTITY,  work_order_id NUMBER NOT NULL,  tenant_code VARCHAR2(30) NOT NULL,  event_type VARCHAR2(30) NOT NULL,  event_ts TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  payload_json JSON,  CONSTRAINT sh29_woe_pk PRIMARY KEY(event_id,event_ts))PARTITION BY RANGE (event_ts)INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))(  PARTITION p_before_2026  VALUES LESS THAN (TIMESTAMP '2026-01-01 00:00:00'));

CREATE INDEX servicehub_owner.sh29_wo_tenant_created_ixON servicehub_owner.sh29_work_orders(  tenant_code,created_at,work_order_id);CREATE INDEX servicehub_owner.sh29_wo_customer_ixON servicehub_owner.sh29_work_orders(  tenant_code,customer_id);CREATE INDEX servicehub_owner.sh29_woe_work_ts_ixON servicehub_owner.sh29_work_order_events(  work_order_id,event_ts)LOCAL;

INSERT INTO servicehub_owner.sh29_customers(  tenant_code,customer_name) VALUES('TENANT_A','North Service Group');INSERT INTO servicehub_owner.sh29_customers(  tenant_code,customer_name) VALUES('TENANT_B','South Service Group');COMMIT;SELECT customer_id,tenant_code,customer_nameFROM servicehub_owner.sh29_customersORDER BY customer_id;

CREATE OR REPLACE PACKAGE servicehub_owner.sh29_work_order_apiAUTHID DEFINERAS  PROCEDURE create_work_order(    p_tenant_code   IN VARCHAR2,    p_customer_id   IN NUMBER,    p_description   IN VARCHAR2,    p_priority_code IN VARCHAR2 DEFAULT 'NORMAL',    p_amount        IN NUMBER DEFAULT 0,    p_work_order_id OUT NUMBER  );  PROCEDURE close_work_order(    p_tenant_code  IN VARCHAR2,    p_work_order_id IN NUMBER  );END sh29_work_order_api;/

CREATE OR REPLACE PACKAGE BODY servicehub_owner.sh29_work_order_apiAS  PROCEDURE create_work_order(    p_tenant_code   IN VARCHAR2,    p_customer_id   IN NUMBER,    p_description   IN VARCHAR2,    p_priority_code IN VARCHAR2 DEFAULT 'NORMAL',    p_amount        IN NUMBER DEFAULT 0,    p_work_order_id OUT NUMBER  )  AS  BEGIN    INSERT INTO sh29_work_orders(      customer_id,tenant_code,description,      priority_code,amount    ) VALUES(      p_customer_id,p_tenant_code,p_description,      p_priority_code,p_amount    )    RETURNING work_order_id INTO p_work_order_id;    INSERT INTO sh29_work_order_events(      work_order_id,tenant_code,event_type,payload_json    ) VALUES(      p_work_order_id,p_tenant_code,'CREATED',      JSON_OBJECT(        'priority' VALUE p_priority_code,        'amount' VALUE p_amount        RETURNING JSON      )    );  END;  PROCEDURE close_work_order(    p_tenant_code   IN VARCHAR2,    p_work_order_id IN NUMBER  )  AS  BEGIN    UPDATE sh29_work_orders    SET status_code='CLOSED',        updated_at=SYSTIMESTAMP    WHERE tenant_code=p_tenant_code      AND work_order_id=p_work_order_id      AND status_code <> 'CLOSED';    IF SQL%ROWCOUNT = 0 THEN      RAISE_APPLICATION_ERROR(        -20029,'work order not found or already closed'      );    END IF;    INSERT INTO sh29_work_order_events(      work_order_id,tenant_code,event_type,payload_json    ) VALUES(      p_work_order_id,p_tenant_code,'CLOSED',      JSON_OBJECT('source' VALUE 'SH29_API' RETURNING JSON)    );  END;END sh29_work_order_api;/

CREATE USER sh29_app NO AUTHENTICATION;GRANT CREATE SESSION TO sh29_app;GRANT EXECUTEON servicehub_owner.sh29_work_order_apiTO sh29_app;GRANT SELECTON servicehub_owner.sh29_customersTO sh29_app;-- For an interactive lab login only:PASSWORD sh29_app-- Set a disposable password interactively; never store it here.

INSERT INTO servicehub_owner.sh29_work_orders(  customer_id,tenant_code,description,amount) VALUES(  1,'TENANT_A','Bypass API',10);-- Expected:-- ORA-01031: insufficient privileges-- (or object-not-visible behavior depending on privilege resolution)

SELECT  grantee,owner,table_name,privilegeFROM dba_tab_privsWHERE grantee='SH29_APP'ORDER BY owner,table_name,privilege;

VARIABLE new_id NUMBERBEGIN  servicehub_owner.sh29_work_order_api.create_work_order(    p_tenant_code   => 'TENANT_A',    p_customer_id   => 1,    p_description   => 'Compressor restart inspection',    p_priority_code => 'HIGH',    p_amount        => 125.50,    p_work_order_id => :new_id  );  COMMIT;END;/PRINT new_id

BEGIN  EXECUTE IMMEDIATE 'NOAUDIT POLICY sh29_api_audit';EXCEPTION WHEN OTHERS THEN NULL;END;/BEGIN  EXECUTE IMMEDIATE 'DROP AUDIT POLICY sh29_api_audit';EXCEPTION WHEN OTHERS THEN NULL;END;/CREATE AUDIT POLICY sh29_api_audit  ACTIONS EXECUTE ON servicehub_owner.sh29_work_order_api;AUDIT POLICY sh29_api_audit BY sh29_app;

SELECT  event_timestamp,  dbusername,  action_name,  object_schema,  object_name,  return_code,  client_program_nameFROM unified_audit_trailWHERE audit_type='Unified Audit'  AND dbusername='SH29_APP'  AND object_name='SH29_WORK_ORDER_API'ORDER BY event_timestamp DESCFETCH FIRST 20 ROWS ONLY;

SELECT  wallet_type,  status,  wrl_parameter,  con_idFROM v$encryption_walletORDER BY con_id;SELECT  tablespace_name,  encryptedFROM dba_tablespacesORDER BY tablespace_name;

SELECT  SYS_CONTEXT('USERENV','SERVICE_NAME') AS service_name,  SYS_CONTEXT('USERENV','CON_NAME') AS con_nameFROM dual;

CREATE TABLE servicehub_owner.sh29_schema_release (  release_id VARCHAR2(30) PRIMARY KEY,  applied_at TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  applied_by VARCHAR2(128) NOT NULL,  artifact_hash VARCHAR2(64) NOT NULL,  acceptance_status VARCHAR2(12) NOT NULL    CHECK (acceptance_status IN ('PENDING','PASSED','FAILED')));INSERT INTO servicehub_owner.sh29_schema_release(  release_id,applied_by,artifact_hash,acceptance_status) VALUES(  'SH29-001',  SYS_CONTEXT('USERENV','SESSION_USER'),  '6a2f9a7c74db6d88472f615b31b2070f3b630d93d9f1bb5651248713506c47c3',  'PENDING');COMMIT;

SELECT table_name,partitionedFROM user_tablesWHERE table_name LIKE 'SH29_%'ORDER BY table_name;SELECT index_name,table_name,uniqueness,statusFROM user_indexesWHERE table_name LIKE 'SH29_%'ORDER BY table_name,index_name;SELECT object_name,object_type,statusFROM user_objectsWHERE object_name LIKE 'SH29_%'  AND status <> 'VALID';SELECT grantee,table_name,privilegeFROM dba_tab_privsWHERE grantee='SH29_APP'ORDER BY table_name,privilege;