
CREATE TABLE PARTITION_META_DATA
(
    META_DATA_ID                         NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    TABLE_OWNER                          VARCHAR2(32) NOT NULL, 
    TABLE_NAME                             VARCHAR2(64) NOT NULL, 
    TABLE_ALIAS                             VARCHAR2(8) NOT NULL, 
    VIEW_NAME                               VARCHAR2(64) NOT NULL, 
    PARTITION_COLUMN_NAME         VARCHAR2(64) NOT NULL, 
    PRIMARYKEY_COLUMN_NAME      VARCHAR2(64) NOT NULL, 
    PARTITION_TYPE                        CHAR(1),                         /*  D-DAILY, W-Weekly, M-MONTHLY, Y-YEARLY */
    NUM_PRECREATE_PARTITIONS    NUMBER(3) DEFAULT 3,
    NUM_RETAIN_PARTITIONS          NUMBER(3) DEFAULT 90,
    AUTO_MANAGE_PARTITIONS       CHAR(1)   DEFAULT 'Y',     /* Y-Yes, N-No */
    DROP_OLD_PARTITIONS             CHAR(1)   DEFAULT 'N',     /* Y-Yes, N-No */
    ENABLE_LOGGING                      CHAR(1)   DEFAULT 'Y'      /* Y-Yes, N-No */
    , CONSTRAINT PK_PARTMETADATA     PRIMARY KEY (META_DATA_ID) ENABLE
    , CONSTRAINT CK1_PMD_AUTOPART  CHECK (AUTO_MANAGE_PARTITIONS IN ('Y', 'N')) ENABLE
    , CONSTRAINT CK2_PMD_DROPPART  CHECK (DROP_OLD_PARTITIONS IN ('Y', 'N')) ENABLE
    , CONSTRAINT CK3_PMD_DEBUG        CHECK (ENABLE_LOGGING IN ('Y', 'N')) ENABLE
    , CONSTRAINT CK4_PMD_TYPE           CHECK (PARTITION_TYPE IN ('D', 'W', 'M', 'Y')) ENABLE
);
 



CREATE TABLE PARTITION_TABLE_DATA
(
    META_DATA_ID          NUMBER NOT NULL,
    PARTITION_TABLE_NAME  VARCHAR2(64) NOT NULL, 
    PARTITION_DATE_VALUE  DATE, 
    CRT_DT                      DATE, 
    STATUS                      CHAR(1)  /* A-Active, D-Deleted  */
);



CREATE TABLE PARTITION_LOG
(
    LOG_ID                  NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
    LOG_TYPE              VARCHAR2(8) NOT NULL,    /* DEBUG, ERROR */
    META_DATA_ID       NUMBER,
    OBJECT_NAME         VARCHAR2(64),
    LOG_MESSAGE        VARCHAR2(4000),
    LOG_DATE               DATE DEFAULT SYSDATE
);
