如何查看oracle剩余表空间
发布网友
发布时间：2022-04-22 21:45
共1个回答
热心网友
时间：2022-05-01 09:14
select
tablespace_name,sum(bytes)/1024/1024
free_space
from
dba_free_space
group
by
tablespace_name;
//详细查看表空间使用状况，包括总大小，使用空间，使用率，剩余空间
select
t.*
from
(select
d.tablespace_name,
space
"sum_space(m)",
blocks
sum_blocks,
space
-
nvl(free_space,
0)
"used_space(m)",
round((1
-
nvl(free_space,
0)
/
space)
*
100,
2)
"used_rate(%)",
free_space
"free_space(m)"
from
(select
tablespace_name,
round(sum(bytes)
/
(1024
*
1024),
2)
space,
sum(blocks)
blocks
from
dba_data_files
group
by
tablespace_name)
d,
(select
tablespace_name,
round(sum(bytes)
/
(1024
*
1024),
2)
free_space
from
dba_free_space
group
by
tablespace_name)
f
where
d.tablespace_name
=
f.tablespace_name(+)
union
all
--if
have
tempfile
select
d.tablespace_name,
space
"sum_space(m)",
blocks
sum_blocks,
used_space
"used_space(m)",
round(nvl(used_space,
0)
/
space
*
100,
2)
"used_rate(%)",
space
-
used_space
"free_space(m)"
from
(select
tablespace_name,
round(sum(bytes)
/
(1024
*
1024),
2)
space,
sum(blocks)
blocks
from
dba_temp_files
group
by
tablespace_name)
d,
(select
tablespace,
round(sum(blocks
*
8192)
/
(1024
*
1024),
2)
used_space
from
v$sort_usage
group
by
tablespace)
f
where
d.tablespace_name
=
f.tablespace(+))
t
order
by
"used_rate(%)"
desc;
select
tablespace_name,
sum
(
blocks
)
as
free_blk
,
trunc
(
sum
(
bytes
)
/
(1024*1024)
)
as
free_m,
max
(
bytes
)
/
(1024)
as
big_chunk_k,
count
(*)
as
num_chunks
from
dba_free_space
group
by
tablespace_name;
