课件：CTOoracle健康检查巡检手册.ppt

* [root@ora9i ~]# su - oracle [oracle@ora9i ~]$ sqlplus /as sysdba SQL*Plus: Release 9.2.0.4.0 - Production on Thu Apr 23 23:42:50 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production * SQL set linesize 1000 SQL set pagesize 1000 SQL select * from v$option； * SQL set linesize 1000 SQL set pagesize 1000 SQL col COMP_NAME for a40 SQL select COMP_ID, COMP_NAME, VERSION,STATUS from dba_registry； * SQL show parameter spfile select name,value from v$parameter where isdefault=FALSE * Alter database backup controlfile to trace; * SQL set linesize 1000 SQL set pagesize 1000 SQL select group#,thread#,bytes/1024/1024 size_MB , members, archived,status from v$Log; * SQL set linesize 1000 SQL set pagesize 1000 SQL select * From v$logfile order by 1; * Archive log list * select count(*),sum(bytes)/1024/1024/1024 from v$datafile; * select TABLESPACE_NAME,FILE_ID, bytes/1024/1024 SIZE_MB,AUTOEXTENSIBLE AUT,STATUS,FILE_NAME from dba_data_files; * select owner,segment_type,segment_name from dba_segments where owner not in(SYS,SYSTEM) and tablespace_name=SYSTEM order by 1; * SELECT df.TABLESPACE_NAME,FILES, extent_management ,sum_m as TOTAL_SIZE,--sum(largest) as MAXFREE_MB, sum_free_m as FREE_MB,to_char(100*sum_free_m/sum_m, 999.99) AS FREE_PCT--,sum(blocks) as FREE_EXTENTS FROM ( SELECT tablespace_name,count(file_id) as files ,sum(bytes)/1024/1024 AS sum_m FROM dba_data_files GROUP BY tablespace_name) df, (SELECT tablespace_name,--max(bytes)/1024/1024 largest, sum(bytes)/1024/1024 AS sum_free_m --,count(blocks) as blocks FROM dba_free_space GROUP BY tablespace_name ) fs,(select tablespace_name,extent_management from dba_tablespaces) ts where df.tablespace_name=fs.tablespace_name and fs.tablespace_name=ts.tablespace_name; * select owner,replace(object_type, ,_) as OBJECT_TYPE,count(*) f