一、 查看Oracle数据库表空间使用情况
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total/(1024*1024*1024) "表空间大小(G)",
free/(1024*1024*1024) "表空间剩余大小(G)",
(total - free)/(1024*1024*1024) "表空间使用大小(G)",
ROUND((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM DBA_FREE_SPACE
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;
二、扩大表空间的四种方法
--给表空间增加数据文件
ALTER TABLESPACE app_data ADD DATAFILE 'D:\dbfile\app_01.dbf' SIZE 1G;
--新增数据文件,并且允许数据文件自动增长
ALTER TABLESPACE app_data ADD DATAFILE 'D:\dbfile\app_02.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G;
--允许已存在的数据文件自动增长
ALTER DATABASE DATAFILE 'D:\dbfile\app_01.dbf'
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
--手工改变已存在数据文件的大小
ALTER DATABASE DATAFILE 'D:\dbfile\app_01.dbf' RESIZE 500M;
三、系统表空间过大的情况
1)system表空间过大(使用率95%以上)
a)检查aud$表大小
--查看数据库表大小SQL
select bytes,owner,segment_name
from dba_segments
where segment_type='TABLE' order by bytes desc;
--查看aud$表大小SQL
select bytes,owner,segment_name
from dba_segments
where segment_type='TABLE' and segment_name = 'AUD$';
b) 如果aud$过大,清理之
导出aud$表后,用truncate清理
c) 如果出现aud$表为空,system表空间使用率照样达到99%,建议增加数据文件,如下:
alter tablespace system add datafile 'D:\dbfile\SYSTEM_02.DBF' size 50M autoextend on
select * from dba_data_files;
2)sysaux表空间(使用率95%以上)
a) 修改统计信息保存时间
select dbms_stats.get_stats_history_retention from dual; --检查统计信息保存时间(默认应该是31天)
exec dbms_stats.alter_stats_history_retention(7); --如果31天将其改为7天
select dbms_stats.get_stats_history_retention from dual; --验证是否修改成功
b) 删除AWR报告快照
批注:Oracle 10g中快照会保留7天,11g的快照保留8天,超出会自动删除。AWR快照可以从其他数据库导入,而这部分数据会保存时间极长。有时候也会遇到自动快照不能自动收集,而手工创建的快照又可以成功,对于这种情况就需要把以前的快照清理掉。
删除AWR有两种方式进行删除:dbms_workload_repository,dbms_swrf_internal。dbms_workload_repository可以删除本地和其他数据库的快照,可以选择不同的快照来进行删除;dbms_swrf_internal只能对其他数据库的快照来进行操作,会把所有的快照直接干掉。
使用dbms_workload_repository包删除
select dbid, retention from dba_hist_wr_control;
select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值';
exec dbms_workload_repository.drop_snapshot_range('得到的min(snap_id)值','得到的max(snap_id)值','得到的dbid值');
select * from dba_hist_snapshot where dbid = '得到的dbid值';
使用dbms_swrf_internal包删除
select dbid, retention from dba_hist_wr_control;
select min(snap_id), max(snap_id)from dba_hist_snapshot where dbid = '得到的dbid值';
exec dbms_swrf_internal.unregister_database('得到的dbid值');
select * from dba_hist_snapshot where dbid = '得到的dbid值';