oracle表空间
一、 分类:
永久表空间:保存永久对象
临时表空间:存放排序的中间结果,存放临时表数据。 回退表空间:undo tablespace --> rollback segment --> 数据修改前的老镜像
查看表空间和数据文件的对应关系:
select tablespace_name,file_id,file_name from dba_data_files; select tablespace_name,status from dba_tablespaces;
二、 监控表空间空间使用情况脚本:
1.select
a.tablespace_name,a.curr_mb,a.max_mb,round(b.free_mb/a.curr_mb,4)*100||'%' free_pct
from
(select tablespace_name,
sum(blocks)/128 curr_mb,
sum(maxblocks)/128 max_mb
from dba_data_files
group by tablespace_name) a,
(select TABLESPACE_NAME,sum(BLOCKS)/128 free_mb from dba_free_space group by TABLESPACE_NAME) b
where a.tablespace_name=b.tablespace_name;
2.自己写的
select
a.tablespace_name,
a.curr_mb,
round((b.free_mb/a.curr_mb)*100,3)||'%' free_pct,
a.max_mb
from
(select tablespace_name,sum(blocks)/128 curr_mb,