RDS Oracle 存储空间占用分析

最新推荐文章于 2025-04-27 16:35:26 发布

Tech_Sharing

最新推荐文章于 2025-04-27 16:35:26 发布

阅读量710

点赞数

分类专栏： AWS RDS 文章标签： oracle rds

本文链接：https://blog.csdn.net/Tech_Sharing/article/details/108468642

版权

AWS RDS 专栏收录该内容

5 篇文章

订阅专栏

前言

我的存储空间都用在了哪里，怎么进行查看
delete大表之后，为什么cloud watch的free storage指标没有变化
如何修改表空间大小

分析解决

以下是一些常见得排查存储空间问题的SQL 语句

1. 确定分配给数据的表空间空间量

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS GBYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

2. 分配给临时文件的空间量

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS GBYTES FROM dba_temp_files GROUP BY TABLESPACE_NAME;

3. 确定分配给数据库的可用空间量：

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS GBYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

4. 确定分配给在线机重做日志的空间量：

SELECT SUM(bytes*members) bytes FROM v$log;

5. 确定分配给控制文件的空间量：

SELECT SUM(block_size * file_size_blks) bytes FROM v$controlfile;

6. 确定分配给审计 ADUMP 和跟踪/日志文件 BDUMP 目录的空间量：

SELECT * FROM DBA_DIRECTORIES;

SELECT * FROM table(rdsadmin.rds_file_util.listdir('ADUMP'));

SELECT * FROM table(rdsadmin.rds_file_util.listdir('BDUMP'));

SELECT SUM(FILESIZE)/1024/1024 as total_mb FROM table(rdsadmin.rds_file_util.listdir('ADUMP'));

SELECT SUM(FILESIZE)/1024/1024 as total_mb FROM table(rdsadmin.rds_file_util.listdir('BDUMP'));

7. 检查是否存在可以删除的、不需要的 .dmp 文件以释放空间。

要在导入之后检查 data_pump_dir 中未清除的 .dmp 文件，请运行以下查询：

SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) ORDER by mtime;

8. 查看普通表空间的利用率

 
select a.tablespace_name,total,free,total-free used from
( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
   group by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
   group by tablespace_name) b
   where a.tablespace_name=b.tablespace_name;

9. 信息汇总SQL：

select
	'===========================================================' || chr(10) ||
	'Total Database Physical Size = ' || round(archlog_size_gb+redolog_size_gb+dbfiles_size_gb+tempfiles_size_gb+ctlfiles_size_gb,2) || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	' Redo Logs Size : ' || round(redolog_size_gb,3) || ' GB' || chr(10) ||
	' Data Files Size : ' || round(dbfiles_size_gb,3) || ' GB' || chr(10) ||
	' Temp Files Size : ' || round(tempfiles_size_gb,3) || ' GB' || chr(10) ||
	' Control Files Size : ' || round(ctlfiles_size_gb,3) || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	'Actual Database Size = ' || db_size_gb || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	' Used Database Size : ' || used_db_size_gb || ' GB' || chr(10) ||
	' Free Database Size : ' || free_db_size_gb || ' GB' ||chr(10) ||
	' Data Pump Directory Size : ' || dpump_db_size_gb || ' GB' || chr(10) ||
	' BDUMP Size : ' || bdump_db_size_gb || ' GB' || chr(10) ||
	' ADUMP Size : ' || adump_db_size_gb || ' GB' || chr(10) ||
	'===========================================================' || chr(10) ||
	'Total Size (including Dump and Log Files) = ' || round(round(redolog_size_gb,2) +round(dbfiles_size_gb,2)+round(tempfiles_size_gb,2)+round(ctlfiles_size_gb,2) +round(adump_db_size_gb,2) +round(dpump_db_size_gb,2)+round(bdump_db_size_gb,2),2)    || ' GB' || chr(10) ||
	'===========================================================' || chr(10)
	 as summary
	from (
	select sys_context('USERENV', 'DB_NAME') db_name,
	(select sum(bytes)/1024/1024/1024 redo_size from v$log ) redolog_size_gb,
	(select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) dbfiles_size_gb
	,(select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) tempfiles_size_gb
	,(select sum(blocks*block_size/1024/1024/1024) size_mb from v$archived_log where DELETED = 'NO') archlog_size_gb
	,(select sum(block_size*file_size_blks)/1024/1024/1024 controlfile_size from v$controlfile) ctlfiles_size_gb
	,round(sum(used.bytes)/1024/1024/1024,3) db_size_gb
	,round(sum(used.bytes)/1024/1024/1024,3) - round(free.f/1024 /1024/ 1024) used_db_size_gb
	,round(free.f/1024/1024/1024,3) free_db_size_gb
	,(select round(sum(filesize)/1024/1024/1024,3) from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('BDUMP')))  bdump_db_size_gb
	,(select round(sum(filesize)/1024/1024/1024,3) from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('ADUMP')))  adump_db_size_gb
	,(select round(sum(filesize)/1024/1024/1024,3) from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')))  dpump_db_size_gb
	from (select bytes from v$datafile
	union all
	select bytes from v$tempfile) used
	,(select sum(bytes) as f from dba_free_space) free
	group by free.f);

10 .可以使用如下SQL单独查询归档日志所占空间大小

select dest_id, sum(blocks*block_size/1024/1024/1024) size_gb from v$archived_log where DELETED = 'NO' group by dest_id;

11 .使用如下SQL 来查询每个DB 用户所占用的空间大小，以及占总容量的百分比：

select owner, round(sum(bytes)/1024/1024/1024,2) Consumed_Space_in_GB,round(ratio_to_report(sum(bytes)/1024/1024/1024) over()*100,2)||'%' as Ratio_PCT  from dba_segments group by owner order by 2 desc;

总结

需要注意的是 Free Database Size, 这是数据库表空间里面的剩余空间，表空间是由数据文件组成的，比如一个表空间包含一个 1000MB大小的数据文件，从操作系统看它的size 就是 1000MB ，但是这个数据文件中真正存放的数据只占用了600MB，那么其free的空间就是400MB，当新的数据进来的时候，就会使用这400MB的数据。而Cloudwatch 中的Freespace 看的是操作系统层面的Free 空间，这与DB 内部的剩余空间不是一个概念。这就是为什么我们在表空间中delete了表之后，cloudwatch没有释放空间

如何缩小表空间

1. 查询file_id

select file#,name from v$datafile;

2. 查该数据文件中数据处在最大位置

select max(block_id) from dba_extents where file_id=15;
假设查出来为383497

3. 查出最大块位置

select 383497*8/1024 from dual;

4. 缩小表空间

当我们确定了那个表空间占用大，且确实有大量的剩余空间，如果想要释放，我们可以使用users表空间举例

SQL> ALTER TABLESPACE USERS RESIZE 10M;

Tablespace altered.

RDS 不可以使用文件的方式

SQL> alter database datafile '/rdsdbdata/db/ORCL_A/datafile/o1_mf_data_tbs_hoczq15x_.dbf' resize 100M;
alter database datafile '/rdsdbdata/db/ORCL_A/datafile/o1_mf_data_tbs_hoczq15x_.dbf' resize 100M
*
ERROR at line 1:
ORA-01031: insufficient privileges