/
tablespace_growth_rpt.sh
420 lines (419 loc) · 19.1 KB
/
tablespace_growth_rpt.sh
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
#!/bin/ksh -p
#============================================================================
# File: tablespace_growth_rpt.sh
# Type: UNIX korn-shell script
# Author: Austin Hackett
# Date: 16Mar2012
#
# Description:
#
# Adaptation of Graham Halsey's tablespace growth report to use the
# AWR views and include last week's growth high water mark and the
# utilization high water mark for yesterday.
#
# General shell script style adapated from Tim Gorman's "UNIX shell
# scripts for Oracle DBAs" at http://www.evdbt.com/tools.htm.
#
# For the specified database, report:
#
# 1. Current tablespace utilization
# 2. Growth yesterday
# 3. Growth for the previous seven days before that
# 4. Number of days left at the current growth rate
#
# IMPORTANT NOTE: This script uses the DBA_HIST_TBSPC_SPACE_USAGE
# AWR view. It seems that the view includes space occupied by the
# recycle bin in it's calculation of used space. This being the
# case, the report can be misleading if the recycle bin isn't empty.
# Oracle Support have opened a bug request on my behalf.
#
# Detailed description of each column in the report:
#
# Tablespace Name
#
# The name of the tablespace
#
# Current Total (GB)
#
# This is the size of the tablespace in gigbytes when
# the report was run (e.g. it is calculated using DBA_DATA_FILES)
#
# Current Used (GB)
#
# This is the amount of used space in gigabytes when the report
# was run (e.g. it is calculated by subtracting free space to the
# total size of the tablespace)
#
# Current Free (GB)
#
# This is the amount of free space in gigabytes when the report was
# run (e.g. it is calculated using DBA_FREE_SPACE)
#
# Current % Used
#
# This is the amount of free space when the report was run, expressed
# as a percentage
#
# Previous 7 Days Inc
#
# This is total growth in megabytes for the 7 days previous to
# yesterday. If yesterday.s growth is similar to, or exceeds the
# growth for the previous 7 days then it.s worth investigating
#
# Previous 7 Days Max Inc (MB)
#
# This is the highest daily rate of growth for the 7 days previous to yesterday.
# By comparing this to yesterday's growth, we can determine whether current growth
# is unusual for this tablespace
#
# Yesterday Inc (MB)
#
# This is yesterdays growth in megabytes
#
# Yesterday Max Used (GB)
#
# This is high water mark for space usage during the previous day
#
# Days Left
#
# Assuming yesterday.s growth rate, the number of days before space
# will be exhausted. Whenever this is less than 21 days, the tablespace
# is flagged with a "<<" symbol. This should be investigated, but it's
# important to check "Previous 7 Days Max Inc (MB)" and see if similar
# growth has occurred in the past. If it has and "Previous 7 Days Inc"
# is low, then the segments are probably subject to regular purges, and it
# should be safe to check growth again in 24 hours (unless "Days Left" is
# very low)
#
# Exit statuses:
# 0 normal succesful completion
# 1 ORACLE_SID not specified - user error
# 2 Cannot connect using "CONNECT / AS SYSDBA"
# 3 SQL*Plus failed to create "spool" file for report
# 4 SQL*Plus failed while generating report
#
# Modifications:
#
# 16Mar2012 Austin Hackett
#
# ps and grep are in /bin on Linux platforms, rather than /usr/bin
# as per Solaris. Added /bin to PATH
#============================================================================
Pgm=tablespace_growth_rpt
#
#----------------------------------------------------------------------------
# Set the correct PATH for the script...
#----------------------------------------------------------------------------
PATH=/bin:/usr/bin:/usr/local/bin; export PATH
#
#----------------------------------------------------------------------------
# Korn-shell function to be called multiple times in the script...
#----------------------------------------------------------------------------
notify_via_email() # ...use email to notify people...
{
cat << __EOF__ | mailx -s "$Pgm $Level $OraSid" dba@mycompany.com
$ErrMsg
$([ -f $SpoolFile ] && cat $SpoolFile)
__EOF__
} # ...end of shell function "notify_via_email"...
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID has been specified on the UNIX command-line...
#----------------------------------------------------------------------------
if (( $# != 1 ))
then
echo "Usage: $Pgm.sh ORACLE_SID; aborting..."
exit 1
fi
OraSid=$1
#
#----------------------------------------------------------------------------
# Verify that the database instance specified is "up"...
#----------------------------------------------------------------------------
Up=`ps -eaf | grep ora_pmon_${OraSid} | grep -v grep | awk '{print $NF}'`
if [[ -z $Up ]]
then
exit 0
fi
#
#----------------------------------------------------------------------------
# Verify that the ORACLE_SID is registered in the ORATAB file...
#----------------------------------------------------------------------------
dbhome $OraSid > /dev/null 2>&1
if (( $? != 0 ))
then
echo "$Pgm: \"$OraSid\" not local to this host; aborting..."
exit 1
fi
#
#----------------------------------------------------------------------------
# Set the Oracle environment variables for this database instance...
#----------------------------------------------------------------------------
export ORACLE_SID=$OraSid
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
unset ORAENV_ASK
#
#----------------------------------------------------------------------------
# Locate the "spool" file for the SQL*Plus report...
#----------------------------------------------------------------------------
SpoolFile=/tmp/${Pgm}_$ORACLE_SID.lst
#
#----------------------------------------------------------------------------
# Connect via SQL*Plus and produce the report...
#----------------------------------------------------------------------------
sqlplus -s /nolog << __EOF__ > /dev/null 2>&1
whenever oserror exit 2
whenever sqlerror exit 2
connect / as sysdba
whenever oserror exit 3
whenever sqlerror exit 4
set pages 1000 lines 200 trims on feed off veri off
break on report
compute sum of alloc_gb on report;
compute sum of used_gb on report;
compute sum of free_gb on report;
compute sum of last_week_mb on report;
compute sum of yesterday_mb on report;
col tsname for a30 head "Tablespace|Name"
col alloc_gb for 9,999,990.00 head "Current|Total (GB)"
col used_gb for 9,999,990.00 head "Current|Used (GB)"
col free_gb for 9,999,990.00 head "Current|Free (GB)"
col pct_used for 990.00 head "Current|% Used"
col last_week_mb for 999,990.00 head "Previous|7 Days Inc"
col last_week_max_mb for 999,990.00 head "Previous 7 Days|Max Inc (MB)"
col yesterday_mb for 999,990.00 head "Yesterday|Inc (MB)"
col yesterday_max_gb for 9,999,990.00 head "Yesterday|Max Used (GB)"
col days_left for 9,990 head "Days|Left"
col flag head ""
spool $SpoolFile
SELECT tsname,
alloc_gb,
used_gb,
free_gb,
pct_used,
last_week_mb,
last_week_max_mb,
yesterday_mb,
yesterday_max_gb,
LEAST (days_left, 999) days_left,
CASE WHEN days_left <= 20 THEN '<<' ELSE NULL END flag
FROM (SELECT curr.tsname,
curr.alloc / (1024 * 1024 * 1024) alloc_gb,
curr.used / (1024 * 1024 * 1024) used_gb,
curr.free / (1024 * 1024 * 1024) free_gb,
curr.pct_used,
last_week.growth / (1024 * 1024) last_week_mb,
last_week_max.growth / (1024 * 1024) last_week_max_mb,
yesterday.growth / (1024 * 1024) yesterday_mb,
yesterday_max.used / (1024 * 1024 * 1024) yesterday_max_gb,
CASE
WHEN yesterday.growth > 0 THEN curr.free / yesterday.growth
ELSE 999
END
days_left
FROM (SELECT df.tablespace_name tsname,
df.alloc,
df.alloc - NVL (fs.free, 0) used,
NVL (fs.free, 0) free,
( (df.alloc - NVL (fs.free, 0)) / df.alloc) * 100
pct_used
FROM ( SELECT tablespace_name, SUM (bytes) free
FROM dba_free_space
GROUP BY tablespace_name) fs,
( SELECT tablespace_name, SUM (bytes) alloc
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name = df.tablespace_name) curr,
( SELECT tsname, SUM (growth) growth
FROM (SELECT s.snap_id,
s.instance_number,
s.dbid,
ti.tsname,
NVL (
NVL (
su.tablespace_usedsize * ti.block_size,
0)
- LAG (
NVL (
su.tablespace_usedsize
* ti.block_size,
0),
1)
OVER (PARTITION BY ti.tsname, su.dbid
ORDER BY su.snap_id),
0)
growth
FROM dba_hist_snapshot s,
dba_hist_tbspc_space_usage su,
( SELECT dbid,
ts#,
tsname,
MAX (block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >=
TRUNC (SYSDATE - 8)
AND s.begin_interval_time <
TRUNC (SYSDATE - 1)
AND su.dbid = (SELECT dbid FROM v\$database)
AND s.instance_number =
(SELECT instance_number FROM v\$instance))
GROUP BY tsname) last_week,
( SELECT tsname, MAX (growth) growth
FROM ( SELECT TRUNC (begin_interval_time, 'DD')
begin_interval_time,
tsname,
SUM (growth) growth
FROM (SELECT s.snap_id,
s.instance_number,
s.dbid,
s.begin_interval_time,
ti.tsname,
NVL (
NVL (
su.tablespace_usedsize
* ti.block_size,
0)
- LAG (
NVL (
su.tablespace_usedsize
* ti.block_size,
0),
1)
OVER (
PARTITION BY ti.tsname,
su.dbid
ORDER BY su.snap_id),
0)
growth
FROM dba_hist_snapshot s,
dba_hist_tbspc_space_usage su,
( SELECT dbid,
ts#,
tsname,
MAX (block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >=
TRUNC (SYSDATE - 8)
AND s.begin_interval_time <
TRUNC (SYSDATE - 1)
AND su.dbid =
(SELECT dbid FROM v\$database)
AND s.instance_number =
(SELECT instance_number
FROM v\$instance))
GROUP BY TRUNC (begin_interval_time, 'DD'), tsname)
GROUP BY tsname) last_week_max,
( SELECT tsname, SUM (growth) growth
FROM (SELECT s.snap_id,
s.instance_number,
s.dbid,
ti.tsname,
NVL (
NVL (
su.tablespace_usedsize * ti.block_size,
0)
- LAG (
NVL (
su.tablespace_usedsize
* ti.block_size,
0),
1)
OVER (PARTITION BY ti.tsname, su.dbid
ORDER BY su.snap_id),
0)
growth
FROM dba_hist_snapshot s,
dba_hist_tbspc_space_usage su,
( SELECT dbid,
ts#,
tsname,
MAX (block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >=
TRUNC (SYSDATE - 1)
AND s.begin_interval_time < TRUNC (SYSDATE)
AND su.dbid = (SELECT dbid FROM v\$database)
AND s.instance_number =
(SELECT instance_number FROM v\$instance))
GROUP BY tsname) yesterday,
( SELECT TRUNC (s.begin_interval_time, 'DD')
begin_interval_time,
ti.tsname,
MAX (su.tablespace_usedsize * ti.block_size) used
FROM dba_hist_snapshot s,
( SELECT dbid,
ts#,
tsname,
MAX (block_size) block_size
FROM dba_hist_datafile
GROUP BY dbid, ts#, tsname) ti,
dba_hist_tbspc_space_usage su
WHERE s.dbid = su.dbid
AND s.snap_id = su.snap_id
AND su.dbid = ti.dbid
AND su.tablespace_id = ti.ts#
AND s.begin_interval_time >= TRUNC (SYSDATE - 1)
AND s.begin_interval_time < TRUNC (SYSDATE)
AND su.dbid = (SELECT dbid FROM v\$database)
AND s.instance_number =
(SELECT instance_number FROM v\$instance)
GROUP BY TRUNC (s.begin_interval_time, 'DD'), ti.tsname) yesterday_max
WHERE curr.tsname = last_week.tsname
AND curr.tsname = yesterday.tsname
AND curr.tsname = last_week_max.tsname
AND curr.tsname = yesterday_max.tsname
AND curr.tsname NOT LIKE 'UNDO%')
ORDER BY days_left ASC
/
exit success
__EOF__
#
#----------------------------------------------------------------------------
# If SQL*Plus exited with a failure status, then exit the script also...
#----------------------------------------------------------------------------
Rtn=$?
if (( $Rtn != 0 ))
then
case "$Rtn" in
2) ErrMsg="$Pgm: Cannot connect using \"CONNECT / AS SYSDBA\"";;
3) ErrMsg="$Pgm: spool of report failed";;
4) ErrMsg="$Pgm: query in report failed" ;;
esac
notify_via_email
exit $Rtn
fi
#
#----------------------------------------------------------------------------
# Send the report via email...
#----------------------------------------------------------------------------
ErrMsg="Tablespace Growth Report: $(date)"
grep "<<" $SpoolFile > /dev/null
if (( $? == 0 ))
then
Level=WARNING
else
Level=INFO
fi
notify_via_email
rm -f $SpoolFile > /dev/null 1>&2
#
#----------------------------------------------------------------------------
# Return the exit status from SQL*Plus...
#----------------------------------------------------------------------------
exit 0