文件名称:
OracleDBA日常维护手册(最全最实用).pdf
开发工具:
文件大小: 453kb
下载次数: 0
上传时间: 2019-07-04
详细说明：OracleDBA日常维护手册(最全最实用)非常适合DBA参考使用，包含日常维护常用命令。资源由
美河学习在线收集提供
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
GROLP BY DD TABLESPACE NAME)D
WHERE D. TABLESPACE NAME F, TABLESPACE NAME
ORDER BY 4 DESC
收缩表空间
ALTER TABLESPCE TS AJ DATA COALESCE
增加表空间大小
SELECT T TABLESPACE NAME, T FILE NAME, T BYTES /1024/1024/1024
FROM DBA DATA FILES T
WHERE T TABLESPACE NAME =TS AJ DATA
ALTER TABLESPACE TS AJ DATA ADD DATAFILE /DATA/TS AJ DATAO5 10G DBF
SIZE 10000M
AUTOEXTEND OFF
检查不起作用的约束
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 D AND BLOCK D+ BLOCKS -1
资源由
美河学习在线收集提供
检查无效的数据库对象
SELECT OWNER, OBJECT NAME, OBJECT TYPE
FROM DBA OBJECTS
WHERE STATUS = INVALID
查看语句执行进度
SELECT SE. SID
OPNAMD
TRUNC(SOFAR/ TOTALWORK 100, 2)7%' AS PCT WORK
ELAPSED SECONDS ELAPSED
ROUND (ELAPSED SECONDS *(TOTALWORK-SOFAR)/ SOFAR) REMAIN TIME
SQL TEXT
FROM VSSESS ION LONGOPS SL, VSSQLAREA SA, VSSESSION SE
WHERE SL SQL HASH VALUE SA HASH VALUE
AND SL. SID SE SID
ND 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)
检查表空间的/o比例
SELECT DE TABLESPACE NAME NAME
DE FILE NAME
FILE
F. PHYRDS
F. PHYBLKRD
F. PHYWRTS
PYW
F. PHYBLKWRT
PBW
FROM VSFILESTAT F, DBA DATA FILES DF
WHERE F FILE# DF FILE D
ORDER BY DE TABLESPACE NAME
资源由
美河学习在线收集提供
检查碎片程度高的表
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 segmen ts
GROUP BY segment name)
检查文件系统的/o比例
SELECT SUBSTR(A FILE#, 1, 2)#
SUBSTR(A NAME, 1, 30)NAME
A STATUS
A BYTES
B. PHYRDS
B. PHYWRTS
FROM VSDATAFILE A, VSFILESTAT B
WHERE A FILE# =B FILE#
检查消耗CPU最高的进程
SELECT P PIDPID
S SIDSID
P SPIDSPID
S USERNAME LSERNAME
S OSUSER OSNAME
P. SERLALE S
P TERMINAL
P PROGRAM PROGRAM
P BACKGROUND
S STATUS
RTRIM(SUBSTR (A SQL TEXT, 1, 80))SQLFROM
FROM VSPROCESS P, VSSESSION S, VSSQLAREA A
WHERE P ADDR =S PADDR
AND S SQL ADDRESS= A ADDRESS (+)
ANDP. SERIAL#<>’1
检查运行很久的sQL
SELECT LSERNAME
资源由
美河学习在线收集提供
OPNAME
ROUND (SOFAR 100/ TOTALWORK, 0)% AS PROGRESS
TIME REMAINING,
SQL TEXT
FROM VSSESS ION LONGOPS, VSSQL
WHERE TIME REMAINING >0
AND SQL ADDRESS ADDRESS
AND SQL HASH VALUE HASH VALUE
等待时间最多的5个系统等待事件的获取
SELECT水
FROM (SELECT y
FROM VSSYSTEM 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 VSSQLAREA
ORDER BY DISK READS DESC)
WHERE ROWNUM 10
检査死锁
SELECT bs username B
locking user bs username DB Use/
WSusername Waiting User, bs. SID SID, WS. SID WSID
bs serial# Serial#, bs. sql address address
bs. sql hash value Sql hash", bs programm ing App
wS program Waiting App, bs machinc Blocking Machin
wSmachine Waiting Machine, bs osuser Blocking OS User
wS osuser Waiting OS User, bs serial# Serial#
ws serial# WSerial=
DECODE (wk. TYPI
资源由
美河学习在线收集提供
MR,’ Media recovery
RT’,’ Redo thread
UN',’ USER Name
TX’,’T
DMI
LL PL/SQL USER LOCK
DX. Distributed xacti
CF,’ Control file’,
Instance state
FS’,’ FILE SET
IR, Instance Recovery
ST Disk space ti
TS, Temp Segment
Cache invalidation
LS’,’ LOG START OR Switch,
RW,’ROWW
SQ
Sequence number 7
TE Extend table
TT,’ Temp TABLE’,
wk TYPE
) lock t
DECODE (hk Imode
0,N
1, NULL
2,’ROW-S(SS)
3,’ROW-X(SX)2
4. SHARE
5, S/ROW-X(SSX)
6. EXCLUSIVE
TO CHAR (hk. I mode)
mode held
DECODE (wk. request
0,’No
1,’NULL
2,’ROW-S(SS)’,
3,’ROW-X(Sx)
4. SHARE
5,S/ROW-X(SSX)
6. EXCLUSIVE
TO CHAR (wk request)
mode requested
TO CHAR (hk id1) lock idl, TO CHAR(hk id2) lock id2
DECODE
(hk BLOCK.
资源由
美河学习在线收集提供
0. NOT Blocking
/**// Not blocking any other processes
1, Blocking
/**//* This lock blocks other
processes为
2. Global
**k//* This lock is global, so we cant tell
TO CHAR(hk BLOCK)
blocking others
ROM vSlock hk,
assession
bs, vslock wk
midsession ws
WheRE hk BLOCK =1
AND hk lmode =0
AND hk lmode =1
AND wk request !=0
AND wk TYPE (+) hk. TYPE
AND wk. idI(+)=hk idl
AND wk. id2(+)= hk id2
AND hk. SID= bs. SID(+)
AND wk. SID=WS. SID(+)
AND (bs username IS NOT NULL)
AND (bs username ( SYSTEM)
AND(bs username (>'SYS)
ORDER BY 1
查看正在运行的J0B
SELECT FROM DBA JOBS RUNNING
检查数据库JoB的完成情况
SELECT JOB, LOG USER, LAST DATE, NEXT DATE, WHAT, FAILURES
FROM DBA JOBS
查看正在运行的J0B对应的SD、 SERIAL#、SPID
SELECT AJOB, B. SID, B SERIAL#, C SPID
FROM DBA JOBS RUNNING A, VSSESSION B, VSPROCESS C
WHERE A SID=B. SID
AND B PADDR =C ADDR
AND JOB=21
资源由
美河学习在线收集提供
通过SD查询 SERIAL#、SP|D
SELECT B. SID, B SERIAL#, C SPID
FROM VSSESSION B. VSPROCESS C
WHERE B. SID =554
AND B PADDR =C ADDR
停止会话
-S1D:554SERI4Lx:34SP1D:23242
ORACLE级别
ALTER SYSTEM KILL SESSION.1134
操作系统级别
ki11-923242
(系统自动生成,下载前可以参看下载内容)
下载文件列表
相关说明
- 本站资源为会员上传分享交流与学习,如有侵犯您的权益,请联系我们删除.
- 本站是交换下载平台，提供交流渠道，下载内容来自于网络，除下载问题外，其它问题请自行百度。
- 本站已设置防盗链，请勿用迅雷、QQ旋风等多线程下载软件下载资源，下载后用WinRAR最新版进行解压.
- 如果您发现内容无法下载，请稍后再次尝试；或者到消费记录里找到下载记录反馈给我们.
- 下载后发现下载的内容跟说明不相乎，请到消费记录里找到下载记录反馈给我们，经确认后退回积分.
- 如下载前有疑问，可以通过点击"提供者"的名字，查看对方的联系方式，联系对方咨询.
