etc/sql/oracle_11_test.sql

0001 DROP SEQUENCE REQUEST_ID_SEQ;
0002
0003 DROP table REQUESTS purge;
0004
0005 --- requests
0006 CREATE SEQUENCE REQUEST_ID_SEQ MINVALUE 1 INCREMENT BY 1 ORDER CACHE 3 NOCYCLE;
0007 CREATE TABLE REQUESTS
0008 (
0009         request_id NUMBER(12),
0010         scope VARCHAR2(25) constraint REQ_SCOPE_NN NOT NULL,
0011         name VARCHAR2(255) constraint REQ_NAME_NN NOT NULL,
0012         requester VARCHAR2(20),
0013         request_type NUMBER(2) constraint REQ_DATATYPE_NN NOT NULL,
0014         transform_tag VARCHAR2(10),
0015         workload_id NUMBER(10),
0016         priority NUMBER(7),
0017         status NUMBER(2) constraint REQ_STATUS_ID_NN NOT NULL,
0018         substatus NUMBER(2),
0019         locking NUMBER(2),
0020         created_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_CREATED_NN NOT NULL,
0021         updated_at DATE DEFAULT SYS_EXTRACT_UTC(systimestamp(0)) constraint REQ_UPDATED_NN NOT NULL,
0022         accessed_at DATE,
0023         expired_at DATE,
0024         errors VARCHAR2(1024),
0025         request_metadata CLOB,
0026         processing_metadata CLOB,
0027         CONSTRAINT REQUESTS_PK PRIMARY KEY (request_id) USING INDEX LOCAL
0028         --- CONSTRAINT REQUESTS_NAME_SCOPE_UQ UNIQUE (name, scope, requester, request_type, transform_tag, workload_id) -- USING INDEX LOCAL,
0029 )
0030 PCTFREE 3
0031 PARTITION BY RANGE(REQUEST_ID)
0032 INTERVAL ( 100000 )
0033 ( PARTITION initial_part VALUES LESS THAN (1) );
0034
0035 CREATE OR REPLACE TRIGGER TRIG_REQUEST_ID
0036     BEFORE INSERT
0037     ON REQUESTS
0038     FOR EACH ROW
0039     BEGIN
0040         :NEW.request_id := REQUEST_ID_SEQ.NEXTVAL ;
0041     END;
0042  /
0043