Rem drv: <create type="tables"/>
Rem
Rem $Header: purge_tables.sql 29-jun-2005.01:47:37 gsbhatia Exp $
Rem
Rem purge_tables.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      purge_tables.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    rpinnama    02/14/05 - Add columns to track whether a purge policy is 
Rem                           invoked or not 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    rpinnama    09/08/03 - rpinnama_purge_policies 
Rem    rpinnama    08/28/03 - Created
Rem

CREATE TABLE MGMT_PURGE_POLICY
(
    policy_name            VARCHAR2(64)    NOT NULL,
    policy_description     VARCHAR2(256)   DEFAULT NULL,
    policy_type            NUMBER          DEFAULT 1,
    rollup_proc_name       VARCHAR2(128)   DEFAULT NULL,
    purge_proc_name        VARCHAR2(128)   DEFAULT NULL,
    execution_group_name   VARCHAR2(64)    DEFAULT 'BASIC',
    policy_retention_hours NUMBER          DEFAULT 0,
    retention_group_name   VARCHAR2(64)    DEFAULT NULL,
    user_configurable      NUMBER          DEFAULT 0,
    is_enabled             NUMBER          DEFAULT 1
)
MONITORING;

COMMENT ON TABLE MGMT_PURGE_POLICY IS
    'This table holds the definitions for the purge policies.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.policy_name IS
    'the name of the policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.policy_description IS
    'the description of the policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.policy_type IS
    'the type of the policy. Possible policy types are
     1 - TARGET based policy
     2 - SYSTEM wide policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.rollup_proc_name IS
    'the rollup callback for the policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.purge_proc_name IS
    'the purge callback for the policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.policy_retention_hours IS
    'the retention hours that should be applied to this policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.retention_group_name IS
    'the retention group to which this policy belongs to'

COMMENT ON COLUMN MGMT_PURGE_POLICY.user_configurable IS
    'specifies whether this purge policy can be modified by the user.';

COMMENT ON COLUMN MGMT_PURGE_POLICY.is_enabled IS
    'specifies whether the purge policy is enabled.';


CREATE TABLE MGMT_PURGE_POLICY_GROUP
(
    group_name            VARCHAR2(64)    NOT NULL,
    group_description     VARCHAR2(256)   DEFAULT ' ',
    group_retention_hours NUMBER          DEFAULT 0
)
MONITORING;

COMMENT ON TABLE MGMT_PURGE_POLICY_GROUP IS
    'This table holds the retention details for a policy group.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_GROUP.group_name IS
    'the name of the purge policy retention group.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_GROUP.group_description IS
    'the description of the purge policy retention group.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_GROUP.group_retention_hours IS
    'the number of hours of data to be retained for this purge policy retention group.';


rem   The MGMT_PURGE_POLICY_TARGET_STATE table holds a details of
rem   till what time the rollup can happen and up to what time the rollup
rem   has already been done.

CREATE TABLE MGMT_PURGE_POLICY_TARGET_STATE
(
    policy_name            VARCHAR2(64)    NOT NULL,
    target_guid            RAW(16)         NOT NULL,
    can_rollup_upto_time   DATE            NOT NULL,
    rolledup_upto_time     DATE            NOT NULL,
    target_retention_hours NUMBER          DEFAULT 0
)
MONITORING;

COMMENT ON TABLE MGMT_PURGE_POLICY_TARGET_STATE IS
    'This table holds the details of till what time the rollup can 
     happen and up to what time the rollup has already been done.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_TARGET_STATE.policy_name IS
    'the policy name for the details.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_TARGET_STATE.target_guid IS
    'the target guid for the details.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_TARGET_STATE.can_rollup_upto_time IS
    'the time up to which rollup can be performed for the target and policy name.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_TARGET_STATE.rolledup_upto_time IS
    'the time up to which rollup has already been performed for the 
     target and policy.';

COMMENT ON COLUMN MGMT_PURGE_POLICY_TARGET_STATE.target_retention_hours IS
    'the hours up to which the data for this policy will be retained for 
     this target.';

@&EM_SQL_ROOT/core/v102010/purge/purge_schema_upgrade.sql