schema.sql

-- Rucio's DB table definitions for Oracle RDBMS  
-- Authors: Rucio team and Gancho Dimitrov 




-- 1) ========================================= ACCOUNTS table ========================================= 

  CREATE TABLE ACCOUNTS 
   (	ACCOUNT VARCHAR2(25), 
	ACCOUNT_TYPE VARCHAR2(7), 
	STATUS VARCHAR2(9), 
	EMAIL VARCHAR2(255), 
	SUSPENDED_AT DATE, 
	DELETED_AT DATE, 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ACCOUNTS_PK PRIMARY KEY (ACCOUNT), 
	 CONSTRAINT ACCOUNTS_TYPE_NN CHECK (ACCOUNT_TYPE IS NOT NULL), 
	 CONSTRAINT ACCOUNTS_STATUS_NN CHECK (STATUS IS NOT NULL), 
	 CONSTRAINT ACCOUNTS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNTS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNTS_TYPE_CHK CHECK (account_type IN ('GROUP', 'USER', 'SERVICE')), 
	 CONSTRAINT ACCOUNTS_STATUS_CHK CHECK (status IN ('ACTIVE', 'DELETED', 'SUSPENDED')), 
	 CONSTRAINT ACCOUNTS_NAME_LOWERCASE_CHK CHECK (account=LOWER(account)) 
   ) ORGANIZATION INDEX;


-- 2) ========================================= IDENTITIES table ========================================= 

  CREATE TABLE IDENTITIES 
   (	IDENTITY VARCHAR2(2048), 
	IDENTITY_TYPE VARCHAR2(8), 
	USERNAME VARCHAR2(255), 
	PASSWORD VARCHAR2(255), 
	EMAIL VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	DELETED NUMBER(1,0), 
	DELETED_AT DATE, 
	SALT BLOB, 
	 CONSTRAINT IDENTITIES_PK PRIMARY KEY (IDENTITY, IDENTITY_TYPE), 
	 CONSTRAINT IDENTITIES_TYPE_NN CHECK (IDENTITY_TYPE IS NOT NULL), 
	 CONSTRAINT IDENTITIES_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT IDENTITIES_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT IDENTITIES_DELETED_NN CHECK (DELETED IS NOT NULL), 
	 CONSTRAINT IDENTITIES_EMAIL_NN CHECK (EMAIL IS NOT NULL), 
	 CONSTRAINT IDENTITIES_TYPE_CHK CHECK (identity_type IN ('X509', 'GSS', 'USERPASS', 'SSH', 'SAML', 'OIDC')), 
	 CONSTRAINT IDENTITIES_DELETED_CHK CHECK (deleted IN (0, 1))
   ) PCTFREE 0;


-- 3) ========================================= ACCOUNT_MAP table ========================================= 

  CREATE TABLE ACCOUNT_MAP 
   (	IDENTITY VARCHAR2(2048), 
	IDENTITY_TYPE VARCHAR2(8), 
	ACCOUNT VARCHAR2(25), 
	IS_DEFAULT NUMBER(1,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ACCOUNT_MAP_PK PRIMARY KEY (IDENTITY, IDENTITY_TYPE, ACCOUNT), 
	 CONSTRAINT ACCOUNT_MAP_IS_DEFAULT_NN CHECK (is_default IS NOT NULL), 
	 CONSTRAINT ACCOUNT_MAP_IS_TYPE_NN CHECK (IDENTITY_TYPE IS NOT NULL), 
	 CONSTRAINT ACCOUNT_MAP_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNT_MAP_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNT_MAP_DEFAULT_CHK CHECK (is_default IN (0, 1)), 
	 CONSTRAINT ACCOUNT_MAP_ID_TYPE_CHK CHECK (identity_type IN ('X509', 'GSS', 'USERPASS', 'SSH', 'SAML', 'OIDC')) , 
	 CONSTRAINT ACCOUNT_MAP_ACCOUNT_FK FOREIGN KEY(ACCOUNT)  REFERENCES ACCOUNTS (ACCOUNT), 
	 CONSTRAINT ACCOUNT_MAP_ID_TYPE_FK FOREIGN KEY(IDENTITY, IDENTITY_TYPE)  REFERENCES IDENTITIES(IDENTITY, IDENTITY_TYPE) 
   ) ORGANIZATION INDEX COMPRESS 1;


-- 4) ========================================= ACCOUNT_ATTR_MAP table ========================================= 

  CREATE TABLE ACCOUNT_ATTR_MAP 
   (	ACCOUNT VARCHAR2(25), 
	KEY VARCHAR2(255), 
	VALUE VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ACCOUNT_ATTR_MAP_PK PRIMARY KEY (ACCOUNT, KEY), 
	 CONSTRAINT ACCOUNT_ATTR_MAP_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS(ACCOUNT), 
	 CONSTRAINT ACCOUNT_ATTR_MAP_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT ACCOUNT_ATTR_MAP_KEY_NN CHECK (KEY IS NOT NULL), 
	 CONSTRAINT ACCOUNT_ATTR_MAP_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNT_ATTR_MAP_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) ORGANIZATION INDEX;

  CREATE INDEX ACCOUNT_ATTR_MAP_KEY_VALUE_IDX ON ACCOUNT_ATTR_MAP (KEY, VALUE) COMPRESS 2;



-- 5) ========================================= SCOPES table ========================================= 

  CREATE TABLE SCOPES 
   (	SCOPE VARCHAR2(25), 
	ACCOUNT VARCHAR2(25), 
	IS_DEFAULT NUMBER(1,0), 
	STATUS CHAR(1), 
	CLOSED_AT DATE, 
	DELETED_AT DATE, 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT SCOPES_PK PRIMARY KEY (SCOPE), 
	 CONSTRAINT SCOPES_IS_DEFAULT_NN CHECK (is_default IS NOT NULL), 
	 CONSTRAINT SCOPES_STATUS_NN CHECK (STATUS IS NOT NULL), 
	 CONSTRAINT SCOPES_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT SCOPES_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT SCOPES_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT SCOPES_DEFAULT_CHK CHECK (is_default IN (0, 1)), 
	 CONSTRAINT SCOPES_STATUS_CHK CHECK (status IN ('C', 'D', 'O')), 
	 CONSTRAINT SCOPES_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS(ACCOUNT) 
   ) ORGANIZATION INDEX;

  CREATE UNIQUE INDEX SCOPES_SCOPE_UQ ON SCOPES (UPPER(SCOPE));



-- 6) ========================================= RSES table ========================================= 

  CREATE TABLE RSES 
   (	ID RAW(16), 
	RSE VARCHAR2(255), 
	RSE_TYPE VARCHAR2(4), 
	DETERMINISTIC NUMBER(1,0), 
	VOLATILE NUMBER(1,0), 
	CITY VARCHAR2(255), 
	REGION_CODE VARCHAR2(2), 
	COUNTRY_NAME VARCHAR2(255), 
	CONTINENT VARCHAR2(2), 
	TIME_ZONE VARCHAR2(255), 
	ISP VARCHAR2(255), 
	LONGITUDE FLOAT(126), 
	LATITUDE FLOAT(126), 
	ASN VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	DELETED NUMBER(1,0), 
	DELETED_AT DATE, 
	AVAILABILITY NUMBER(3,0) DEFAULT 7, 
	STAGING_AREA NUMBER(1,0),
    VO VARCHAR2(3) DEFAULT 'def' NOT NULL, 
	 CONSTRAINT RSES_PK PRIMARY KEY (ID), 
	 CONSTRAINT RSES_RSE_UQ UNIQUE (RSE, VO), 
	 CONSTRAINT RSES_RSE_NN CHECK (RSE IS NOT NULL), 
	 CONSTRAINT RSES_TYPE_NN CHECK (RSE_TYPE IS NOT NULL), 
	 CONSTRAINT RSES_TYPE_CHK CHECK (RSE_TYPE IN ('DISK', 'TAPE')), 
	 CONSTRAINT RSE_DETERMINISTIC_CHK CHECK (deterministic IN (0, 1)), 
	 CONSTRAINT RSE_VOLATILE_CHK CHECK (VOLATILE IN (0, 1)), 
	 CONSTRAINT RSE_STAGING_AREA_CHK CHECK (STAGING_AREA IN (0, 1)),
     CONSTRAINT RSES_VOS_FK FOREIGN KEY(VO) REFERENCES VOS (VO) 
   ) ORGANIZATION INDEX ;




-- 7) ========================================= RSE_ATTR_MAP table ========================================= 

  CREATE TABLE RSE_ATTR_MAP 
   (	RSE_ID RAW(16), 
	KEY VARCHAR2(255), 
	VALUE VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT RSE_ATTR_MAP_PK PRIMARY KEY (RSE_ID, KEY), 
	 CONSTRAINT RSE_ATTR_MAP_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_ATTR_MAP_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_ATTR_MAP_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES(ID) 
   ) ORGANIZATION INDEX;

  CREATE INDEX RSE_ATTR_MAP_KEY_VALUE_IDX ON RSE_ATTR_MAP(KEY, VALUE) COMPRESS 1;



-- 8) =========================================  DID_KEYS table ========================================= 

  CREATE TABLE DID_KEYS 
   (	KEY VARCHAR2(255), 
	KEY_TYPE VARCHAR2(10), 
	VALUE_TYPE VARCHAR2(255), 
	VALUE_REGEXP VARCHAR2(255), 
	IS_ENUM NUMBER(1,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT DID_KEYS_PK PRIMARY KEY (KEY), 
	 CONSTRAINT DID_KEYS_TYPE_NN CHECK (key_type IS NOT NULL), 
	 CONSTRAINT DID_KEYS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT DID_KEYS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT DID_KEYS_TYPE_CHK CHECK (key_type IN ('ALL', 'DERIVED', 'COLLECTION', 'FILE')) 
   ) ORGANIZATION INDEX  ;


-- 9) ========================================= DID_KEY_MAP table ========================================= 

  CREATE TABLE DID_KEY_MAP 
   (	KEY VARCHAR2(255), 
	VALUE VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT DID_KEY_MAP_PK PRIMARY KEY (KEY, VALUE), 
	 CONSTRAINT DID_KEY_MAP_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT DID_KEY_MAP_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT DID_MAP_KEYS_FK FOREIGN KEY(KEY)  REFERENCES DID_KEYS(KEY) 
   ) ORGANIZATION INDEX  ;


-- 10) ========================================= SUBSCRIPTIONS table ========================================= 

  CREATE TABLE SUBSCRIPTIONS 
   (	ID RAW(16), 
	NAME VARCHAR2(64), 
	ACCOUNT VARCHAR2(25), 
	STATE CHAR(1), 
	POLICYID NUMBER(3,0), 
	LAST_PROCESSED DATE, 
	LIFETIME DATE, 
	RETROACTIVE NUMBER(1,0), 
	FILTER VARCHAR2(2048), 
	REPLICATION_RULES VARCHAR2(1024), 
	EXPIRED_AT DATE, 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	COMMENTS VARCHAR2(4000), 
	 CONSTRAINT SUBSCRIPTIONS_PK PRIMARY KEY(ID), 
	 CONSTRAINT SUBSCRIPTIONS_NAME_ACCOUNT_UQ UNIQUE (NAME, ACCOUNT), 
	 CONSTRAINT SUBSCRIPTIONS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS(ACCOUNT), 
	 CONSTRAINT SUBSCRIPTIONS_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT SUBSCRIPTIONS_RETROACTIVE_NN CHECK (RETROACTIVE IS NOT NULL), 
	 CONSTRAINT SUBSCRIPTIONS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT SUBSCRIPTIONS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT SUBSCRIPTIONS_STATE_CHK CHECK (state IN ('I', 'A', 'B', 'U', 'N')), 
	 CONSTRAINT SUBSCRIPTIONS_RETROACTIVE_CHK CHECK (retroactive IN (0, 1)) 
   ) PCTFREE 0;

  CREATE INDEX SUBSCRIPTIONS_STATE_IDX ON SUBSCRIPTIONS (STATE) COMPRESS 1;
--   CREATE INDEX SUBSCRIPTIONS_NAME_IDX ON SUBSCRIPTIONS (NAME); /* the SUBSCRIPTIONS_NAME_ACCOUNT_UQ is already prefixed with NAME*/



-- 11) =========================================  SUBSCRIPTIONS_HISTORY table ========================================= 

  CREATE TABLE SUBSCRIPTIONS_HISTORY 
   (	ID RAW(16), 
	NAME VARCHAR2(64), 
	FILTER VARCHAR2(2048), 
	REPLICATION_RULES VARCHAR2(1024), 
	POLICYID NUMBER(2,0), 
	STATE CHAR(1), 
	LAST_PROCESSED DATE, 
	ACCOUNT VARCHAR2(25), 
	LIFETIME DATE, 
	RETROACTIVE NUMBER(1,0), 
	EXPIRED_AT DATE, 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	COMMENTS VARCHAR2(4000), 
	CONSTRAINT SUBSCRIPTIONS_HISTORY_PK PRIMARY KEY (ID, UPDATED_AT) USING INDEX LOCAL, 
	CONSTRAINT SUBS_HISTORY_RETROACTIVE_CHK CHECK (retroactive IN (0, 1)), 
	CONSTRAINT SUBS_HISTORY_STATE_CHK CHECK (state IN ('I', 'A', 'B', 'U', 'N')) 
   ) 
PCTFREE 0 COMPRESS FOR OLTP
PARTITION BY RANGE (updated_at) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
(
PARTITION "DATA_BEFORE_01092013" VALUES LESS THAN (TO_DATE('01-09-2013', 'DD-MM-YYYY'))
);



-- 12) ==========================  DIDS table (Data IDentifierS) List-type partitioned and sub-partitioned ========================= 

  CREATE TABLE DIDS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	ACCOUNT VARCHAR2(25), 
	DID_TYPE CHAR(1), 
	IS_OPEN NUMBER(1,0), 
	MONOTONIC NUMBER(1,0) DEFAULT 0, 
	HIDDEN NUMBER(1,0) DEFAULT 0, 
	OBSOLETE NUMBER(1,0) DEFAULT 0, 
	COMPLETE NUMBER(1,0), 
	IS_NEW NUMBER(1,0) DEFAULT 1, 
	AVAILABILITY CHAR(1), 
	SUPPRESSED NUMBER(1,0) DEFAULT 0, 
	BYTES NUMBER(19,0), 
	LENGTH NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	EXPIRED_AT DATE, 
	DELETED_AT DATE, 
	EVENTS NUMBER(19,0), 
	GUID RAW(16), 
	PROJECT VARCHAR2(50), 
	DATATYPE VARCHAR2(50), 
	RUN_NUMBER NUMBER(10,0), 
	STREAM_NAME VARCHAR2(70), 
	PROD_STEP VARCHAR2(50), 
	VERSION VARCHAR2(50), 
	TASK_ID NUMBER(11,0), 
	PANDA_ID NUMBER(11,0), 
	CAMPAIGN VARCHAR2(50), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	LUMIBLOCKNR NUMBER(10,0), 
	PROVENANCE VARCHAR2(2), 
	PHYS_GROUP VARCHAR2(25), 
	TRANSIENT NUMBER(1,0), 
	ACCESSED_AT DATE, 
	CLOSED_AT DATE, 
	PURGE_REPLICAS NUMBER(1,0) DEFAULT 1, 
	EOL_AT DATE, 
	IS_ARCHIVE NUMBER(1,0), 
	CONSTITUENT NUMBER(1,0), 
	ACCESS_CNT NUMBER(10,0), 
	 CONSTRAINT DIDS_PK PRIMARY KEY (SCOPE, NAME) USING INDEX COMPRESS 1, 
	 CONSTRAINT DIDS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS(ACCOUNT), 
	 CONSTRAINT DIDS_SCOPE_FK FOREIGN KEY(SCOPE) REFERENCES SCOPES (SCOPE), 
	 CONSTRAINT DIDS_PHYS_GROUP_FK FOREIGN KEY(PHYS_GROUP) REFERENCES ACCOUNTS(ACCOUNT),
	 CONSTRAINT DIDS_MONOTONIC_NN CHECK (MONOTONIC IS NOT NULL), 
	 CONSTRAINT DIDS_OBSOLETE_NN CHECK (OBSOLETE IS NOT NULL), 
	 CONSTRAINT DIDS_SUPP_NN CHECK (SUPPRESSED IS NOT NULL), 
	 CONSTRAINT DIDS_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT DIDS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT DIDS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT DIDS_TYPE_CHK CHECK (did_type IN ('C', 'D', 'F')), 
	 CONSTRAINT DIDS_IS_OPEN_CHK CHECK (is_open IN (0, 1)), 
	 CONSTRAINT DIDS_MONOTONIC_CHK CHECK (monotonic IN (0, 1)), 
	 CONSTRAINT DIDS_HIDDEN_CHK CHECK (hidden IN (0, 1)), 
	 CONSTRAINT DIDS_OBSOLETE_CHK CHECK (obsolete IN (0, 1)), 
	 CONSTRAINT DIDS_COMPLETE_CHK CHECK (complete IN (0, 1)), 
	 CONSTRAINT DIDS_IS_NEW_CHK CHECK (is_new IN (0, 1)), 
	 CONSTRAINT DIDS_AVAILABILITY_CHK CHECK (availability IN ('A', 'D', 'L')), 
	 CONSTRAINT FILES_SUPP_CHK CHECK (suppressed IN (0, 1)), 
	 CONSTRAINT DIDS_TRANSIENT_CHK CHECK (transient in (0, 1)), 
	 CONSTRAINT DIDS_PURGE_REPLICAS_CHK CHECK (purge_replicas IN (0, 1)) , 
	 CONSTRAINT DIDS_PURGE_REPLICAS_NN CHECK (purge_replicas IS NOT NULL), 
	 CONSTRAINT DIDS_IS_ARCHIVE_CHK CHECK (IS_ARCHIVE IN (0, 1)) , 
	 CONSTRAINT DIDS_CONSTITUENT_CHK CHECK (CONSTITUENT IN (0, 1)) 
   )
PCTFREE 0 
PARTITION BY LIST(SCOPE)
SUBPARTITION BY LIST(DID_TYPE)
SUBPARTITION TEMPLATE
    (
    SUBPARTITION C VALUES('C'),
    SUBPARTITION D VALUES('D'),
    SUBPARTITION F VALUES('F')
    )
(
PARTITION INITIAL_PARTITION VALUES ('INITIAL_PARTITION')
);

  CREATE UNIQUE INDEX DIDS_GUID_IDX ON DIDS(GUID) ;
  CREATE INDEX DIDS_IS_NEW_IDX ON DIDS(IS_NEW) COMPRESS 1;
  CREATE INDEX DIDS_EXPIRED_AT_IDX ON DIDS(EXPIRED_AT) ;



-- 13) ===========================  DELETED_DIDS table (List-type partitioned + OLTP compression) ================================


  CREATE TABLE DELETED_DIDS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	ACCOUNT VARCHAR2(25), 
	DID_TYPE CHAR(1), 
	IS_OPEN NUMBER(1,0), 
	MONOTONIC NUMBER(1,0) DEFAULT 0, 
	HIDDEN NUMBER(1,0) DEFAULT 0, 
	OBSOLETE NUMBER(1,0) DEFAULT 0, 
	COMPLETE NUMBER(1,0), 
	IS_NEW NUMBER(1,0) DEFAULT 1, 
	AVAILABILITY CHAR(1), 
	SUPPRESSED NUMBER(1,0) DEFAULT 0, 
	BYTES NUMBER(19,0), 
	LENGTH NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	RULE_EVALUATION_REQUIRED DATE, 
	RULE_EVALUATION_ACTION CHAR(1), 
	EXPIRED_AT DATE, 
	DELETED_AT DATE, 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	EVENTS NUMBER(22,0), 
	GUID RAW(16), 
	PROJECT VARCHAR2(50), 
	DATATYPE VARCHAR2(50), 
	RUN_NUMBER NUMBER(10,0), 
	STREAM_NAME VARCHAR2(70), 
	PROD_STEP VARCHAR2(50), 
	VERSION VARCHAR2(50), 
	TASK_ID NUMBER(11,0), 
	PANDA_ID NUMBER(11,0), 
	CAMPAIGN VARCHAR2(50), 
	LUMIBLOCKNR NUMBER(10,0), 
	PROVENANCE VARCHAR2(2), 
	PHYS_GROUP VARCHAR2(25), 
	TRANSIENT NUMBER(1,0), 
	ACCESSED_AT DATE, 
	CLOSED_AT DATE, 
	PURGE_REPLICAS NUMBER(1,0), 
	EOL_AT DATE, 
	IS_ARCHIVE NUMBER(1,0), 
	CONSTITUENT NUMBER(1,0), 
	ACCESS_CNT NUMBER(10,0), 
	 CONSTRAINT DELETED_DIDS_PK PRIMARY KEY (SCOPE, NAME) USING INDEX LOCAL COMPRESS 1, 
	 CONSTRAINT DIDS_HIST_PURGE_RPLCS_CHK CHECK (purge_replicas IN (0, 1)), 
	 CONSTRAINT DIDS_HIST_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT DIDS_HIST_NAME_NN CHECK (NAME IS NOT NULL), 
	 CONSTRAINT DIDS_HIST_DID_TYPE_NN CHECK (DID_TYPE IS NOT NULL) 
   ) PCTFREE 0 
COMPRESS FOR OLTP 
PARTITION BY LIST (SCOPE) 
(
    PARTITION INITIAL_PARTITION VALUES ('INITIAL_PARTITION') 
);




-- 14) =============================  CONTENTS table (Index-Organised tables and List partitioned )======================

  CREATE TABLE CONTENTS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	CHILD_SCOPE VARCHAR2(25), 
	CHILD_NAME VARCHAR2(255), 
	DID_TYPE CHAR(1), 
	CHILD_TYPE CHAR(1), 
	LENGTH NUMBER(22,0), 
	BYTES NUMBER(22,0), 
	ADLER32 VARCHAR2(8), 
	MD5 VARCHAR2(32), 
	RULE_EVALUATION NUMBER(1,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	GUID RAW(16), 
	EVENTS NUMBER(19,0), 
	 CONSTRAINT CONTENTS_PK PRIMARY KEY (SCOPE, NAME, CHILD_SCOPE, CHILD_NAME), 
	 CONSTRAINT CONTENTS_ID_FK FOREIGN KEY(SCOPE, NAME)  REFERENCES DIDS(SCOPE, NAME), 
	 CONSTRAINT CONTENTS_CHILD_ID_FK FOREIGN KEY(CHILD_SCOPE, CHILD_NAME) REFERENCES DIDS(SCOPE, NAME), 
	 CONSTRAINT CONTENTS_DID_TYPE_NN CHECK (DID_TYPE        IS NOT NULL), 
	 CONSTRAINT CONTENTS_CHILD_TYPE_NN CHECK (CHILD_TYPE    IS NOT NULL), 
	 CONSTRAINT CONTENTS_CREATED_NN CHECK (CREATED_AT       IS NOT NULL), 
	 CONSTRAINT CONTENTS_UPDATED_NN CHECK (UPDATED_AT       IS NOT NULL), 
	 CONSTRAINT CONTENTS_TYPE_CHK CHECK (did_type             IN ('C', 'D', 'F')), 
	 CONSTRAINT CONTENTS_CHILD_IS_TYPE_CHK CHECK (child_type  IN ('C', 'D', 'F')), 
	 CONSTRAINT CONTENTS_RULE_EVAL_CHK CHECK (rule_evaluation IN (0, 1)) 
   ) ORGANIZATION INDEX COMPRESS 2 
PARTITION BY LIST (SCOPE)
(
    PARTITION INITIAL_PARTITION VALUES ('Initial_partition')
);


  CREATE INDEX CONTENTS_CHILD_SCOPE_NAME_IDX ON CONTENTS (CHILD_SCOPE, CHILD_NAME) COMPRESS 1;
  CREATE INDEX CONTENTS_RULE_EVAL_FB_IDX ON CONTENTS (CASE RULE_EVALUATION WHEN 1 THEN RULE_EVALUATION ELSE NULL END );



-- 15 ) =========================== CONTENTS_HISTORY table ( RANGE INTERVAL partitioned + OLTP compression ) =========================

  CREATE TABLE CONTENTS_HISTORY 
   (	SCOPE VARCHAR2(25) CONSTRAINT CONTENTS_HIST_SCOPE_NN NOT NULL, 
	NAME VARCHAR2(255) CONSTRAINT CONTENTS_NAME_NN NOT NULL, 
	CHILD_SCOPE VARCHAR2(25) CONSTRAINT CONTENTS_HIST_CHILD_SCOPE_NN NOT NULL, 
	CHILD_NAME VARCHAR2(255) CONSTRAINT CONTENTS_HIST_CHILD_NAME_NN NOT NULL, 
	DID_TYPE CHAR(1), 
	CHILD_TYPE CHAR(1), 
	LENGTH NUMBER(22,0), 
	BYTES NUMBER(22,0), 
	ADLER32 VARCHAR2(8), 
	MD5 VARCHAR2(32), 
	GUID RAW(16), 
	EVENTS NUMBER(19,0), 
	RULE_EVALUATION NUMBER(1,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	DID_CREATED_AT DATE, 
	DELETED_AT DATE, 
	 CONSTRAINT CONTENTS_HIST_DID_TYPE_NN CHECK (DID_TYPE IS NOT NULL), 
	 CONSTRAINT CONTENTS_HIST_CHILD_TYPE_NN CHECK (CHILD_TYPE IS NOT NULL), 
	 CONSTRAINT CONTENTS_HIST_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT CONTENTS_HIST_DID_CREATED_NN CHECK (DID_CREATED_AT IS NOT NULL), 
	 CONSTRAINT CONTENTS_HIST_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT CONTENTS_HIST_TYPE_CHK CHECK (did_type IN ('C', 'D', 'F')), 
	 CONSTRAINT CONTENTS_HIST_CHILD_ISTYPE_CHK CHECK (child_type IN ('C', 'D', 'F')), 
	 CONSTRAINT CONTENTS_HIST_RULE_EVAL_CHK CHECK (rule_evaluation IN (0, 1)) 
   )
PCTFREE 0 
COMPRESS FOR OLTP 
PARTITION BY RANGE(did_created_at) INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') )
( 
PARTITION DATA_BEFORE_01012018 VALUES LESS THAN (TO_DATE('01-01-2018', 'DD-MM-YYYY')) 
);


 CREATE INDEX CONTENTS_HISTORY_INDX ON CONTENTS_HISTORY (SCOPE, NAME) LOCAL COMPRESS 2 ;


-- 16) ========================================= REPLICAS table ( List-type partitioned ) ========================================= 

  CREATE TABLE REPLICAS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RSE_ID RAW(16), 
	BYTES NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	STATE CHAR(1), 
	LOCK_CNT NUMBER(5,0), 
	ACCESSED_AT DATE, 
	TOMBSTONE DATE, 
	PATH VARCHAR2(1024), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT REPLICAS_PK PRIMARY KEY (SCOPE, NAME, RSE_ID) USING INDEX LOCAL COMPRESS 1, 
	 CONSTRAINT REPLICAS_LFN_FK FOREIGN KEY(SCOPE, NAME)  REFERENCES DIDS (SCOPE, NAME),
 	 CONSTRAINT REPLICAS_RSE_ID_FK FOREIGN KEY(RSE_ID)  REFERENCES RSES (ID), 
	 CONSTRAINT REPLICAS_STATE_NN CHECK (STATE IS NOT NULL), 
	 CONSTRAINT REPLICAS_BYTES_NN CHECK (bytes IS NOT NULL), 
	 CONSTRAINT REPLICAS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT REPLICAS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT REPLICAS_LOCK_CNT_NN CHECK (LOCK_CNT IS NOT NULL), 
	 CONSTRAINT REPLICAS_STATE_CHK CHECK (state IN ('A', 'C', 'B', 'U', 'D', 'T')) 
   ) 
PCTFREE 0 
PARTITION BY LIST (SCOPE)
(
    PARTITION INITIAL_PARTITION VALUES ('Initial_partition')
);


  CREATE INDEX REPLICAS_TOMBSTONE_IDX ON REPLICAS (CASE  WHEN TOMBSTONE IS NOT NULL THEN RSE_ID END, TOMBSTONE) COMPRESS 1;
  CREATE INDEX REPLICAS_STATE_IDX ON REPLICAS (CASE WHEN STATE<>'A' THEN RSE_ID END ) COMPRESS 1;
  CREATE INDEX REPLICAS_PATH_IDX ON REPLICAS(PATH);



-- 17) ======================== BAD_REPLICAS table (Range Interval partitioned + OLTP compression) ========================= 

  CREATE TABLE BAD_REPLICAS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RSE_ID RAW(16), 
	REASON VARCHAR2(255), 
	STATE CHAR(1), 
	ACCOUNT VARCHAR2(25), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	BYTES NUMBER(19,0), 
	EXPIRES_AT DATE,
	 CONSTRAINT BAD_REPLICAS_PK PRIMARY KEY (SCOPE, NAME, RSE_ID, STATE, CREATED_AT) USING INDEX LOCAL COMPRESS 2, 
	 CONSTRAINT BAD_REPLICAS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS (ACCOUNT), 
	 CONSTRAINT BAD_REPLICAS_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT BAD_REPLICAS_NAME_NN CHECK (NAME IS NOT NULL), 
	 CONSTRAINT BAD_REPLICAS_RSE_ID_NN CHECK (RSE_ID IS NOT NULL), 
	 CONSTRAINT BAD_REPLICAS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT BAD_REPLICAS_UPDATED_AT CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT BAD_REPLICAS_STATE_CHK CHECK (state in ('B', 'D', 'L', 'R', 'S', 'T'))
   )
PCTFREE 0 COMPRESS FOR OLTP 
PARTITION BY RANGE(CREATED_AT)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION DATA_BEFORE_01012018 VALUES LESS THAN (TO_DATE('01-01-2018', 'DD-MM-YYYY'))
);


  CREATE INDEX BAD_REPLICAS_STATE_IDX ON BAD_REPLICAS (RSE_ID, STATE) COMPRESS 1;
  CREATE INDEX BAD_REPLICAS_ACCOUNT_IDX ON BAD_REPLICAS (ACCOUNT) COMPRESS 1;
  CREATE INDEX BAD_REPLICAS_EXPIRES_AT_IDX ON BAD_REPLICAS (expires_at);


-- 18 ) ========================================= REPLICAS_HISTORY table ========================================= 

  CREATE TABLE REPLICAS_HISTORY 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RSE_ID RAW(16), 
	BYTES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT REPLICAS_HIST_PK PRIMARY KEY (SCOPE, NAME, RSE_ID) USING INDEX COMPRESS 1, 
	 CONSTRAINT REPLICAS_HIST_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES (ID),
	 CONSTRAINT REPLICAS_HIST_BYTES_NN CHECK (BYTES IS NOT NULL), 
	 CONSTRAINT REPLICAS_HIST_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT REPLICAS_HIST_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL)
   ) 
PCTFREE 0 COMPRESS FOR OLTP 
PARTITION BY RANGE(CREATED_AT)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION DATA_BEFORE_01012018 VALUES LESS THAN (TO_DATE('01-01-2018', 'DD-MM-YYYY'))
);




-- 19 ) =========================================  RULES table ========================================= 

  CREATE TABLE RULES 
   (	ID RAW(16), 
	SUBSCRIPTION_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	DID_TYPE CHAR(1), 
	STATE CHAR(1), 
	RSE_EXPRESSION VARCHAR2(3000), 
	COPIES NUMBER(4,0) DEFAULT 1, 
	EXPIRES_AT DATE, 
	WEIGHT VARCHAR2(255), 
	LOCKED NUMBER(1,0) DEFAULT 0, 
	GROUPING CHAR(1), 
	ERROR VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	LOCKS_OK_CNT NUMBER(10,0) DEFAULT 0, 
	LOCKS_REPLICATING_CNT NUMBER(10,0) DEFAULT 0, 
	LOCKS_STUCK_CNT NUMBER(10,0) DEFAULT 0, 
	SOURCE_REPLICA_EXPRESSION VARCHAR2(255), 
	ACTIVITY VARCHAR2(50), 
	NOTIFICATION VARCHAR2(1), 
	STUCK_AT DATE, 
	PURGE_REPLICAS NUMBER(1,0), 
	IGNORE_AVAILABILITY NUMBER(1,0), 
	COMMENTS VARCHAR2(255), 
	IGNORE_ACCOUNT_LIMIT NUMBER(1,0), 
	CHILD_RULE_ID RAW(16), 
	PRIORITY NUMBER(1,0), 
	EOL_AT DATE, 
	SPLIT_CONTAINER NUMBER(1,0), 
	META VARCHAR2(4000), 
	 CONSTRAINT RULES_PK PRIMARY KEY(ID), 
	 CONSTRAINT RULES_SCOPE_ACC_NAME_CO_RSE_UQ UNIQUE (SCOPE, ACCOUNT, NAME, COPIES, RSE_EXPRESSION), 
	 CONSTRAINT RULES_SCOPE_NAME_FK FOREIGN KEY(SCOPE, NAME) REFERENCES DIDS(SCOPE, NAME), 
	 CONSTRAINT RULES_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS(ACCOUNT), 
	 CONSTRAINT RULES_SUBS_ID_FK FOREIGN KEY(SUBSCRIPTION_ID) REFERENCES SUBSCRIPTIONS(ID), 
	 CONSTRAINT RULES_CHILD_RULE_ID_FK FOREIGN KEY(CHILD_RULE_ID) REFERENCES RULES(ID), 
	 CONSTRAINT RULES_STATE_NN CHECK (STATE IS NOT NULL), 
	 CONSTRAINT RULES_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT RULES_NAME_NN CHECK (NAME IS NOT NULL), 
	 CONSTRAINT RULES_GROUPING_NN CHECK (GROUPING IS NOT NULL), 
	 CONSTRAINT RULES_COPIES_NN CHECK (COPIES IS NOT NULL), 
	 CONSTRAINT RULES_LOCKED_NN CHECK (LOCKED IS NOT NULL), 
	 CONSTRAINT RULES_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT RULES_LOCKS_OK_CNT_NN CHECK (LOCKS_OK_CNT IS NOT NULL), 
	 CONSTRAINT RULES_LOCKS_REPLICATING_CNT_NN CHECK (LOCKS_REPLICATING_CNT IS NOT NULL), 
	 CONSTRAINT RULES_LOCKS_STUCK_CNT_NN CHECK (LOCKS_STUCK_CNT IS NOT NULL), 
	 CONSTRAINT RULES_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT RULES_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT RULES_DID_TYPE_CHK CHECK (did_type IN ('C', 'D', 'F')), 
	 CONSTRAINT RULES_LOCKED_CHK CHECK (locked IN (0, 1)), 
	 CONSTRAINT RULES_GROUPING_CHK CHECK (grouping IN ('A', 'D', 'N')), 
	 CONSTRAINT RULES_NOTIFICATION_CHK CHECK (notification IN ('Y', 'N', 'C', 'P')), 
	 CONSTRAINT RULES_PURGE_REPLICAS_CHK CHECK (purge_replicas IN (0, 1)), 
	 CONSTRAINT RULES_IGNORE_AVAILABILITY_CHK CHECK (ignore_availability IN (0, 1)), 
	 CONSTRAINT RULES_IGNORE_ACCOUNT_LIMIT_CHK CHECK (ignore_account_limit IN (0, 1)) , 
	 CONSTRAINT RULES_STATE_CHK CHECK (state IN ('S', 'R', 'U', 'O', 'W', 'I')) , 
	 CONSTRAINT RULES_SPLIT_CONTAINER_CHK CHECK (split_container IN (0, 1)) 
   ) PCTFREE 3;


  CREATE INDEX RULES_SCOPE_NAME_IDX ON RULES (SCOPE, NAME) COMPRESS 2;
  CREATE INDEX RULES_STUCKSTATE_IDX ON RULES (CASE  WHEN STATE='S' THEN STATE ELSE NULL END ) COMPRESS 1;
  CREATE INDEX RULES_EXPIRES_AT_IDX ON RULES (EXPIRES_AT, NAME) COMPRESS 1;
  CREATE INDEX RULES_INJECTSTATE_IDX ON RULES (CASE  WHEN STATE='I' THEN STATE ELSE NULL END ) COMPRESS 1;
  CREATE INDEX RULES_APPROVALSTATE_IDX ON RULES (CASE  WHEN STATE='W' THEN STATE ELSE NULL END ) COMPRESS 1;
  CREATE INDEX RULES_CHILD_RULE_ID_IDX ON RULES (CHILD_RULE_ID) ;




-- 20) ========================================= RULES_HIST_RECENT table (Range Interval partitioned )========================================= 


  CREATE TABLE RULES_HIST_RECENT 
   (	HISTORY_ID RAW(16), 
	ID RAW(16), 
	SUBSCRIPTION_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	DID_TYPE CHAR(1), 
	STATE CHAR(1), 
	RSE_EXPRESSION VARCHAR2(3000), 
	COPIES NUMBER(4,0) DEFAULT 1, 
	EXPIRES_AT DATE, 
	WEIGHT VARCHAR2(255), 
	LOCKED NUMBER(1,0) DEFAULT 0, 
	GROUPING CHAR(1), 
	ERROR VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	SOURCE_REPLICA_EXPRESSION VARCHAR2(255), 
	ACTIVITY VARCHAR2(50), 
	LOCKS_OK_CNT NUMBER(10,0) DEFAULT 0, 
	LOCKS_REPLICATING_CNT NUMBER(10,0) DEFAULT 0, 
	LOCKS_STUCK_CNT NUMBER(10,0) DEFAULT 0, 
	NOTIFICATION CHAR(1), 
	STUCK_AT DATE, 
	PURGE_REPLICAS NUMBER(1,0) DEFAULT 0, 
	IGNORE_AVAILABILITY NUMBER(1,0) DEFAULT 0, 
	COMMENTS VARCHAR2(255), 
	IGNORE_ACCOUNT_LIMIT NUMBER(1,0), 
	CHILD_RULE_ID RAW(16), 
	PRIORITY NUMBER(1,0), 
	EOL_AT DATE, 
	SPLIT_CONTAINER NUMBER(1,0), 
	META VARCHAR2(4000)
   )
PCTFREE 0 
PARTITION BY RANGE(updated_at)
INTERVAL ( NUMTODSINTERVAL(7,'DAY') )
(
PARTITION DATA_BEFORE_01012015 VALUES LESS THAN (TO_DATE('01-01-2015', 'DD-MM-YYYY'))
);


  CREATE INDEX RULES_HIST_RECENT_ID_IDX ON RULES_HIST_RECENT (ID) LOCAL COMPRESS 1;
  CREATE INDEX RULES_HIST_RECENT_SC_NA_IDX ON RULES_HIST_RECENT (SCOPE, NAME) LOCAL COMPRESS 1;

-- 21) ========================================= RULES_HISTORY table (Range Interval partitioned) ========================================= 


  CREATE TABLE RULES_HISTORY 
   (	HISTORY_ID RAW(16), 
	ID RAW(16), 
	SUBSCRIPTION_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	DID_TYPE CHAR(1), 
	STATE CHAR(1), 
	RSE_EXPRESSION VARCHAR2(3000), 
	COPIES NUMBER(4,0) DEFAULT 1, 
	EXPIRES_AT DATE, 
	WEIGHT VARCHAR2(255), 
	LOCKED NUMBER(1,0) DEFAULT 0, 
	GROUPING CHAR(1), 
	ERROR VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	SOURCE_REPLICA_EXPRESSION VARCHAR2(255), 
	ACTIVITY VARCHAR2(50), 
	LOCKS_OK_CNT NUMBER(10,0) DEFAULT 0, 
	LOCKS_REPLICATING_CNT NUMBER(10,0) DEFAULT 0, 
	LOCKS_STUCK_CNT NUMBER(10,0) DEFAULT 0, 
	NOTIFICATION CHAR(1), 
	STUCK_AT DATE, 
	PURGE_REPLICAS NUMBER(1,0) DEFAULT 0, 
	IGNORE_AVAILABILITY NUMBER(1,0) DEFAULT 0, 
	COMMENTS VARCHAR2(255), 
	IGNORE_ACCOUNT_LIMIT NUMBER(1,0), 
	CHILD_RULE_ID RAW(16), 
	PRIORITY NUMBER(1,0), 
	EOL_AT DATE, 
	SPLIT_CONTAINER NUMBER(1,0), 
	META VARCHAR2(4000)
   ) 
PCTFREE 0 COMPRESS FOR OLTP 
PARTITION BY RANGE(updated_at)
INTERVAL ( NUMTOYMINTERVAL(1,'MONTH') )
( 
PARTITION "DATA_BEFORE_01012015" VALUES LESS THAN (TO_DATE('01-01-2015', 'DD-MM-YYYY')) 
);

 CREATE INDEX RULES_HISTORY_SCOPENAME_IDX ON RULES_HISTORY (SCOPE, NAME) LOCAL COMPRESS 2;






-- 22 ) ========================================= LOCKS table ( List-type partitioned ) ========================================= 

  CREATE TABLE LOCKS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RULE_ID RAW(16), 
	RSE_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	BYTES NUMBER(19,0), 
	STATE CHAR(1), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	REPAIR_CNT NUMBER(19,0), 
	 CONSTRAINT LOCKS_PK PRIMARY KEY (SCOPE, NAME, RULE_ID, RSE_ID) USING INDEX LOCAL COMPRESS 1, 
	 CONSTRAINT LOCKS_RULE_ID_FK FOREIGN KEY(RULE_ID)  REFERENCES RULES (ID), 
	 CONSTRAINT LOCKS_ACCOUNT_FK FOREIGN KEY(ACCOUNT)  REFERENCES ACCOUNTS (ACCOUNT),
	 CONSTRAINT LOCKS_STATE_NN CHECK (STATE IS NOT NULL), 
	 CONSTRAINT LOCKS_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT LOCKS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT LOCKS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT LOCKS_STATE_CHK CHECK (state IN ('S', 'R', 'O')) 
   ) 
PCTFREE 3 
PARTITION BY LIST(SCOPE)
(
    PARTITION INITIAL_PARTITION VALUES ('Initial_partition')
);


  CREATE INDEX LOCKS_RULE_ID_IDX ON LOCKS(RULE_ID) COMPRESS 1;



-- 23 ) =========================================  DATASET_LOCKS table ========================================= 

  CREATE TABLE DATASET_LOCKS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RULE_ID RAW(16), 
	RSE_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	STATE CHAR(1), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	LENGTH NUMBER(19,0), 
	BYTES NUMBER(19,0), 
	ACCESSED_AT DATE, 
	 CONSTRAINT DATASET_LOCKS_PK PRIMARY KEY (SCOPE, NAME, RULE_ID, RSE_ID) USING INDEX COMPRESS 1, 
	 CONSTRAINT DATASET_LOCKS_DID_FK FOREIGN KEY(SCOPE, NAME)  REFERENCES DIDS (SCOPE, NAME), 
	 CONSTRAINT DATASET_LOCKS_RULE_ID_FK FOREIGN KEY(RULE_ID)  REFERENCES RULES (ID), 
	 CONSTRAINT DATASET_LOCKS_ACCOUNT_FK FOREIGN KEY(ACCOUNT)  REFERENCES ACCOUNTS (ACCOUNT), 
	 CONSTRAINT DATASET_LOCKS_STATE_NN CHECK (STATE IS NOT NULL), 
	 CONSTRAINT DATASET_LOCKS_ACCOUNT_NN CHECK (ACCOUNT IS NOT NULL), 
	 CONSTRAINT DATASET_LOCKS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT DATASET_LOCKS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT DATASET_LOCKS_STATE_CHK CHECK (STATE IN ('S', 'R', 'O')) 
   ) PCTFREE 0; 

  CREATE INDEX DATASET_LOCKS_RULE_ID_IDX ON DATASET_LOCKS(RULE_ID) COMPRESS 1; 
  CREATE INDEX DATASET_LOCKS_RSE_ID_IDX ON DATASET_LOCKS(RSE_ID) COMPRESS 1; 


-- 24 ) ========================================= RSE_LIMITS table ========================================= 

  CREATE TABLE RSE_LIMITS 
   (	RSE_ID RAW(16), 
	NAME VARCHAR2(255), 
	VALUE NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT RSE_LIMITS_PK PRIMARY KEY (RSE_ID, NAME), 
	 CONSTRAINT RSE_LIMITS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_LIMITS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_LIMIT_RSE_ID_FK FOREIGN KEY(RSE_ID)  REFERENCES RSES (ID) 
   ) ORGANIZATION INDEX COMPRESS 1;



-- 25 ) ========================================= RSE_PROTOCOLS table ========================================= 

  CREATE TABLE RSE_PROTOCOLS 
   (	RSE_ID RAW(16), 
	SCHEME VARCHAR2(255), 
	HOSTNAME VARCHAR2(255), 
	PORT NUMBER(6,0), 
	PREFIX VARCHAR2(1024), 
	IMPL VARCHAR2(255) NOT NULL, 
	READ_LAN NUMBER(1,0), 
	WRITE_LAN NUMBER(1,0), 
	DELETE_LAN NUMBER(1,0), 
	READ_WAN NUMBER(1,0), 
	WRITE_WAN NUMBER(1,0), 
	DELETE_WAN NUMBER(1,0), 
	EXTENDED_ATTRIBUTES VARCHAR2(4000), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	THIRD_PARTY_COPY NUMBER(1,0), 
	THIRD_PARTY_COPY_READ NUMBER(1,0), 
	THIRD_PARTY_COPY_WRITE NUMBER(1,0), 
	 CONSTRAINT RSE_PROTOCOLS_PK PRIMARY KEY (RSE_ID, SCHEME, HOSTNAME, PORT), 
	 CONSTRAINT RSE_PROTOCOLS_IMPL_NN CHECK (IMPL IS NOT NULL), 
	 CONSTRAINT RSE_PROTOCOLS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_PROTOCOLS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_PROTOCOL_RSE_ID_FK FOREIGN KEY(RSE_ID)  REFERENCES RSES (ID) 
   ) ORGANIZATION INDEX COMPRESS 2 ;


-- 26 ) ========================================= REQUESTS table ========================================= 

  CREATE TABLE REQUESTS 
   (	ID RAW(16), 
	STATE CHAR(1), 
	REQUEST_TYPE CHAR(1), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	DID_TYPE CHAR(1) DEFAULT 'F', 
	DEST_RSE_ID RAW(16), 
	EXTERNAL_ID VARCHAR2(64), 
	RETRY_COUNT NUMBER(3,0) DEFAULT 0, 
	ATTRIBUTES VARCHAR2(4000), 
	ERR_MSG VARCHAR2(4000), 
	PREVIOUS_ATTEMPT_ID RAW(16), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	RULE_ID RAW(16), 
	BYTES NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	DEST_URL VARCHAR2(2048), 
	EXTERNAL_HOST VARCHAR2(256), 
	ACTIVITY VARCHAR2(50), 
	SUBMITTED_AT DATE, 
	TRANSFERRED_AT DATE, 
	SUBMITTER_ID NUMBER(10,0), 
	STARTED_AT DATE, 
	ESTIMATED_AT DATE, 
	STAGING_STARTED_AT DATE,
	STAGING_FINISHED_AT DATE,
	SOURCE_RSE_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	REQUESTED_AT DATE, 
	PRIORITY NUMBER(1,0), 
	ESTIMATED_STARTED_AT DATE, 
	ESTIMATED_TRANSFERRED_AT DATE,
    STAGING_STARTED_AT DATE,
    STAGING_FINISHED_AT DATE,
	 CONSTRAINT REQUESTS_PK PRIMARY KEY (ID), 
	 CONSTRAINT REQUESTS_DID_FK FOREIGN KEY(SCOPE, NAME) REFERENCES DIDS (SCOPE, NAME), 
	 CONSTRAINT REQUESTS_RSES_FK FOREIGN KEY(DEST_RSE_ID)  REFERENCES RSES (ID), 
	 CONSTRAINT REQUESTS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS (ACCOUNT), 
	 CONSTRAINT REQUESTS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT REQUESTS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT REQUESTS_RSE_ID_NN CHECK (dest_rse_id IS NOT NULL), 
	 CONSTRAINT REQUESTS_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT REQUESTS_NAME_NN CHECK (NAME IS NOT NULL), 
	 CONSTRAINT REQUESTS_TYPE_CHK CHECK (request_type in ('U', 'D', 'T', 'I', 'O')), 
	 CONSTRAINT REQUESTS_STATE_CHK CHECK (state IN ('Q', 'G', 'S', 'D', 'F', 'L', 'N', 'O', 'A', 'U', 'W', 'M', 'P'))
   ) PCTFREE 3;



  CREATE INDEX REQUESTS_DEST_RSE_ID_IDX ON REQUESTS (DEST_RSE_ID) COMPRESS 1;
  CREATE INDEX REQUESTS_SCOPE_NAME_RSE_IDX ON REQUESTS (SCOPE, NAME, DEST_RSE_ID, REQUEST_TYPE) COMPRESS 1;
  CREATE INDEX REQUESTS_RULEID_IDX ON REQUESTS (RULE_ID) COMPRESS 1;
  CREATE INDEX REQUESTS_EXTERNALID_UQ ON REQUESTS (EXTERNAL_ID);
  CREATE INDEX REQUESTS_TYP_STA_UPD_IDX ON REQUESTS (REQUEST_TYPE, STATE, ACTIVITY) COMPRESS 3;



-- 27 ) ========================== REQUESTS_HISTORY table ( Range Interval partitioned + OLTP compression ) =========================

  CREATE TABLE REQUESTS_HISTORY  
   (	CREATED_AT DATE CONSTRAINT REQUESTS_HIST_CREATED_AT_NN NOT NULL, 
	UPDATED_AT DATE, 
	STATE CHAR(1), 
	REQUEST_TYPE CHAR(1), 
	DID_TYPE CHAR(1), 
	EXTERNAL_ID VARCHAR2(64), 
	SCOPE VARCHAR2(25) CONSTRAINT REQUESTS_HIST_SCOPE_NN NOT NULL, 
	NAME VARCHAR2(255) CONSTRAINT REQUESTS_HIST_NAME_NN NOT NULL, 
	DEST_RSE_ID RAW(16), 
	ID RAW(16), 
	PREVIOUS_ATTEMPT_ID RAW(16), 
	RETRY_COUNT NUMBER(3,0), 
	ATTRIBUTES VARCHAR2(4000), 
	ERR_MSG VARCHAR2(4000), 
	RULE_ID RAW(16), 
	BYTES NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	DEST_URL VARCHAR2(2048), 
	EXTERNAL_HOST VARCHAR2(256), 
	ACTIVITY VARCHAR2(50), 
	SUBMITTED_AT DATE, 
	TRANSFERRED_AT DATE, 
	SUBMITTER_ID NUMBER(10,0), 
	STARTED_AT DATE, 
	ESTIMATED_AT DATE, 
	STAGING_STARTED_AT DATE,
	STAGING_FINISHED_AT DATE,
	SOURCE_RSE_ID RAW(16), 
	ACCOUNT VARCHAR2(25), 
	REQUESTED_AT DATE, 
	PRIORITY NUMBER(1,0), 
	ESTIMATED_STARTED_AT DATE, 
	ESTIMATED_TRANSFERRED_AT DATE,
    STAGING_STARTED_AT DATE,
    STAGING_FINISHED_AT DATE
   )
PCTFREE 0 
COMPRESS FOR OLTP
PARTITION BY RANGE(CREATED_AT)
INTERVAL ( NUMTODSINTERVAL(1,'DAY') )
(
PARTITION "DATA_BEFORE_01102013" VALUES LESS THAN (TO_DATE('01-10-2013', 'DD-MM-YYYY'))
);


CREATE INDEX REQ_HIST_SCOPE_NAME_RSE_IDX ON REQUESTS_HISTORY(SCOPE, NAME, DEST_RSE_ID) LOCAL COMPRESS 1;


-- 28 ) ========================================= ACCOUNT_USAGE table ========================================= 

  CREATE TABLE ACCOUNT_USAGE 
   (	ACCOUNT VARCHAR2(25), 
	RSE_ID RAW(16), 
	FILES NUMBER(19,0), 
	BYTES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ACCOUNT_USAGE_PK PRIMARY KEY (ACCOUNT, RSE_ID), 
	 CONSTRAINT ACCOUNT_USAGE_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNT_USAGE_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT ACCOUNT_USAGE_ACCOUNT_FK FOREIGN KEY(ACCOUNT)  REFERENCES ACCOUNTS(ACCOUNT), 
	 CONSTRAINT ACCOUNT_USAGE_RSES_ID_FK FOREIGN KEY(RSE_ID)  REFERENCES RSES(ID) 
   ) ORGANIZATION INDEX COMPRESS 1 ;


-- 29 ) ========================================= ACCOUNT_USAGE_HISTORY table ========================================= 

  CREATE TABLE ACCOUNT_USAGE_HISTORY 
   (	ACCOUNT VARCHAR2(25), 
	RSE_ID RAW(16), 
	FILES NUMBER(19,0), 
	BYTES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ACCOUNT_USAGE_HISTORY_PK PRIMARY KEY (ACCOUNT, RSE_ID, UPDATED_AT) 
   ) ORGANIZATION INDEX COMPRESS 1 ;

-- 30 ) ========================================= RSE_USAGE table ========================================= 

  CREATE TABLE RSE_USAGE 
   (	RSE_ID RAW(16), 
	SOURCE VARCHAR2(255), 
	USED NUMBER(19,0), 
	FREE NUMBER(19,0), 
	FILES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT RSE_USAGE_PK PRIMARY KEY (RSE_ID, SOURCE), 
	 CONSTRAINT RSE_USAGE_RSE_ID_FK FOREIGN KEY(RSE_ID)  REFERENCES RSES (ID), 
	 CONSTRAINT RSE_USAGE_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_USAGE_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL)
   ) ORGANIZATION INDEX COMPRESS 1 ;



-- 31 ) =========================================  RSE_USAGE_HISTORY table ========================================= 

  CREATE TABLE RSE_USAGE_HISTORY 
   (	RSE_ID RAW(16), 
	SOURCE VARCHAR2(255), 
	USED NUMBER(19,0), 
	FREE NUMBER(19,0), 
	FILES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT RSE_USAGE_HISTORY_PK PRIMARY KEY (RSE_ID, SOURCE, UPDATED_AT) 
   ) ORGANIZATION INDEX COMPRESS 2;


-- 32 ) =========================================  UPDATED_DIDS table ========================================= 

  CREATE TABLE UPDATED_DIDS 
   (	ID RAW(16) NOT NULL, 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RULE_EVALUATION_ACTION CHAR(1), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT UPDATED_DIDS_PK PRIMARY KEY (ID), 
	 CONSTRAINT UPDATED_DIDS_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT UPDATED_DIDS_NAME_NN CHECK (NAME IS NOT NULL), 
	 CONSTRAINT UPDATED_DIDS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT UPDATED_DIDS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT UPDATED_DIDS_RULE_EVAL_ACT_CHK CHECK (rule_evaluation_action IN ('A', 'B', 'D')) 
   ) ORGANIZATION INDEX;

  CREATE INDEX UPDATED_DIDS_SCOPERULENAME_IDX ON UPDATED_DIDS (SCOPE, RULE_EVALUATION_ACTION, NAME) COMPRESS 2 ;


-- 33 ) ========================================= UPDATED_ACCOUNT_COUNTERS table ========================================= 

  CREATE TABLE UPDATED_ACCOUNT_COUNTERS 
   (	ID RAW(16) NOT NULL, 
	ACCOUNT VARCHAR2(25), 
	RSE_ID RAW(16), 
	FILES NUMBER(19,0), 
	BYTES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT UPDATED_ACCNT_CNTRS_PK PRIMARY KEY (ID), 
	 CONSTRAINT UPDATED_ACCNT_CNTRS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT UPDATED_ACCNT_CNTRS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT UPDATED_ACCNT_CNTRS_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES (ID), 
	 CONSTRAINT UPDATED_ACCNT_CNTRS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS (ACCOUNT) 
   ) ORGANIZATION INDEX ;

  CREATE INDEX UPDATED_ACCNT_CNTRS_RSE_IDX ON UPDATED_ACCOUNT_COUNTERS (ACCOUNT, RSE_ID) COMPRESS 1;



-- 34 ) ========================================= UPDATED_RSE_COUNTERS table ========================================= 

  CREATE TABLE UPDATED_RSE_COUNTERS 
   (	ID RAW(16), 
	RSE_ID RAW(16), 
	FILES NUMBER(19,0), 
	BYTES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT UPDATED_RSE_CNTRS_PK PRIMARY KEY (ID), 
	 CONSTRAINT UPDATED_RSE_CNTRS_RSE_FK FOREIGN KEY(RSE_ID) REFERENCES RSES (ID), 
	 CONSTRAINT UPDATED_RSE_CNTRS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT UPDATED_RSE_CNTRS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) ORGANIZATION INDEX ;

  CREATE INDEX UPDATED_RSE_CNTRS_RSE_IDX ON UPDATED_RSE_COUNTERS (RSE_ID)  ;


-- 35 ) =========================================  ALEMBIC_VERSION table ========================================= 

  CREATE TABLE ALEMBIC_VERSION 
   (	
	VERSION_NUM VARCHAR2(32) CONSTRAINT ALEMBIC_VERSION_NN NOT NULL 
   );


-- 36 ) ========================================= ACCOUNT_LIMITS table ========================================= 

  CREATE TABLE ACCOUNT_LIMITS 
   (	ACCOUNT VARCHAR2(25), 
	RSE_ID RAW(16), 
	BYTES NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ACCOUNT_LIMITS_PK PRIMARY KEY (ACCOUNT, RSE_ID), 
	 CONSTRAINT ACCOUNT_LIMITS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS(ACCOUNT), 
	 CONSTRAINT ACCOUNT_LIMITS_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES(ID),
	 CONSTRAINT ACCOUNT_LIMITS_CREATED_NN CHECK (created_at is not null), 
	 CONSTRAINT ACCOUNT_LIMITS_UPDATED_NN CHECK (updated_at is not null) 
   ) ORGANIZATION INDEX COMPRESS 1;


-- 37 ) ========================================= CONFIGS table ========================================= 

  CREATE TABLE CONFIGS 
   (	SECTION VARCHAR2(128), 
	OPT VARCHAR2(128), 
	VALUE VARCHAR2(4000), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT CONFIGS_PK PRIMARY KEY (SECTION, OPT) USING INDEX COMPRESS 1, 
	 CONSTRAINT CONFIGS_CREATED_NN CHECK (created_at is not null), 
	 CONSTRAINT CONFIGS_UPDATED_NN CHECK (updated_at is not null) 
   ) PCTFREE 0;


-- 38 ) ========================================= CONFIGS_HISTORY table ========================================= 

  CREATE TABLE CONFIGS_HISTORY 
   (	SECTION VARCHAR2(128), 
	OPT VARCHAR2(128), 
	VALUE VARCHAR2(4000), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT CONFIGS_HISTORY_PK PRIMARY KEY (SECTION, OPT, UPDATED_AT) USING INDEX COMPRESS 1
   ) PCTFREE 0 ;


-- 39 ) ========================================= MESSAGES table ========================================= 

  CREATE TABLE MESSAGES 
   (	ID RAW(16), 
	UPDATED_AT TIMESTAMP(6), 
	CREATED_AT TIMESTAMP(6), 
	EVENT_TYPE VARCHAR2(1024), 
	PAYLOAD VARCHAR2(4000), 
	PAYLOAD_NOLIMIT CLOB,
	SERVICES VARCHAR2(2048),
	 CONSTRAINT MESSAGES_PK PRIMARY KEY(ID),
	 CONSTRAINT MESSAGES_EVENT_TYPE_NN CHECK (EVENT_TYPE IS NOT NULL), 
	 CONSTRAINT MESSAGES_PAYLOAD_NN CHECK (PAYLOAD IS NOT NULL), 
	 CONSTRAINT MESSAGES_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT MESSAGES_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) PCTFREE 0
   LOB(payload_nolimit) STORE AS (DISABLE STORAGE IN ROW);



-- 40 ) =========================================  MESSAGES_HISTORY table ( RANGE INTERVAL partitioned ) ========================================= 

  CREATE TABLE MESSAGES_HISTORY 
   (	ID RAW(16), 
	UPDATED_AT TIMESTAMP(6), 
	CREATED_AT TIMESTAMP(6), 
	DELETED_AT TIMESTAMP(6), 
	EVENT_TYPE VARCHAR2(1024), 
	PAYLOAD VARCHAR2(4000),
	PAYLOAD_NOLIMIT CLOB,
	SERVICES VARCHAR2(2048)
   ) 
PCTFREE 0 
LOB(payload_nolimit) STORE AS (DISABLE STORAGE IN ROW) 
PARTITION BY RANGE(created_at) INTERVAL ( NUMTODSINTERVAL(1,'DAY') )
( 
PARTITION DATA_BEFORE_01052015 VALUES LESS THAN (TO_DATE('01-05-2015', 'DD-MM-YYYY')) 
);


 CREATE INDEX MESSAGES_HISTORY_ETYPE_INDX ON MESSAGES_HISTORY (EVENT_TYPE) LOCAL COMPRESS 1;


-- 41 ) ========================================= TOKENS table ========================================= 

  CREATE TABLE TOKENS 
   (	ACCOUNT VARCHAR2(25), 
	EXPIRED_AT DATE, 
	TOKEN VARCHAR2(3072), 
	IP VARCHAR2(39), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	IDENTITY VARCHAR2(2048),
    OIDC_SCOPE VARCHAR2(2048),
    AUDIENCE VARCHAR2(315),
    REFRESH_TOKEN VARCHAR2(315),
    REFRESH NUMBER(1,0),
    REFRESH_START DATE,
    REFRESH_EXPIRED_AT DATE,
    REFRESH_LIFETIME NUMBER(19,0),
	 CONSTRAINT TOKENS_EXPIRED_AT_NN CHECK (EXPIRED_AT IS NOT NULL), 
	 CONSTRAINT TOKENS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT TOKENS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT TOKENS_PK PRIMARY KEY (TOKEN), 
	 CONSTRAINT TOKENS_ACCOUNT_FK FOREIGN KEY(ACCOUNT) REFERENCES ACCOUNTS (ACCOUNT),
     CONSTRAINT TOKENS_REFRESH_CHK CHECK (REFRESH IN (0, 1)) 
   );

  CREATE INDEX TOKENS_ACCOUNT_EXPIRED_AT_IDX ON TOKENS (ACCOUNT, EXPIRED_AT) COMPRESS 1 ;



-- 42 ) ========================================= COLLECTION_REPLICAS table ========================================= 

  CREATE TABLE COLLECTION_REPLICAS 
   (	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RSE_ID RAW(16), 
	DID_TYPE CHAR(1), 
	BYTES NUMBER(19,0), 
	STATE CHAR(1), 
	ACCESSED_AT DATE, 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	AVAILABLE_REPLICAS_CNT NUMBER(19,0), 
	LENGTH NUMBER(19,0), 
	AVAILABLE_BYTES NUMBER(19,0), 
	 CONSTRAINT COLLECTION_REPLICAS_PK PRIMARY KEY (SCOPE, NAME, RSE_ID), 
	 CONSTRAINT COLLECTION_REPLICAS_LFN_FK FOREIGN KEY(SCOPE, NAME)  REFERENCES DIDS (SCOPE, NAME), 
	 CONSTRAINT COLLECTION_REPLICAS_RSE_ID_FK FOREIGN KEY(RSE_ID)  REFERENCES RSES (ID), 
	 CONSTRAINT COLLECTION_REPLICAS_TYPE_CHK CHECK (did_type IN ('C', 'D', 'F')), 
	 CONSTRAINT COLLECTION_REPLICAS_STATE_NN CHECK (STATE IS NOT NULL), 
	 CONSTRAINT COLLECTION_REPLICAS_BYTES_NN CHECK (bytes IS NOT NULL), 
	 CONSTRAINT COLLECTION_REPLICAS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT COLLECTION_REPLICAS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT COLLECTION_REPLICAS_STATE_CHK CHECK (state IN ('A', 'C', 'B', 'U', 'D', 'T')) 
   ) ORGANIZATION INDEX COMPRESS 1;

  CREATE INDEX COLLECTION_REPLICAS_RSE_ID_IDX ON COLLECTION_REPLICAS (RSE_ID) COMPRESS 1;

-- 43) ========================================= SOURCES table ========================================= 

  CREATE TABLE SOURCES 
   (	REQUEST_ID RAW(16), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	RSE_ID RAW(16), 
	DEST_RSE_ID RAW(16), 
	URL VARCHAR2(2048), 
	BYTES NUMBER(19,0), 
	RANKING NUMBER(7,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	IS_USING NUMBER(1,0), 
	 CONSTRAINT SOURCES_PK PRIMARY KEY (REQUEST_ID, SCOPE, NAME, RSE_ID)  USING INDEX COMPRESS 1, 
	 CONSTRAINT SOURCES_REQ_ID_FK FOREIGN KEY(REQUEST_ID)  REFERENCES REQUESTS (ID), 
	 CONSTRAINT SOURCES_REPLICAS_FK FOREIGN KEY(SCOPE, NAME, RSE_ID) REFERENCES REPLICAS (SCOPE, NAME, RSE_ID), 
	 CONSTRAINT SOURCES_RSES_FK FOREIGN KEY(RSE_ID) REFERENCES RSES (ID), 
	 CONSTRAINT SOURCES_DEST_RSES_FK FOREIGN KEY(DEST_RSE_ID) REFERENCES RSES (ID), 
	 CONSTRAINT SOURCES_REQUEST_ID_NN CHECK (REQUEST_ID IS NOT NULL), 
	 CONSTRAINT SOURCES_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT SOURCES_NAME_NN CHECK (NAME IS NOT NULL), 
	 CONSTRAINT SOURCES_RSE_ID_NN CHECK (RSE_ID IS NOT NULL), 
	 CONSTRAINT SOURCES_DEST_RSE_NN CHECK (DEST_RSE_ID IS NOT NULL), 
  	 CONSTRAINT SOURCES_BYTES_NN CHECK (BYTES IS NOT NULL),
	 CONSTRAINT SOURCES_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT SOURCES_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT SOURCES_IS_USING_CHK CHECK (is_using IN (0, 1))
   ) PCTFREE 0;


  CREATE INDEX SOURCES_SC_NM_DST_IDX ON SOURCES (SCOPE, RSE_ID, NAME) COMPRESS 2;
  CREATE INDEX SOURCES_SRC_DST_IDX ON SOURCES (RSE_ID, DEST_RSE_ID) COMPRESS 2;
  CREATE INDEX SOURCES_DEST_RSEID_IDX ON SOURCES (DEST_RSE_ID) COMPRESS 1;


-- 44 ) ========================================= SOURCES_HISTORY table ========================================= 
-- Exists in ADCR but has not been used! To be dropped ? 

  CREATE TABLE SOURCES_HISTORY 
   (	REQUEST_ID RAW(16) NOT NULL, 
	SCOPE VARCHAR2(25) NOT NULL, 
	NAME VARCHAR2(255) NOT NULL, 
	RSE_ID RAW(16) NOT NULL, 
	DEST_RSE_ID RAW(16) NOT NULL, 
	URL VARCHAR2(2048), 
	BYTES NUMBER(19,0), 
	RANKING NUMBER(7,0), 
	IS_USING NUMBER(1,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE
   )
PCTFREE 0  
COMPRESS FOR OLTP
PARTITION BY RANGE(CREATED_AT)
INTERVAL ( NUMTODSINTERVAL(1,'DAY') )
(
PARTITION DATA_BEFORE_01112015 VALUES LESS THAN (TO_DATE('01-11-2015', 'DD-MM-YYYY'))
);

  CREATE INDEX SOURCES_HIST_REQID_IDX ON SOURCES_HISTORY(REQUEST_ID) LOCAL COMPRESS 1;




-- 45 ) ========================================= AMICONVENTIONS table ========================================= 

  CREATE TABLE AMICONVENTIONS 
   (	PROJECT VARCHAR2(64) constraint AMICONVENTIONS_PROJ_NN NOT NULL, 
	CONVENTION VARCHAR2(1024) constraint AMICONVENTIONS_CONV_NN NOT NULL, 
	CREATIONDATE DATE DEFAULT sysdate constraint AMICONVENTIONS_CRDATE_NN NOT NULL, 
	MODIFIEDDATE DATE DEFAULT sysdate constraint AMICONVENTIONS_MODIFDATE_NN NOT NULL, 
	ENABLED NUMBER(1,0) DEFAULT 1 constraint AMICONVENTIONS_ENABLED_NN NOT NULL, 
	 CONSTRAINT AMICONVENTIONS_PK PRIMARY KEY (PROJECT) 
   ) ORGANIZATION INDEX;


-- 46 ) ========================================= NAMING_CONVENTIONS table ========================================= 

  CREATE TABLE NAMING_CONVENTIONS 
   (	SCOPE VARCHAR2(25), 
	REGEXP VARCHAR2(255), 
	CONVENTION_TYPE VARCHAR2(10), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT NAMING_CONVENTIONS_PK PRIMARY KEY (SCOPE), 
	 CONSTRAINT NAMING_CONVENTIONS_SCOPE_FK FOREIGN KEY(SCOPE)  REFERENCES SCOPES (SCOPE),
	 CONSTRAINT NAMING_CONVENTIONS_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT NAMING_CONVENTIONS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT NAMING_CONVENTIONS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT CVT_TYPE_CHK CHECK (convention_type IN ('ALL', 'DATASET', 'CONTAINER', 'COLLECTION', 'FILE')) 
   ) ORGANIZATION INDEX;


-- 47 ) ========================================= HEARTBEATS table ========================================= 


  CREATE TABLE HEARTBEATS 
   (	EXECUTABLE VARCHAR2(256), 
	READABLE VARCHAR2(4000), 
	HOSTNAME VARCHAR2(128), 
	PID NUMBER(10,0), 
	THREAD_ID NUMBER(16,0), 
	THREAD_NAME VARCHAR2(64), 
    PAYLOAD VARCHAR2(3000),
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT HEARTBEATS_PK PRIMARY KEY (EXECUTABLE, HOSTNAME, PID, THREAD_ID) USING INDEX COMPRESS 1, 
	 CONSTRAINT HEARTBEATS_EXECUTABLE_NN CHECK (EXECUTABLE IS NOT NULL), 
	 CONSTRAINT HEARTBEATS_READABLE_NN CHECK (READABLE IS NOT NULL), 
	 CONSTRAINT HEARTBEATS_HOSTNAME_NN CHECK (HOSTNAME IS NOT NULL), 
	 CONSTRAINT HEARTBEATS_PID_NN CHECK (PID IS NOT NULL), 
	 CONSTRAINT HEARTBEATS_THREAD_ID_NN CHECK (THREAD_ID IS NOT NULL), 
	 CONSTRAINT HEARTBEATS_THREAD_NAME_NN CHECK (THREAD_NAME IS NOT NULL) 
   ) PCTFREE 0;


-- 48 ) ========================================= DISTANCES table ========================================= 
/*

These constrain exist in GitHub, but not in ADCR setup. To be added? - YES 

CONSTRAINT DISTANCES_SRC_RSES_FK FOREIGN KEY(SRC_RSE_ID) REFERENCES RSES (ID),
CONSTRAINT DISTANCES_DEST_RSES_FK FOREIGN KEY(DEST_RSE_ID) REFERENCES RSES (ID),

*/

  CREATE TABLE DISTANCES 
   (	SRC_RSE_ID RAW(16), 
	DEST_RSE_ID RAW(16), 
	RANKING NUMBER(7,0), 
	AGIS_DISTANCE NUMBER(7,0), 
	GEOIP_DISTANCE NUMBER(7,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	ACTIVE NUMBER(7,0), 
	SUBMITTED NUMBER(7,0), 
	FINISHED NUMBER(7,0), 
	FAILED NUMBER(7,0), 
	TRANSFER_SPEED NUMBER(7,0), 
	PACKET_LOSS NUMBER(7,0), 
	LATENCY NUMBER(7,0), 
	MBPS_FILE NUMBER(7,0), 
	MBPS_LINK NUMBER(7,0), 
	QUEUED_TOTAL NUMBER(7,0), 
	DONE_1H NUMBER(7,0), 
	DONE_6H NUMBER(7,0), 
	 CONSTRAINT DISTANCES_PK PRIMARY KEY (SRC_RSE_ID, DEST_RSE_ID) USING INDEX COMPRESS 1,  
	 CONSTRAINT DISTANCES_SRC_RSES_FK FOREIGN KEY(SRC_RSE_ID) REFERENCES RSES(ID),
	 CONSTRAINT DISTANCES_DEST_RSES_FK FOREIGN KEY(DEST_RSE_ID) REFERENCES RSES(ID),
	 CONSTRAINT DISTANCES_SRC_RSE_ID_NN CHECK (SRC_RSE_ID IS NOT NULL), 
	 CONSTRAINT DISTANCES_DEST_RSE_ID_NN CHECK (DEST_RSE_ID IS NOT NULL), 
	 CONSTRAINT DISTANCES_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT DISTANCES_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) PCTFREE 0;

  CREATE INDEX DISTANCES_DEST_RSEID_IDX ON DISTANCES (DEST_RSE_ID) COMPRESS 1;
 


-- 49 ) ========================================= RSE_TRANSFER_LIMITS table ========================================= 

  CREATE TABLE RSE_TRANSFER_LIMITS 
   (	RSE_ID RAW(16), 
	ACTIVITY VARCHAR2(50), 
	RSE_EXPRESSION VARCHAR2(3000), 
	MAX_TRANSFERS NUMBER(19,0), 
	TRANSFERS NUMBER(19,0), 
	WAITINGS NUMBER(19,0),
        VOLUME NUMBER(19,0),
	UPDATED_AT DATE, 
	CREATED_AT DATE,
    DEADLINE NUMBER(19,0),
    STRATEGY VARCHAR2(25 CHAR),
    DIRECTION VARCHAR2(25 CHAR),
	 CONSTRAINT RSE_TRANSFER_LIMITS_PK PRIMARY KEY (RSE_ID, ACTIVITY), 
	 CONSTRAINT RSE_TRANSFER_LIMITS_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES (ID),
	 CONSTRAINT RSE_TRANSFER_LIMITS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT RSE_TRANSFER_LIMITS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) ORGANIZATION INDEX COMPRESS 1;


-- 50 ) ========================================= QUARANTINED_REPLICAS table ========================================= 

  CREATE TABLE QUARANTINED_REPLICAS 
   (	RSE_ID RAW(16), 
	PATH VARCHAR2(1024), 
	BYTES NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT QUARANTINED_REPLICAS_PK PRIMARY KEY (RSE_ID, PATH), 
	 CONSTRAINT QURD_REPLICAS_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES (ID), 
	 CONSTRAINT QURD_REPLICAS_RSE_ID_NN CHECK (RSE_ID IS NOT NULL), 
	 CONSTRAINT QURD_REPLICAS_PATH_NN CHECK (PATH IS NOT NULL), 
	 CONSTRAINT QURD_REPLICAS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT QURD_REPLICAS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) ORGANIZATION INDEX COMPRESS 1 ;


  CREATE UNIQUE INDEX QUARANTINED_REPLICAS_PATH_IDX ON QUARANTINED_REPLICAS (PATH, RSE_ID);

-- 51) ========================================= QUARANTINED_REPLICAS_HISTORY table ========================================= 

  CREATE TABLE QUARANTINED_REPLICAS_HISTORY 
   (	RSE_ID RAW(16), 
	PATH VARCHAR2(1024), 
	BYTES NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	DELETED_AT DATE, 
	 CONSTRAINT QURD_REPLICAS_HIST_RSE_ID_NN CHECK (RSE_ID IS NOT NULL), 
	 CONSTRAINT QURD_REPLICAS_HIST_PATH_NN CHECK (PATH IS NOT NULL), 
	 CONSTRAINT QURD_REPLICAS_HIST_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT QURD_REPLICAS_HIST_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL)  
   ) 
PCTFREE 0 
COMPRESS FOR OLTP 
PARTITION BY RANGE(created_at) 
INTERVAL ( NUMTOYMINTERVAL(3,'MONTH') ) 
( 
PARTITION DATA_BEFORE_01032016 VALUES LESS THAN (TO_DATE('01-03-2016', 'DD-MM-YYYY')) 
);


-- 52) ========================================= TMP_DIDS table ========================================= 

  CREATE TABLE TMP_DIDS 
   (	SCOPE VARCHAR2(25) , 
	NAME VARCHAR2(255) , 
	RSE_ID RAW(16), 
	PATH VARCHAR2(1024), 
	BYTES NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	ADLER32 VARCHAR2(8), 
	EXPIRED_AT DATE, 
	PARENT_SCOPE VARCHAR2(25), 
	PARENT_NAME VARCHAR2(255), 
	GUID RAW(16), 
	EVENTS NUMBER(19,0), 
	TASK_ID NUMBER(11,0), 
	PANDA_ID NUMBER(11,0), 
	OFFSET NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT TMP_DIDS_PK PRIMARY KEY (SCOPE, NAME) USING INDEX COMPRESS 1,  
	 CONSTRAINT TMP_DIDS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT TMP_DIDS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL), 
	 CONSTRAINT TMP_DIDS_SCOPE_NN CHECK (SCOPE IS NOT NULL), 
	 CONSTRAINT TMP_DIDS_NAME_NN CHECK (SCOPE IS NOT NULL)
   ) PCTFREE 0;

  CREATE INDEX TMP_DIDS_EXPIRED_AT_IDX ON TMP_DIDS (CASE WHEN EXPIRED_AT IS NOT NULL THEN RSE_ID END ) COMPRESS 1;


-- 53) ========================================= LIFETIME_EXCEPT table ========================================= 

  CREATE TABLE LIFETIME_EXCEPT 
   (	ID RAW(16), 
	SCOPE VARCHAR2(25), 
	NAME VARCHAR2(255), 
	DID_TYPE CHAR(1), 
	ACCOUNT VARCHAR2(25), 
	COMMENTS VARCHAR2(4000), 
	PATTERN VARCHAR2(255), 
	STATE CHAR(1), 
	UPDATED_AT DATE, 
	EXPIRES_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT LIFETIME_EXCEPT_DID_TYPE_NN CHECK (did_type IN ('C', 'D', 'F', 'Y', 'X', 'Z')), 
	 CONSTRAINT LIFETIME_EXCEPT_STATE_CHK CHECK (state IN ('A', 'R', 'W')) 
   ) PCTFREE 0;


-- 54 ) ========================================= ARCHIVE_CONTENTS table ========================================= 

  CREATE TABLE ARCHIVE_CONTENTS 
   (	CHILD_SCOPE VARCHAR2(25) constraint ARCH_CONTENTS_CHILD_SCOPE_NN NOT NULL, 
	CHILD_NAME VARCHAR2(255) constraint ARCH_CONTENTS_CHILD_NAME_NN NOT NULL, 
	SCOPE VARCHAR2(25) constraint ARCH_CONTENTS_SCOPE_NN NOT NULL, 
	NAME VARCHAR2(255) constraint ARCH_CONTENTS_NAME_NN NOT NULL, 
	BYTES NUMBER(19,0), 
	ADLER32 VARCHAR2(8), 
	OFFSET NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	GUID RAW(16), 
	LENGTH NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ARCH_CONTENTS_PK PRIMARY KEY (CHILD_SCOPE, CHILD_NAME, SCOPE, NAME), 
	 CONSTRAINT ARCH_CONTENTS_PARENT_FK FOREIGN KEY(SCOPE, NAME) REFERENCES DIDS (SCOPE, NAME), 
	 CONSTRAINT ARCH_CONTENTS_CHLD_FK FOREIGN KEY(CHILD_SCOPE, CHILD_NAME) REFERENCES DIDS (SCOPE, NAME), 
	 CONSTRAINT ARCH_CONTENTS_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT ARCH_CONTENTS_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL)
   ) ORGANIZATION INDEX COMPRESS 1;


  CREATE INDEX ARCH_CONT_SCOPE_NAME_IDX ON ARCHIVE_CONTENTS (SCOPE, NAME) COMPRESS 1;



-- 55 ) ========================================= ARCHIVE_CONTENTS_HISTORY table =================================== 

  CREATE TABLE ARCHIVE_CONTENTS_HISTORY 
   (	SCOPE VARCHAR2(25) constraint ARCH_CONT_HIST_SCOPE_NN NOT NULL,
	NAME VARCHAR2(255) constraint ARCH_CONT_HIST_NAME_NN NOT NULL, 
	CHILD_SCOPE VARCHAR2(25) constraint ARCH_CONT_HIST_CHILD_SCOPE_NN NOT NULL, 
	CHILD_NAME VARCHAR2(255) constraint ARCH_CONT_HIST_CHILD_NAME_NN NOT NULL, 
	BYTES NUMBER(19,0), 
	ADLER32 VARCHAR2(8), 
	OFFSET NUMBER(19,0), 
	MD5 VARCHAR2(32), 
	GUID RAW(16), 
	LENGTH NUMBER(19,0), 
	UPDATED_AT DATE, 
	CREATED_AT DATE, 
	 CONSTRAINT ARCH_CONT_HIST_CREATED_NN CHECK (CREATED_AT IS NOT NULL), 
	 CONSTRAINT ARCH_CONT_HIST_UPDATED_NN CHECK (UPDATED_AT IS NOT NULL) 
   ) 
PCTFREE 0 
COMPRESS FOR OLTP 
PARTITION BY RANGE (created_at) 
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
   PARTITION DATA_BEFORE_01012017 VALUES LESS THAN (TO_DATE('01-01-2017', 'DD-MM-YYYY')) 
);

CREATE INDEX ARCH_CONT_HIST_IDX ON ARCHIVE_CONTENTS_HISTORY (SCOPE, NAME) COMPRESS 2 LOCAL; 



-- 56 ) ======================== UPDATED_COL_REP table ( List partitioned table based on visual column ) ===================================
-- In order to comply with the existing Rucio SW keep all the columns in. The only difference is the virtual column and the partitioning based on it
-- Note: The table cannot be IOT as error "ORA-54008: expression column is not supported for an index organized table" is raised 

CREATE TABLE UPDATED_COL_REP 
(
    ID           RAW(16),
    SCOPE        VARCHAR2(25) constraint UPDATED_COLL_SCOPE_NN not null,
    NAME         VARCHAR2(255) constraint UPDATED_COLL_NAME_NN not null,
    DID_TYPE     CHAR(1),
    RSE_ID       RAW(16),
    UPDATED_AT   DATE,
    CREATED_AT   DATE,
    CONSTRAINT UPDATED_COLL_REP_PK PRIMARY KEY (ID),
    CONSTRAINT UPDATED_COLL_REP_TYPE_CHK CHECK (did_type IN ('C','D','F')),
    VIRT_SCOPE_GROUP VARCHAR2(6) GENERATED ALWAYS AS 
			(DECODE
				(
				SUBSTR(scope,1,2), 
				'mc', 'mc', 
				'da', 'data', 
				'pa', 'panda', 
				'us', 'user',
				'others'
				)
			) VIRTUAL 
) 
PCTFREE 5 
PARTITION BY LIST (VIRT_SCOPE_GROUP)
( 
PARTITION MC_PART VALUES ('mc'), 
PARTITION DATA_PART VALUES ('data'),
PARTITION PANDA_PART VALUES ('panda'),
PARTITION USER_PART VALUES ('user'),
PARTITION OTHERS_PART VALUES ('others') 
);


-- Index on "SCOPE", "NAME"
CREATE INDEX UPD_COLL_REP_SCOPE_NAME_IDX ON UPDATED_COL_REP (SCOPE, NAME) COMPRESS 1;



-- 57 ) ========================================= LOGGING_TABPARTITIONS table ========================================= 
-- Auxiliary table with logging information about the List-type table partitions creation

  CREATE TABLE LOGGING_TABPARTITIONS 
   (	TABLE_NAME VARCHAR2(30) CONSTRAINT TABPART_TABLE_NN NOT NULL, 
	PARTITION_NAME VARCHAR2(30) CONSTRAINT TABPART_PART_NN NOT NULL, 
	PARTITION_VALUE VARCHAR2(30) CONSTRAINT TABPART_VALUE_NN NOT NULL, 
	ACTION_TYPE VARCHAR2(10), 
	ACTION_DATE TIMESTAMP (0), 
	PART_MARKED_FOR_REMOVAL CHAR(1), 
	MARKED_FOR_REMOVAL_ON DATE, 
	REMOVED_ON DATE, 
	EXECUTED_SQL_STMT VARCHAR2(1000), 
	MESSAGE VARCHAR2(1000), 
	 CONSTRAINT PART_MARKED_FOR_REMOVAL_YN_CHK CHECK (PART_MARKED_FOR_REMOVAL in ( 'Y', 'N' )) 
   ) PCTFREE 0;



-- 58 ) =================================== BAD_PFNS ==================================================

CREATE TABLE bad_pfns 
(
   path VARCHAR2(2048),
   state CHAR(1),
   reason VARCHAR2(255),
   account VARCHAR2(25),
   expires_at DATE,
   updated_at DATE,
   created_at DATE,
   CONSTRAINT BAD_PFNS_PK PRIMARY KEY(path, state), 
   CONSTRAINT BAD_PFNS_ACCOUNT_FK FOREIGN KEY(account) REFERENCES ACCOUNTS(account),
   CONSTRAINT BAD_PFNS_STATE_CHK CHECK (state IN ('A', 'S', 'B', 'T'))
)
organization index;





-- =================================================================================================================
-- ======================================= ATLAS specific tables ===================================================
-- =================================================================================================================


-- 59 ) ========================================= RUCIO_ACCOUNTING_TAB table ========================================= 

  CREATE TABLE RUCIO_ACCOUNTING_TAB 
   (	CURRTIME DATE, 
	RSE VARCHAR2(255), 
	SCOPE VARCHAR2(25), 
	STREAM_NAME VARCHAR2(70), 
	DATATYPE VARCHAR2(50), 
	TOMBSTONE VARCHAR2(30), 
	HIDDEN NUMBER(1,0), 
	ACCOUNT VARCHAR2(25), 
	PROVENANCE VARCHAR2(5), 
	CAMPAIGN VARCHAR2(50), 
	PHYS_GROUP VARCHAR2(25), 
	PROD_STEP VARCHAR2(50), 
	GROUP_CNT NUMBER, 
	BYTES NUMBER, 
	CURR_SCOPE VARCHAR2(25), 
	TIER VARCHAR2(5), 
	SPACETOKEN VARCHAR2(255), 
	GRP_DATATYPE VARCHAR2(50), 
	SITE VARCHAR2(255)
   ); 

  CREATE INDEX ACCOUNTING_TAB_CURRTIME_IDX ON RUCIO_ACCOUNTING_TAB (CURRTIME) COMPRESS 1; 


-- 60 ) ============== RUCIO_ACCOUNTING_HIST_TAB table (Range Interval partitioned + Basic compression )===================

  CREATE TABLE RUCIO_ACCOUNTING_HIST_TAB 
   (	CURRTIME DATE, 
	RSE VARCHAR2(255), 
	SCOPE VARCHAR2(25), 
	STREAM_NAME VARCHAR2(70), 
	DATATYPE VARCHAR2(50), 
	TOMBSTONE VARCHAR2(30), 
	HIDDEN NUMBER(1,0), 
	ACCOUNT VARCHAR2(25), 
	PROVENANCE VARCHAR2(5), 
	CAMPAIGN VARCHAR2(50), 
	PHYS_GROUP VARCHAR2(25), 
	PROD_STEP VARCHAR2(50), 
	GROUP_CNT NUMBER, 
	BYTES NUMBER, 
	CURR_SCOPE VARCHAR2(25), 
	TIER VARCHAR2(5), 
	SPACETOKEN VARCHAR2(255), 
	GRP_DATATYPE VARCHAR2(50), 
	SITE VARCHAR2(255)
   )
PCTFREE 0 
COMPRESS BASIC 
PARTITION BY RANGE (CURRTIME) INTERVAL (NUMTOYMINTERVAL(2,'MONTH')) 
(
PARTITION DATA_BEFORE_01082016  VALUES LESS THAN (TO_DATE('01-08-2016', 'DD-MM-YYYY'))
);




-- 61 ) ================ RUCIO_ACCOUNTING_LOGICAL_BYTES table (Range Interval partitioned + OLTP compression) ================

  CREATE TABLE RUCIO_ACCOUNTING_LOGICAL_BYTES 
   (	CURRTIME DATE DEFAULT sysdate, 
	CURR_SCOPE VARCHAR2(25), 
	COMMON_SCOPE VARCHAR2(25), 
	STREAM_NAME VARCHAR2(70), 
	DATATYPE VARCHAR2(50), 
	HIDDEN NUMBER(1,0), 
	ACCOUNT VARCHAR2(25), 
	PROVENANCE VARCHAR2(5), 
	CAMPAIGN VARCHAR2(50), 
	PHYS_GROUP VARCHAR2(25), 
	PROD_STEP VARCHAR2(50), 
	GROUP_CNT NUMBER, 
	BYTES NUMBER
   ) 
PCTFREE 0 
COMPRESS for OLTP 
PARTITION BY RANGE (CURRTIME) INTERVAL (NUMTODSINTERVAL(7,'DAY')) 
(
PARTITION DATA_BEFORE_01092017  VALUES LESS THAN (TO_DATE('01-09-2017', 'DD-MM-YYYY')
) 
);

CREATE INDEX RUCIO_ACC_LOGICAL_BYTES_IDX ON RUCIO_ACCOUNTING_LOGICAL_BYTES (CURRTIME) LOCAL COMPRESS 1; 


-- 62 ) ================ ACCOUNT_GLOB_LIMITS table  ================

CREATE TABLE account_glob_limits (
    account VARCHAR2(25 CHAR), 
    rse_expression VARCHAR2(3000 CHAR), 
    bytes NUMBER(19), 
    created_at DATE, 
    updated_at DATE,
    CONSTRAINT "ACCOUNT_GLOB_LIMITS_PK" PRIMARY KEY (account, rse_expression),
    CONSTRAINT "ACCOUNT_GLOB_LIMITS_CREATED_NN" CHECK (created_at is not null),
    CONSTRAINT "ACCOUNT_GLOB_LIMITS_UPDATED_NN" CHECK (updated_at is not null),
    CONSTRAINT "ACCOUNT_GLOBAL_LIMITS_ACC_FK" FOREIGN KEY(account) REFERENCES accounts (account)
)
ORGANIZATION INDEX COMPRESS 1 
tablespace ATLAS_RUCIO_ATTRIBUTE_DATA01;


-- 63 ) ================ DIDS_FOLLOWED table  ================

CREATE TABLE dids_followed (
    scope VARCHAR2(25 CHAR), 
    name VARCHAR2(255 CHAR), 
    account VARCHAR2(25 CHAR), 
    did_type CHAR(1), 
    updated_at DATE, 
    created_at DATE, 
    CONSTRAINT "DIDS_FOLLOWED_TYPE_CHK" CHECK (did_type IN ('A', 'C', 'D', 'F', 'Y', 'X', 'Z')),
    CONSTRAINT "DIDS_FOLLOWED_PK" PRIMARY KEY (scope, name, account),
    CONSTRAINT "DIDS_FOLLOWED_SCOPE_NN" CHECK (scope is not null),
    CONSTRAINT "DIDS_FOLLOWED_NAME_NN" CHECK (name is not null),
    CONSTRAINT "DIDS_FOLLOWED_ACCOUNT_NN" CHECK (account is not null),
    CONSTRAINT "DIDS_FOLLOWED_DID_TYPE_NN" CHECK (did_type is not null),
    CONSTRAINT "DIDS_FOLLOWED_CREATED_NN" CHECK (created_at is not null),
    CONSTRAINT "DIDS_FOLLOWED_UPDATED_NN" CHECK (updated_at is not null),
    CONSTRAINT "DIDS_FOLLOWED_ACCOUNT_FK" FOREIGN KEY(account) REFERENCES accounts (account),
    CONSTRAINT "DIDS_FOLLOWED_SCOPE_NAME_FK" FOREIGN KEY(scope, name) REFERENCES dids (scope, name)
)
ORGANIZATION INDEX COMPRESS 1
tablespace ATLAS_RUCIO_TRANSIENT_DATA01;


-- 64 ) ================ DIDS_FOLLOWED_EVENTS table  ================

CREATE TABLE dids_followed_events (
    scope VARCHAR2(25 CHAR), 
    name VARCHAR2(255 CHAR), 
    account VARCHAR2(25 CHAR), 
    did_type CHAR(1), 
    updated_at DATE, 
    created_at DATE, 
    event_type VARCHAR2(1024 CHAR), 
    payload CLOB, 
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_TYPE_CHK" CHECK (did_type IN ('A', 'C', 'D', 'F', 'Y', 'X', 'Z')),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_PK" PRIMARY KEY (scope, name, account),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_SCOPE_NN" CHECK (scope is not null),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_NAME_NN" CHECK (name is not null),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_ACC_NN" CHECK (account is not null),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_TYPE_NN" CHECK (did_type is not null),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_CRE_NN" CHECK (created_at is not null),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_UPD_NN" CHECK (updated_at is not null),
    CONSTRAINT "DIDS_FOLLOWED_EVENTS_ACC_FK" FOREIGN KEY(account) REFERENCES accounts (account)
)
PCTFREE 3
tablespace ATLAS_RUCIO_TRANSIENT_DATA01;

CREATE INDEX "DIDS_FOLLOWED_EVENTS_ACC_IDX" ON dids_followed_events (account) COMPRESS 1 tablespace ATLAS_RUCIO_TRANSIENT_DATA01;

-- 65 ) ================ RSE_QOS_MAP table  ================

CREATE TABLE RSE_QOS_MAP (
    RSE_ID RAW(16),
    QOS_POLICY VARCHAR2(64),
    CONSTRAINT RSE_QOS_MAP_PK PRIMARY KEY (RSE_ID, QOS_POLICY),
    CONSTRAINT RSE_QOS_MAP_RSE_ID_FK FOREIGN KEY(RSE_ID) REFERENCES RSES(ID)
)
PCTFREE 0;

-- 66 ) ================ OAUTH_REQUESTS table  ================


CREATE TABLE OAUTH_REQUESTS (
    ACCOUNT VARCHAR2(25),
    STATE VARCHAR2(50),
    NONCE VARCHAR2(50),
    ACCESS_MSG VARCHAR2(2048),
    REDIRECT_MSG VARCHAR2(2048),
    REFRESH_LIFETIME NUMBER(19,0),
    IP VARCHAR2(39),
    EXPIRED_AT DATE,
    CREATED_AT DATE,
    UPDATED_AT DATE
);

ALTER TABLE oauth_requests ADD CONSTRAINT "OAUTH_REQUESTS_PK" PRIMARY KEY (state);
ALTER TABLE oauth_requests ADD CONSTRAINT "OAUTH_REQUESTS_EXPIRED_AT_NN" CHECK (expired_at is not null);
CREATE INDEX "OAUTH_REQUESTS_ACC_EXP_AT_IDX" ON oauth_requests (account, expired_at);
CREATE INDEX "OAUTH_REQUESTS_ACCESS_MSG_IDX" ON oauth_requests (access_msg);

-- 67 ) ================ VOS table  ================

CREATE TABLE VOS (
    VO VARCHAR2(3),
    DESCRIPTION VARCHAR2(255),
    EMAIL VARCHAR2(255),
    CREATED_AT DATE,
    UPDATED_AT DATE
);

ALTER TABLE vos ADD CONSTRAINT "VOS_PK" PRIMARY KEY (vo);