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......
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:-) maybe it's in SQL developer somewhere..... Oracle rarely miss a trick.....,
ReplyDelete