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

Materialized view

=======>Step1  for dropping the materialized view

drop materialized view MV_SUB_MASTER;

=======>Step2:  for dropping the exiting mv refresh interval

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.DAILY_REFRESH’);
END;
/

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.REFRESH’);
END;
/

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.REGULAR_REFRESH’);
END;
/

BEGIN
Dbms_Refresh.Destroy(name => ‘scott.HOURLY_REFRESH’);
END;
/

=======>Step3: for creating the mv

CREATE or replace MATERIALIZED VIEW MV_SUB_MASTER
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT WIMAX_ID,TEMPLATE_ID,PACKAGE_NAME,PAYTYPE,STATUS_CODE_V,EMAIL,
PHONE,START_DATE,EXPIRY_DATE,FIRST_NAME,BANDWIDTH,WIMAX_ID as CUI
FROM MV_SUB_MASTER@post2p;

=======>Step4: configure the refresh interval of mv

BEGIN
Dbms_Refresh.Make(
name => ‘scott.DAILY_REFRESH’,
list => ”,
next_date => SYSDATE,
interval => ‘SYSDATE+1260/1440’,
implicit_destroy => FALSE,
lax => FALSE,
job => 0,
rollback_seg => NULL,
push_deferred_rpc => TRUE,
refresh_after_errors => TRUE,
purge_option => NULL,
parallelism => NULL,
heap_size => NULL);
END;
/

SELECT TO_CHAR(sysdate+360/1440, ‘dd-mm-yy hh24:mi:ss’) from dual;
SELECT TO_CHAR(SYSDATE+1260/1440, ‘dd-mm-yy hh24:mi:ss’) from dual;
SELECT TO_CHAR(trunc(SYSDATE+1)+1260/1440, ‘dd-mm-yy hh24:mi:ss’) from dual;
select TO_CHAR(SYSDATE, ‘dd-mm-yy hh24:mi:ss’) from dual;
SELECT TO_CHAR(SYSDATE+1, ‘dd-mm-yy hh24:mi:ss’) from dual;

=======>Step5:

BEGIN
Dbms_Refresh.Add(
name => ‘scott.DAILY_REFRESH’,
list => ‘scott.MV_SUB_MASTER’,
lax => TRUE);
END;
/
select failures from user_jobs;

=======>Step6:  Create Index on WIMAX_ID

CREATE INDEX IDX_WIMAXID ON MV_SUB_MASTER(WIMAX_ID);

select * from sys.indexes where object_id = (select object_id from sys.objects where name = ‘IDX_WIMAXID’);

==============================================================================================================
============================ NEW WAY OF CREATING MV AND UPDATE IT AUTOMATICALLY ==============================

CREATE MATERIALIZED VIEW MV_SUB_MASTER
refresh
complete
next sysdate+360/1440
AS
SELECT WIMAX_ID,TEMPLATE_ID,PACKAGE_NAME,PAYTYPE,STATUS_CODE_V,EMAIL,PHONE,START_DATE,EXPIRY_DATE,FIRST_NAME,BANDWIDTH,WIMAX_ID as CUI FROM MV_SUB_MASTER@post2p;

select count(*) from mv_sub_master;
select count(*) from mv_sub_master@post2p;