הנה שליפה קצרה שאני תמיד מחפש אותה במצבי “חרום” אבל בסוף מוצא את עצמי כותב אותה מחדש…

הבעיה: אני רוצה לפנות מקום פנוי ב-tablespace אבל ה-high water mark חסום על ידי איזשהו אובייקט (אני משתמש בסקריפט הזה לפינוי מקום ב-tablespace). בדרך כלל המחשבה הבאה שלי זה "נו? אז מה חוסם אותי עכשיו?".

הפתרון: השליפה הבאה. היא מביאה את ה- tablespace map מסודר לפי קבצים ומסמנת גם את הבלוקים התפוסים (ועל ידי מה) וגם את הבלוקים הפנויים.

col OWNER          format a40;
col SEGMENT_NAME   format a40;
col PARTITION_NAME format a40;
 
define m_tablespace = 'USERS'
 
create table tablespace_map_free as
select
    file_id,
    block_id,
    block_id + blocks - 1   end_block,
    owner,
    segment_name,
    partition_name,
    segment_type
from
    dba_extents
where
    tablespace_name = '&m_tablespace'
union all
select 
    file_id,
    block_id,
    block_id + blocks - 1   end_block,
    'free'          owner,
    'free'          segment_name,
    null            partition_name,
    null            segment_type
from
    dba_free_space
where
    tablespace_name = '&m_tablespace'
order by
    1,2 desc
/

והתוצאה:

   FILE_ID   BLOCK_ID  END_BLOCK OWNER                                    SEGMENT_NAME                             PARTITION_NAME                           SEGMENT_TYPE
---------- ---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------
         9        256        639 free                                     free
         9        248        255 ZOHAR                                    T2_IDX                                                                            INDEX
         9        240        247 ZOHAR                                    T2                                                                                TABLE
         9        232        239 ZOHAR                                    T1                                                                                TABLE
         9        224        231 free                                     free
         9        216        223 ZOHAR                                    A                                                                                 TABLE
         9        208        215 ZOHAR                                    A                                                                                 TABLE
         9        200        207 ZOHAR                                    A                                                                                 TABLE
         9        192        199 ZOHAR                                    A                                                                                 TABLE
         9        184        191 ZOHAR                                    A                                                                                 TABLE
         9        176        183 ZOHAR                                    A                                                                                 TABLE
         9        168        175 ZOHAR                                    A                                                                                 TABLE
         9        160        167 ZOHAR                                    A                                                                                 TABLE
         9        152        159 ZOHAR                                    A                                                                                 TABLE
         9        144        151 ZOHAR                                    A                                                                                 TABLE
         9        136        143 ZOHAR                                    A                                                                                 TABLE
         9        128        135 ZOHAR                                    A                                                                                 TABLE
 
17 rows selected.

שימושי.