Table Management DWH

Configure Partition on TBLMYUSERSTATISTICS

cat TBLMYUSERSTATISTICS_patch.sql
CREATE TABLESPACE PUSERSTAT20130101 ;
CREATE TABLESPACE PUSERSTAT20130201 ;
CREATE TABLESPACE PUSERSTAT20130301 ;
CREATE TABLESPACE PUSERSTAT20130401 ;
CREATE TABLESPACE PUSERSTAT20130501 ;
CREATE TABLESPACE PUSERSTAT20130601 ;
CREATE TABLESPACE PUSERSTAT20130701 ;
CREATE TABLESPACE PUSERSTAT20130801 ;
CREATE TABLESPACE PUSERSTAT20130901 ;
CREATE TABLESPACE PUSERSTAT20131001 ;
CREATE TABLESPACE PUSERSTAT20131101 ;
CREATE TABLESPACE PUSERSTAT20131201 ;
CREATE TABLESPACE PUSERSTATMAXDATE;

connect &MYORA_user/&MYORA_pwd

 CREATE TABLE TBLMYUSERSTATISTICS
   (    USERSTATISTICSID NUMBER(*,0),
        CREATE_DATE TIMESTAMP (6),
        USER_NAME VARCHAR2(253 BYTE),
        REPLY_MESSAGE VARCHAR2(253 BYTE),
        NAS_IP_ADDRESS VARCHAR2(20 BYTE),
        NAS_PORT NUMBER(10,0),
        SERVICE_TYPE VARCHAR2(50 BYTE),
        FRAMED_PROTOCOL VARCHAR2(50 BYTE),
        FRAMED_IP_ADDRESS VARCHAR2(20 BYTE),
        FRAMED_IP_NETMASK VARCHAR2(20 BYTE),
        FRAMED_ROUTING VARCHAR2(50 BYTE),
        FILTER_ID VARCHAR2(50 BYTE),
        FRAMED_MTU NUMBER(10,0),
        CALLBACK_NUMBER VARCHAR2(50 BYTE),
        CALLBACK_ID VARCHAR2(50 BYTE),
        FRAMED_ROUTE VARCHAR2(50 BYTE),
        SESSION_TIMEOUT NUMBER(10,0),
        IDLE_TIMEOUT NUMBER(10,0),
        TERMINATION_ACTION VARCHAR2(50 BYTE),
        CALLED_STATION_ID VARCHAR2(253 BYTE),
        CALLING_STATION_ID VARCHAR2(253 BYTE),
        NAS_IDENTIFIER VARCHAR2(50 BYTE),
        PROXY_STATE NUMBER(10,0),
        NAS_PORT_TYPE VARCHAR2(60 BYTE),
        PORT_LIMIT NUMBER(10,0),
        EVENT_TIMESTAMP NUMBER(14,0),
        NAS_PORT_ID VARCHAR2(255 BYTE),
        CONNECT_INFO VARCHAR2(253 BYTE),
        PARAM_STR0 VARCHAR2(253 BYTE),
        PARAM_STR1 VARCHAR2(253 BYTE),
        PARAM_STR2 VARCHAR2(253 BYTE),
        PARAM_STR3 VARCHAR2(253 BYTE),
        PARAM_STR4 VARCHAR2(253 BYTE),
        PARAM_STR5 VARCHAR2(253 BYTE),
        PARAM_STR6 VARCHAR2(253 BYTE),
        PARAM_STR7 VARCHAR2(253 BYTE),
        PARAM_STR8 VARCHAR2(253 BYTE),
        PARAM_STR9 VARCHAR2(253 BYTE),
        PARAM_INT0 NUMBER(*,0),
        PARAM_INT1 NUMBER(*,0),
        PARAM_INT2 NUMBER(*,0),
        PARAM_INT3 NUMBER(*,0),
        PARAM_INT4 NUMBER(*,0),
        PARAM_DATE0 TIMESTAMP (6),
        PARAM_DATE1 TIMESTAMP (6),
        PARAM_DATE2 TIMESTAMP (6),
        GROUPNAME VARCHAR2(60 BYTE),
        USER_IDENTITY VARCHAR2(100 BYTE),
        ESN_MEID VARCHAR2(16 BYTE),
        CUI VARCHAR2(64 BYTE),
        HA_IP VARCHAR2(64 BYTE),
        BS_ID VARCHAR2(64 BYTE),
        PCF_SGSN_AGW VARCHAR2(64 BYTE),
        NAP_OPERATOR_CARRIER VARCHAR2(64 BYTE),
        LOCATION VARCHAR2(255 BYTE)
   )
  PARTITION BY RANGE (CREATE_DATE)
 (PARTITION PUSERSTAT20130101  VALUES LESS THAN (TIMESTAMP' 2013-02-01 00:00:00')   TABLESPACE PUSERSTAT20130101,
  PARTITION PUSERSTAT20130201  VALUES LESS THAN (TIMESTAMP' 2013-03-01 00:00:00')   TABLESPACE PUSERSTAT20130201 ,
  PARTITION PUSERSTAT20130301  VALUES LESS THAN (TIMESTAMP' 2013-04-01 00:00:00')   TABLESPACE PUSERSTAT20130301 ,
  PARTITION PUSERSTAT20130401  VALUES LESS THAN (TIMESTAMP' 2013-05-01 00:00:00')   TABLESPACE PUSERSTAT20130401 ,
  PARTITION PUSERSTAT20130501  VALUES LESS THAN (TIMESTAMP' 2013-06-01 00:00:00')   TABLESPACE PUSERSTAT20130501 ,
  PARTITION PUSERSTAT20130601  VALUES LESS THAN (TIMESTAMP' 2013-07-01 00:00:00')   TABLESPACE PUSERSTAT20130601 ,
  PARTITION PUSERSTAT20130701  VALUES LESS THAN (TIMESTAMP' 2013-08-01 00:00:00')   TABLESPACE PUSERSTAT20130701 ,
  PARTITION PUSERSTAT20130801  VALUES LESS THAN (TIMESTAMP' 2013-09-01 00:00:00')   TABLESPACE PUSERSTAT20130801 ,
  PARTITION PUSERSTAT20130901  VALUES LESS THAN (TIMESTAMP' 2013-10-01 00:00:00')   TABLESPACE PUSERSTAT20130901 ,
  PARTITION PUSERSTAT20131001  VALUES LESS THAN (TIMESTAMP' 2013-11-01 00:00:00')   TABLESPACE PUSERSTAT20131001 ,
  PARTITION PUSERSTAT20131101  VALUES LESS THAN (TIMESTAMP' 2013-12-01 00:00:00')   TABLESPACE PUSERSTAT20131101 ,
  PARTITION PUSERSTAT20131201  VALUES LESS THAN (TIMESTAMP' 2014-01-01 00:00:00')   TABLESPACE PUSERSTAT20131201 ,
  PARTITION PUSERSTATMAXDATE  VALUES LESS THAN (MAXVALUE)   TABLESPACE PUSERSTATMAXDATE
  );

ADD NEW PARTITION CODE


CREATE OR REPLACE PROCEDURE “MYORA”.”TBL_USERSTAT_ADD_PARTITION” (towner varchar2,tname varchar2,action varchar2) as v_exists number; new_part varchar2(255); loc_data varchar2(4000):=’/u01/app/oracle/oradata/orcl/’; BEGIN IF ACTION=’PADD’ THEN –dbms_output.put_line(‘———–part’||to_char(ADD_MONTHS(TRUNC(SYSDATE,’mon’),RECMON),’yyyymmdd’)||’———–‘); –dbms_output.put_line(‘CREATE TABLESPACE tbsuserstat’||to_char(ADD_MONTHS(TRUNC(SYSDATE,’mon’),RECMON),’yyyymmdd’)|| ‘ DATAFILE ‘ || CHR(39)||’/u01/app/oracle/oradata/orcl/tbsuserstat’||to_char(ADD_MONTHS(TRUNC(SYSDATE,’mon’),RECMON),’yyyymmdd’)||’.dbf’||chr(39)|| ‘ SIZE 1M’ ||’ ;’); –dbms_output.put_line(‘alter table ‘||towner||’.’||tname||’ add partition P’||to_char(ADD_MONTHS(TRUNC(SYSDATE,’mon’),RECMON),’yyyymmdd’) || ‘ values less than (to_date(”’||to_char(ADD_MONTHS(TRUNC(SYSDATE,’mon’),RECMON+1),’dd.mm.yyyy’)||”’,”dd.mm.yyyy”))’||’ TABLESPACE ‘||’tbsuserstat’||to_char(ADD_MONTHS(TRUNC(SYSDATE,’mon’),RECMON),’yyyymmdd’)||’;’); select ‘PUSERSTAT’||(case when to_number(substr(partition_name,14,2))=12 then to_number(substr(partition_name,10,4))+1 else to_number(substr(partition_name,10,4)) end)||(case when length((case when to_number(substr(partition_name,14,2))=12 then 1 when to_number(substr(partition_name,14,2)) IN (1,2,3,4,5,6,7,8,9,10,11) then to_number(substr(partition_name,14,2)) + 1 end)) = 1 then ‘0’||(case when to_number(substr(partition_name,14,2))=12 then 1 when to_number(substr(partition_name,14,2)) IN (1,2,3,4,5,6,7,8,9,10,11) then to_number(substr(partition_name,14,2)) + 1 end) else to_char((case when to_number(substr(partition_name,14,2))=12 then 1 when to_number(substr(partition_name,14,2)) IN (1,2,3,4,5,6,7,8,9,10,11) then to_number(substr(partition_name,14,2)) + 1 end)) end)||’01’ into new_part from user_tab_partitions where table_name=tname and substr(partition_name,10) = (select max(substr(partition_name,10)) from user_tab_partitions where table_name=tname and partition_name != ‘PUSERSTATMAXDATE’); SELECT count(*) INTO v_exists FROM user_tab_partitions WHERE partition_name = ‘PUSERSTATMAXDATE’; IF v_exists > 0 THEN execute immediate ‘ALTER TABLE TBLMYUSERSTATISTICS DROP PARTITION PUSERSTATMAXDATE UPDATE INDEXES’; dbms_output.put_line(‘CREATE TABLESPACE ‘||new_part|| ‘ DATAFILE ‘ || CHR(39)||loc_data||new_part||’.dbf’||chr(39)|| ‘ SIZE 1M;’); execute immediate ‘CREATE TABLESPACE ‘||new_part|| ‘ DATAFILE ‘ || CHR(39)||loc_data||new_part||’.dbf’||chr(39)|| ‘ SIZE 1M’; dbms_output.put_line(‘alter table ‘||towner||’.’||tname||’ add partition ‘||new_part|| ‘ values less than (to_date(”’||to_char(ADD_MONTHS(TO_DATE(SUBSTR(new_part,10),’yyyymmdd’),1),’dd.mm.yyyy’)||”’,”dd.mm.yyyy”))’||’ TABLESPACE ‘||new_part); execute immediate ‘alter table ‘||towner||’.’||tname||’ add partition ‘||new_part|| ‘ values less than (to_date(”’||to_char(ADD_MONTHS(TO_DATE(SUBSTR(new_part,10),’yyyymmdd’),1),’dd.mm.yyyy’)||”’,”dd.mm.yyyy”))’||’ TABLESPACE ‘||new_part; execute immediate ‘ALTER TABLE TBLMYUSERSTATISTICS ADD PARTITION PUSERSTATMAXDATE VALUES LESS THAN (MAXVALUE) TABLESPACE PUSERSTATMAXDATE’; ELSE execute immediate ‘ALTER TABLE TBLMYUSERSTATISTICS ADD PARTITION PUSERSTATMAXDATE VALUES LESS THAN (MAXVALUE) TABLESPACE PUSERSTATMAXDATE’; END IF; END IF; END; /

DROP TABLE PARTITIONS CODE


CREATE OR REPLACE PROCEDURE “MYORA”.”TBL_USERSTAT_DROP_PARTITION” as v_exists number; BEGIN SELECT count(*) INTO v_exists FROM USER_TABLESPACES WHERE tablespace_name = ‘PUSERSTATMAXDATE’; IF v_exists > 0 THEN execute immediate ‘ALTER TABLE TBLMYUSERSTATISTICS DROP PARTITION PUSERSTATMAXDATE UPDATE INDEXES’; FOR cc IN (SELECT partition_name, high_value,tablespace_name FROM user_tab_partitions WHERE table_name = ‘TBLMYUSERSTATISTICS’ AND partition_name != ‘PUSERSTATMAXDATE’) LOOP EXECUTE IMMEDIATE ‘BEGIN IF sysdate >= ADD_MONTHS(‘ || cc.high_value || ‘, 6) THEN EXECUTE IMMEDIATE ”ALTER TABLE TBLMYUSERSTATISTICS DROP PARTITION ‘ || cc.partition_name ||’ UPDATE INDEXES’ ||’ ”; END IF; END;’; — dbms_output.put_line(‘Drop Table(TBLMYUSERSTATISTICS) Partition:-‘||cc.partition_name); EXECUTE IMMEDIATE ‘BEGIN IF sysdate >= ADD_MONTHS(‘ || cc.high_value || ‘, 6) THEN EXECUTE IMMEDIATE ”DROP TABLESPACE ‘ || cc.tablespace_name ||’ INCLUDING CONTENTS AND DATAFILES’ ||’ ”; END IF; END;’; — dbms_output.put_line(‘Drop Tablespace(TBLMYUSERSTATISTICS):-‘||cc.tablespace_name); END LOOP; execute immediate ‘ALTER TABLE TBLMYUSERSTATISTICS ADD PARTITION PUSERSTATMAXDATE VALUES LESS THAN (MAXVALUE) TABLESPACE PUSERSTATMAXDATE’; ELSE NULL; END IF; END; /

Create New Table Partition

SET SERVEROUTPUT ON
EXEC tbl_userstat_add_partition('MYORA','TBLMYUSERSTATISTICS','PADD');

Drop Old Table Partition

 SET SERVEROUTPUT ON
 EXEC  tbl_userstat_drop_partition();

Atomization

-bash-3.2$ pwd
/u01/oracle/hitesh
-bash-3.2$
-bash-3.2$ ll
total 20
-rwxr-xr-x 1 oracle oinstall 209 Aug 31 18:38 manage_tblmyuserstatistics.sh

-bash-3.2$ cat manage_tbluserstatistics.sh
#!/bin/bash
. /u01/oracle/.bash_profile
sqlplus scott/tiger< /u01/oracle/hitesh/tablepartition_cron.log 2>&1

Ref

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