-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdb_size.sql
More file actions
58 lines (54 loc) · 1.34 KB
/
db_size.sql
File metadata and controls
58 lines (54 loc) · 1.34 KB
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
--db size report
col tablespace_name format 999,999,999 heading 'TABLESPACE|NAME'
col space_alloc format 999,999,999 heading 'MBytes|ALLOC'
col space_free format 999,999,999 heading 'MBytes|FREE'
col space_used format 999,999,999 heading 'MBytes|USED'
col pct_used format 999.9 heading 'PCT|USED'
col pct_free format 999.9 heading 'PCT|FREE'
set feedback off
drop table ts_usage
/
create table ts_usage (
tablespace_name varchar(75),
space_alloc number (30),
space_free number (30),
space_used number (30),
pct_used number (5,2),
pct_free number (5,2)
)
tablespace users
/
insert into ts_usage (tablespace_name, space_alloc)
select a.tablespace_name,
sum (a.bytes)/1024/1024 malloc
from dba_data_files a
group by a.tablespace_name
/
update ts_usage c
set space_free =
(select sum (b.bytes)/1024/1024 mfree
from dba_free_space b
where b.tablespace_name = c.tablespace_name
group by b.tablespace_name)
/
update ts_usage a
set space_used =
(select space_alloc-space_free
from ts_usage b
where b.tablespace_name = a.tablespace_name
)
/
update ts_usage a
set pct_used =
(select (space_used * 100) / space_alloc
from ts_usage b
where b.tablespace_name = a.tablespace_name
)
/
update ts_usage a
set pct_free =
(select (space_free * 100) / space_alloc
from ts_usage b
where b.tablespace_name = a.tablespace_name
)
/