导航
导航

Oracle问题排查

查询表空间信息:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT UPPER(F.TABLESPACE_NAME) "表空间名 ",
D.MAX_GROOTTE_MB "表空间最大值 (M)",
D.TOT_GROOTTE_MB "当前已分配表空间 (M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "当前已使用表空间 (M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.MAX_GROOTTE_MB * 100,
2),
'990.99') "总表空间使用比",
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,
ROUND(SUM(DD.MAXBYTES) / (1024 * 1024), 2) MAX_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
and D.TABLESPACE_NAME like 'AMB%'
ORDER BY 5 DESC;

查询是否有失败Job:

1
select * from user_jobs where broken='Y' or failures>0 or next_date<trunc(SYSDATE);

查询是否存在重复Job:

1
select t.JOB, t.WHAT, t.INTERVAL from user_jobs t order by t.WHAT

两个timestamp时间相差5分钟以内:

1
select * from table t where extract(minute from (t.time1 - t.time2) <= 5