Oracle数据库巡检SQL

-- |----------------------------------------------------------------------------|-- | FILE : Datebase_XunJian.sql |-- +----------------------------------------------------------------------------+promptprompt +-----------------------------------------------------------------------------------------+prompt | Database XunJian |prompt |-----------------------------------------------------------------------------------------+prompt | |prompt +-----------------------------------------------------------------------------------------+promptprompt Creating database report.prompt This script must be run as a user with SYSDBA privileges.prompt This process can take several minutes to complete.set termout offpromptset pagesize 50000set linesize 80define fileName=Database_XunJiandefine versionNumber=1.0COLUMN tdate NEW_VALUE _date NOPRINTSELECT TO_CHAR(SYSDATE,'MM/DD/YYYY') tdate FROM dual;COLUMN time NEW_VALUE _time NOPRINTSELECT TO_CHAR(SYSDATE,'HH24:MI:SS') time FROM dual;COLUMN date_time NEW_VALUE _date_time NOPRINTSELECT TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS') date_time FROM dual;COLUMN date_time_timezone NEW_VALUE _date_time_timezone NOPRINTSELECT TO_CHAR(systimestamp, 'Mon DD, YYYY (') || TRIM(TO_CHAR(systimestamp, 'Day')) || TO_CHAR(systimestamp, ') "at" HH:MI:SS AM') || TO_CHAR(systimestamp, ' "in Timezone" TZR') date_time_timezoneFROM dual;COLUMN spool_time NEW_VALUE _spool_time NOPRINTSELECT TO_CHAR(SYSDATE,'YYYYMMDD') spool_time FROM dual;COLUMN dbname NEW_VALUE _dbname NOPRINTSELECT name dbname FROM v$database;COLUMN dbid NEW_VALUE _dbid NOPRINTSELECT dbid dbid FROM v$database;COLUMN platform_id NEW_VALUE _platform_id NOPRINTSELECT platform_id platform_id FROM v$database;COLUMN platform_name NEW_VALUE _platform_name NOPRINTSELECT platform_name platform_name FROM v$database;COLUMN global_name NEW_VALUE _global_name NOPRINTSELECT global_name global_name FROM global_name;COLUMN blocksize NEW_VALUE _blocksize NOPRINTSELECT value blocksize FROM v$parameter WHERE name='db_block_size';COLUMN startup_time NEW_VALUE _startup_time NOPRINTSELECT TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') startup_time FROM v$instance;COLUMN host_name NEW_VALUE _host_name NOPRINTSELECT host_name host_name FROM v$instance;COLUMN instance_name NEW_VALUE _instance_name NOPRINTSELECT instance_name instance_name FROM v$instance;COLUMN instance_number NEW_VALUE _instance_number NOPRINTSELECT instance_number instance_number FROM v$instance;COLUMN thread_number NEW_VALUE _thread_number NOPRINTSELECT thread# thread_number FROM v$instance;COLUMN cluster_database NEW_VALUE _cluster_database NOPRINTSELECT value cluster_database FROM v$parameter WHERE name='cluster_database';COLUMN cluster_database_instances NEW_VALUE _cluster_database_instances NOPRINT SELECT value cluster_database_instances FROM v$parameter WHERE name='cluster_database_instances';COLUMN reportRunUser NEW_VALUE _reportRunUser NOPRINTSELECT user reportRunUser FROM dual;spool &FileName._&_instance_name._&_spool_time..txtpromptprompt -------------------------------------report_header-------------------promptprompt Report Name: &FileName._&_dbname._&_spool_time..txt prompt Snapshot Database Version:&versionNumberprompt Run Date / Time / Timezone:&_date_time_timezoneprompt Host Name:&_host_nameprompt Database Name:&_dbnameprompt Database ID:&_dbidprompt Global Database Name:&_global_nameprompt Platform Name / ID:&_platform_name / &_platform_idprompt Clustered Database?:&_cluster_databaseprompt Clustered Database Instances:&_cluster_database_instancesprompt Instance Name:&_instance_nameprompt Instance Number:&_instance_numberprompt Thread Number:&_thread_numberprompt Database Startup Time:&_startup_timeprompt Database Block Size:&_blocksizeprompt Report Run User:&_reportRunUserpromptpromptprompt -------------------Version--------------------------promptSelect * from v$version;promptprompt -------------------opatch----------------------promptcol comp_name form a30 ENTMAP offSelect comp_name,version from dba_registry;promptprompt -------------------psu----------------------promptcol comments format a50Select id,version,comments,action_time,action from dba_registry_history;prompt -------------------SGA----------------------promptcol name form a30 ENTMAP offcol value form 99999999999999999 ENTMAP offpromptshow sga;promptshow parameter sga;promptshow parameter pga;promptcol name form a30 ENTMAP offcol value form a30 ENTMAP offSelect name,value from v$parameter where name like '%ga%';promptprompt -------------------Controlfile---------------------promptcol name form a60 ENTMAP offSelect name from v$controlfile;prompt -------------------Logfile--------------------------promptcol status form a10 ENTMAP offselect GROUP#,THREAD#,SEQUENCE#,BYTES,MEMBERS,ARCHIVED,STATUS from v$log; promptselect group#,sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') from v$log; promptcol member form a53 ENTMAP offpromptselect group#,status,member from v$logfile;promptprompt -------------------archive mode-------------------archive log list;promptprompt -------------------Tablespace user-------------------promptpromptCOLUMN TABLESPACE_NAME FORMAT A28;COLUMN SUM_M FORMAT A12;COLUMN USED_M FORMAT A12;COLUMN FREE_M FORMAT A12;COLUMN PTUSED FORMAT 99.99;SET PAGES 200 LINES 200;promptSELECT S.TABLESPACE_NAME,CEIL(SUM(S.BYTES/1024/1024))||'M' SUM_M,CEIL(SUM(EDSPACE/1024/1024))||'M'USED_M,CEIL(SUM(S.FREESPACE/1024/1024))||'M' FREE_M, round(SUM(EDSPACE)/SUM(S.BYTES)*100,2) PTUSEDFROM (SELECT B.FILE_ID,B.TABLESPACE_NAME,B.BYTES, (B.BYTES-SUM(NVL(A.BYTES,0))) USEDSPACE,SUM(NVL(A.BYTES,0)) FREESPACE,(SUM(NVL(A.BYTES,0))/(B.BYTES)) * 100 FREEPERCENTRATIO FROM SYS.DBA_FREE_SPACE A,SYS.DBA_DATA_FILES B WHERE A.FILE_ID(+)=B.FILE_ID GROUP BY B.FILE_ID,B.TABLESPACE_NAME,B.BYTES ORDER BY B.TABLESPACE_NAME) SGROUP BY S.TABLESPACE_NAME ORDER BY SUM(S.FREESPACE)/SUM(S.BYTES) asc;prompt -------------------TEMP Tablespace user-------------------promptSELECT d.tablespace_name "Name",TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"FROM sys.dba_tablespaces d,(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) tWHERE d.tablespace_name = a.tablespace_name(+)AND d.tablespace_name = t.tablespace_name(+)AND d.extent_management like 'LOCAL'AND d.contents like 'TEMPORARY'/prompt -------------------Autoextensible------------------------promptCol file_name format a60 ENTMAP offpromptselect file_name,autoextensible from dba_data_files where autoextensible='YES';prompt -------------------Users---------------------------------promptcol default_tablespace form a25 ENTMAP offcol temporary_tablespace form a10 ENTMAP offcol username form a20 ENTMAP offcol account_status form a18 ENTMAP offpromptselect username,default_tablespace,temporary_tablespace,account_status from dba_users;promptprompt -------------------Jobs----------------------------------promptcol NEXT_SEC format a10 ENTMAP offcol job format 9999 ENTMAP offcol what format a50 ENTMAP offselect job, next_date,next_sec,failures,what from dba_jobs where failures !=0; promptprompt -------------------Invalid index-------------------------promptCol index_name form a20 ENTMAP offCol owner form a10 ENTMAP offCol table_name form a20 ENTMAP offCol tablespace_name form a20 ENTMAP offpromptselect index_name,owner,table_name,tablespace_namefrom dba_indexeswhere owner not in ('SYS','SYSTEM')and status != 'VALID';promptprompt -------------------Invalid object---------------------------------promptCOL OBJECT_NAME FORM A40 HEADING 'OBJECT_NAME' ENTMAP offselect object_name, object_type, owner,statusfrom dba_objectswhere status !='VALID'and owner not in ('SYS','SYSTEM')and object_type in('TRIGGER','VIEW','PROCEDURE','FUNCTION');spool off;promptprompt --------------------OS----------------------------promptprompt --------------------/etc/hosts----------------------------prompthost more /etc/hosts >> &FileName._&_dbname._&_spool_time..txtpromptpromptprompt ---------------------disk -----------------------prompthost df -k >> &FileName._&_dbname._&_spool_time..txthost bdf >> &FileName._&_dbname._&_spool_time..txtpromptpromptprompt ------------------------cpu ---------------------prompthost vmstat 2 10 >> &FileName._&_dbname._&_spool_time..txt promptpromptprompt -------------------------crs-----------------------prompthost crs_stat -t >> &FileName._&_dbname._&_spool_time..txtprompt ------------------------ora_ ---------------------prompthost ps -ef|grep ora_ >> &FileName._&_dbname._&_spool_time..txt promptpromptexit;。