精心整理了一套Oracle日常巡检脚本，速速收藏！

sqlplus  nologconn as sysdba(或者conn 账号/密码)
复制

set linesize 500;select instance_name,host_name,startup_time,status,database_statusfrom v$instance;
复制

select group#,status,type,member from v$logfile;
复制

select tablespace_name,status from dba_tablespaces;
复制

select name,status from v$datafile;
复制

select owner,object_name,object_typefrom dba_objectswhere status!='VALID' and owner!='SYS' and owner!='SYSTEM';
复制

SELECT owner, object_name, object_typeFROM dba_objectsWHERE status= 'INVALID';
复制

select segment_name,status from dba_rollback_segs;
复制

select resource_name,max_utilization,initial_allocation, limit_valuefrom v$resource_limit;
复制

select count(*) from v$session;select sid,serial#,username,program,machine,status from v$session;
复制

alter system kill session 'SID,SERIAL#';
复制

[oracle@local ~]$ df -h
复制

select f.tablespace_name,a.total,f.free,round((f.free  a.total) * 100) "% Free"from (select tablespace_name, sum(bytes / (1024 * 1024)) totalfrom dba_data_filesgroup by tablespace_name) a, (select tablespace_name, round(sum(bytes / (1024 * 1024))) freefrom dba_free_spacegroup by tablespace_name) fWHERE a.tablespace_name = f.tablespace_name(+)order by "% Free";
复制

select Segment_Name,Segment_Type,TableSpace_Name, (Extents  Max_extents) * 100 PercentFrom sys.DBA_SegmentsWhere Max_Extents != 0and (Extents  Max_extents) * 100 >= 95order By Percent;
复制

select distinct (owner)from dba_tableswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM'unionselect distinct (owner)from dba_indexeswhere tablespace_name = 'SYSTEM'and owner != 'SYS'and owner != 'SYSTEM';
复制

select a.table_name, a.next_extent, a.tablespace_namefrom all_tables a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunkunionselect a.index_name, a.next_extent, a.tablespace_namefrom all_indexes a, (select tablespace_name, max(bytes) as big_chunk from dba_free_space group by tablespace_name) fwhere f.tablespace_name = a.tablespace_name and a.next_extent > f.big_chunk;
复制

#cat backup/hotbackup/hotbackup-09-7-22.log|grep –i error
复制

#ls –lt backup/hotbackup
复制

#tail –n 300 var/mail/oracle
复制

set pages 80set lines 120col event for a40select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAITfrom v$session_waitwhere event not like 'SQL%'and event not like 'rdbms%';
复制

SELECT SQL_TEXTFROM (SELECT * FROM V$SQLAREA ORDER BY DISK_READS)WHERE ROWNUM <= 5;
复制

SELECT *FROM (SELECT PARSING_USER_ID EXECUTIONS,SORTS,COMMAND_TYPE,DISK_READS, SQL_TEXTFROM V$SQLAREAORDER BY DISK_READS DESC)WHERE ROWNUM < 10;
复制

SELECT *FROM (SELECT *FROM V$SYSTEM_EVENTWHERE EVENT NOT LIKE 'SQL%'ORDER BY TOTAL_WAITS DESC)WHERE ROWNUM <= 5;
复制

COLUMN USERNAME FORMAT A12COLUMN OPNAME FORMAT A16COLUMN PROGRESS FORMAT A8SELECT USERNAME,       SID,       OPNAME,       ROUND(SOFAR * 100 / TOTALWORK, 0) || '%' AS PROGRESS,       TIME_REMAINING,       SQL_TEXTFROM V$SESSION_LONGOPS, V$SQLWHERE TIME_REMAINING <> 0AND SQL_ADDRESS = ADDRESSAND SQL_HASH_VALUE = HASH_VALUE;
复制

SET LINE 240SET VERIFY OFFCOLUMN SID FORMAT 999COLUMN PID FORMAT 999COLUMN S_# FORMAT 999COLUMN USERNAME FORMAT A9 HEADING "ORA USER"COLUMN PROGRAM FORMAT A29COLUMN SQL FORMAT A60COLUMN OSNAME FORMAT A9 HEADING "OS USER"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 V$PROCESS P,       V$SESSION S,       V$SQLAREA A WHERE P.ADDR = S.PADDR AND S.SQL_ADDRESS = A.ADDRESS(+) AND P.SPID LIKE '%&1%';
复制

SELECT segment_name table_name, COUNT(*) extentsFROM dba_segmentsWHERE owner NOT IN ('SYS', 'SYSTEM')GROUP BY segment_nameHAVING COUNT(*) = (SELECT MAX(COUNT(*))                     FROM dba_segments                    GROUP BY segment_name);
复制

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 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#;
复制

select sid,       serial#,       username,       SCHEMANAME,       osuser,       MACHINE,        terminal,       PROGRAM,       owner,       object_name,       object_type,       o.object_id  from dba_objects o, v$locked_object l, v$session s where o.object_id = l.object_id   and s.sid = l.session_id;
复制

alter system kill session '&sid,&serial#';
复制

#kill -9 pid
复制

#top
复制

# free -m
复制

# iostat -k 1 3
复制

#uptime
复制

select spid from v$process where addr not in (select paddr from v$session);
复制

select table_name, num_rows, chain_cntFrom dba_tablesWhere owner = 'CTAIS2'And chain_cnt <> 0;
复制

analyze table tablename list chained rows;
复制

create table aa as select a.* from sb_zsxx a,chained_rows b where a.rowid=b.head_rowid and b.table_name ='SB_ZSXX';delete from sb_zsxx where rowid in (select head_rowid from chained_rows where table_name = 'SB_ZSXX');insert into sb_zsxx select * from chained_row where table_name = 'SB_ZSXX';
复制

Select table_name,num_rows,last_analyzed From user_tables where table_name ='DJ_NSRXX'
复制

exec sys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade => TRUE,degree => 4);
复制

SELECT a.VALUE + b.VALUE logical_reads,       c.VALUE phys_reads,       round(100 * (1 - c.value  (a.value + b.value)), 4) hit_ratioFROM v$sysstat a, v$sysstat b, v$sysstat cWHERE a.NAME = 'db block gets'   AND b.NAME = 'consistent gets'   AND c.NAME = 'physical reads';
复制

select sum(pinhits)  sum(pins) * 100 from v$librarycache;
复制

select name,value from v$sysstat where name like '%sort%';
复制

select name,value from v$sysstat where name in ('redo entries','redo buffer allocation retries');
复制

# grep -i accepted var/log/secure
复制

# grep -i inval var/log/secure &&grep -i failed var/log/secure
复制

alter user USER_NAME identified by PASSWORD;
复制

exec dbms_workload_repository.create_snapshot();
复制

@?/rdbms/admin/awrrpt.sql
复制