SQL SCRIPT TO GET INTERVAL PARTITION

SQL SCRIPT TO GET INTERVAL PARTITION

SELECT 'ALTER TABLE ' ||TABLE_OWNER||'.'||TABLE_NAME || ' ADD PARTITION P'||to_char((date'2023-03-22' + (LEVEL-2)),'YYYYMMDD','NLS_CALENDAR=GREGORIAN')|| ' VALUES LESS THAN ('''|| TO_DATE(to_char(date'2023-03-22' + (LEVEL-1)) ,'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')|| ''') TABLESPACE USERS'  FROM dba_tab_partitions where interval='YES' connect by LEVEL <= (365*1);

SELECT 'ALTER TABLE ' ||TABLE_OWNER||'.'||TABLE_NAME || ' ADD PARTITION P'||to_char((date'2023-03-22' + (LEVEL-2)),'YYYYMMDD','NLS_CALENDAR=GREGORIAN')|| ' VALUES LESS THAN ('''|| to_char(add_months(date '2023-01-01',level),'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')|| ''') TABLESPACE USERS'  FROM dba_tab_partitions where interval='NO' and  TABLE_OWNER='C##HYDRA' connect by level <= (1*12);