Oracle 数据库日常巡检

阅读目录

• 1. 检查数据库基本状况
• 2. 检查Oracle相关资源的使用情况
• 3. 检查Oracle数据库备份结果
• 4. 检查Oracle数据库性能
• 5. 检查数据库cpu、I/O、内存性能
• 6. 检查数据库安全性
• 7. 其他检查

select instance_name,host_name,startup_time,status,database_status from v$instance; 

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

select tablespace_name,status from dba_tablespaces; 

select name,status from v$datafile;

select file_name,status from dba_data_files; 

select owner,object_name,object_type from dba_objects where status!=‘VALID‘ and owner!=‘SYS‘ and owner!=‘SYSTEM‘;

SELECT owner, object_name, object_type FROM dba_objects WHERE status= ‘INVALID‘;

select segment_name,status from dba_rollback_segs;

select resource_name,
max_utilization,
initial_allocation, 
limit_value
from v$resource_limit;

select count(*) from v$session;

select sid,serial#,username,program,machine,status from v$session;

select f.tablespace_name,
a.total,
f.free,
round((f.free / a.total) * 100) "% Free"
from (select tablespace_name, sum(bytes / (1024 * 1024)) total
from dba_data_files
group by tablespace_name) a, 
(select tablespace_name, round(sum(bytes / (1024 * 1024))) free
from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";

select Segment_Name,
Segment_Type,
TableSpace_Name, 
(Extents / Max_extents) * 100 Percent
From sys.DBA_Segments
Where Max_Extents != 0
and (Extents / Max_extents) * 100 >= 95
order By Percent;

select distinct (owner)
from dba_tables
where tablespace_name = ‘SYSTEM‘
and owner != ‘SYS‘
and owner != ‘SYSTEM‘
union
select distinct (owner)
from dba_indexes
where tablespace_name = ‘SYSTEM‘
and owner != ‘SYS‘
and owner != ‘SYSTEM‘;

select a.table_name, a.next_extent, a.tablespace_name
from all_tables a, 
(select tablespace_name, max(bytes) as big_chunk 
from dba_free_space 
group by tablespace_name) f
where f.tablespace_name = a.tablespace_name 
and a.next_extent > f.big_chunk
union
select a.index_name, a.next_extent, a.tablespace_name
from all_indexes a, 
(select tablespace_name, max(bytes) as big_chunk 
from dba_free_space 
group by tablespace_name) f
where f.tablespace_name = a.tablespace_name 
and a.next_extent > f.big_chunk;

set pages 80

set lines 120

col event for a40

select sid, event, p1, p2, p3, WAIT_TIME, SECONDS_IN_WAIT
  from v$session_wait
 where event not like ‘SQL%‘
   and event not like ‘rdbms%‘;

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

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 *
  FROM (SELECT *
          FROM V$SYSTEM_EVENT
         WHERE EVENT NOT LIKE ‘SQL%‘
         ORDER BY TOTAL_WAITS DESC)
 WHERE ROWNUM <= 5;

COLUMN USERNAME FORMAT A12

COLUMN OPNAME FORMAT A16

COLUMN PROGRESS FORMAT A8

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;

SET LINE 240

SET VERIFY OFF

COLUMN SID FORMAT 999

COLUMN PID FORMAT 999

COLUMN S_# FORMAT 999

COLUMN USERNAME FORMAT A9 HEADING "ORA USER"

COLUMN PROGRAM FORMAT A29

COLUMN SQL FORMAT A60

COLUMN 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(*) 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);

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#‘;

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

select table_name, num_rows, chain_cnt
  From dba_tables
 Where 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‘
select count(*) from DJ_NSRXX如num_rows和count(*)

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_ratio
  FROM v$sysstat a, v$sysstat b, v$sysstat c
 WHERE 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‘);

alter user USER_NAME identified by PASSWORD; 

select job,what,last_date,next_date,failures,broken from dba_jobs Where schema_user=‘CAIKE‘;

exec sys.dbms_job.remove(1);
commit;
exec sys.dbms_job.isubmit(1,‘REFRESH_ALL_SNAPSHOT;‘,SYSDATE+1/1440,‘SYSDATE+4/1440‘);
commit;

select A.tablespace_name, (1 - (A.total) / B.total) * 100 used_percent
  from (select tablespace_name, sum(bytes) total
          from dba_free_space
         group by tablespace_name) A,
       (select tablespace_name, sum(bytes) total
          from dba_data_files
         group by tablespace_name) B
 where A.tablespace_name = B.tablespace_name;

alter tablespace <tablespace_name> add datafile ‘<file>’ size <size> autoextend off;

alter database backup controlfile to ‘/home/backup/control.bak‘;

alter database backup controlfile to trace;

select index_name, table_name, tablespace_name, status
  From dba_indexes
 Where owner = ‘CTAIS2‘
   And status <> ‘VALID‘;

alter index INDEX_NAME rebuild tablespace TABLESPACE_NAME; 

SELECT owner, constraint_name, table_name, constraint_type, status
  FROM dba_constraints
 WHERE status = ‘DISABLE‘
   and constraint_type = ‘P‘;

alter Table TABLE_NAME Enable Constraints CONSTRAINT_NAME;

SELECT owner, trigger_name, table_name, status FROM dba_triggers WHERE status = ‘DISABLED‘;

alter Trigger TRIGGER_NAME Enable;