大佬教程收集整理的这篇文章主要介绍了ORACLE数据库日常维护,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
SELECT USERENV('LANGUAGE') FROM DUAL;
@H_696_8@查看OracLE实例状态SELECT INSTANCE_name,host_name,STARTUP_TIME,STATUS,DATABASE_STATUS
FROM v$INSTANCE;
@H_696_8@查看OracLE监听状态lsnrctl status
@H_696_8@查看数据库归档模式
SELECT name,LOG_MODE,@R_197_3997@ FROM v$DATABASE;
@H_696_8@查看回收站中对象SELECT object_name,original_name,TYPE FROM RECYCLEBIN;
@H_696_8@清空回收站中对象
PURGE RECYCLEBIN;
@H_696_8@还原回收站中的对象
FLASHBACK table "BIN$GOZUQZ6GS222JZDCCTFLHQ==$0" TO BEFORE DROP Rename TO TEST;
@H_696_8@闪回误删除的表FLASHBACK table AAA TO BEFORE DROP;
@H_696_8@闪回表中记录到某一时间点
ALTER table TEST ENABLE ROW @H_152_13@mOVEMENT;
FLASHBACK table TEST TO timestAMP TO_timestAMP('2009-10-15 21:17:47','YYYY-MM-DD HH24:MI:SS');
@H_696_8@查看当前会话SELECT SID,serial#,USERname,PROGRAM,@H_562_15@mACHINE,STATUS FROM v$sesSION;
@H_696_8@查看DDL锁SELECT *
FROM DBA_DDL_LOCKS
WHERE owneR = 'FWYANG';
@H_696_8@检查等待事件SELECT SID, A.USERname, EVENT, WAIT_CLASS, T1.sql_TEXT
FROM v$session a, v$sqlAREA T1
WHERE WAIT_CLASS <> 'IDle'
AND A.sql_ID = T1.sql_ID;
@H_696_8@检查数据文件状态SELECT file_name,STATUS FROM DBA_DATA_fileS;
@H_696_8@检查表空间使用情况SELECT UPPER(F.tablespace_name) "表空间名",
D.TOT_GROottE_MB "表空间大小(M)",
D.TOT_GROottE_MB - F.@R_832_10586@L_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROottE_MB - F.@R_832_10586@L_BYTES) / D.TOT_GROottE_MB * 100, 2),
'990.99') "使用比",
F.@R_832_10586@L_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT tablespace_name,
ROUND(SUM(BYTES) / (1024 * 1024), 2) @R_832_10586@L_BYTES,
ROUND(@H_152_13@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 4 DESC;
@H_456_874@ @H_696_8@收缩表空间ALTER tableSPCE TS_AJ_DATA COALESCE;
@H_696_8@增加表空间大小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_DATA05_10G.DBF'
SIZE 10000@H_562_15@m
autoEXTEND OFF;
@H_696_8@检查不起作用的约束SELECT owneR, constraint_name, table_name, constraint_type, STATUS
FROM DBA_CONSTraiNTS
WHERE STATUS = 'disABLE';
@H_696_8@检查发生坏块的数据库对象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;
@H_696_8@检查无效的数据库对象SELECT owneR,object_name,object_type
FROM dba_objecTS
WHERE STATUS ='INVALID';
@H_696_8@查看语句执行进度SELECT SE.SID,
OPname,
Trunc(SOFAR / @R_832_10586@LWORK * 100, 2) || '%' AS PCT_WORK,
ELAPSED_SECONDS ELAPSED,
ROUND(ELAPSED_SECONDS * (@R_832_10586@LWORK - SOFAR) / SOFAR) REMAIN_TIME,
sql_TEXT
FROM v$sesSION_LONGOPS SL, v$sqlAREA SA, v$sesSION SE
WHERE Sl.sql_hash_value = SA.hash_value
AND Sl.SID = SE.SID
AND SOFAR != @R_832_10586@LWORK
ORDER BY START_TIME;
@H_696_8@检查碎片程度高的表
SELECT SEGMENT_NAME table_name, COUNT(*) extents
FROM DBA_SEGMENTS
WHERE owneR NOT IN ('SYS', 'SYstem')
GROUP BY SEGMENT_NAME
HAVING COUNT(*) = (SELECT @H_152_13@mAX(COUNT(*))
FROM DBA_SEGMENTS
GROUP BY SEGMENT_NAME);
@H_696_8@检查表空间的 I/O 比例
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
ORDER BY DF.tablespace_name;
@H_696_8@检查碎片程度高的表SELECT SEGMENT_NAME table_name, COUNT(*) extents
FROM dba_segments
WHERE owner NOT IN ('SYS', 'SYstem')
GROUP BY SEGMENT_NAME
HAVING COUNT(*) = (SELECT @H_152_13@mAX(COUNT(*))
FROM dba_segments
GROUP BY SEGMENT_NAME);
@H_696_8@检查文件系统的 I/O 比例SELECT SUBSTR(A.file#, 1, 2) "#",
SUBSTR(A.name, 30) "name",
A.STATUS,
A.bYTES,
B.PHYRDS,
B.PHYWRTS
FROM v$DATAFILE A, v$fileSTAT B
@H_696_8@检查消耗cpu最高的进程
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, 80)) sqlFROM
FROM v$process p, v$sesSION S, v$sqlAREA A
AND s.sql_ADDRESS = A.ADDRESS(+)
AND P.serial# <> '1';
@H_696_8@检查运行很久的sqlSELECT USERname,
SID,
ROUND(SOFAR * 100 / @R_832_10586@LWORK, 0) || '%' AS PROGRESS,
TIME_REMAINING,
sql_TEXT
WHERE TIME_REMAINING <> 0
AND sql_ADDRESS = ADDRESS
AND sql_hash_value = hash_value;
@H_696_8@等待时间最多的5个系统等待事件的获取SELECT *
FROM (SELECT *
FROM v$SYstem_EVENT
WHERE EVENT NOT liKE 'sql%'
ORDER BY TOTAL_WAITS DESC)
WHERE ROWNUM <= 5;
@H_696_8@查找前十条性能差的sql
SELECT *
FROM (SELECT PARSING_user_iD EXECUTIONS,
SORTS,
COMMAND_TYPE,
sql_TEXT
FROM v$sqlAREA
ORDER BY DISK_READS DESC)
WHERE ROWNUM < 10;
@H_696_8@检查死锁SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "WaiTing User", bs.sid "SID", ws.sid "WSID",
bs.serial# "serial#", bs.sql_address "address",
bs.sql_hash_value "sql hash", bs.program "Blocking App",
ws.program "WaiTing App", bs.machine "Blocking Machine",
ws.machine "WaiTing Machine", bs.osuser "Blocking OS User",
ws.osuser "WaiTing OS User", bs.serial# "serial#",
DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER name',
'TX', 'transaction',
'TM', 'DML',
'ul', 'pl/sql USER LOCK',
'DX', 'diStributed Xaction',
'CF', 'Control file',
'IS', 'Instance State',
'FS', 'file SET',
'IR', 'Instance Recovery',
'ST', 'disk SPACE transaction',
'TS', 'Temp Segment',
'IV', 'library Cache InvalIDation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence number',
'TE', 'Extend table',
'TT', 'temp table',
wk.TYPE
) lock_type,
DECODE (hk.@R_262_8729@,
0, 'None',
1, 'NulL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
) mode_Held,
DECODE (wk.request,
) mode_requested,
TO_CHAR (hk.ID1) lock_ID1, TO_CHAR (hk.ID2) lock_ID2,
DECODE
(hk.bLOCK, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global,so we can't tell */
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.bLOCK = 1
AND wk.TYPE(+) = hk.TYPE
AND wk.ID1(+) = hk.ID1
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;
@H_696_8@查看正在运行的JOB
SELECT * FROM DBA_JOBS_RUNNING;
@H_696_8@检查数据库JOB的完成情况SELECT JOB, LOG_USER, LAST_DATE, next_date, what, @R_853_4895@
FROM DBA_JOBS;
@H_696_8@查看正在运行的JOB对应的SID、serial#、SPIDSELECT A.JOB, B.SID, b.serial#, C.SPID
FROM DBA_JOBS_RUNNING A, v$sesSION B, v$PROCESS C
WHERE A.SID = B.SID
AND B.paddr = C.ADDR
AND JOB = '21';
@H_696_8@通过SID查询serial#、SPIDSELECT B.SID, C.SPID
WHERE B.SID = '554'
AND B.paddr = C.ADDR;
@H_696_8@停止会话--SID:554 serial#:1134 SPID:23242
OracLE级别
ALTER SYstem KILL SESSION '554,1134';
操作系统级别
kill -9 23242
以上是大佬教程为你收集整理的ORACLE数据库日常维护全部内容,希望文章能够帮你解决ORACLE数据库日常维护所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。