本文主要是介绍RDS Oracle 存储空间占用分析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
前言
- 我的存储空间都用在了哪里，怎么进行查看
- 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_filesgroup by tablespace_name) a,
( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_spacegroup by tablespace_name) bwhere 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 summaryfrom (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_gbfrom (select bytes from v$datafileunion allselect bytes from v$tempfile) used,(select sum(bytes) as f from dba_free_space) freegroup 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
这篇关于RDS Oracle 存储空间占用分析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
