oracle中维护文件,ORACLE数据库日常维护

ORACLE 日常维护手册

查看数据库版本

SELECT*FROMV$VERSION;

查看数据库语言环境

SELECTUSERENV('LANGUAGE')FROMDUAL;

查看ORACLE实例状态

SELECTINSTANCE_NAME,HOST_NAME,STARTUP_TIME,STATUS,DATABASE_STATUS

FROMV$INSTANCE;

查看ORACLE监听状态

lsnrctl status

查看数据库归档模式

SELECTNAME,LOG_MODE,OPEN_MODEFROMV$DATABASE;

查看回收站中对象

SELECTOBJECT_NAME,ORIGINAL_NAME,TYPEFROMRECYCLEBIN;

清空回收站中对象

PURGERECYCLEBIN;

还原回收站中的对象

FLASHBACKTABLE"BIN$GOZUQZ6GS222JZDCCTFLHQ==$0"TOBEFOREDROPRENAMETOTEST;

闪回误删除的表

FLASHBACKTABLEAAATOBEFOREDROP;

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

ALTERTABLETESTENABLEROWMOVEMENT;

FLASHBACKTABLETESTTOTIMESTAMPTO_TIMESTAMP('2009-10-15 21:17:47','YYYY-MM-DD HH24:MI:SS');

查看当前会话

SELECTSID,SERIAL#,USERNAME,PROGRAM,MACHINE,STATUSFROMV$SESSION;

查看DDL锁

SELECT*

FROMDBA_DDL_LOCKS

WHEREOWNER='FWYANG';

检查等待事件

SELECTSID,A.USERNAME,EVENT,WAIT_CLASS,T1.SQL_TEXT

FROMV$SESSION A,V$SQLAREA T1

WHEREWAIT_CLASS<>'Idle'

ANDA.SQL_ID=T1.SQL_ID;

检查数据文件状态

SELECTFILE_NAME,STATUSFROMDBA_DATA_FILES;

检查表空间使用情况

SELECTUPPER(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(SELECTTABLESPACE_NAME,

ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,

ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTES

FROMSYS.DBA_FREE_SPACE

GROUPBYTABLESPACE_NAME)F,

(SELECTDD.TABLESPACE_NAME,

ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MB

FROMSYS.DBA_DATA_FILES DD

GROUPBYDD.TABLESPACE_NAME)D

WHERED.TABLESPACE_NAME=F.TABLESPACE_NAME

ORDERBY4DESC;

收缩表空间

ALTERTABLESPCE TS_AJ_DATACOALESCE;

增加表空间大小

SELECTT.TABLESPACE_NAME,T.FILE_NAME,T.BYTES/1024/1024/1024

FROMDBA_DATA_FILES T

WHERET.TABLESPACE_NAME='TS_AJ_DATA';

ALTERTABLESPACETS_AJ_DATAADDDATAFILE'/DATA/TS_AJ_DATA05_10G.DBF'

SIZE10000M

AUTOEXTENDOFF;

检查不起作用的约束

SELECTOWNER,CONSTRAINT_NAME,TABLE_NAME,CONSTRAINT_TYPE,STATUS

FROMDBA_CONSTRAINTS

WHERESTATUS='DISABLE';

检查发生坏块的数据库对象

SELECTTABLESPACE_NAME,SEGMENT_TYPE,OWNER,SEGMENT_NAME

FROMDBA_EXTENTS

WHEREFILE_ID=

ANDBETWEENBLOCK_IDANDBLOCK_ID+BLOCKS-1;

检查无效的数据库对象

SELECTOWNER,OBJECT_NAME,OBJECT_TYPE

FROMDBA_OBJECTS

WHERESTATUS='INVALID';

查看语句执行进度

SELECTSE.SID,

OPNAME,

TRUNC(SOFAR/TOTALWORK*100,2)||'%'ASPCT_WORK,

ELAPSED_SECONDS ELAPSED,

ROUND(ELAPSED_SECONDS*(TOTALWORK-SOFAR)/SOFAR)REMAIN_TIME,

SQL_TEXT

FROMV$SESSION_LONGOPS SL,V$SQLAREA SA,V$SESSION SE

WHERESL.SQL_HASH_VALUE=SA.HASH_VALUE

ANDSL.SID=SE.SID

ANDSOFAR!=TOTALWORK

ORDERBYSTART_TIME;

检查碎片程度高的表

SELECTSEGMENT_NAME TABLE_NAME,COUNT(*)EXTENTS

FROMDBA_SEGMENTS

WHEREOWNERNOTIN('SYS','SYSTEM')

GROUPBYSEGMENT_NAME

HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))

FROMDBA_SEGMENTS

GROUPBYSEGMENT_NAME);

检查表空间的 I/O 比例

SELECTDF.TABLESPACE_NAMENAME,

DF.FILE_NAME       "FILE",

F.PHYRDS           PYR,

F.PHYBLKRD         PBR,

F.PHYWRTS          PYW,

F.PHYBLKWRT        PBW

FROMV$FILESTAT F,DBA_DATA_FILESDF

WHEREF.FILE#=DF.FILE_ID

ORDERBYDF.TABLESPACE_NAME;

检查碎片程度高的表

SELECTsegment_name table_name,COUNT(*)extents

FROMdba_segments

WHEREownerNOTIN('SYS','SYSTEM')

GROUPBYsegment_name

HAVINGCOUNT(*)=(SELECTMAX(COUNT(*))

FROMdba_segments

GROUPBYsegment_name);

检查文件系统的 I/O 比例

SELECTSUBSTR(A.FILE#,1,2)"#",

SUBSTR(A.NAME,1,30)"NAME",

A.STATUS,

A.BYTES,

B.PHYRDS,

B.PHYWRTS

FROMV$DATAFILE A,V$FILESTAT B

WHEREA.FILE#=B.FILE#;

检查消耗CPU最高的进程

SELECTP.PID PID,

S.SIDSID,

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

FROMV$PROCESS P,V$SESSION S,V$SQLAREA A

WHEREP.ADDR=S.PADDR

ANDS.SQL_ADDRESS=A.ADDRESS(+)

ANDP.SERIAL#<>'1';

检查运行很久的SQL

SELECTUSERNAME,

SID,

OPNAME,

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

TIME_REMAINING,

SQL_TEXT

FROMV$SESSION_LONGOPS,V$SQL

WHERETIME_REMAINING<>0

ANDSQL_ADDRESS=ADDRESS

ANDSQL_HASH_VALUE=HASH_VALUE;

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

SELECT*

FROM(SELECT*

FROMV$SYSTEM_EVENT

WHEREEVENTNOTLIKE'SQL%'

ORDERBYTOTAL_WAITSDESC)

WHEREROWNUM<=5;

查找前十条性能差的SQL

SELECT*

FROM(SELECTPARSING_USER_ID EXECUTIONS,

SORTS,

COMMAND_TYPE,

DISK_READS,

SQL_TEXT

FROMV$SQLAREA

ORDERBYDISK_READSDESC)

WHEREROWNUM<10;

检查死锁

SELECTbs.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

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

WHEREhk.BLOCK=1

ANDhk.lmode!=0

ANDhk.lmode!=1

ANDwk.request!=0

ANDwk.TYPE(+)=hk.TYPE

ANDwk.id1(+)=hk.id1

ANDwk.id2(+)=hk.id2

ANDhk.SID=bs.SID(+)

ANDwk.SID=ws.SID(+)

AND(bs.usernameISNOTNULL)

AND(bs.username<>'SYSTEM')

AND(bs.username<>'SYS')

ORDERBY1;

查看正在运行的JOB

SELECT*FROMDBA_JOBS_RUNNING;

检查数据库JOB的完成情况

SELECTJOB,LOG_USER,LAST_DATE,NEXT_DATE,WHAT,FAILURES

FROMDBA_JOBS;

查看正在运行的JOB对应的SID、SERIAL#、SPID

SELECTA.JOB,B.SID,B.SERIAL#,C.SPID

FROMDBA_JOBS_RUNNING A,V$SESSION B,V$PROCESS C

WHEREA.SID=B.SID

ANDB.PADDR=C.ADDR

ANDJOB='21';

通过SID查询SERIAL#、SPID

SELECTB.SID,B.SERIAL#,C.SPID

FROMV$SESSION B,V$PROCESS C

WHEREB.SID='554'

ANDB.PADDR=C.ADDR;

停止会话

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

ORACLE级别

ALTERSYSTEMKILLSESSION'554,1134';

操作系统级别

kill -9 23242