重庆思庄Oracle、Redhat认证学习论坛
标题: 常用空间查询和处理的运维SQL [打印本页]
作者: windjack 时间: 2020-11-20 18:22
标题: 常用空间查询和处理的运维SQL
--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
--查询数据文件空间使用情况
select
b.file_name 物理文件名,
b.tablespace_name 表空间,
b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) 利用率
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.bytes
order by b.tablespace_name;
--查询表空间的free space
select tablespace_name,
count(*) as extends,
round(sum(bytes) / 1024 / 1024, 2) as MB,
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
--查询表空间的总容量
select tablespace_name, sum(bytes) / 1024 / 1024 as MB
from dba_data_files
group by tablespace_name;
--查询表空间使用率
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;
--查看datafile 的HWM,估算resize小size脚本，方便数据迁移
SELECT a.tablespace_name,
file_name,
c.VALUE / 1024 "Blk. size(Kb)",
CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "smallest(Mb) - HWM",
CEIL (blocks * c.VALUE / 1024 / 1024) "currsize(Mb)",
CEIL (blocks * c.VALUE / 1024 / 1024) - CEIL ((NVL (hwm, 1) * c.VALUE) / 1024 / 1024) "savings(Mb)"
FROM dba_data_files a,
(SELECT file_id, MAX (block_id + blocks - 1) hwm
FROM dba_extents
GROUP BY file_id) b,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size') c
WHERE a.file_id = b.file_id(+)
and a.status !='INVALID';
--smallest(Mb) - HWM：为最小值
--修改：Alter database datafile 'xxx' resize 最小值；
--查询表空间使用率
select total.ts tablespace,
total.mb total_mb,
NVL(total.mb - free.mb, total.mb) used_mb,
NVL(free.mb, 0) free_mb,
DECODE(total.mb,
NULL,
0,
NVL(ROUND((total.mb - free.mb) / (total.mb) * 100, 2), 100)) pct_used
from (select tablespace_name ts, sum(bytes) / 1024 / 1024 mb
from dba_data_files
group by tablespace_name) total,
(select tablespace_name ts, sum(bytes) / 1024 / 1024 mb
from dba_free_space
group by tablespace_name) free
where total.ts = free.ts(+)
order by 5 desc;
--查询表空间使用率超过90%的数据文件明细
col file_name for a60 ;
col tablespace_name for a20;
set line 200
select file_id,
file_name,
tablespace_name,
bytes / 1024 / 1024 as "size(M)",
status,
autoextensible
from dba_data_files
where tablespace_name in
(select tablespace
from (select total.ts tablespace,
total.mb total_mb,
NVL(total.mb - free.mb, total.mb) used_mb,
NVL(free.mb, 0) free_mb,
DECODE(total.mb,
NULL,
0,
NVL(ROUND((total.mb - free.mb) / (total.mb) * 100,
2),
100)) pct_used
from (select tablespace_name ts,
sum(bytes) / 1024 / 1024 mb
from dba_data_files
group by tablespace_name) total,
(select tablespace_name ts,
sum(bytes) / 1024 / 1024 mb
from dba_free_space
group by tablespace_name) free
where total.ts = free.ts(+))
where pct_used > 90)
order by 3;
--开启小于25G的数据文件的自动扩展
set pagesize 1000 set head off
select 'alter database datafile ' || file_id || ' autoextend on;'
from dba_data_Files
where autoextensible = 'NO'
and (bytes / 1024 / 1024 / 1024) < 25;
--关闭大于30G的数据文件的自动扩展
set pagesize 1000 ;
set head off;
select 'alter database datafile ' || file_id || ' autoextend off;'
from dba_data_Files
where autoextensible = 'YES'
and (bytes / 1024 / 1024 / 1024) > 30;
--查看每天表空间增长率
SELECT A.NAME, B.TABLESPACE_ID,B.DATETIME,B.USED_SIZE_MB,B.INC_MB,
CASE WHEN SUBSTR(INC_RATE,1,1)='.' THEN '0'||INC_RATE
WHEN SUBSTR(INC_RATE,1,2)='-.' THEN '-0'||SUBSTR(INC_RATE,2,LENGTH(INC_RATE))
ELSE INC_RATE
END AS INC_RATEX
FROM V$TABLESPACE A,
(
SELECT TABLESPACE_ID,DATETIME,
USED_SIZE_MB,
(DECODE(PREV_USE_MB,0,0,USED_SIZE_MB)-PREV_USE_MB) AS INC_MB,
TO_CHAR(ROUND((DECODE(PREV_USE_MB,0,0,USED_SIZE_MB)-PREV_USE_MB)/DECODE(PREV_USE_MB,0,1,PREV_USE_MB)*100,2))||'%' AS INC_RATE
FROM
(
SELECT TABLESPACE_ID,
TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) DATETIME,
MAX(TABLESPACE_USEDSIZE * 8 / 1024) USED_SIZE_MB,
LAG(MAX(TABLESPACE_USEDSIZE * 8 / 1024),1,0) OVER(PARTITION BY TABLESPACE_ID ORDER BY TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) ) AS PREV_USE_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE
WHERE TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) > TRUNC(SYSDATE - 30)
GROUP BY TABLESPACE_ID, TRUNC(TO_DATE(RTIME, 'mm/dd/yyyy hh24:mi:ss')) )
) B
WHERE A.TS# = B.TABLESPACE_ID
ORDER BY B.TABLESPACE_ID,DATETIME;
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |