检测数据库大小的sql
来源:互联网 发布:sql显示男生前十 编辑:程序博客网 时间:2024/06/13 22:58
DBA的日常功能SQL之一,绝对原创。
检测数据库大小的SQL,一个SQL搞定。
with tmp_datafile as (
select *
from (
--表空间的数据文件的使用率
select 'DATAFILE' as gather_level,
(select dbid from v$database) AS DATABASE_ID,
(select name from v$database) AS DATABASE_NAME,
dd.tablespace_name,
dd.tablespace_name || '-' || dd.file_name as datafile_name,
dd.AUTOEXTENSIBLE,
nvl(round(sum(case when dd.maxbytes=0 then dd.BYTES else dd.MAXBYTES end) / 1024 / 1024 / 1024, 3),0) maxsum_GB,
nvl(round(sum(dd.bytes) / 1024 / 1024 / 1024, 3),0) sum_GB,
nvl(round(sum(nvl(dt.bytes, 0)) / 1024 / 1024 / 1024, 3),0) free_GB,
nvl(round(( sum(dd.bytes) - sum(nvl(dt.bytes, 0)) ) / 1024 / 1024 / 1024, 3),0) as used_GB,
nvl(round(( sum(dd.bytes) - sum(nvl(dt.bytes, 0)) ) / sum(dd.bytes), 6) * 100,0) used_precent,
nvl(round(( sum(dd.bytes) - sum(nvl(dt.bytes, 0)) ) / sum(case when dd.maxbytes=0 then dd.BYTES else dd.MAXBYTES end), 6) * 100,0) used_of_max_precent
from ( select /*+ gather_plan_statistics */
tablespace_name, file_id, sum(bytes) bytes
from dba_free_space
group by tablespace_name, file_id ) dt,
dba_data_files dd
where 1 = 1
and dt.file_id(+) = dd.file_id
group by dd.tablespace_name,dd.tablespace_name || '-' || dd.file_name,dd.AUTOEXTENSIBLE
order by dd.tablespace_name,dd.tablespace_name || '-' || dd.file_name,dd.AUTOEXTENSIBLE
)
union all
select *
from (
--临时表空间的数据文件的使用率
select/*+ no_merge(dt)*/
'TEMPDATAFILE' as gather_level,
(select dbid from v$database) AS DATABASE_ID,
(select name from v$database) AS DATABASE_NAME,
dtf.tablespace_name,
dtf.tablespace_name || '-' || dtf.file_name as datafile_name,
dtf.autoextensible,
nvl(round(sum(case when dtf.maxbytes=0 then dtf.BYTES else dtf.MAXBYTES end) / 1024 / 1024 / 1024, 3),0) maxsum_GB,
nvl(round(sum(dtf.bytes) / 1024 / 1024 / 1024, 3),0) sum_GB,
nvl(round((sum(nvl(dtf.bytes,0)) - sum(nvl(dt.temp_used_size, 0))) / 1024 / 1024 / 1024, 3),0) as free_GB,
nvl(round(sum(dt.temp_used_size) / 1024 / 1024 / 1024, 3),0) as used_GB,
nvl(round(sum(dt.temp_used_size) / sum(dtf.bytes), 6) * 100,0) used_precent,
nvl(round(sum(dt.temp_used_size) / sum(case when dtf.maxbytes=0 then dtf.BYTES else dtf.MAXBYTES end), 6) * 100,0) used_of_max_precent
from ( select/*+ gather_plan_statistics */
tu.TABLESPACE,tu.SEGRFNO#,
sum(tu.BLOCKS*(select vp.VALUE from v$parameter vp where 1=1 and vp.NAME='db_block_size')) as temp_used_size
from v$tempseg_usage tu
group by tu.TABLESPACE, tu.SEGRFNO#
) dt,
dba_temp_files dtf
where 1=1
and dt.tablespace(+)=dtf.TABLESPACE_NAME
and dt.SEGRFNO#(+) = dtf.RELATIVE_FNO
group by dtf.tablespace_name,dtf.tablespace_name || '-' || dtf.file_name,dtf.AUTOEXTENSIBLE
order by dtf.tablespace_name,dtf.tablespace_name || '-' || dtf.file_name,dtf.AUTOEXTENSIBLE
)
)
--数据库的信息
select *
from (
select
--grouping(tp0.database_name)||grouping(tp0.gather_level),
decode(grouping(tp0.gather_level),0,decode(tp0.gather_level,'DATAFILE','DATABASE NO TEMP','DATABASE ONLY TEMP'),'DATABASE WITH TEMP' ) AS GATHER_LEVEL,
tp0.database_id,
tp0.database_name,
'' as tablespace_name,
'' as datafile_name,
'' as AUTOEXTENSIBLE,
sum(tp0.maxsum_gb) as maxsum_gb,
sum(tp0.sum_gb) as sum_gb,
sum(tp0.free_gb) as free_gb,
sum(tp0.used_gb) as used_gb,
round(sum(tp0.used_gb) / sum(tp0.sum_gb), 6) * 100 as used_precent,
round(sum(tp0.used_gb) / sum(tp0.maxsum_gb), 6) * 100 as used_of_max_precent
from tmp_datafile tp0
where 1=1
group by rollup((tp0.database_id,tp0.database_name),tp0.gather_level)
having not grouping(tp0.database_name)||grouping(tp0.gather_level)=11
order by tp0.gather_level
) t_database
union all
--表空间的信息
select *
from (
select
decode(tp1.gather_level,'DATAFILE','++TABLESPACE','++TEMPTABLESPACE') AS GATHER_LEVEL,
tp1.database_id,
tp1.database_name,
TP1.tablespace_name,
'' as level_name,
'' as AUTOEXTENSIBLE,
sum(tp1.maxsum_gb) as maxsum_gb,
sum(tp1.sum_gb) as sum_gb,
sum(tp1.free_gb) as free_gb,
sum(tp1.used_gb) as used_gb,
round(sum(tp1.used_gb) / sum(tp1.sum_gb), 6) * 100 as used_precent,
round(sum(tp1.used_gb) / sum(tp1.maxsum_gb), 6) * 100 as used_of_max_precent
from tmp_datafile tp1
where 1=1
group by tp1.database_id,tp1.database_name,tp1.gather_level, tp1.tablespace_name
order by tp1.database_id,tp1.database_name,tp1.gather_level, tp1.tablespace_name
) t_database
union all
--数据文件的信息
select '----'||tp0.gather_level,
tp0.database_id,
tp0.database_name,
tp0.tablespace_name,
tp0.datafile_name,
tp0.autoextensible,
tp0.maxsum_gb,
tp0.sum_gb,
tp0.free_gb,
tp0.used_gb,
tp0.used_precent,
tp0.used_of_max_precent
from tmp_datafile tp0
- 检测数据库大小的sql
- 查看sql server数据库的空间大小...
- 查看Mysql数据库大小的SQL语句
- Sql Server, 查询数据库中表的大小
- 在 sql server 中,查询 数据库的大小 和 数据库中各表的大小
- Shell之-定期检测数据库大小
- 电脑大小端的检测
- 机器大小端的检测
- 机器大小端的检测
- Android 检测屏幕的大小
- 压缩SQL SERVER 数据库大小
- 区分SQL数据库数据表大小
- 获取SQL Server数据库里表的占用容量大小
- 查询sql数据库中表占用的空间大小
- 查询sql数据库中表占用的空间大小
- sql server 查询数据库表的大小语句
- SQL查询数据库里表大小的命令
- 查询SQL数据库中各表大小的存储过程
- iOS图片拉伸技巧
- alter table move跟shrink space的区别
- leedcode--Excel Sheet Column Number
- [学习开发板]iTOP-4412开发板AVIN驱动配置
- Eclipse-JEE解决Cannot change version of project facet Dynamic web module to 2.4
- 检测数据库大小的sql
- VC包含目录、附加依赖项、库目录及具体设置
- 项目适配iOS9遇到的一些问题及解决办法(更新两个小问题)
- FreeRTOS内存管理
- HDU 3466 Proud Merchants
- html 虚线
- Asp.Net4.5 mvc4 页面创建与讲解
- 【三层】VB.net版登录实例
- 在python中获取当前日期字符串(格式:X年X月X日)