Database Reference
In-Depth Information
With this data available to me in the
df
external table, it is easy to start working on the query. All I need to do is
join
df
to
DBA_DATA_FILES
with a join condition that matches the longest mount point possible to each file name.
My single-query solution to the issue in this example is shown in the next bit of code. Here's what's happening on
some of the lines:
EODA@ORA12CR1> with fs_data
2 as
3 (select /*+ materialize */ *
4 from df
5 )
6 select mount,
7 file_name,
8 bytes/1024/1024 mbytes,
9 tot_bytes/1024/1024 tot_mbytes,
10 avail_bytes/1024/1024 avail_mbytes,
11 case
12 when 0.2 * tot_bytes < avail_bytes
13 then 'OK'
14 else 'Short on disk space'
15 end status
16 from (
17 select file_name, mount, avail_bytes, bytes,
18 sum(bytes) over
19 (partition by mount) tot_bytes
20 from (
21 select a.file_name,
22 b.mount,
23 b.avail*1024 avail_bytes, a.bytes,
24 row_number() over
25 (partition by a.file_name
26 order by length(b.mount) DESC) rn
27 from dba_data_files a,
28 fs_data b
29 where a.file_name
30 like b.mount || '%'
31 )
32 where rn = 1
33 )
34 order by mount, file_name
35 /
MOUNT FILE_NAME MBYTES TOT_MBYTES AVAIL_MBYTES STATUS
------- ---------------------------------------- ---------- ---------- ------------ --------
/ora01 /ora01/dbfile/ORA12CR1/cia_data_01.dbf 1024 93486 240989.959 OK
/ora01 /ora01/dbfile/ORA12CR1/config_tbsp_1_01.dbf 1500 93486 240989.959 OK
...
/ora04 /ora04/dbfile/ORA12CR1/dim_data08.dbf 30720 136202 279289.674 OK
/ora04 /ora04/dbfile/ORA12CR1/dim_data_02.dbf 30720 136202 279289.674 OK
48 rows selected.
