oracle数据库占用,Oracle数据库表空间占用过大的解决办法

最近调接口的时候接口访问历史表报错：

检查以后发现表空间满了，随后把相应表空间进行了扩展，顺带总结了Oracle检查&调整表空间的sql语句以下：

--查看某张表的表空间

select table_name,tablespace_name from user_tables where TABLE_NAME='表名';

--查询表空间的使用状况

SELECT UPPER(F.TABLESPACE_NAME)"表空间名",

D.TOT_GROOTTE_MB "表空间大小(M)",

D.TOT_GROOTTE_MB-F.TOTAL_BYTES "已使用空间(M)",

TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')||'%'"使用比",

F.TOTAL_BYTES "空闲空间(M)",

F.MAX_BYTES "最大块(M)"

FROM(SELECT TABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,

ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES

FROM SYS.DBA_FREE_SPACE

GROUP BY TABLESPACE_NAME)F,

(SELECT DD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB

FROM SYS.DBA_DATA_FILES DD

GROUP BY DD.TABLESPACE_NAME)D

WHERE D.TABLESPACE_NAME=F.TABLESPACE_NAME

ORDERBY1;

--查询某个用户的默认表空间和默认临时表空间

select username,default_tablespace,temporary_tablespace from dba_users where username='用户名';

-- 查询某个用户下全部表使用的表空间

select owner,table_name,tablespace_name from dba_tables where owner='用户名';

--查询某个表空间下面的全部表

select * from all_tables where tablespace_name='表空间名'

--检查表空间数据文件占用

select

b.file_name 物理文件名,

b.tablespace_name 表空间,

b.bytes/1024/1024大小M,

(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,

substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率

from dba_free_space a,dba_data_files b

where a.file_id=b.file_id

group by b.tablespace_name,b.file_name,b.bytes

order by b.tablespace_name;

--增长表空间

一、先查看一下数据文件存放位置：select * from dba_data_files;

二、登陆服务器查看相关目录下是否有足够的空间  df -h

----css_app02.dbf 表示这是css_app表空间的其中一份存储文件. 表空间存储文件命名方式：表空间名01.dbf,表空间名02.dbf .....表空间名0N.dbf

三、拓展表空间的两种方法：

方法一：

给表空间增长数据文件：

--增长固定大小的文件，不容许数据文件自动增加

alter tablespace 表空间名 add  datafile ‘/mnt/disk/oracle/CSS_APP02.dbf’size 30720M  autoextend off;

--新增数据文件，而且容许数据文件自动增加

alter tablespace 表空间名 add  datafile ‘/mnt/disk/oracle/CSS_APP02.dbf’size 30720M  autoextend on next 50M maxsize 5G;

--新增数据文件，而且容许数据文件自动增加(无限制)

alter tablespace 表空间名 add  datafile ‘/mnt/disk/oracle/CSS_APP02.dbf’size 30720M  autoextend on next 50M maxsize unlimited;

方法二：

改变已存在的数据文件的大小：

alter datdabase datafile  ‘/mnt/disk/oracle/CSS_APP01.dbf’ resize 30720M;

--系统表空间过大的状况

一、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%的状况，建议增长数据文件

二、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值';使用dbm_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值';