一.查看表空间使用率
1.查看数据库表空间文件:
--查看数据库表空间文件
select * from dba_data_files;
2.查看所有表空间的总容量:
--查看所有表空间的总容量
select dba.TABLESPACE_NAME, sum(bytes)/1024/1024 as MB
from dba_data_files dba
group by dba.TABLESPACE_NAME;
3.查看数据库表空间使用率
--查看数据库表空间使用率
select total.tablespace_name,round(total.MB, 2) as Total_MB,round(total.MB - free.MB, 2) as Used_MB,round((1-free.MB / total.MB)* 100, 2) || '%' as Used_Pct
from (
select tablespace_name, sum(bytes) /1024/1024 as MB
from dba_free_space group by tablespace_name) free,
(select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files group by tablespace_name) total
where free.tablespace_name = total.tablespace_name
order by used_pct desc;
4.1.查看表空间总大小、使用率、剩余空间
--查看表空间总大小、使用率、剩余空间
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%"
from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;
二、修改满了的表空间
1、当表空间不足时
扩展表空间
当表空间文件本身较小时,可以选择扩大表空间文件的大小,sql语句:
alter database datafile 'C:\app\Administrator\oradata\ITD_PLUS\SDE_1.dbf' resize 500m;
首先选择设置自增长,sql语句:
alter database datafile 'C:\app\Administrator\oradata\ITD_PLUS\SDE_1.dbf' autoextend on next 400M Maxsize UNLIMITED;
在自增长失灵了之后,需要增加数据文件,sql语句:
Alter tablespace 表空间名 add datafile ‘数据文件存放的路径’ size 数据文件大小M autoextend on next 每次自增长大小M Maxsize UNLIMITED;
示例:
alter tablespace SYSTEM add datafile 'C:\app\Administrator\oradata\ITD_PLUS\SDE_1.dbf' size 10240M autoextend on next 400M Maxsize UNLIMITED;
alter tablespace SYSAUX add datafile 'C:\app\Administrator\oradata\ITD_PLUS\SDE_2.dbf' size 10240M autoextend on next 400M Maxsize UNLIMITED;
2、 删除表空间文件
有时表空间文件添加错误,需要删除重新添加,sql语句:
alter tablespace SYSTEM drop datafile 'C:\app\Administrator\oradata\ITD_PLUS\SDE_1.dbf';