1、查看表空间使用情况
select a.tablespace_name "表空间名称",trunc(a.total) "已分配(M)",trunc((a.total-b.free),1) "已使用(M)" ,trunc((1-b.free/a.total)*100,1) "已分配使用(%)",trunc(b.free,1) "已分配可用(M)"
,c.status,e.file_sum "文件数",ceil((a.total_max)) "最大可用空间(M)" , round( (1- trunc((a.total-b.free),1)/ceil((a.total_max)) ) *100,2 ) "可用空间%"
from
(select tablespace_name, sum(nvl(bytes,0))/1024/1024 total,sum((case nvl(maxbytes,0) when 0 then bytes else maxbytes end) )/1024/1024 total_max from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(nvl(bytes,0))/1024/1024 free from dba_free_space group by tablespace_name) b,
(select tablespace_name, count(*) file_sum from dba_data_files group by tablespace_name ) e ,
dba_tablespaces c
where a.tablespace_name=b.tablespace_name and b.tablespace_name=c.tablespace_name and c.tablespace_name = e.tablespace_name
2、表空间将满的预警服务
a、推荐人工定期(如每周)检查,发现接近85%就要考虑添加文件
b、oracle工具,如em,需要配置告警阈值,及邮件服务
启用会占用一定资源,可参考配置手册进行配置
https://blog.csdn.net/Jerry_mg/article/details/78771129
c、商业工具
3、分析表空间使用
大表查询需定期查询并记录大小,然后计算增长情况;
–排除sys等用户,查询前30个较大对象;
select *from (
select a.segment_name, a.owner, sum(a.bytes) / 1024 / 1024 "MB"
from dba_segments a where owner not in ('SYS','SYSMAN','DBSNMP')
group by a.segment_name, a.owner
order by 3 desc ) where rownum <30 ;
–查询所有用户前50个较大对象;
select *from (
select a.segment_name, a.owner, sum(a.bytes) / 1024 / 1024 "MB"
from dba_segments a group by a.segment_name, a.owner
order by 3 desc ) where rownum <50 ;
–查询lob段相关的表或分区名称
SELECT * FROM dba_segments t WHERE t.segment_name IN ( 'SYS_LOB0000502257C00008$$'
,'SYS_IL0000502257C00008$$' );
4、添加表空间文件
表空间添加,要根据客户现场的库的部署情况添加
文件位置不能搞错,添加表空间是数据库维护类工作,不建议直接操作,要找客户管理员操作
参考语句如下
(1)查询当前表空间位置;
select name from v$datafile ;
注意:RAC环境下表空间位置为ASM磁盘组,或共享存储位置 磁盘组名称如‘+DATA’
(2)根据查询的位置添加表空间文件;
(2.1)rac环境添加表空间:
–创建表空间 (nstcsms用户 nstc_sms表空间,磁盘组‘+datadg’,初始文件大小20g,每个文件增加到32g;)
create tablespace nstc_sms datafile '+DATADG' size 20g autoextend on next 500m ;
- 添加文件
alter tablespace nstc_sms add datafile '+DATADG' size 20g autoextend on next 500m ;
- 临时表空间
create temporary tablespace nstc_fss_temp tempfile '+DATADG' size 4g autoextend on next 500m maxsize unlimited ;
alter tablespace nstc_fss_temp add tempfile '+DATADG' size 4g autoextend on next 500m maxsize unlimited ;
(2.2)普通文件系统添加表空间:
create tablespace nstc_ws datafile '/u01/app/oracle/oradata/orcl19/nstc_ws_01.dbf' size 10g autoextend on next 500m ;
alter tablespace nstc_ws add datafile '/u01/app/oracle/oradata/orcl19/nstc_ws_02.dbf' size 10g autoextend on next 500 ;
- 临时表空间
create temporary tablespace nstc_temp tempfile '/u01/app/oracle/oradata/orcl19/nstc_temp_01.dbf' size 4g autoextend on next 500m maxsize unlimited ;
alter tablespace nstc_temp add tempfile '/u01/app/oracle/oradata/orcl19/nstc_temp_02.dbf' size 4g autoextend on next 500m maxsize unlimited ;