24 Mart 2014 Pazartesi

ORACLE - Add List partition with for loop

Following script let you create list partition  between dates you specify.
Dates  , tablename and tablespace are all depend on variables that you can change manually.

DECLARE

p_BAS_TAR  DATE  ;
p_BIT_TAR   DATE;
pTABLE_NAME VARCHAR2( 500 CHAR);
pTABLESPACE_NAME  VARCHAR2( 500 CHAR);
DIFF NUMBER ;
INC_VAL  NUMBER :=0;
V_DYNTASK LONG;


BEGIN

DIFF :=0;
p_BAS_TAR:= TO_DATE('20140316','YYYYMMDD');  -- INCULING THIS DAY
p_BIT_TAR:= TO_DATE('20200101','YYYYMMDD');  -- TILL THIS DAY NOT INCLUDED
pTABLE_NAME := 'TABLE_NAME;  --   TABLE_NAME
pTABLESPACE_NAME:='TABLESPACE_NAME';  -- TABLESPACE_NAME

SELECT trunc(TO_DATE(''||p_BIT_TAR||'') )  -  TO_date(TO_DATE(''||p_BAS_TAR||'', 'dd/mm/yyyy') )     INTO  DIFF FROM DUAL;


DBMS_OUTPUT.PUT_LINE(DIFF);


FOR Lcntr IN 1..DIFF
LOOP
  

V_DYNTASK:='ALTER TABLE '||pTABLE_NAME||' ADD PARTITION P_'||to_char(p_BAS_TAR, 'YYYYMMDD')||' VALUES   (TO_DATE('''||p_BAS_TAR||''', ''dd.mm.yyyy'')) '||(case when pTABLESPACE_NAME is null then null else 'TABLESPACE '||pTABLESPACE_NAME end);
DBMS_OUTPUT.PUT_LINE(V_DYNTASK);
EXECUTE IMMEDIATE V_DYNTASK;
COMMIT;

p_BAS_TAR :=  p_BAS_TAR  +  1;


END LOOP;


END; 

Hiç yorum yok:

Yorum Gönder