כאשר נוצר מצב שבו ב-tablespace מתפנה מקום רב, אורקל מאפשרים להקטין את ה-tablespace על ידי מעבר על ה-datafile-ים שלו וביצוע פקודות resize. הבעיה מתעוררת כאשר ישנו Tablespace עם מספר datafiles (ולפעמים זה יכול להגיע גם לעשרות) ואז העבודה של מציאת הסגמנט ש"חוסם" את שחרור השטח הפנוי הופך להיות משימה מהגהינום: גם הזיהוי בצורה ידנית של הבלוק התפוס העליון (HWM) וגם ה-resize שלפעמים לוקח זמן.

דרך אגב, אם יש tablespace  במצב read-only ובו אינדקסים של אובייקט שהוא לא ב-read-only, אז כאשר זורקים את הטבלה או ה-partition אז השטח ב-tablespace הזה לא משתחרר – הסגמנטים של האינדקס הופכים ל-temp segments ועד שלא נפתח את ה-tablespace ל-read write השטח לא יזוהה כפנוי.

כדי להתגבר על כל הבעיות האלה ניתן להשתמש בפרוצדורה הסופר שימושית הבאה.

הפרוצדורה יודעת לקבל שם של tablespace ואת כמות השטח (באחוזים) שאנחנו רוצים לשחרר (לדוגמה, אם יש 100 מגה פנוי שניתן לשחרר ואנחנו רוצים שישאר 20, ניתן לפרוצדורה 0.8) והיא יודעת לעבור בכל datafile ותשחרר את השטח הפנוי עד ל-High water mark. הפרוצדורה יודעת גם לייצר סקריפט מבלי להריץ את הפקודות בפועל ואף לייצר תחזית של כמות השטח שיתפנה מה-tablespace.

ונעבור לפרוצדורה:

procedure shrink_files_to_limit(p_tbs      in varchar2,
                                p_in_mode  in char default 'N',
                                p_full_pct in number default 0.2,
                                p_do_cmd   varchar2 default 'N') is
    cmd    varchar2(200);
    p_mode varchar2(20);

    sum_mbytes_freed number := 0;
    tbs_status       varchar2(100);

    -- Protected do_command:
    -- 1. Run command only if the p_do_cmd = Y
    -- 2. Igonres ORA-03297
    procedure do_command_protect(p_cmd    in varchar2,
                                 p_do_cmd in varchar2) is
        sql_err_msg varchar2(1000);
    begin
        dbms_output.put_line(p_cmd || ';');
        if p_do_cmd = 'Y'
        then
            begin
                execute immediate (p_cmd);
            exception
                when others then
                    sql_err_msg := sqlerrm;
                    null;
                    if sqlcode = -3297
                    then
                        -- if unable to resize, ignore
                        null;
                    else
                        dbms_output.put_line('error was ' || replace(sql_err_msg, 'ORA', 'ora'));
                        null;
                        -- raise;
                    end if;
            end;
        end if;
    end do_command_protect;

begin
    p_mode := upper(p_in_mode);
    if p_mode not in ('N', 'SHRINK')
    then
        raise_application_error(-20001, 'Please enter valid shrink mode:  SHRINK/N');
    end if;

    if p_do_cmd not in ('Y', 'N')
    then
        raise_application_error(-20002, 'Please enter valid do mode: Y/N');
    end if;

    -- Open the R/O tablespace (this will drop automaticly temp segments)
    begin
        select distinct t.status
          into tbs_status
          from dba_data_files f
          join dba_tablespaces t on f.tablespace_name = t.tablespace_name
         where f.tablespace_name = p_tbs
           and t.status in ('READ ONLY', 'ONLINE')
           and t.contents = 'PERMANENT';

        if p_mode = 'SHRINK' and tbs_status = 'READ ONLY'
        then
            cmd := 'alter tablespace ' || p_tbs || ' READ WRITE';
            do_command_protect(cmd, p_do_cmd);
        end if;
    exception
        when no_data_found then
            raise_application_error(-20003, 'Tablespace ' || p_tbs || ' does not exist');
    end;

    -- Check for the last extent on each datafile in the tablespace. This is the min resize size of the datafile.
    for cur in (select substr(f.file_name, 1, 70) filename,
                       f.tablespace_name,
                       ceil(max(nvl(e.block_id * t.block_size + e.bytes,
                                    nvl(t.next_extent, 104857600) + 4 * t.block_size)) / 1024) min_k_size,
                       (max(f.bytes) / 1024 -
                       ceil(max(nvl(e.block_id * (e.bytes / e.blocks) + e.bytes,
                                     nvl(t.next_extent, 104857600) + 4 * t.block_size)) / 1024)) free_k_size,
                       max(f.bytes) / 1024 curr_k_size,
                       f.autoextensible
                  from dba_extents e
                 right outer join dba_data_files f on e.file_id = f.file_id
                                                  and e.tablespace_name = f.tablespace_name
                  join dba_tablespaces t on f.tablespace_name = t.tablespace_name
                 where f.tablespace_name = p_tbs
                   and t.status in ('READ ONLY', 'ONLINE')
                   and t.contents = 'PERMANENT'
                 group by f.file_name, f.tablespace_name, f.autoextensible)
    loop
        -- Check if there is free space to be removed
        if cur.free_k_size > 0 and
           cur.min_k_size + ceil((1 - p_full_pct) * cur.free_k_size) < cur.curr_k_size
        then
            if p_mode = 'SHRINK'
            then
                -- If the datafile is not autoextandable, make the datafile autoextendable and set the maximum size to
                -- the size we just resized from. This is done to protect the datafile from over-shrinking
                if cur.autoextensible = 'NO'
                then
                    cmd := 'alter database datafile ''' || cur.filename ||
                           '''  autoextend on next 16000k maxsize ' || trim(to_char(cur.curr_k_size)) || 'k';
                    do_command_protect(cmd, p_do_cmd);
                end if;

                -- resize the datafile to the new size
                cmd := 'alter database datafile ''' || cur.filename || ''' resize ' ||
                       to_char(cur.min_k_size + ceil((1 - p_full_pct) * cur.free_k_size)) || 'k';

                do_command_protect(cmd, p_do_cmd);
            end if;
            sum_mbytes_freed := sum_mbytes_freed + floor((p_full_pct) * cur.free_k_size);
        end if;
    end loop;

    if p_mode = 'N'
    then
        dbms_output.put_line('Total space that can be freed for tablespace ' || p_tbs || ' (' ||
                             tbs_status || '): ' || round(sum_mbytes_freed / 1024, 1) || 'M');
    end if;
end shrink_files_to_limit;