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

蔚落 2022-07-13 04:19 1542阅读 0赞

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

![Center][]

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

***--查看某张表的表空间***

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

![Center 1][]

--查询表空间的使用情况
    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;

![Center 2][]

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

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

![Center 3][]

***-- 查询某个用户下所有表使用的表空间***

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

![Center 4][]

*-**-查询某个表空间下面的所有表***

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

![Center 5][]

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

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;

![Center 6][]

**--增加表空间**

1、先查看一下数据文件存放位置：select \* from dba\_data\_files;

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

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

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

方法一：

给表空间增加数据文件：

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

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;

**--系统表空间过大的情况**

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%的情况，建议增加数据文件

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值';

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

[Center]: /images/20220713/e6c044e7b34743ea88a0cfbd8f7099af.png
[Center 1]: /images/20220713/387fd8a51c7047ad829ece00ec93cfb7.png
[Center 2]: /images/20220713/9f8fd9290aa44058b235dcc0aa822a08.png
[Center 3]: /images/20220713/dac5ce32e6cd4f078d9f38bd108b3533.png
[Center 4]: /images/20220713/909399b305d746c28a11bf054839ed93.png
[Center 5]: /images/20220713/cb0c1c7bdc65476e9b6ecf64c1bc504b.png
[Center 6]: /images/20220713/2da38c470fc64f5b9869b9b3d1a87744.png