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
|
drop function occupation_filtrage
/
drop type occupation_table_type
/
drop type occupation_type
/
create or replace type occupation_type
as object
( equipement varchar2(30),
occupation number(5,2)
)
/
create or replace type occupation_table_type
as table of occupation_type
/
create or replace
function occupation_filtrage
return occupation_table_type
authid CURRENT_USER
PIPELINED
as
pragma autonomous_transaction;
type rc is ref cursor;
l_table rc;
l_partition rc;
l_unf number;
l_unfb number;
l_fs1 number;
l_fs1b number;
l_fs2 number;
l_fs2b number;
l_fs3 number;
l_fs3b number;
l_fs4 number;
l_fs4b number;
l_full number;
l_fullb number;
l_used number;
l_taille number;
l_sql long;
l_data occupation_table_type := occupation_table_type();
l_equipement varchar2(30);
l_table_name varchar2(30);
l_partition_name varchar2(30);
l_subpartition_name varchar2(30);
begin
l_sql := 'SELECT DISTINCT RTRIM (LTRIM (t.tablespace_name, ''TAB_FILTRE_''), ''_L''),
s.table_name,
s.partition_name,v.taille
FROM dba_tablespaces t,all_tab_subpartitions s,
(SELECT t.tablespace_name,
SUM (DECODE (d.BYTES, NULL, 0, d.BYTES)) as taille
FROM dba_data_files d, dba_tablespaces t
WHERE t.tablespace_name = d.tablespace_name
AND t.tablespace_name LIKE ''TAB_FILTRE%''
GROUP BY t.tablespace_name) v
WHERE s.table_owner=''DB_SPOT''
AND s.tablespace_name=t.tablespace_name
AND s.tablespace_name=v.tablespace_name
AND t.tablespace_name LIKE ''TAB_FILTRE%''';
execute immediate 'alter session set cursor_sharing=force';
open l_table for l_sql;
execute immediate 'alter session set cursor_sharing=exact';
loop
fetch l_table into l_equipement,l_table_name,l_partition_name,l_taille;
exit when l_table%notfound;
begin
l_sql := 'SELECT subpartition_name
FROM all_tab_subpartitions
WHERE table_owner=''DB_SPOT''
AND table_name=''' || upper(l_table_name) || '''
AND partition_name=''' || upper(l_partition_name) || '''';
execute immediate 'alter session set cursor_sharing=force';
open l_partition for l_sql;
execute immediate 'alter session set cursor_sharing=exact';
l_used :=0;
loop
fetch l_partition into l_subpartition_name;
exit when l_partition%notfound;
begin
dbms_space.space_usage(segment_owner => 'DB_SPOT',
segment_name => l_table_name,
partition_name => upper(l_subpartition_name),
segment_type => 'TABLE SUBPARTITION',
unformatted_blocks => l_unf,
unformatted_bytes => l_unfb,
fs1_blocks => l_fs1, fs1_bytes => l_fs1b,
fs2_blocks => l_fs2, fs2_bytes => l_fs2b,
fs3_blocks => l_fs3, fs3_bytes => l_fs3b,
fs4_blocks => l_fs4, fs4_bytes => l_fs4b,
full_blocks => l_full, full_bytes => l_fullb);
l_used:= l_used + l_fullb + (0.25*l_fs4b) +(0.5*l_fs3b) + (0.75*l_fs2b) + l_fs1b;
exception
when others then null;
end;
end loop;
close l_partition;
pipe row (occupation_type(equipement => l_equipement, occupation =>round(l_used/l_taille*100,2)));
exception
when others then null;
end;
end loop;
close l_table;
return;
end;
/ |
