常用空间维护SQL

最新推荐文章于 2024-09-20 17:14:37 发布

置顶

yfleng2002

最新推荐文章于 2024-09-20 17:14:37 发布

阅读量1.4k

点赞数

分类专栏： ORACLE 文章标签： file schema table 数据库脚本

本文链接：https://blog.csdn.net/yfleng2002/article/details/7011274

版权

本文介绍了如何使用SQL查询数据库的使用空间情况，包括查看整个数据库的总空间、数据文件使用、SCHEMA空间占用、特定表空间及表的大小，以及表空间碎片和移动对象的表空间操作。

摘要由CSDN通过智能技术生成

一、查看整个数据库使用总空间

select sum("file_size(M)") as "total_size(M)" from (
select 'SPFILE' as "file_type",value as "file_name",1 as "file_size(M)" from v$parameter where  name ='spfile'
UNION
select 'CONTROL' as "file_type",name as "file_name",round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) as "file_size(M)" from v$controlfile
UNION
select  'REDOLOG' as "file_type",v$logfile.member as "file_name",round(v$log.bytes/1024/1024,2) as "file_size(M)" from v$log,v$logfile where v$log.group#=v$logfile.group#
UNION
select 'TEMP' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$tempfile
UNION
select 'DATAFILE' as "file_type",name as "file_name",round(bytes/1024/1024,2) as "file_size(M)" from v$datafile
);

二、查看数据库中各个文件占用的系统空间

select * from (
select 'SPFILE' as "file_type",value as "file_name",1 as "file_size(M)" from v$parameter where  name ='spfile'
UNION
select 'CONTROL' as "file_type",name as "file_name",round(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024,2) as "file_size(M)" from v$controlfile
UNION
select  'REDOLOG' as "file_type",v$logfile.member as "file_name",round(v$log.bytes/1024/1024,2) as "file_size(M)" from v$log,v$logfile where v$log.group#=v$logfile.group#
UNION
select 'TEMP' as "file_type&