text: -- 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(4000), 
  	REPLICATION_RULES VARCHAR2(4000), 
  	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(4000), 
  	REPLICATION_RULES VARCHAR2(4000), 
  	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);
    CREATE INDEX REPLICAS_RSE_ID_IDX ON REPLICAS (RSE_ID);
  
  
  
  -- 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,
  	TRANSFERTOOL VARCHAR2(64),
  	 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;
    CREATE INDEX REQUESTS_TYP_STA_TRA_ACT_IDX ON REQUESTS (REQUEST_TYPE, STATE, TRANSFERTOOL, ACTIVITY);
  
  
  
  -- 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,
      TRANSFERTOOL VARCHAR2(64)
     )
  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 
     ) 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);
  
    CREATE INDEX MESSAGES_SERVICES_IDX ON MESSAGES (SERVICES, EVENT_TYPE) COMPRESS 1 ;
  
  
  -- 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')) 
  );
  
  
  
  -- 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),
  	REP_TYPE NUMBER(3)
     ); 
  
    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),
  	REP_TYPE NUMBER(3)
     )
  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);
  
  -- 68 ) ================ VIRTUAL PLACEMENTS table  ================
  
  CREATE TABLE VIRTUAL_PLACEMENTS (
      scope VARCHAR2(25 CHAR), 
      name VARCHAR2(255 CHAR), 
      PLACEMENTS CLOB,
      CREATED_AT DATE,
      UPDATED_AT DATE,
  	CONSTRAINT VP_PK PRIMARY KEY (scope, name),
  	CONSTRAINT VP_FK FOREIGN KEY (scope, name) REFERENCES dids (scope, name) 
  );
  
  -- 69 ) ================ TRANSFER HOPS table  ================
  
  CREATE TABLE TRANSFER_HOPS (
      request_id RAW(16),
      next_hop_request_id RAW(16),
      initial_request_id RAW(16),
      created_at DATE,
      updated_at DATE,
      CONSTRAINT TRANSFER_HOPS_CREATED_NN CHECK (created_at IS NOT NULL),
      CONSTRAINT TRANSFER_HOPS_UPDATED_NN CHECK (updated_at IS NOT NULL),
      CONSTRAINT TRANSFER_HOPS_PK PRIMARY KEY (request_id, next_hop_request_id, initial_request_id),
      CONSTRAINT TRANSFER_HOPS_INIT_REQ_ID_FK FOREIGN KEY (initial_request_id) REFERENCES REQUESTS(id),
      CONSTRAINT TRANSFER_HOPS_NH_REQ_ID_FK FOREIGN KEY (next_hop_request_id) REFERENCES REQUESTS(id),
  );
  
  CREATE INDEX TRANSFER_HOPS_INITIAL_REQ_IDX ON TRANSFER_HOPS (initial_request_id);
  CREATE INDEX TRANSFER_HOPS_NH_REQ_IDX ON TRANSFER_HOPS (next_hop_request_id);
  