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