Oracle表空间管理查看,扩充
来源:互联网 发布:一周休三天 知乎 编辑:程序博客网 时间:2024/06/06 01:45
1.查看表空间使用率(包括临时表空间)
select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
select * from (
Select a.tablespace_name,
to_char(a.bytes/1024/1024,'99,999.999') total_bytes,
to_char(b.bytes/1024/1024,'99,999.999') free_bytes,
to_char(a.bytes/1024/1024 - b.bytes/1024/1024,'99,999.999') use_bytes,
to_char((1 - b.bytes/a.bytes)*100,'99.99') || '%' use
from (select tablespace_name,
sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name,
sum(bytes) bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select c.tablespace_name,
to_char(c.bytes/1024/1024,'99,999.999') total_bytes,
to_char( (c.bytes-d.bytes_used)/1024/1024,'99,999.999') free_bytes,
to_char(d.bytes_used/1024/1024,'99,999.999') use_bytes,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%' use
from
(select tablespace_name,sum(bytes) bytes
from dba_temp_files group by tablespace_name) c,
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool group by tablespace_name) d
where c.tablespace_name = d.tablespace_name
)
order by tablespace_name
2.查看文件是否自动扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_data_files d
如果想查看临时表空间文件是否自动扩展
select d.file_name,d.tablespace_name,d.autoextensible from dba_temp_files d;
3.对临时文件进行扩展。
1)TOSTEMP表空间使用率接近100%,对它进行扩展。
SQL> alter database tempfile 'C:xxxxxx\TOSTEMP01.DBF'resize 500M;
2)若是发现 表空间使用率接近100%,且不可扩展修改文件自动可扩展性
alter database datafile 'E:xxxxxxESCALADE.ORA' autoextend on;
3)修改可扩展上限为无限制
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited;
阅读全文
0 0
- Oracle表空间管理查看,扩充
- oracle扩充表空间
- Oracle扩充表空间步骤
- Oracle查询表空间使用情况&扩充表空间大小
- oracle表空间查询及临时表空间扩充语句
- 扩充表空间
- oracle查看表空间
- oracle 查看表空间
- oracle 查看表空间
- Oracle查看表空间
- oracle查看表空间
- oracle 查看表空间
- oracle 表空间查看
- 查看oracle表空间
- ORACLE查看表空间
- oracle查看表空间
- Oracle 如何将已经扩充的表空间缩小
- ORACLE 表空间管理
- 使用opencv的SVM和神经网络实现车牌识别
- oracle笔记之交集(INTERSECT)、并集(UNION UNION ALL )、差集(MINUS)
- ImageMagick popen_utf8 命令注入漏洞分析报告
- spring之注解开发
- 前端常识篇--CSS
- Oracle表空间管理查看,扩充
- 配置ubuntu16.10无线网络dns
- 使用opencv的SVM实现车牌区域识别
- linux之体系结构
- redis字符串类型操作命令
- miniui给列添加链接
- POJ 1089 不要62
- 【Echo的继承和多态】
- axios基本用法