Before we go to the subject of this blog post, let me explain why the post have the #JoelKallmanDay tag!
Since 2016, Oracle Community have a day where we push out content on the same day and try to get a community buzz! This is organized by Tim Hall from Oracle Base!
The name has changed in 2021 to “Joel Kallman Day” to honor everything Joel Kallman did for the community!
You can read it more here:
https://oracle-base.com/blog/2025/09/24/joel-kallman-day-2025-announcement
Let’s go!
—
A couple of weeks ago, I was asked by a client:
This is a good question that clients always ask.
In the companies where I worked as a consultant, some DBAs built scripts to load metrics into customized history tables where they store useful information.
But what about the environments where you don’t have this custom solution built?
The idea was to get the growth by month for the top segments.
I decided to use the view DBA_HIST_SEG_STAT, as we have the SNAP_ID and OBJ# columns. We can also join this view with others like DBA_OBJECTS, DBA_SEGMENTS, and DBA_HIST_SNAPSHOT.
The great thing about DBA_HIST_SEG_STAT is that even though it’s a DBA_HIST view, this one is not part of the Diagnostic Pack. So, you don’t need to worry about buying the Diagnostic Pack license if you don’t have it, as stated in the screenshot below:
You can read more about it here:
Database Licensing Information User Manual
As we are going to use DBA_HIST_SEG_STAT, and we have the SNAP_ID column (which will be joined with DBA_HIST_SNAPSHOT to get the size by month), please keep in mind that the amount of history available will depend on the AWR configuration in your environment. So, the longer the retention, the more space you will consume from the SYSAUX tablespace.
However, sometimes client has not changed the AWR configuration and you only have the default (which is 8 days), you can read about it here:
Database 2 Day + Performance Tuning Guide
So, even though DBA_HIST_SEG_STAT is great, it might not be feasible for you if you don’t want to retain AWR data for a long period of time — in this case, building a custom solution would be better for you.
But… this blog post is not about building a custom solution for it.
This blog post is to show how to get the requested information using the out-of-the-box data available to us.
I created a script called seggrowth.sql.
I am not interested in getting the growth of indexes, but you are free to modify the script to include indexes if you want.
You will also notice that I am only retrieving TABLES and LOBSEGMENTS, but you can change it to include TABLE PARTITIONS (and also SUBPARTITIONS) if you prefer. I mean, you are free to change it the way you want! 🙂
In this script, we’re getting only the top 20 segments with the most growth by month.
OK, let’s go — you can download the script from here:
The execution is pretty simple:
@seggrowth.sql
RNK OWNER OBJECT_NAME START_M INCREASE_GBYTES CURRENT_SIZE_GB PCT_INCREASE
--- --------------- ------------------------------ ------- --------------- --------------- ------------
1 SAPSR3 SYS_LOB0000121246C00007$$ 2025-04 856.41 4,333.98 24.63
2025-05 981.96 5,315.94 22.66
2025-06 1,038.75 6,354.69 19.54
2025-07 1,109.27 7,463.96 17.46
2025-08 1,236.82 8,700.78 16.57
2025-09 1,142.45 9,843.23 13.13
2025-10 277.90 10,121.13 2.82
2 SAPSR3 ZTPETBIT_LOG008 2025-04 151.81 7,616.93 2.03
2025-05 239.38 7,856.30 3.14
2025-06 283.25 8,139.55 3.61
2025-07 281.81 8,421.37 3.46
2025-08 274.94 8,696.30 3.26
2025-09 273.64 8,969.94 3.15
2025-10 54.78 9,024.73 .61
3 SAPSR3 SYS_LOB0000504452C00006$$ 2025-08 864.26 863.47
2025-09 106.41 969.88 12.32
2025-10 9.41 979.29 .97
4 SAPSR3 /1FE/0TTAG4IT00 2025-04 60.16 1,851.44 3.36
2025-05 175.97 2,027.40 9.50
2025-06 157.67 2,185.07 7.78
2025-07 132.07 2,317.14 6.04
2025-08 152.57 2,469.71 6.58
2025-09 120.74 2,590.45 4.89
2025-10 11.65 2,602.10 .45
5 SAPSR3 SXMSCLUR 2025-07 764.78 383.08
2025-08 8.23 391.32 2.15
2025-09 2.83 394.14 .72
2025-10 4.16 398.30 1.06
6 SAPSR3 DFKKCOD 2025-04 33.39 3,105.63 1.09
2025-05 93.75 3,199.38 3.02
2025-06 99.90 3,299.28 3.12
2025-07 113.67 3,412.95 3.45
2025-08 119.39 3,532.34 3.50
2025-09 107.80 3,640.14 3.05
2025-10 21.96 3,662.10 .60
7 SAPSR3 BDSCONT3 2025-04 57.29 3,097.01 1.88
2025-05 88.82 3,185.83 2.87
2025-06 94.00 3,279.83 2.95
2025-07 110.47 3,390.30 3.37
2025-08 99.48 3,489.78 2.93
2025-09 108.16 3,597.94 3.10
2025-10 21.32 3,619.26 .59
8 SAPSR3 SYS_LOB0000492922C00009$$ 2025-07 476.90 239.20
2025-08 .76 239.96 .32
9 SAPSR3 SOC3 2025-07 452.56 448.93
2025-08 5.20 454.14 1.16
2025-09 4.83 458.96 1.06
2025-10 1.13 460.09 .25
10 SAPSR3 SXMSCLUP 2025-04 44.90 1,024.09 4.59
2025-05 17.91 1,042.01 1.75
2025-06 37.11 1,079.12 3.56
2025-07 53.23 1,132.35 4.93
2025-08 33.81 1,166.16 2.99
2025-09 45.37 1,211.53 3.89
2025-10 16.43 1,227.96 1.36
11 SAPSR3 SYS_LOB0000461853C00007$$ 2025-07 185.25 194.44 2,016.54
12 SAPSR3 DFKKINVBILL_H 2025-07 123.43 123.37
2025-08 8.33 131.69 6.75
2025-09 7.14 138.83 5.42
2025-10 .69 139.52 .50
13 SAPSR3 DFKKINV_TRIG 2025-07 101.32 101.26
2025-08 7.50 108.76 7.41
2025-09 6.38 115.14 5.86
2025-10 .69 115.83 .60
14 SAPSR3 DFKKINVBILL_I 2025-04 14.94 466.45 3.31
2025-05 25.13 491.58 5.39
2025-06 23.63 515.20 4.81
2025-07 21.63 536.83 4.20
2025-08 22.56 559.39 4.20
2025-09 20.01 579.40 3.58
2025-10 2.66 582.06 .46
15 SAPSR3 DFKKOP 2025-04 11.61 1,825.52 .64
2025-05 16.84 1,842.36 .92
2025-06 17.40 1,859.76 .94
2025-07 20.67 1,880.43 1.11
2025-08 19.01 1,899.44 1.01
2025-09 19.96 1,919.40 1.05
2025-10 5.33 1,924.73 .28
16 SAPSR3 FKKMAZE 2025-04 11.06 1,416.36 .79
2025-05 16.94 1,433.30 1.20
2025-06 17.00 1,450.30 1.19
2025-07 21.25 1,471.55 1.47
2025-08 18.50 1,490.05 1.26
2025-09 18.28 1,508.33 1.23
2025-10 3.63 1,511.95 .24
17 SAPSR3 ZTPTBITUPDATE 2025-04 2.23 15.79 16.43
2025-05 23.88 39.66 151.20
2025-06 21.08 60.75 53.16
2025-07 19.25 79.99 31.68
2025-08 12.62 92.62 15.78
2025-09 7.66 100.28 8.27
2025-10 1.69 101.97 1.68
18 SAPSR3 DFKKLOCKS 2025-07 77.87 77.68
2025-08 1.27 78.95 1.64
2025-09 .31 79.26 .40
19 SAPSR3 BDOC_TRACK 2025-09 76.91 76.47
2025-10 .13 76.60 .16
20 SAPSR3 SBCMCONT1 2025-04 7.04 50.13 16.33
2025-05 13.71 63.84 27.36
2025-06 11.53 75.37 18.07
2025-07 14.33 89.71 19.01
2025-08 14.48 104.18 16.14
2025-09 13.29 117.48 12.76
2025-10 3.37 120.85 2.87
As you noticed, I have done this in a environment running SAP ECC. We observed that there is a LOB increasing in a rate of ~1TB/month. We already discussed with app team and they are checking on it.
Hope this helps!
Peace!
Vinicius
