最近调接口的时候接口访问历史表报错:
检查之后发现表空间满了,随后把相应表空间进行了扩展,顺带总结了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;
--增加表空间
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值';