43 lines
1.5 KiB
SQL
43 lines
1.5 KiB
SQL
declare
|
|
-- Local variables here
|
|
V_COUNT integer;
|
|
begin
|
|
|
|
for r_tab_part in
|
|
( select t.table_name, t.partition_name
|
|
from user_tab_partitions t
|
|
where t.table_name not in ('BCK_CRM_VALIDAZIONE_IN', 'BCK_CRM_VALIDAZIONE_OUT')
|
|
and t.partition_position =
|
|
( select max(t1.partition_position)-1
|
|
from user_tab_partitions t1
|
|
where t1.table_name = t.table_name
|
|
)
|
|
)
|
|
loop
|
|
-- split della partizione da eliminare
|
|
begin
|
|
execute immediate('ALTER TABLE '|| r_tab_part.table_name ||'
|
|
SPLIT PARTITION ' || r_tab_part.partition_name || ' AT (TO_DATE('' 2013-01-01 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))
|
|
INTO (PARTITION T4_2012, PARTITION ' ||r_tab_part.partition_name||') UPDATE GLOBAL INDEXES');
|
|
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM '||r_tab_part.TABLE_NAME||' PARTITION ('||r_tab_part.partition_name||')'INTO V_COUNT;
|
|
|
|
IF V_COUNT = 0 THEN
|
|
|
|
EXECUTE IMMEDIATE 'alter table '||r_tab_part.TABLE_NAME||' drop partition '||r_tab_part.partition_name||' update global indexes';
|
|
|
|
ELSE
|
|
DBMS_OUTPUT.put_line('Impossibile eliminare la partizione ' || r_tab_part.partition_name || ' della tabella '|| r_tab_part.TABLE_NAME
|
|
|| ' per la presenza di record');
|
|
|
|
END IF;
|
|
|
|
exception when others then
|
|
DBMS_OUTPUT.put_line('Errore nello spilt della partizione ' || r_tab_part.partition_name || ' della tabella '|| r_tab_part.TABLE_NAME
|
|
|| ' ' || SQLERRM);
|
|
end;
|
|
|
|
end loop;
|
|
|
|
end;
|
|
/ |