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 ;
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
--------------------------------------------------------
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 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 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;
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;
God's Word for the day
Mourning for the dead lasts seven days,
But for the foolish or the ungodly
it lasts all the days of their lives.
Do not talk much with a senseless person
or visit an unintelligent person.
Stay clear of him or you may have trouble,
and be spattered when he shakes himself off.
Avoid him and you will find rest,
and you will never be wearied by his lack of sense.
What is heavier than lead?
And what is its name except "Fool"?
Sand, salt and a piece of iron
are easier to bear than a stupid person.
Sirach 22:12-15
Gospel teachings of Jesus
Again, the kingdom of heaven is like a merchant
in search of fine pearls;
On finding one of great value,
he went and sold all that he had and bought it.
Mathew 13:45
No comments:
Post a Comment