–create tablespaces for TBLMSESSIONUSAGECDR
DEFINE db_datafile=”&&dbf_storage_directory”
prompt &db_datafile
CREATE TABLESPACE tbs_usagecdr_1 DATAFILE ‘/ORADATA2/tbs_usagecdr_1.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_2 DATAFILE ‘/ORADATA2/tbs_usagecdr_2.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_3 DATAFILE ‘/ORADATA2/tbs_usagecdr_3.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_4 DATAFILE ‘/ORADATA2/tbs_usagecdr_4.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_5 DATAFILE ‘/ORADATA2/tbs_usagecdr_5.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_6 DATAFILE ‘/ORADATA2/tbs_usagecdr_6.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_7 DATAFILE ‘/ORADATA2/tbs_usagecdr_7.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_8 DATAFILE ‘/ORADATA2/tbs_usagecdr_8.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_9 DATAFILE ‘/ORADATA2/tbs_usagecdr_9.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_10 DATAFILE ‘/ORADATA2/tbs_usagecdr_10.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_11 DATAFILE ‘/ORADATA2/tbs_usagecdr_11.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_12 DATAFILE ‘/ORADATA2/tbs_usagecdr_12.dbf’ size 250M 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
–Backup Existing Table
SELECT COUNT(1) FROM TBLMSESSIONUSAGECDR;
RENAME TBLMSESSIONUSAGECDR TO TBLMSESSIONUSAGECDR_BCK031213;
CREATE TABLE TBLMSESSIONUSAGECDR
(CREATE_DATE TIMESTAMP(6),
USERID VARCHAR2(50),
MONITORINGKEY VARCHAR2(100) NOT NULL ENABLE,
USAGETIME NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
UPLOADOCTETS NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
DOWNLOADOCTETS NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
TOTALOCTETS NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
AGGREGATEKEY VARCHAR2(128),
LASTUPDATETIME TIMESTAMP (6),
METERINGLEVEL VARCHAR2(50),
CUI VARCHAR2(50),
V_CREATE_DATE NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(CREATE_DATE,’MM’))) VIRTUAL VISIBLE
)
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
);
ALTER TABLE TBLMSESSIONUSAGECDR INITRANS 100;
ALTER TABLE TBLMSESSIONUSAGECDR ENABLE ROW MOVEMENT;
–Create Procedure for TRUNCATE PARTITION which are older then 6 months
create or replace PROCEDURE PROC_NVUSAGECDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN
/*
EliteCSM NetVertex Module
EliteCore Technologies Pvt. Ltd.
*/
FOR REC IN (SELECT distinct ‘p_’||TO_NUMBER(TO_CHAR(CREATE_DATE,’MM’))
p_name
FROM TBLMSESSIONUSAGECDR
WHERE CREATE_DATE + 185 < SYSDATE)
LOOP
EXECUTE IMMEDIATE ‘ALTER TABLE TBLMSESSIONUSAGECDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES’;
DBMS_OUTPUT.PUT_LINE (‘ALTER TABLE TBLMSESSIONUSAGECDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES; ——-> successfully completed’);
END LOOP;
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_USAGECDR_MAINTAINANCE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_NVUSAGECDR_MAINTAINANCE(); END;’,
START_DATE => SYSTIMESTAMP,
repeat_interval => ‘freq=yearly;bymonth=6;bymonthday=30;byhour=7;byminute=5;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘”NETVERTEXP”‘,
tabname => ‘”TBLMSESSIONUSAGECDR”‘,
estimate_percent => 1
);
end;
/
–create tablespaces for TBLEDR
DEFINE db_datafile=”&&dbf_storage_directory”
prompt &db_datafile
CREATE TABLESPACE tbs_edr_1 DATAFILE ‘/ORADATA1/tbs_edr_1.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_2 DATAFILE ‘/ORADATA1/tbs_edr_2.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_3 DATAFILE ‘/ORADATA1/tbs_edr_3.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_4 DATAFILE ‘/ORADATA1/tbs_edr_4.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_5 DATAFILE ‘/ORADATA1/tbs_edr_5.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_6 DATAFILE ‘/ORADATA1/tbs_edr_6.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_7 DATAFILE ‘/ORADATA1/tbs_edr_7.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_8 DATAFILE ‘/ORADATA1/tbs_edr_8.dbf’ size 250M AUTOEXTEND ON;
connect &&username/&&password
–Keep history of TBLEDR without depend on year so every
–new calendar month same partition will use next year as well
–Backup Existing Table
SELECT COUNT(1) FROM TBLEDR;
RENAME TBLEDR TO TBLEDR_BCK03122013;
CREATE TABLE TBLEDR
(EDRID NUMBER(20,0),
SESSIONID VARCHAR2(100),
USERNAME VARCHAR2(264),
EXPIREDRULE VARCHAR2(100),
NEWRULE VARCHAR2(100),
OLDBW VARCHAR2(32),
NEWBW VARCHAR2(32),
PARAM1 VARCHAR2(64),
PARAM2 VARCHAR2(64),
CREATEDATE TIMESTAMP (6),
LASTMODIFIEDDATE TIMESTAMP (6),
TIMESTAMP TIMESTAMP (6),
V_CREATEDATE NUMBER
GENERATED ALWAYS AS
(
TO_NUMBER(TO_CHAR(CREATEDATE,’DD’))
) VIRTUAL
)PARTITION BY LIST (V_CREATEDATE)
(
PARTITION p_1 VALUES (1) tablespace tbs_edr_1,
PARTITION p_2 VALUES (2) tablespace tbs_edr_2,
PARTITION p_3 VALUES (3) tablespace tbs_edr_3,
PARTITION p_4 VALUES (4) tablespace tbs_edr_4,
PARTITION p_5 VALUES (5) tablespace tbs_edr_5,
PARTITION p_6 VALUES (6) tablespace tbs_edr_6,
PARTITION p_7 VALUES (7) tablespace tbs_edr_7,
PARTITION p_8 VALUES (8) tablespace tbs_edr_8,
PARTITION p_9 VALUES (9) tablespace tbs_edr_1,
PARTITION p_10 VALUES (10) tablespace tbs_edr_2,
PARTITION p_11 VALUES (11) tablespace tbs_edr_3,
PARTITION p_12 VALUES (12) tablespace tbs_edr_4,
PARTITION p_13 VALUES (13) tablespace tbs_edr_5,
PARTITION p_14 VALUES (14) tablespace tbs_edr_6,
PARTITION p_15 VALUES (15) tablespace tbs_edr_7,
PARTITION p_16 VALUES (16) tablespace tbs_edr_8,
PARTITION p_17 VALUES (17) tablespace tbs_edr_1,
PARTITION p_18 VALUES (18) tablespace tbs_edr_2,
PARTITION p_19 VALUES (19) tablespace tbs_edr_3,
PARTITION p_20 VALUES (20) tablespace tbs_edr_4,
PARTITION p_21 VALUES (21) tablespace tbs_edr_5,
PARTITION p_22 VALUES (22) tablespace tbs_edr_6,
PARTITION p_23 VALUES (23) tablespace tbs_edr_7,
PARTITION p_24 VALUES (24) tablespace tbs_edr_8,
PARTITION p_25 VALUES (25) tablespace tbs_edr_1,
PARTITION p_26 VALUES (26) tablespace tbs_edr_2,
PARTITION p_27 VALUES (27) tablespace tbs_edr_3,
PARTITION p_28 VALUES (28) tablespace tbs_edr_4,
PARTITION p_29 VALUES (29) tablespace tbs_edr_5,
PARTITION p_30 VALUES (30) tablespace tbs_edr_6,
PARTITION p_31 VALUES (31) tablespace tbs_edr_7
);
ALTER TABLE TBLEDR
ADD CONSTRAINT “PK_PEDR” PRIMARY KEY (“EDRID”);
ALTER TABLE TBLEDR INITRANS 100;
ALTER INDEX PK_PEDR INITRANS 100;
ALTER TABLE TBLEDR ENABLE ROW MOVEMENT;
–Create Procedure for TRUNCATE PARTITION which are older then 6 months
create or replace PROCEDURE PROC_TBLEDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN
/*
EliteCSM NetVertex Module
EliteCore Technologies Pvt. Ltd.
*/
FOR REC IN (SELECT distinct ‘p_’||TO_NUMBER(TO_CHAR(CREATEDATE,’DD’)) p_name FROM TBLEDR WHERE CREATEDATE + 15 < SYSDATE)
LOOP
EXECUTE IMMEDIATE ‘ALTER TABLE TBLEDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES’;
DBMS_OUTPUT.PUT_LINE (‘ALTER TABLE TBLEDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES; ——-> successfully completed’);
END LOOP;
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_TBLEDR_MAINTAINANCE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_TBLEDR_MAINTAINANCE(); END;’,
START_DATE => SYSTIMESTAMP,
repeat_interval => ‘freq=monthly;bymonthday=14,28;byhour=6;byminute=33;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/
begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘”NETVERTEXP”‘,
tabname => ‘”TBLEDR”‘,
estimate_percent => 1
);
end;
/
🙂
😦
Insert data only single table space so we should improve insert performance via taking advantage of SAN storage
specify multiple tablespaces