Automatic Daily – Interval Partitioned Table Management

Automatic Daily – Interval partitioned table management

spool APP_DBC_PARTITION-TBLCDR_v1.log

--create tablespaces for TBLCDR
DEFINE db_datafile="&&dbf_storage_directory"
prompt &db_datafile

CREATE TABLESPACE tbs_cdr_1   DATAFILE '&&db_datafile/tbs_cdr_1.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_2   DATAFILE '&&db_datafile/tbs_cdr_2.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_3   DATAFILE '&&db_datafile/tbs_cdr_3.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_4   DATAFILE '&&db_datafile/tbs_cdr_4.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_5   DATAFILE '&&db_datafile/tbs_cdr_5.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_6   DATAFILE '&&db_datafile/tbs_cdr_6.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_7   DATAFILE '&&db_datafile/tbs_cdr_7.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_8   DATAFILE '&&db_datafile/tbs_cdr_8.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_9   DATAFILE '&&db_datafile/tbs_cdr_9.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_10  DATAFILE '&&db_datafile/tbs_cdr_10.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_11  DATAFILE '&&db_datafile/tbs_cdr_11.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_12  DATAFILE '&&db_datafile/tbs_cdr_12.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_13  DATAFILE '&&db_datafile/tbs_cdr_13.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_14  DATAFILE '&&db_datafile/tbs_cdr_14.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_15  DATAFILE '&&db_datafile/tbs_cdr_15.dbf' size 250M AUTOEXTEND ON;

CREATE TABLESPACE tbs_idxcdr  DATAFILE '&&db_datafile/tbs_idxcdr.dbf' size 250M AUTOEXTEND ON;

connect &&username/&&password

set long 10000
set pagesize 0
select dbms_metadata.get_dependent_ddl('INDEX','TBLCDR') from duAL;

SELECT DBMS_METADATA.GET_DDL('TABLE','TBLCDR') FROM DUAL;

RENAME TBLCDR TO OLD_TBLCDR;

CREATE TABLE TBLCDR
  (
    CDRID            NUMBER(20,0),
    SESSIONID        VARCHAR2(100 BYTE),
    USERNAME         VARCHAR2(264 BYTE),
    FRAMMEDIPADDRESS VARCHAR2(32 BYTE),
    GATEWAYADDRESS   VARCHAR2(32 BYTE),
    REQUESTTYPE      VARCHAR2(30 BYTE),
    SERVICETYPE      VARCHAR2(30 BYTE),
    MONITORINGKEY    VARCHAR2(64 BYTE),
    INPUTOCTETS      NUMBER(32,0),
    OUTPUTOCTETS     NUMBER(32,0),
    TOTALOCTETS      NUMBER(32,0),
    USAGETIME        VARCHAR2(64 BYTE),
    PARAM1           VARCHAR2(64 BYTE),
    PARAM2           VARCHAR2(64 BYTE),
    PARAM3           VARCHAR2(64 BYTE),
    PARAM4           VARCHAR2(64 BYTE),
    PARAM5           VARCHAR2(64 BYTE),
    CREATEDATE       TIMESTAMP (6),
    LASTMODIFIEDDATE TIMESTAMP (6),
    TIMESTAMP        TIMESTAMP (6),
    AGGREGATEKEY  VARCHAR2(128 BYTE),
    IMSI          VARCHAR2(100 BYTE),
    APN           VARCHAR2(25 BYTE),
    LAC           VARCHAR2(5 BYTE),
    USEREQUIPMENT VARCHAR2(25 BYTE)
  )
PARTITION BY RANGE (CREATEDATE)
INTERVAL (NUMTODSINTERVAL(1,'day')) store in (tbs_cdr_1,tbs_cdr_2,tbs_cdr_3,tbs_cdr_4,tbs_cdr_5,tbs_cdr_6,tbs_cdr_7,tbs_cdr_8,tbs_cdr_9,tbs_cdr_10,tbs_cdr_11,tbs_cdr_12,tbs_cdr_13,tbs_cdr_14,tbs_cdr_15)
(PARTITION p_first VALUES LESS THAN (TO_DATE('19-12-2013', 'DD-MM-YYYY')))
NOLOGGING;

 CREATE UNIQUE INDEX IDX_UNQ_CDR ON TBLCDR(CDRID)
 TABLESPACE tbs_idxcdr
 NOLOGGING;

 ALTER TABLE TBLCDR INITRANS 200;

 ALTER INDEX IDX_UNQ_CDR INITRANS 200;

 ALTER SEQUENCE SEQ_CDR CACHE 2000;

 ALTER TABLE TBLCDR ENABLE ROW MOVEMENT;

 set long 10000
 set pagesize 0
 select dbms_metadata.get_dependent_ddl('INDEX','TBLCDR') from DUAL;

 SELECT DBMS_METADATA.GET_DDL('TABLE','TBLCDR') FROM DUAL;

--Create Procedure for TRUNCATE PARTITION which are older then 6 months
-- TRUNCATE PARTITION which are older then 6 months (MANUAL OAM)

-- ALTER TABLE TBLCDR TRUNCATE PARTITION 'NAME_OF_PARTITION' UPDATE INDEXES;

spool off;

Automatic Maintenance

create or replace procedure sp_netvertex_data_purge_cycle(t_name varchar2)
as
    v_date     DATE;
    v_date_cut DATE := trunc(SYSDATE) - 15;
    --v_date_cut DATE := add_months(trunc(SYSDATE, 'mm'), purge_month_interval);
BEGIN

/*

-- Automatic Daily Interval Partition Configured on below modules
    1.) TBLCDR
    2.) TBLEDR
    3.) TBLMSESSIONUSAGECDR

-- This Code is for Automatic data purging cycle configuration to drop the specified partitin
   which is older than 3 or 6 months

-- How to use this procedure

 EXEC sp_netvertex_data_purge_cycle('TBLEDR',1);
 EXEC sp_netvertex_data_purge_cycle('TBLCDR',1);
 EXEC sp_netvertex_data_purge_cycle('TBLSESSIONUSAGECDR',1);

 we can also configure the database scheduler and configured in pick off hour for automize the maintainanace

*/

    FOR r IN (SELECT table_name,high_value,interval,partition_name FROM   user_tab_partitions tp WHERE  tp.table_name = t_name)
    LOOP
        EXECUTE IMMEDIATE 'SELECT ' || r.high_value || ' from dual' INTO v_date;
        IF v_date < v_date_cut
        THEN
            IF r.interval = 'NO'
            THEN
                EXECUTE IMMEDIATE 'alter table ' ||  r.table_name || ' truncate partition ' ||r.partition_name || ' UPDATE INDEXES';

                --Debug Command
                DBMS_OUTPUT.PUT_LINE('alter table ' || r.table_name || ' truncate partition ' ||r.partition_name|| ' UPDATE INDEXES');
            ELSE
                EXECUTE IMMEDIATE 'alter table ' ||  r.table_name || ' drop partition ' ||r.partition_name || ' UPDATE INDEXES';

                --Debug Command
			          DBMS_OUTPUT.PUT_LINE('alter table ' || r.table_name || ' drop partition ' ||r.partition_name|| ' UPDATE INDEXES');
            END IF;
        END IF;
    END LOOP;

END;

ALTER TABLE TBLEDR ADD PARTITION “P2 “VALUES LESS THAN (TIMESTAMP’ 2013-12-16 00:00:00′)
Error report –
SQL Error: ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

You can not drop the oldest, root, partition in an interval partitioned table.

ORA-14758: Last partition in the range section cannot be dropped
https://forums.oracle.com/message/10220371

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.