Script není moje práce, ale převzal jsem ho z internetu.
SQL> clear columns
columns cleared
SQL> set verify off
SQL> col tot_mon noprint new_value range
SQL> — Compute how many months have gone since the database was created
SQL> select ceil(months_between(sysdate, created)) tot_mon
2 from v$database
3 /
SQL> col maxinc noprint new_value max_inc SQL> -- Compute the maximum number of times a file created in 'autoextend' mode SQL> -- has grown SQL> select max(round((d.bytes - d.create_bytes) / f.inc / d.block_size)) maxinc 2 from sys.file$ f, 3 v$datafile d 4 where f.inc > 0 5 and f.file# = d.file# 6 and d.bytes > d.create_bytes 7 /
SQL> col GB format 9999990.00
SQL> col volume format A60
SQL> --
SQL> -- The factorized subquery tries to build a 'size history' for all
SQL> -- files that are in autoextend mode. It generates a list fo file#,
SQL> -- associated to 'prior size' and 'new size'. The snag is that a crucial
SQL> -- element is missing: the date when autoextension was triggered.
SQL> -- To fill the blanks as best as we can, we try to get the creation date
SQL> -- of the oldest data or index segment the segment header of which is
SQL> -- physically located in the new extension.
SQL> --
SQL> with extended_files as
2 (select file#,
3 nvl(lag(file_size, 1) over (partition by file#
4 order by file_size), 0)
5 prior_size,
6 file_size,
7 block_size
8 from (select f.file#,
9 f.create_blocks + x.rn * f.inc file_size,
10 f.block_size
11 from (select f.file#,
12 d.create_bytes / d.block_size create_blocks,
13 f.inc,
14 d.bytes / d.block_size blocks,
15 d.block_size
16 from sys.file$ f,
17 v$datafile d
18 where f.inc > 0
19 and f.file# = d.file#
20 and d.bytes > d.create_bytes
21 and rownum > 0) f,
22 (select rownum - 1 rn
23 from dual
24 connect by level <= &max_inc + 1) x
25 where (f.create_blocks + x.rn * f.inc) <= f.blocks))
26 select "MONTH",
27 round(cumul/1024, 2) GB,
28 -- Draw a histogram
29 rpad('=', round(60 * cumul / current_M), '=') volume
30 from (select to_char(cal.mon, 'MON-YYYY') "MONTH",
31 sum(nvl(evt.M, 0)) over (order by cal.mon range unbounded
32 preceding) cumul,
33 tot.curr_M current_M,
34 cal.mon
35 from -- current database size (data size)
36 (select round(sum(bytes)/1024/1024) curr_M
37 from v$datafile) tot,
38 -- all the months since the database was created
39 (select add_months(trunc(sysdate, 'MONTH'), -rn) mon
40 from (select rownum - 1 rn
41 from dual
42 connect by level <= &range)) cal,
43 -- all the months when the size of the database changed
44 (select size_date,
45 round(sum(bytes)/1024/1024) M
46 from (-- files in autoextend mode
47 select file#, max(bytes) bytes, size_date
48 from (select file#, bytes, trunc(min(ctime), 'MONTH')
49 size_date
50 -- Get the oldest creation date of tables or indexes
51 -- that are located in extensions.
52 -- Other segment types are ignored.
53 from (select s.file#,
54 f.file_size * f.block_size bytes,
55 o.ctime
56 from sys.seg$ s,
57 extended_files f,
58 sys.tab$ t,
59 sys.obj$ o
60 where s.file# = f.file#
61 and s.type# = 5
62 and s.block# between f.prior_size and
63 f.file_size
64 and s.file# = t.file#
65 and s.block# = t.block#
66 and t.obj# = o.obj#
67 union all
68 select s.file#,
69 f.file_size * f.block_size bytes,
70 o.ctime
71 from sys.seg$ s,
72 extended_files f,
73 sys.ind$ i,
74 sys.obj$ o
75 where s.file# = f.file#
76 and s.type# = 6
77 and s.block# between f.prior_size and
78 f.file_size
79 and s.file# = i.file#
80 and s.block# = i.block#
81 and i.obj# = o.obj#)
82 group by file#, bytes)
83 group by file#, size_date
84 union all
85 -- files that are not in autoextend mode
86 select d.file#,
87 d.create_bytes bytes,
88 trunc(d.creation_time, 'MONTH') size_date
89 from v$datafile d,
90 sys.file$ f
91 where nvl(f.inc, 0) = 0
92 and f.file# = d.file#)
93 group by size_date) evt
94 where evt.size_date (+) = cal.mon)
95 order by mon
96 /
MONTH GB VOLUME -------- ----------- ------------------------------------------------------------ JUN-2013 1.62 JUL-2013 1.62 AUG-2013 1.62 SEP-2013 2.33 OCT-2013 2.33 NOV-2013 3.02 DEC-2013 3.02 JAN-2014 3.03 FEB-2014 8.54 = MAR-2014 18.56 === APR-2014 52.62 ========= MAY-2014 99.05 ================ JUN-2014 122.64 ==================== JUL-2014 182.61 ============================== AUG-2014 192.21 =============================== SEP-2014 235.83 ======================================= OCT-2014 262.05 =========================================== NOV-2014 294.86 ================================================ DEC-2014 294.86 ================================================