RDS Oracle 存储空间占用分析

前言

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

分析解决

以下是一些常见得排查存储空间问题的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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值