没有合适的资源?快使用搜索试试~ 我知道了~
oracleDBA表空间管理 表空间管理 表空间管理
资源推荐
资源详情
资源评论
(1).如何查看各个表空间占用磁盘情况?
SELECT a.tablespace_name,
TO_CHAR(a.bytes, '999990.999') "Total(MB)",
TO_CHAR(a.bytes - NVL(b.bytes, 0) , '999990.999') "Used(MB)",
TO_CHAR(NVL( b.bytes, 0) , '999990.999') "Avail(MB)",
TO_CHAR(b.bytes/a.bytes*100,'9999.9999') pre
from
( select tablespace_name, sum( bytes )/1024/1024 bytes from dba_data_files group by tablespace_name ) a,
( select tablespace_name, sum( bytes )/1024/1024 bytes from dba_free_space group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
order by pre
查看表占用的空间
下面的更全面,包括TEMP表空间
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
SELECT a.tablespace_name,
TO_CHAR(a.bytes, '999990.999') "Total(MB)",
TO_CHAR(a.bytes - NVL(b.bytes, 0) , '999990.999') "Used(MB)",
TO_CHAR(NVL( b.bytes, 0) , '999990.999') "Avail(MB)",
TO_CHAR(b.bytes/a.bytes*100,'9999.9999') pre
from
( select tablespace_name, sum( bytes )/1024/1024 bytes from dba_data_files group by tablespace_name ) a,
( select tablespace_name, sum( bytes )/1024/1024 bytes from dba_free_space group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
order by pre
查看表占用的空间
下面的更全面,包括TEMP表空间
select t.*
from (SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
FREE_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL --if have tempfile
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
select sum(t.bytes) from dba_extents t where t.segment_name = 'MRP_FORM_QUERY'
当删除某个表DELETE FROM mrp_form_query后
必需truncate table mrp.MRP_FORM_QUERY后才能真正释放表占用的空间!
软件环境:
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为:C:\ORANT
SQL语句:
SQL> col tablespace format a20
SQL> select
SELECT D.TABLESPACE_NAME,
SPACE "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
SPACE - USED_SPACE "FREE_SPACE(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE,
ROUND(SUM(BLOCKS * 8192) / (1024 * 1024), 2) USED_SPACE
FROM V$SORT_USAGE
GROUP BY TABLESPACE) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE(+)) t
order by "USED_RATE(%)" desc;
select sum(t.bytes) from dba_extents t where t.segment_name = 'MRP_FORM_QUERY'
当删除某个表DELETE FROM mrp_form_query后
必需truncate table mrp.MRP_FORM_QUERY后才能真正释放表占用的空间!
软件环境:
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为:C:\ORANT
SQL语句:
SQL> col tablespace format a20
SQL> select
剩余5页未读,继续阅读
资源评论
qq_30434791
- 粉丝: 0
- 资源: 1
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功