转：
Oracle数据库查看表空间sql语句
2018-09-03 15:49:51 兰海泽 阅读数 6212
版权声明：本文为博主原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。
1、oracle查看表空间当前用户
select
username,default_tablespace
from user_users;- 1
- 2
- 3
2、oracle 查看表所属表空间
SELECT
TABLE_NAME,TABLESPACE_NAME
FROM USER_TABLES
where TABLE_NAME = 'test_table'- 1
- 2
- 3
- 4
3、oracle查看表空间大小(单位不是GB)
SELECT
a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
( total - free ) "表空间使用大小",
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- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
4、oracle查看表空间大小 -单位GB
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;- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17