ManuallyPartitionTable

ManuallyPartitionTable

--create required function (note will probably already exist)

drop FUNCTION GET_HIGH_VALUE_YYYYMM;

create or replace FUNCTION GET_HIGH_VALUE_YYYYMM (

p_TableOwner IN VARCHAR2,

p_TableName IN VARCHAR2,

p_PatitionName IN VARCHAR2

) RETURN VARCHAR2

IS

v_Year VARCHAR2(4);

v_Day VARCHAR2(2);

v_LongVal LONG;

BEGIN

SELECT HIGH_VALUE INTO v_LongVal FROM DBA_TAB_PARTITIONS

WHERE TABLE_OWNER = p_TableOwner AND TABLE_NAME = p_TableName AND PARTITION_NAME = p_PatitionName;

-- v_Year := substr(v_LongVal,12,4);

-- v_Day := substr(v_LongVal,17,2);

v_Year := substr(v_LongVal,11,4);

v_Day := substr(v_LongVal,16,2);

RETURN v_Year||v_Day;

END GET_HIGH_VALUE_YYYYMM;

/

select get_high_value_yyyymm('ABC','TAB_NAME','SYS_P1037') from dual;

--TAB_NAME

--GET APPROPRIATE STARTING DATE

select min(LOAD_DATE),MAX(LOAD_DATE) from ABC.TAB_NAME;

#rename TAB_NAME to TAB_NAMEO;

--CREATE AUTO PARTITIONED TABLE

CREATE TABLE ABC.TAB_NAME

PARTITION BY RANGE (LOAD_DATE)

INTERVAL(NUMTOYMINTERVAL ( 1, 'MONTH' ))

(PARTITION dummy VALUES LESS THAN ( TO_TIMESTAMP ( ' 2014-01-01 00:00:00', 'YYYY-mm-dd hh24:mi:ss' ) ))

COMPRESS FOR OLTP NOLOGGING PCTFREE 5 TABLESPACE BIGTSP

AS SELECT * FROM ABC.TAB_NAMEO;

--defaults

alter table ABC.TAB_NAME modify "LOAD_DATE" DATE DEFAULT SYSDATE;

alter table ABC.TAB_NAME modify "UPDATE_DATE" DATE DEFAULT SYSDATE;

--GRANTS

GRANT SELECT on ABC.TAB_NAME to HEDW_STAGE;

GRANT SELECT on ABC.TAB_NAME to EDW_READ_ONLY;

--DEPENDENCIES

alter PACKAGE ABC.LOAD_TRACKING_STANDBY compile BODY ;

--CHECK RECORD COUNTS

SELECT COUNT (0) FROM ABC.TAB_NAME;

SELECT COUNT (0) FROM ABC.TAB_NAMEO;

--DROP OLD TABLE --(save original create table cmd first for constraints and as a final check)

DROP TABLE ABC.TAB_NAMEO;

--RENAME PARTITIONS

--check for auto partitioned tables

select t.owner,t.table_name,nvl(pt.partitioning_type,'NOT_PARTITIONED') partitioning_type, NVL(pt.interval,'NOT_AUTO_PARTITIONED') interval

from dba_tables t, dba_part_tables pt where t.owner like 'ABC' and t.table_name = pt.table_name (+) order by table_name;

--note, some partitions may exist that were not created by auto partitioning even though interval partitioning is switched on. so previous check is better

select 'alter table '||table_owner||'.'||table_name||' rename partition '||partition_name||' to '|| substr(table_name,1,23)||'_'||to_char(ADD_MONTHS(to_date(get_high_value_yyyymm(table_owner,table_name,partition_name),'yyyymm'),-1),'yyyymm')||';'

from dba_tab_partitions

where table_name in ( SELECT distinct table_name from dba_tab_partitions where interval = 'YES' and table_name like 'TAB_NAME') order by to_date(get_high_value_yyyymm(table_owner,table_name,partition_name),'yyyymm');

--e.g.

alter table ABC.TAB_NAME rename partition DUMMY to TAB_NAME_201510;

alter table ABC.TAB_NAME rename partition SYS_P1037 to TAB_NAME_201511;

..

--ADD CONSTRAINTS

ALTER TABLE ABC.TAB_NAME ADD CONSTRAINT "TAB_NAME_PK" PRIMARY KEY ("TMESTP") USING INDEX TABLESPACE "BIG" PCTFREE 5 LOCAL;

--ADD INDEXES

CREATE INDEX "ABC"."PTE_PARCELS_PCL_ID_IDX" ON "ABC"."TAB_NAME" ("PARCELS_PCL_ID") PCTFREE 5 TABLESPACE "BIGTSP" LOCAL ;

--REMOVE AUTO PARTITIONING

select 'alter table '||owner||'.'||table_name||' set INTERVAL();' from dba_tables where table_name in ( SELECT distinct table_name from dba_tab_partitions where interval = 'YES' and table_name not like 'BIN%' )

alter table "ABC"."TAB_NAME" set INTERVAL();

--stats

--alter session set "_fix_control"='9728438:OFF';

begin

SYS.DBMS_STATS.GATHER_TABLE_STATS (

ownname=>'ABC',

tabname=>'TAB_NAME'

--estimate_percent=>40,

--degree=>20

--Cascade=>TRUE

);

end;

/

===============================================================