Archiving - A generic solution - Part 3

Create the required sequences as given below

-------------------------------------------------------- 5_ddl_cre_archive_sequences.sql --------------------------------------------------------

--------------------------------------------------------
--  DDL for Sequence ARCHIVE_LOG_RULE_ID
--------------------------------------------------------

CREATE SEQUENCE  ARCHIVE_LOG_RULE_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 100001 CACHE 200 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------
--  DDL for Sequence SEQ_ARCHIVE_BATCH_ID
--------------------------------------------------------

CREATE SEQUENCE  SEQ_ARCHIVE_BATCH_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 107801 CACHE 200 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------SS
--  DDL for Sequence SEQ_ARCHIVE_LOG_DETAILS_ID
--------------------------------------------------------

CREATE SEQUENCE  SEQ_ARCHIVE_LOG_DETAILS_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1151601 CACHE 200 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------
--  DDL for Sequence SEQ_ARCHIVE_LOG_EXCEPTIONS_ID
--------------------------------------------------------

CREATE SEQUENCE  SEQ_ARCHIVE_LOG_EXCEPTIONS_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 135801 CACHE 200 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------
--  DDL for Sequence SEQ_ARCHIVE_LOG_HEADER_ID
--------------------------------------------------------

CREATE SEQUENCE  SEQ_ARCHIVE_LOG_HEADER_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 108001 CACHE 200 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------
--  DDL for Sequence SEQ_ARCHIVE_LOG_ID
--------------------------------------------------------

CREATE SEQUENCE  SEQ_ARCHIVE_LOG_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 132001 CACHE 1000 NOORDER  CYCLE  NOKEEP  NOSCALE  GLOBAL ;
--------------------------------------------------------
--  DDL for Sequence SEQ_TEMP_ARCHIVE_ERROR_ID
--------------------------------------------------------

CREATE SEQUENCE  SEQ_TEMP_ARCHIVE_ERROR_ID  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 100001 CACHE 200 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;

--Rollback Sequence creation

DROP SEQUENCE HR_ARCHIVE.ARCHIVE_LOG_RULE_ID;
DROP SEQUENCE HR_ARCHIVE.SEQ_ARCHIVE_BATCH_ID;
DROP SEQUENCE HR_ARCHIVE.SEQ_ARCHIVE_LOG_DETAILS_ID;
DROP SEQUENCE HR_ARCHIVE.SEQ_ARCHIVE_LOG_EXCEPTIONS_ID;
DROP SEQUENCE HR_ARCHIVE.SEQ_ARCHIVE_LOG_HEADER_ID;
DROP SEQUENCE HR_ARCHIVE.SEQ_ARCHIVE_LOG_ID;
DROP SEQUENCE HR_ARCHIVE.SEQ_TEMP_ARCHIVE_ERROR_ID;

-------------------------------------------------------- -- DDL for Table ARCHIVE_CONFIG -------------------------------------------------------- 6_ddl_cre_archive_tables.sql

--------------------------------------------------------
--  DDL for Table ARCHIVE_CONFIG
--------------------------------------------------------

CREATE TABLE ARCHIVE_CONFIG 
   (	TABLE_NAME VARCHAR2(30), 
	RECORD_FILTER_QUERY VARCHAR2(4000), 
	ACTIVE_FLAG VARCHAR2(1), 
	CRTD_BY VARCHAR2(100) DEFAULT USER, 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100), 
	UPDTD_DT DATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table ARCHIVE_ERROR_LOG
--------------------------------------------------------

CREATE TABLE ARCHIVE_ERROR_LOG 
   (	NUM_ARCHIVE_LOG_ID NUMBER, 
	TXT_PROGRAM_NAME VARCHAR2(100), 
	TXT_TABLE_NAME VARCHAR2(30), 
	TXT_REMARKS VARCHAR2(4000), 
	TXT_SQLCODE VARCHAR2(20), 
	TXT_SQLERRM VARCHAR2(4000), 
	DAT_ARCHIVE_DATE DATE, 
	CRTD_BY VARCHAR2(100) DEFAULT USER, 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100), 
	UPDTD_DT DATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table ARCHIVE_LOG_EXCEPTIONS
--------------------------------------------------------

CREATE TABLE ARCHIVE_LOG_EXCEPTIONS 
   (	NUM_ARCHIVE_LOG_EXCEPTIONS_ID NUMBER(20,0), 
	NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0), 
	TXT_TABLE_NM VARCHAR2(100), 
	NUM_EXCEPTION_ERR_CODE NUMBER, 
	TXT_EXCEPTION_ERR_DESC VARCHAR2(4000), 
	TXT_REMARKS VARCHAR2(500), 
	CRTD_BY VARCHAR2(100) DEFAULT USER, 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100), 
	UPDTD_DT DATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table ARCHIVE_LOG_HEADER
--------------------------------------------------------

CREATE TABLE ARCHIVE_LOG_HEADER 
   (	NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0), 
	NUM_ARCHIVE_BATCH_ID NUMBER(20,0), 
	TXT_RULE_CD VARCHAR2(20), 
	TXT_TABLE_NAME VARCHAR2(100), 
	TXT_TBL_ARCHIVAL_STATUS VARCHAR2(20), 
	NUM_RECORDS_TO_ARCHIVE NUMBER(10,0), 
	NUM_RECORDS_ARCHIVED NUMBER(10,0), 
	NUM_RECORDS_FAILED_ARCHIVAL NUMBER(10,0), 
	NUM_RECORDS_PENDING_ARCHIVAL NUMBER(10,0), 
	CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	UPDTD_DT DATE DEFAULT SYSDATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table ARCHIVE_LOG_MAIN
--------------------------------------------------------

CREATE TABLE ARCHIVE_LOG_MAIN 
   (	NUM_ARCHIVE_BATCH_ID NUMBER(20,0), 
	DAT_ARCHIVE_DATE DATE, 
	TXT_ARCHIVAL_STATUS VARCHAR2(20), 
	CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	UPDTD_DT DATE DEFAULT SYSDATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table ARCHIVE_PK_DTLS
--------------------------------------------------------

CREATE TABLE ARCHIVE_PK_DTLS 
   (	NUM_ARCHIVE_BATCH_ID NUMBER(20,0), 
	NUM_ARCHIVE_LOG_HEADER_ID NUMBER(20,0), 
	TXT_TBL_NM VARCHAR2(50), 
	TXT_PK_COL_NMS VARCHAR2(500), 
	TXT_PK_COL_VALS VARCHAR2(1000), 
	TXT_PK_COL_DTYPS VARCHAR2(1000), 
	TXT_ARCH_STAT VARCHAR2(20), 
	CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	UPDTD_DT DATE DEFAULT SYSDATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table ARCHIVE_RULES
--------------------------------------------------------

CREATE TABLE ARCHIVE_RULES 
   (	TXT_RULE_CD VARCHAR2(20), 
	NUM_RULE_PRIORITY NUMBER(3,0), 
	TXT_RULE_DESC VARCHAR2(1000), 
	TXT_RULE_QUERY VARCHAR2(4000), 
	TXT_ACTIVE_FLG VARCHAR2(1) DEFAULT 'Y', 
	TXT_PRIMARY_TABLE VARCHAR2(100), 
	TXT_ARCHIVE_TABLE_LIST VARCHAR2(1000), 
	CRTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	CRTD_DT DATE DEFAULT SYSDATE, 
	UPDTD_BY VARCHAR2(100) DEFAULT 'SYSTEM', 
	UPDTD_DT DATE DEFAULT SYSDATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table EMPLOYEES
--------------------------------------------------------

CREATE TABLE EMPLOYEES 
   (	EMPLOYEE_ID NUMBER(6,0), 
	FIRST_NAME VARCHAR2(20), 
	LAST_NAME VARCHAR2(25), 
	EMAIL VARCHAR2(25), 
	PHONE_NUMBER VARCHAR2(20), 
	HIRE_DATE DATE, 
	JOB_ID VARCHAR2(10), 
	SALARY NUMBER(8,2), 
	COMMISSION_PCT NUMBER(2,2), 
	MANAGER_ID NUMBER(6,0), 
	DEPARTMENT_ID NUMBER(4,0), 
	ARCHIVE_LOG_HEADER_ID NUMBER(20,0), 
	ARCHIVE_DATE DATE
   ) TABLESPACE HR_ARCHIVE;
--------------------------------------------------------
--  DDL for Table JOB_HISTORY
--------------------------------------------------------

CREATE TABLE JOB_HISTORY 
   (	EMPLOYEE_ID NUMBER(6,0), 
	START_DATE DATE, 
	END_DATE DATE, 
	JOB_ID VARCHAR2(10), 
	DEPARTMENT_ID NUMBER(4,0), 
	ARCHIVE_DATE DATE, 
	ARCHIVE_LOG_HEADER_ID NUMBER(20,0)
   ) TABLESPACE HR_ARCHIVE;

---------------------------------------------------- DDL for Indexes on HR_ARCHIVE tables -------------------------------------------------------- 7_ddl_cre_archive_indexes.sql

--------- DDL for Index IDX_ARCHIVE_ERROR_LOG ---------
CREATE INDEX IDX_ARCHIVE_ERROR_LOG ON ARCHIVE_ERROR_LOG (TXT_TABLE_NAME, DAT_ARCHIVE_DATE) 
TABLESPACE INDX ;

--------- DDL for Index IDX_ARCHIVE_ERROR_LOG_1 ---------
CREATE INDEX IDX_ARCHIVE_ERROR_LOG_1 ON ARCHIVE_ERROR_LOG (TXT_PROGRAM_NAME) 
TABLESPACE INDX ;

------------- DDL for Index PK_ARCHIVE_BATCH_ID ---------
CREATE UNIQUE INDEX PK_ARCHIVE_BATCH_ID ON ARCHIVE_LOG_MAIN (NUM_ARCHIVE_BATCH_ID) 
TABLESPACE INDX;

------------ DDL for Index PK_ARCHIVE_LOG_HEADER_ID ---------
CREATE UNIQUE INDEX PK_ARCHIVE_LOG_HEADER_ID ON ARCHIVE_LOG_HEADER (NUM_ARCHIVE_LOG_HEADER_ID) 
TABLESPACE INDX;

------------ DDL for Index PK_ARCHIVE_LOG_ID ---------
CREATE UNIQUE INDEX PK_ARCHIVE_LOG_ID ON ARCHIVE_ERROR_LOG (NUM_ARCHIVE_LOG_ID) 
TABLESPACE INDX;

------------ DDL for Index PK_EXCEPTION_DETAILS_ID --------
CREATE UNIQUE INDEX PK_EXCEPTION_DETAILS_ID ON ARCHIVE_LOG_EXCEPTIONS (NUM_ARCHIVE_LOG_EXCEPTIONS_ID) 
TABLESPACE INDX;

------------ DDL for Index PK_RULE_CD ---------
CREATE UNIQUE INDEX PK_RULE_CD ON ARCHIVE_RULES (TXT_RULE_CD) TABLESPACE INDX;

--------------------------------------------------------
--  DDL for Index EMP_EMP_ID_PK
--------------------------------------------------------
CREATE UNIQUE INDEX EMP_EMP_ID_PK ON EMPLOYEES (EMPLOYEE_ID) TABLESPACE INDX;

--------------------------------------------------------
--  DDL for Index JHIST_EMP_ID_ST_DATE_PK
--------------------------------------------------------
CREATE UNIQUE INDEX JHIST_EMP_ID_ST_DATE_PK ON JOB_HISTORY (EMPLOYEE_ID, START_DATE) TABLESPACE INDX;

-------------------------------------------------------- -- Constraints for HR_ARCHIVE tables -------------------------------------------------------- 8_ddl_cre_archive_constraints.sql

------------ Constraints for Table ARCHIVE_ERROR_LOG ---------
ALTER TABLE ARCHIVE_ERROR_LOG ADD CONSTRAINT PK_ARCHIVE_LOG_ID PRIMARY KEY (NUM_ARCHIVE_LOG_ID)
USING INDEX TABLESPACE INDX ENABLE;

------------ Constraints for Table ARCHIVE_LOG_EXCEPTIONS ---------
ALTER TABLE ARCHIVE_LOG_EXCEPTIONS ADD CONSTRAINT PK_EXCEPTION_DETAILS_ID PRIMARY KEY (NUM_ARCHIVE_LOG_EXCEPTIONS_ID)
USING INDEX TABLESPACE INDX ENABLE;

------------ Constraints for Table ARCHIVE_LOG_HEADER ---------
ALTER TABLE ARCHIVE_LOG_HEADER ADD CONSTRAINT PK_ARCHIVE_LOG_HEADER_ID PRIMARY KEY (NUM_ARCHIVE_LOG_HEADER_ID)
USING INDEX TABLESPACE INDX ENABLE;

------------ Constraints for Table ARCHIVE_LOG_MAIN ---------
ALTER TABLE ARCHIVE_LOG_MAIN ADD CONSTRAINT PK_ARCHIVE_BATCH_ID PRIMARY KEY (NUM_ARCHIVE_BATCH_ID)
USING INDEX TABLESPACE INDX ENABLE;
--------------------------------------------------------
--  Constraints for Table EMPLOYEES
--------------------------------------------------------
ALTER TABLE EMPLOYEES MODIFY (LAST_NAME CONSTRAINT EMP_LAST_NAME_NN NOT NULL ENABLE);
ALTER TABLE EMPLOYEES MODIFY (EMAIL CONSTRAINT EMP_EMAIL_NN NOT NULL ENABLE) ;
ALTER TABLE EMPLOYEES MODIFY (HIRE_DATE CONSTRAINT EMP_HIRE_DATE_NN NOT NULL ENABLE);
ALTER TABLE EMPLOYEES MODIFY (JOB_ID CONSTRAINT EMP_JOB_NN NOT NULL ENABLE);
ALTER TABLE EMPLOYEES ADD CONSTRAINT EMP_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID)
USING INDEX EMP_EMP_ID_PK ENABLE;
--------------------------------------------------------
--  Constraints for Table JOB_HISTORY
--------------------------------------------------------
ALTER TABLE JOB_HISTORY MODIFY (EMPLOYEE_ID CONSTRAINT JHIST_EMPLOYEE_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY MODIFY (START_DATE CONSTRAINT JHIST_START_DATE_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY MODIFY (END_DATE CONSTRAINT JHIST_END_DATE_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY MODIFY (JOB_ID CONSTRAINT JHIST_JOB_NN NOT NULL ENABLE);
ALTER TABLE JOB_HISTORY ADD CONSTRAINT JHIST_EMP_ID_ST_DATE_PK PRIMARY KEY (EMPLOYEE_ID, START_DATE)
USING INDEX JHIST_EMP_ID_ST_DATE_PK  ENABLE;

------------ Constraints for Table ARCHIVE_RULES ---------
ALTER TABLE ARCHIVE_RULES ADD CONSTRAINT PK_RULE_CD PRIMARY KEY (TXT_RULE_CD)
USING INDEX TABLESPACE INDX ENABLE;

Rollback script to drop archive tables.

DROP TABLE HR_ARCHIVE.ARCHIVE_CONFIG CASCADE CONSTRAINTS;
DROP TABLE HR_ARCHIVE.ARCHIVE_ERROR_LOG CASCADE CONSTRAINTS;
DROP TABLE HR_ARCHIVE.ARCHIVE_LOG_EXCEPTIONS CASCADE CONSTRAINTS;
DROP TABLE HR_ARCHIVE.ARCHIVE_LOG_HEADER CASCADE CONSTRAINTS;
DROP TABLE HR_ARCHIVE.ARCHIVE_LOG_MAIN CASCADE CONSTRAINTS;
DROP TABLE HR_ARCHIVE.ARCHIVE_RULES CASCADE CONSTRAINTS;