Whats at the end of my datafile?



Sometimes space pressure necessitates frantically trying to identify any files that can be shrunk in order to free up disk space on the filesystem - now this can of course just be done by trial and error - just keep trying to shrink the files and the error tells you how much under the minimum size you actually tried to go.

I wrote a little script to just display what is at the end of a file - is it free space of is there some extent of a segment sat there preventing you shrinking the file.

This is the code

select file_id,blocks,owner,segment_name,segment_type,src
from (with exts as (select file_id,block_id,blocks,'n/a' as owner,'n/a' as segment_name,'n/a' as segment_type,'FREE' as src from dba_free_space
union
select file_id,block_id,blocks,owner,segment_name,segment_type,'USED' as src from dba_extents)
select file_id,block_id,blocks,owner,segment_name,segment_type,src,max(block_id) over (partition by file_id) as last_extent
from exts)
where last_extent=block_id;

Which gives output similar to this

         1      11269 n/a              n/a                              n/a                                                    FREE
         2       7944 n/a              n/a                              n/a                                                    FREE
         3        120 n/a              n/a                              n/a                                                    FREE
         4          8 TP_INT           SYS_C0033531                     INDEX                                                  USED
         5      12672 n/a              n/a                              n/a                                                    FREE
         6      12800 n/a              n/a                              n/a                                                    FREE
         7          8 TP_INT           TP_DATA_BUCKET_REPORT_MNTH_IDX   INDEX                                                  USED
         8          8 TP_INT_DEMO      FUEL_PRICE_PK                    INDEX                                                  USED
         9          8 TP_INT_DEMO      MSID_SHADOW_PRICE_IDX            INDEX                                                  USED
        10        128 TP_INT_DEMO      MODEL_SCEN_MODEL_SCEN_DATA_IDX   INDEX                                                  USED
        11          8 TP_INT_DEMO      MSID_FUEL_PRICE_IDX              INDEX                                                  USED
        12        768 TP_INT_DEMO      MODEL_SCEN_MODEL_SCEN_DATA_IDX   INDEX                                                  USED
        13          8 TP_INT_DEMO      MSID_FUEL_PRICE_IDX              INDEX                                                  USED
        14          8 TP_INT_DEMO      MSID_FUEL_PRICE_IDX              INDEX                                                  USED
        15      11136 n/a              n/a                              n/a                                                    FREE
        16      12672 n/a              n/a                              n/a                                                    FREE
        17     233472 n/a              n/a                              n/a                                                    FREE
        18          8 TP_INT_DEMO      MSID_FUEL_PRICE_IDX              INDEX                                                  USED
        19          8 TP_INT_DEMO      MS_M_TS_MODEL_SCEN_DATA_IDX      INDEX                                                  USED
        20          8 TP_INT           MODEL_SCENARIO_TIMESLOT_IDX      INDEX                                                  USED
        21     229760 n/a              n/a                              n/a                                                    FREE
        22      64000 n/a              n/a                              n/a                                                    FREE
        23     130560 n/a              n/a                              n/a                                                    FREE
        24          8 TP_INT_DEMO      MODEL_SCENARIO_TIMESLOT_IDX      INDEX                                                  USED
        25          8 TP_INT_DEMO      UNIT_AUDIT                       TABLE                                                  USED
        26        120 n/a              n/a                              n/a                                                    FREE
        27        120 n/a              n/a                              n/a                                                    FREE
        28          8 TP_INT_DEMO      SHADOW_PRICE_PK                  INDEX                                                  USED
        29          8 TP_INT_DEMO      FUEL_PRICE_PK                    INDEX                                                  USED
        30        120 n/a              n/a                              n/a                                                    FREE
        31          8 TP_INT_DEMO      MODEL_SCENARIO_TIMESLOT_IDX      INDEX                                                  USED
        32          8 TP_INT_DEMO      MODEL_SCENARIO_TIMESLOT_IDX      INDEX                                                  USED
        33        128 SYSTEM           TEST                             TABLE                                                  USED
        34       6520 n/a              n/a                              n/a                                                    FREE
        35          8 NJS              MS_M_TS_MODEL_SCEN_DATA_IDX      INDEX                                                  USED
        36          8 NJS              MS_M_TS_MODEL_SCEN_DATA_IDX      INDEX                                                  USED
        37        128 NJS              MODEL_SCEN_MODEL_SCEN_DATA_IDX   INDEX                                                  USED
        38        128 NJS              MODEL_SCEN_MODEL_SCEN_DATA_IDX   INDEX                                                  USED
        39      20664 n/a              n/a                              n/a                                                    FREE
        40     213376 n/a              n/a                              n/a                                                    FREE
        41     216576 n/a              n/a                              n/a                                                    FREE
        42      29056 n/a              n/a                              n/a                                                    FREE
        43     498560 n/a              n/a                              n/a                                                    FREE


So here for example file 43 has 498560 free blocks at the end of it so could be shrunk by that size (498560*8K in this case - which is about 4GB) whereas file 35 has a small index extent at the end of it so cannot be shrunk - though i guess the index could be rebuilt and then a shrink attempted.

You could easily built a script to shrink the files based on some of the logic above - it just needs some more added to calculate the size reduction and build the later command.

It would be nice if oracle just brought out a command that just let you shrink the file to the minimum size, i don't think they did yet - maybe they stuck it in 12c and i missed it though - with 500 new features there is a lot to take in......

2 comments:

  1. This is something Toad was able to calculate already years ago, when you tried to shrink a datafile it told you what the minimum size could be, since it haven't been built in I guess it is an implicit sales booster from Oracle to Quest. ;-)

    ReplyDelete
  2. :-) maybe it's in SQL developer somewhere..... Oracle rarely miss a trick.....,

    ReplyDelete