oracle 日常维护常用命令

oracle 日常维护常用命令

–查看数据库版本
SELECT * FROM V$VERSION;--查看数据库语言环境SELECTUSERENV{(}^{\prime }LANGUAG{E}^{\prime })FROMDUAL;--查看oracle实例状态SELECTINSTANC{E}_{N}AME,HOS{T}_{N}AME,STARTU{P}_{T}IME,STATUS,DATABAS{E}_{S}TATUSFROMV$INSTANCE;
–查看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$sessiont,v$locked_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$SESSIONA,V$SQLAREA 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$SESSIO{N}_{L}ONGOPSSL,V$SQLAREA 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$DATAFILEA,V$FILESTAT 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$PROCESSP,V$SESSION 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$SESSIONB,V$PROCESS 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$SESSIONB,V$PROCESS 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