Automatic Data Management and Purging

Design New Table with 12 Months Partitioning without depend on year so
every new calendar month same partition will use next year as well
Keep history of TBLMEMPSUMMARY into newly design table Create
Procedure for TRUNCATE PARTITION which are older then 6 months
DBMS_SCHEDULER for automate the process for the next to last day of
every month Test the Process and Include with Product dbc

–create tablespaces for TBLEMPCDR

DEFINE db_datafile="&&dbf_storage_directory"
prompt &db_datafile

CREATE TABLESPACE tbs_usagecdr_1   DATAFILE '&&db_datafile/tbs_usagecdr_1.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_2   DATAFILE '&&db_datafile/tbs_usagecdr_2.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_3   DATAFILE '&&db_datafile/tbs_usagecdr_3.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_4   DATAFILE '&&db_datafile/tbs_usagecdr_4.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_5   DATAFILE '&&db_datafile/tbs_usagecdr_5.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_6   DATAFILE '&&db_datafile/tbs_usagecdr_6.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_7   DATAFILE '&&db_datafile/tbs_usagecdr_7.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_8   DATAFILE '&&db_datafile/tbs_usagecdr_8.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_9   DATAFILE '&&db_datafile/tbs_usagecdr_9.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_10  DATAFILE '&&db_datafile/tbs_usagecdr_10.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_11  DATAFILE '&&db_datafile/tbs_usagecdr_11.dbf' size 1M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_12  DATAFILE '&&db_datafile/tbs_usagecdr_12.dbf' size 1M AUTOEXTEND ON;

connect &&username/&&password

–Keep history of TBLMSESSIONUSAGESUMMARY without depend on year so every
–new calendar month same partition will use next year as well

CREATE TABLE TBLEMPCDR
(
    CREATE_DATE         TIMESTAMP,
        USERID              VARCHAR(50),
    MONITORINGKEY           VARCHAR(100)                                NOT NULL,
    USAGETIME               NUMBER(20,0)                DEFAULT 0       NOT NULL,
    UPLOADOCTETS            NUMBER(20,0)                DEFAULT 0       NOT NULL,
    DOWNLOADOCTETS          NUMBER(20,0)                DEFAULT 0       NOT NULL,
    TOTALOCTETS             NUMBER(20,0)                DEFAULT 0       NOT NULL,
    AGGREGATEKEY            VARCHAR(128),
    LASTUPDATETIME      TIMESTAMP,
    METERINGLEVEL           VARCHAR(50),
    SUBSCRIBERIDENTITY  VARCHAR(255),
        V_CREATE_DATE       NUMBER
        GENERATED ALWAYS AS
      (
        TO_NUMBER(TO_CHAR(CREATE_DATE,'MM'))
      ) VIRTUAL
)PARTITION BY LIST (V_CREATE_DATE)
(
 PARTITION p_1 VALUES (1)  tablespace tbs_usagecdr_1,
 PARTITION p_2 VALUES (2)  tablespace tbs_usagecdr_2,
 PARTITION p_3 VALUES (3)  tablespace tbs_usagecdr_3,
 PARTITION p_4 VALUES (4)  tablespace tbs_usagecdr_4,
 PARTITION p_5 VALUES (5)  tablespace tbs_usagecdr_5,
 PARTITION p_6 VALUES (6)  tablespace tbs_usagecdr_6,
 PARTITION p_7 VALUES (7)  tablespace tbs_usagecdr_7,
 PARTITION p_8 VALUES (8)  tablespace tbs_usagecdr_8,
 PARTITION p_9 VALUES (9)  tablespace tbs_usagecdr_9,
 PARTITION p_10 VALUES(10) tablespace  tbs_usagecdr_10,
 PARTITION p_11 VALUES(11) tablespace  tbs_usagecdr_11,
 PARTITION p_12 VALUES(12) tablespace  tbs_usagecdr_12
 );

–Create Procedure for TRUNCATE PARTITION which are older then 6 months

CREATE OR REPLACE PROCEDURE PROC_EMPUSAGECDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN

   SELECT 'p_'||TO_NUMBER(TO_CHAR(CREATE_DATE,'MM'))
   INTO p_name
   FROM TBLEMPCDR
   WHERE CREATE_DATE + 185  successfully completed');

        EXCEPTION
        WHEN NO_DATA_FOUND THEN
    NULL;
END;
/

— Run on the next to last day of every month:

BEGIN
  -- Job defined entirely by the CREATE JOB procedure.
  DBMS_SCHEDULER.create_job (
    job_name => 'JOB_EMPUSAGECDR_MAINTAINANCE',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN PROC_EMPUSAGECDR_MAINTAINANCE(); END;',
    START_DATE => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=-2;',
    end_date => NULL,
    enabled => TRUE,
    comments => 'Job defined entirely by the CREATE JOB procedure.');
END;
/

Happy OAM with good sleep 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s