Clear empty partitions

Clear empty partitions

DECLARE
    g_owner      VARCHAR2(30) := USER;
    g_table_name VARCHAR2(30) := 'PART_TEST';

    PROCEDURE sp_execute(p_str VARCHAR2) IS
    BEGIN
        dbms_output.put_line(p_str);
        EXECUTE IMMEDIATE p_str;
    END sp_execute;

    FUNCTION is_segment_empty(p_table_owner    VARCHAR2,
                              p_table_name     VARCHAR2,
                              p_partition_name VARCHAR2) RETURN BOOLEAN IS
        l_tmp  NUMBER;
        l_stmt VARCHAR2(32767);
    BEGIN
        l_stmt := 'SELECT count(*) from ' || p_table_owner || '.' || p_table_name || CASE
                      WHEN p_partition_name IS NOT NULL THEN
                       ' partition (' || p_partition_name || ') '
                  END || ' where rownum = 1';
        EXECUTE IMMEDIATE l_stmt
            INTO l_tmp;
        IF l_tmp <> 0
        THEN
            RETURN FALSE;
        ELSE 
            RETURN TRUE;
        END IF;

    END is_segment_empty;

BEGIN
    FOR rec IN (SELECT * FROM all_tab_partitions p WHERE p.table_owner = g_owner AND table_name = g_table_name)
    LOOP
        IF is_segment_empty(rec.table_owner,
                            rec.table_name,
                            rec.partition_name)
        THEN
            sp_execute('ALTER TABLE ' || rec.table_owner || '.' || rec.table_name || ' TRUNCATE PARTITION ' ||
                       rec.partition_name || ' DROP ALL STORAGE UPDATE INDEXES');
        END IF;
    END LOOP;
END;
/