ORACLE数据库日常维护

ORACLE 日常维护手册

查看数据库版本

SELECT * FROM V$VERSION;

查看数据库语言环境

SELECT USERENV('LANGUAGE') FROM DUAL;

查看ORACLE实例状态

SELECT INSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUS

FROM V$INSTANCE;

查看ORACLE监听状态

lsnrctl status

查看数据库归档模式

SELECT NAME,LOG_MODE,OPEN_MODE FROM V$DATABASE;

查看回收站中对象

SELECT OBJECT_NAME,ORIGINAL_NAME,TYPE FROM RECYCLEBIN;

清空回收站中对象

PURGE RECYCLEBIN;

还原回收站中的对象

FLASHBACK TABLE "BIN$GOZUQZ6GS222JZDCCTFLHQ==$0" TO BEFORE DROP RENAME TO TEST;

闪回误删除的表

FLASHBACK TABLE AAA TO BEFORE DROP;

闪回表中记录到某一时间点

ALTER TABLE TEST ENABLE ROW MOVEMENT;

FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP('2009-10-15 21:17:47','YYYY-MM-DD HH24:MI:SS');

查看当前会话

SELECT SID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUS FROM V$SESSION;

查看DDL锁

SELECT *

FROM DBA_DDL_LOCKS

WHERE OWNER = 'FWYANG';

检查等待事件

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;

检查数据文件状态

SELECT FILE_NAME,STATUS FROM DBA_DATA_FILES;

检查表空间使用情况

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",

  D.TOT_GROOTTE_MB "表空间大小(M)",

 D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",

  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

FROM SYS.DBA_DATA_FILES DD

GROUP 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_DATA05_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_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$SESSION_LONGOPS SL, 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/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

WHERE F.FILE# = DF.FILE_ID

ORDER BY DF.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_segments

GROUP BY segment_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$DATAFILE A, 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$PROCESS P, V$SESSION S, V$SQLAREA A

WHERE P.ADDR = S.PADDR

AND S.SQL_ADDRESS = A.ADDRESS(+)

AND P.SERIAL# <> '1';

检查运行很久的SQL

SELECT USERNAME,

SID,

       OPNAME,

ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,

       TIME_REMAINING,

       SQL_TEXT

FROM V$SESSION_LONGOPS, V$SQL

WHERE TIME_REMAINING <> 0

AND SQL_ADDRESS = ADDRESS

AND SQL_HASH_VALUE = HASH_VALUE;

等待时间最多的5个系统等待事件的获取

SELECT *

FROM (SELECT *

FROM V$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;

检查死锁

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#",

         ws.serial# "WSerial#",

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.lmode,

0, 'None',

1, 'NULL',

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE',

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

                 TO_CHAR (hk.lmode)

) mode_held,

DECODE (wk.request,

0, 'None',

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_id1, 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', /**//* This lock is global, so we can't tell */

             TO_CHAR (hk.BLOCK)

) blocking_others

FROM v$lock hk, v$session bs, v$lock wk, v$session ws

WHERE hk.BLOCK = 1

AND hk.lmode != 0

AND hk.lmode != 1

AND wk.request != 0

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;

查看正在运行的JOB

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$SESSION B, 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$SESSION B, V$PROCESS C

WHERE B.SID = '554'

AND B.PADDR = C.ADDR;

停止会话

--SID:554 SERIAL#:1134 SPID:23242

ORACLE级别

ALTER SYSTEM KILL SESSION '554,1134';

操作系统级别

kill -9 23242

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户画像

新闻发布 sql server代码

834
来自专栏沃趣科技

SQL优化案例-分区索引之无前缀索引(六)

无前缀索引:分区索引不包含分区字段就叫无前缀索引,那么什么时候用无前缀索引和前缀索引呢?

1302
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

731
来自专栏Ryan Miao

oracle创建表相关

1 --创建表 2 create table person( 3 id number primary key, 4 name varchar2(40),...

2675
来自专栏杨建荣的学习笔记

通过shell脚本得到数据字典的信息 (r2笔记72天)

在平时的工作中,可能需要查询一些数据字典的信息,比如数据字典对应的基表信息,可以得到更多数据库内部的一些详细信息。 比如user_objects这个数据字典视图...

2917
来自专栏杨建荣的学习笔记

oracle坏块修复实例

最近几天发现库里有坏块了,环境是11gR2, linux平台的64位的库。以下是我的修复办法,基于dbms_repair做的在线修复,也可以基于备份rman来修...

3629
来自专栏乐沙弥的世界

Oracle 性能相关常用脚本(SQL)

在缺乏的可视化工具来监控数据库性能的情形下,常用的脚本就派上用场了,下面提供几个关于Oracle性能相关的脚本供大家参考。以下脚本均在Oracle 10g测试通...

1252
来自专栏me的随笔

T-SQL基础(五)之增删改

在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

892
来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之数据字典元数据(r4笔记第33天)

MySQL和Oracle虽然在架构上有很大的不同,但是如果从某些方面比较起来,它们有些方面也是相通的。 毕竟学习的主线是MySQL,所以会从MySQL的角度来对...

3026
来自专栏自由而无用的灵魂的碎碎念

oracle 10g 手动创建scott(tiger) schema

转自:http://cnhtm.itpub.net/post/39970/496967

1203

扫码关注云+社区

领取腾讯云代金券