看准网 · 题目详情

如何查询表空间总量,剩余,已用情况




1:查询ORACLE数据库

SELECT F.TABLESPACE_NAME 表空间,

(T.TOTAL_SPACE – F.FREE_SPACE) 已用表空间,

F.FREE_SPACE 剩余表空间,

T.TOTAL_SPACE 总表空间,

(ROUND((F.FREE_SPACE / T.TOTAL_SPACE) * 100)) || ‘% ‘ 剩余表空间百分比

FROM (SELECT TABLESPACE_NAME,

ROUND(SUM(BLOCKS *

(SELECT VALUE / 1024

FROM V$PARAMETER

WHERE NAME = ‘db_block_size’) / 1024)) FREE_SPACE

FROM DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME) F,

(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES / 1048576)) TOTAL_SPACE

FROM DBA_DATA_FILES

GROUP BY TABLESPACE_NAME) T

WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME

2:查询DB2数据库

select * from table(snapshot_tbs_cfg(‘STUDY’,-1));

其中STUDY是数据库名

-1 表示当前活动的数据库分区

—————————————————————————-

执行命令:(该命令只检查DMS即数据库类型的表空间)

select

substr(varchar(t.SNAPSHOT_TIMESTAMP),1,13) as time ,

t.TABLESPACE_ID,

substr(t.TABLESPACE_NAME,1,30) as TABLESPACE_NAME,

t.TOTAL_PAGES*t.PAGE_SIZE/1024/1024 as TOTAL_MB_SIZE ,

t.FREE_PAGES*t.PAGE_SIZE/1024/1024 as FREE_MB_SIZE ,

decimal(t.USED_PAGES,31,3)/t.TOTAL_PAGES*100 as USAGE_PERCENT

from table(snapshot_tbs_cfg(‘TPAHIS’,-2)) as t

where t.TABLESPACE_TYPE=0

order by t.TABLESPACE_ID;
0 0 964

评论(0)

您还可以输入3000

首页
顶部

帮助 反馈 登录 注册 导航

Copyright © 2020 kanzhun.com
看准网 版权所有

违法和不良公司举报邮箱:kz-report@kanzhun.com 举报电话:400-612-9066

客服邮箱:support@kanzhun.com