当前位置：Gxlcms >
数据库问题 >
zbb20170816 oracle Oracle 查看表空间、数据文件的大小及使用情况sql语句
zbb20170816 oracle Oracle 查看表空间、数据文件的大小及使用情况sql语句
时间：2021-07-01 10:21:17
帮助过：9人阅读
--1G=
1024MB
--1M=
1024KB
--1K=
1024Bytes
--1M=
11048576Bytes
--1G=
1024*11048576Bytes=
11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free)
"表空间使用大小",
total / (
1024 *
1024 *
1024)
"表空间大小(G)",
free / (
1024 *
1024 *
1024)
"表空间剩余大小(G)",
(total - free) / (
1024 *
1024 *
1024)
"表空间使用大小(G)",
round((total - free) / total,
4) *
100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name =
b.tablespace_name;
--
数据文件
select b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes /
1024 /
1024 大小M,
(b.bytes - sum(nvl(a.bytes,
0))) /
1024 /
1024 已使用M,
substr((b.bytes - sum(nvl(a.bytes,
0))) / (b.bytes) *
100,
1,
5) 利用率
from dba_free_space a,
dba_data_files b where a.file_id =
b.file_id group by b.tablespace_name,
b.file_name,
b.bytes order by b.tablespace_name;
--
1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (
1024 *
1024)),
0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name =
d.tablespace_name
GROUP BY t.tablespace_name;
--
2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (
1024 *
1024),
0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--
3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent /
1024) initialextent,
(next_extent /
1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+
)
ORDER BY segment_name;
--
4、查看控制文件
SELECT NAME FROM v$controlfile;
--
5、查看日志文件
SELECT MEMBER FROM v$logfile;
--
6、查看表空间的使用情况
SELECT SUM(bytes) / (
1024 *
1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes *
100) / a.bytes
"% USED ",
(c.bytes *
100) / a.bytes
"% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name =
b.tablespace_name
AND a.tablespace_name =
c.tablespace_name;
--
7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*
) count#
FROM all_objects
GROUP BY owner, object_type, status;
--
8、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1,
6) =
‘Oracle‘;
--
9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
zbb20170816 oracle Oracle 查看表空间、数据文件的大小及使用情况sql语句
标签：database space table 控制文件 stat ack 名称 rac blog
