oracle 日常维护常用命令
–查看数据库版本
SELECT * FROM V
V
E
R
S
I
O
N
;
−
−
查
看
数
据
库
语
言
环
境
S
E
L
E
C
T
U
S
E
R
E
N
V
(
′
L
A
N
G
U
A
G
E
′
)
F
R
O
M
D
U
A
L
;
−
−
查
看
o
r
a
c
l
e
实
例
状
态
S
E
L
E
C
T
I
N
S
T
A
N
C
E
N
A
M
E
,
H
O
S
T
N
A
M
E
,
S
T
A
R
T
U
P
T
I
M
E
,
S
T
A
T
U
S
,
D
A
T
A
B
A
S
E
S
T
A
T
U
S
F
R
O
M
V
VERSION; --查看数据库语言环境 SELECT USERENV('LANGUAGE') FROM DUAL; --查看oracle实例状态 SELECT INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUS FROM V
VERSION;−−查看数据库语言环境SELECTUSERENV(′LANGUAGE′)FROMDUAL;−−查看oracle实例状态SELECTINSTANCENAME,HOSTNAME,STARTUPTIME,STATUS,DATABASESTATUSFROMVINSTANCE;
–查看oracle监听状态
lsnrctl status
–查看数据库归档模式
SELECT NAME,LOG_MODE,OPEN_MODE FROM VKaTeX parse error: Expected 'EOF', got '#' at position 397: …LECT SID,SERIAL#̲,USERNAME,PROGR…SESSION;
–查看ddl锁
SELECT * FROM DBA_DDL_LOCKS WHERE OWNER =‘ORACLE’;
select t.SID,
t.SERIAL#,
t2.OBJECT_TYPE,
t.USERNAME ORACLE_USER,
t2.OBJECT_NAME,
t.OSUSER,
t.MACHINE,
t3.SQL_TEXT
from v
s
e
s
s
i
o
n
t
,
v
session t, v
sessiont,vlocked_object t1, dba_objects t2, v$sql t3
where t.SID = t1.SESSION_ID
AND t1.OBJECT_ID = t2.OBJECT_ID
AND T3.SQL_ID=T.SQL_ID
–解锁
ALTER SYSTEM KILL SESSION ‘25,396’
–检查等待事件
SELECT SID,A.USERNAME,EVENT,WAIT_CLASS,T1.SQL_TEXT
FROM V
S
E
S
S
I
O
N
A
,
V
SESSION A,V
SESSIONA,VSQLAREA T1
WHERE WAIT_CLASS <>‘Idle’
AND A.SQL_ID=T1.SQL_ID;
–检查数据文件状态
SELECT FILE_NAME,STATUS FROM DBA_DATA_FILES;
–检查不起作用的约束
SELECT OWNER,CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,STATUS FROM DBA_CONSTRAINTS WHERE STATUS=‘DISABLE’;
–检查发生坏块的数据库对象
SELECT TABLESPACE_NAME,SEGMENT_TYPE,OWNER,SEGMENT_NAME
FROM DBA_EXTENTS
WHERE FILE_ID=< AFN >
AND < BLOCK > BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
–检查无效的数据库对像
SELECT OWNER,OBJECT_NAME,OBJECT_TYPE FROM DBA_OBJECTS WHERE STATUS=‘INVALID’;
–查看语句执行进度
SELECT SE.SID,
OPNAME,
TRUNC(SOFAR / TOTALWORK *100,2)||’%’ AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
SQL_TEXT
FROM V
S
E
S
S
I
O
N
L
O
N
G
O
P
S
S
L
,
V
SESSION_LONGOPS SL, V
SESSIONLONGOPSSL,VSQLAREA SA, V$SESSION SE
WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
AND SL.SID = SE.SID
AND SOFAR != TOTALWORK
ORDER BY START_TIME;
–检查碎片程度高的表
SELECT SEGMENT_NAME TABLE_NAME, COUNT() EXTENTS
FROM DBA_SEGMENTS
WHERE OWNER NOT IN (‘SYS’, ‘SYSTEM’)
GROUP BY SEGMENT_NAME
HAVING COUNT() = (SELECT MAX(COUNT(*))
FROM DBA_SEGMENTS
GROUP BY SEGMENT_NAME);
–检查表空间的i/0比例
SELECT DF.TABLESPACE_NAME NAME,
DF.FILE_NAME “FILE”,
F.PHYRDS PYR,
F.PHYBLKRD PBR,
F.PHYWRTS PYW,
F.PHYBLKWRT PBW
FROM V$FILESTAT F, DBA_DATA_FILES DF
WHERE F.FILE# = DF.FILE_ID
ORDER BY DF.TABLESPACE_NAME;
–检查文件系统I/O比例
SELECT SUBSTR(A.FILE#, 1, 2) “#”,
SUBSTR(A.NAME, 1, 30) “NAME”,
A.STATUS,
A.BYTES,
B.PHYRDS,
B.PHYWRTS
FROM V
D
A
T
A
F
I
L
E
A
,
V
DATAFILE A, V
DATAFILEA,VFILESTAT B
WHERE A.FILE# = B.FILE#;
–检查消耗cpu最高的进程
SELECT P.PID PID,
S.SID SID,
P.SPID SPID,
S.USERNAME USERNAME,
S.OSUSER OSNAME,
P.SERIAL# S_#,
P.TERMINAL,
P.PROGRAM PROGRAM,
P.BACKGROUND,
S.STATUS,
RTRIM(SUBSTR(A.SQL_TEXT, 1, 80)) SQLFROM
FROM V
P
R
O
C
E
S
S
P
,
V
PROCESS P, V
PROCESSP,VSESSION S, VKaTeX parse error: Expected 'EOF', got '#' at position 79: …+) AND P.SERIAL#̲ <> '1'; 检查运行很久…SESSION_LONGOPS, VKaTeX parse error: Unexpected character: '' at position 89: …E = HASH_VALUE;̲ 等待时间最多的 5 个系统等…SYSTEM_EVENT
WHERE EVENT NOT LIKE ‘SQL%’
ORDER BY TOTAL_WAITS DESC)
WHERE ROWNUM <= 5;
–查找前十条性能查的sql
SELECT *
FROM (SELECT PARSING_USER_ID EXECUTIONS,
SORTS,
COMMAND_TYPE,
DISK_READS,
SQL_TEXT
FROM V$SQLAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;
–查看正在运行job
SELECT * FROM DBA_JOBS_RUNNING;
–检查数据job的完成情况
SELECT JOB,LOG_USER,LAST_DATE,NEXT_DATE,WHAT,FAILURES
FROM DBA_JOBS;
SELECT * FROM DBA_JOBS_RUNNING;
–查看数据库job的完成情况
SELECT JOB,LOG_USER,LAST_DATE,NEXT_DATE,WHAT,FAILURES FROM DBA_JOBS;
–查看正在运行的job对应的sid,serial#,spid
SELECT A.JOB, B.SID, B.SERIAL#, C.SPID
FROM DBA_JOBS_RUNNING A, V
S
E
S
S
I
O
N
B
,
V
SESSION B, V
SESSIONB,VPROCESS C
WHERE A.SID = B.SID
AND B.PADDR = C.ADDR
AND JOB = ‘21’;
–通过sid查询serial#，spid
SELECT B.SID,B.SERIAL#,C.SPID
FROM V
S
E
S
S
I
O
N
B
,
V
SESSION B,V
SESSIONB,VPROCESS C
WHERE B.SID=‘554’
AND B.PADDR=C.ADDR
–停止会话
SID:554 SERIAL#:1123 SPID:23232
oracle级别
ALTER SYSTEM KILL SESSION ‘554,1123’
系统级别
kill -9 spid