TA的每日心情 | 开心 2023-8-9 11:05 |
---|
|
- Rem database healthcheck scripts
- Rem author:zhangyu mobile:15631192730
- SET TERMOUT ON
- PROMPT starting database healthcheck,please wait...
- SET TERMOUT OFF
- SET ECHO OFF
- SET FEEDBACK OFF
- SET HEADING ON
- SET LINESIZE 200
- SET PAGESIZE 2000
- SET TIMING OFF
- SET TRIMSPOOL ON
- SET VERIFY OFF
- alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
- alter session set nls_date_language = english;
- column host_name new_value host_name
- select host_name from v$instance noprint;
- column instance_name new_value instname
- SELECT INSTANCE_NAME FROM v$INSTANCE noprint;
- column checktime new_value time noprint;
- select to_char(sysdate,'YYYYMMDD_HH24MI') checktime from dual;
- SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON
- spool &host_name._&instname._&time..xls
- host echo 'collect basic database info'
- PROMPT ====basic Database Info
-
- CLEAR COLUMNS BREAKS COMPUTES
- SELECT 'Machine Name' "Database Summary"
- ,listagg(host_name, ', ') within
- GROUP (
- ORDER BY instance_number
- ) "Description"
- FROM gv$instance
- UNION ALL
- SELECT 'OS Platform'
- ,platform_name
- FROM v$database
- UNION ALL
- SELECT 'Memory Size'
- ,listagg(i.host_name || ': ' || round(value / 1024 / 1024 / 1024, 2) || ' GB') within
- GROUP (
- ORDER BY instance_number
- )
- FROM gv$osstat o
- ,gv$instance i
- WHERE o.stat_name = 'PHYSICAL_MEMORY_bytes'
- AND o.inst_id = i.instance_number
- UNION ALL
- SELECT 'CPUs'
- ,i.host_name || ': ' || listagg(CASE
- WHEN stat_name = 'NUM_CPU_CORES'
- THEN 'COREs - ' || o.value
- ELSE 'CPUs - ' || o.value
- END, ', ') within
- GROUP (
- ORDER BY instance_number
- ) || ''
- FROM gv$osstat o
- ,gv$instance i
- WHERE o.stat_name IN (
- 'NUM_CPU_CORES'
- ,'NUM_CPUS'
- )
- AND i.instance_number = o.inst_id
- GROUP BY i.host_name
- UNION ALL
- SELECT 'Database Name'
- ,name
- FROM v$database
- UNION ALL
- SELECT 'Instance Name'
- ,listagg(instance_name, ', ') within
- GROUP (
- ORDER BY instance_number
- )
- FROM gv$instance
- UNION ALL
- SELECT 'RAC(YES/NO)'
- ,parallel
- FROM v$instance
- UNION ALL
- SELECT 'RDBMS Version'
- ,version
- FROM v$instance
- UNION ALL
- SELECT 'Archive Mode'
- ,log_mode
- FROM v$database
- UNION ALL
- SELECT 'Force Logging'
- ,force_logging
- FROM v$database
- UNION ALL
- SELECT 'Optimizer Compatible'
- ,value
- FROM v$parameter
- WHERE name = 'compatible'
- UNION ALL
- SELECT 'Optimizer Mode'
- ,value
- FROM v$parameter
- WHERE name = 'optimizer_mode'
- UNION ALL
- SELECT 'Character Set'
- ,property_value
- FROM database_properties
- WHERE property_name = 'NLS_CHARACTERSET'
- UNION ALL
- SELECT 'National Character Set'
- ,property_value
- FROM database_properties
- WHERE property_name = 'NLS_NCHAR_CHARACTERSET'
- UNION ALL
- SELECT 'DB Time Zone'
- ,dbtimezone
- FROM dual
- UNION ALL
- SELECT 'Storage Type(File System/Raw Decice/ASM)'
- ,CASE
- WHEN substr(name, 1, 4) LIKE '/dev%'
- THEN 'Raw Device'
- WHEN substr(name, 1, 4) LIKE '+%'
- THEN 'ASM'
- ELSE 'File System'
- END Type
- FROM v$datafile
- WHERE rownum = 1
- UNION ALL
- SELECT 'Tablespace Count'
- ,to_char(count(name))
- FROM v$tablespace
- UNION ALL
- SELECT 'Datafiles'
- ,to_char(count(d.name))
- FROM v$datafile d
- UNION ALL
- SELECT 'Tempfiles'
- ,to_char(count(t.name))
- FROM v$tempfile t
- UNION ALL
- SELECT 'Datafiles Total Size'
- ,round(sum(d.bytes) / 1024 / 1024 / 1024, 2) || ' GB' GB
- FROM dba_data_files d
- UNION ALL
- SELECT 'Tempfiles Total Size'
- ,round(sum(t.bytes) / 1024 / 1024 / 1024, 2) || ' GB'
- FROM dba_temp_files t
- UNION ALL
- SELECT 'Logfile Groups'
- ,listagg('Thread ' || thread# || ': ' || count(DISTINCT group#)) within
- GROUP (
- ORDER BY thread#
- )
- FROM v$log
- GROUP BY thread#
- UNION ALL
- SELECT 'Controlfile Count'
- ,to_char(count(name))
- FROM v$controlfile
- UNION ALL
- SELECT 'SPFILE/PFILE'
- ,listagg(i.instance_name || ': ' || nvl2(name, 'SPFILE', 'PFILE')) within
- GROUP (
- ORDER BY p.inst_id
- )
- FROM gv$parameter p
- ,gv$instance i
- WHERE p.name = 'spfile'
- AND p.inst_id = i.instance_number
- UNION ALL
- SELECT 'Session Count'
- ,listagg(i.instance_name || ': ' || s.STATUS || ' - ' || count(*)) within
- GROUP (
- ORDER BY s.inst_id
- ) cnt
- FROM gv$session s
- ,gv$instance i
- WHERE s.inst_id = i.instance_number
- GROUP BY s.inst_id
- ,i.instance_name
- ,s.STATUS
- UNION ALL
- SELECT 'MEMORY_TARGET'
- ,listagg(i.instance_name || ': ' || round(value / 1024 / 1024 / 1024, 2)) within
- GROUP (
- ORDER BY p.inst_id
- )
- FROM gv$parameter p
- ,gv$instance i
- WHERE p.name = 'memory_target'
- AND p.inst_id = i.instance_number
- UNION ALL
- SELECT 'SGA_TARGET'
- ,listagg(i.instance_name || ': ' || round(value / 1024 / 1024 / 1024, 2)) within
- GROUP (
- ORDER BY p.inst_id
- )
- FROM gv$parameter p
- ,gv$instance i
- WHERE p.name = 'sga_target'
- AND p.inst_id = i.instance_number
- UNION ALL
- SELECT 'PGA_AGGREGATE_TARGET'
- ,listagg(i.instance_name || ': ' || round(value / 1024 / 1024 / 1024, 2)) within
- GROUP (
- ORDER BY p.inst_id
- )
- FROM gv$parameter p
- ,gv$instance i
- WHERE p.name = 'pga_aggregate_target'
- AND p.inst_id = i.instance_number
- UNION ALL
- SELECT 'DB Block Size'
- ,value
- FROM v$parameter p
- WHERE p.name = 'db_block_size'
- /
- host echo 'collect database properties info'
- PROMPT ====Database properties
- select * from database_properties
- /
- host echo 'collect database info'
- PROMPT ====Database Info
- SELECT dbid
- ,name
- ,db_unique_name
- ,created
- ,log_mode
- ,open_mode
- ,protection_mode
- ,database_role
- ,force_logging
- ,platform_name
- ,flashback_on
- ,dbtimezone
- FROM v$database
- /
- host echo 'collect instance info'
- PROMPT ====Instance Info
- SELECT inst_id
- ,instance_name
- ,host_name
- ,version
- ,startup_time
- ,STATUS
- ,parallel AS "RAC"
- FROM gv$instance
- ORDER BY inst_id
- /
- host echo 'collect instance startup history info'
- PROMPT ====start hist into
- CLEAR COLUMNS BREAKS COMPUTES
- SELECT instance_number inst
- ,instance_name
- ,startup_time
- FROM dba_hist_database_instance
- WHERE rownum <= 10
- ORDER BY 1,3
- /
- host echo 'collect database option info'
- PROMPT ====Database Options
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN parameter FORMAT a45 HEADING 'Option Name'
- COLUMN value FORMAT a15 HEADING 'Value'
- SELECT
- parameter
- , value
- FROM
- v$option
- ORDER BY 2
- /
- host echo 'collect Database Registry info'
- PROMPT ====Database Registry
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN comp_name FORMAT a45 HEADING 'Comp Name'
- COLUMN version FORMAT a20 HEADING 'Version'
- COLUMN status FORMAT a10 HEADING 'Status'
- SELECT
- comp_name
- , version
- , status
- FROM
- dba_registry
- /
- host echo 'collect Database Patchset info'
- PROMPT ====Database Patchset
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN action_time FORMAT a15 HEADING 'TIME'
- COLUMN action FORMAT a15 HEADING 'ACTION'
- COLUMN NAMESPACE FORMAT a15 HEADING 'NAMESPACE'
- COLUMN VERSION FORMAT a15 HEADING 'VERSION'
- COLUMN COMMENTS FORMAT a30 HEADING 'COMMENTS'
- select
- TO_CHAR(action_time, 'yyyy/mm/dd') action_time,
- action,
- namespace,
- version,
- comments
- from dba_registry_history
- /
- host echo 'collect Database HWM info'
- PROMPT ====Database HWM
- SELECT name
- ,version
- ,highwater
- ,last_value
- ,description
- FROM dba_high_water_mark_statistics
- /
- host echo 'collect Control Files info'
- PROMPT ====Control Files
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN name FORMAT a85 HEADING "Name"
- COLUMN ctl_size FORMAT 999,999,999 HEADING "Size_MB"
- SELECT
- name,
- round(block_size*file_size_blks/1024/1024,0) ctl_size
- FROM v$controlfile
- ORDER BY name
- /
- host echo 'collect Redo Log info'
- PROMPT ====Redo Log
- CLEAR COLUMNS BREAKS COMPUTES
- BREAK ON instance_name ON thread#
- COLUMN instance_name FORMAT a20 HEADING 'Instance Name'
- COLUMN thread# FORMAT 999 HEADING 'Thread#'
- COLUMN group# FORMAT 999 HEADING 'Group#'
- COLUMN member FORMAT a50 HEADING 'Member'
- COLUMN type FORMAT a10 HEADING 'Type'
- COLUMN status FORMAT a15 HEADING 'Status'
- COLUMN bytes FORMAT 999,999 HEADING 'LogSize MB'
- COLUMN archived FORMAT a15 HEADING 'Archived'
- SELECT
- i.instance_name
- , i.thread#
- , f.group#
- , f.member
- , f.type
- , l.status
- , l.bytes/1024/1024 bytes
- FROM
- gv$logfile f
- , gv$log l
- , gv$instance i
- WHERE
- f.group# = l.group#
- AND l.thread# = i.thread#
- AND i.inst_id = f.inst_id
- AND f.inst_id = l.inst_id
- ORDER BY
- i.instance_name
- , f.group#
- , f.member
- /
- host echo 'collect Redo Log Switch info'
- PROMPT ====Redo Log Switch
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN DAY FORMAT a8 HEADING 'D/T'
- COLUMN H00 FORMAT 999 HEADING '00'
- COLUMN H01 FORMAT 999 HEADING '01'
- COLUMN H02 FORMAT 999 HEADING '02'
- COLUMN H03 FORMAT 999 HEADING '03'
- COLUMN H04 FORMAT 999 HEADING '04'
- COLUMN H05 FORMAT 999 HEADING '05'
- COLUMN H06 FORMAT 999 HEADING '06'
- COLUMN H07 FORMAT 999 HEADING '07'
- COLUMN H08 FORMAT 999 HEADING '08'
- COLUMN H09 FORMAT 999 HEADING '09'
- COLUMN H10 FORMAT 999 HEADING '10'
- COLUMN H11 FORMAT 999 HEADING '11'
- COLUMN H12 FORMAT 999 HEADING '12'
- COLUMN H13 FORMAT 999 HEADING '13'
- COLUMN H14 FORMAT 999 HEADING '14'
- COLUMN H15 FORMAT 999 HEADING '15'
- COLUMN H16 FORMAT 999 HEADING '16'
- COLUMN H17 FORMAT 999 HEADING '17'
- COLUMN H18 FORMAT 999 HEADING '18'
- COLUMN H19 FORMAT 999 HEADING '19'
- COLUMN H20 FORMAT 999 HEADING '20'
- COLUMN H21 FORMAT 999 HEADING '21'
- COLUMN H22 FORMAT 999 HEADING '22'
- COLUMN H23 FORMAT 999 HEADING '23'
- COLUMN TOTAL FORMAT 999,999,999 HEADING 'Total'
- BREAK ON report
- SELECT
- SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DAY
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
- , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
- , COUNT(*) TOTAL
- FROM
- v$log_history a
- where first_time>sysdate-15
- GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
- ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
- /
- host echo 'collect archivelog config'
- PROMPT ====archivelog config
- SELECT 'Database log mode' name
- ,log_mode value
- FROM v$database
- UNION ALL
- SELECT i.instance_name || '.' || p.name
- ,p.value
- FROM gv$parameter p
- ,gv$instance i
- WHERE name LIKE 'log_archive%'
- AND p.inst_id = i.instance_number
- AND p.name NOT LIKE 'log_archive_dest_state%'
- AND p.value IS NOT NULL
- UNION ALL
- SELECT i.instance_name || '.' || p.name
- ,p.value
- FROM gv$parameter p
- ,gv$instance i
- WHERE name LIKE 'log_archive_dest_state%'
- AND p.inst_id = i.instance_number
- AND to_number(ltrim(name, 'log_archive_dest_')) IN (
- SELECT dest_id
- FROM v$archive_dest
- WHERE target = 'PRIMARY'
- AND destination IS NOT NULL
- )
- ORDER BY 1
- /
- host echo 'collect archivelog list info'
- PROMPT ====archivelog list
- SELECT trunc(first_time) AS DATETIME
- ,round(sum(blocks * block_size / 1024 / 1024), 2) AS "Archivelog(MB)/DAY"
- FROM v$archived_log a
- ,v$archive_dest b
- WHERE a.dest_id = b.dest_id
- AND b.target = 'PRIMARY'
- AND b.destination IS NOT NULL
- AND sysdate - a.first_time <= 30
- GROUP BY trunc(first_time)
- ORDER BY 1
- /
- host echo 'collect recovery area Info'
- PROMPT ====recovery area
- SELECT *
- FROM v$recovery_area_usage
- /
- host echo 'collect Tablespaces Usage Info'
- PROMPT ====tablespace usage info
- WITH tbs
- AS (
- SELECT t2.ts#
- ,t1.tablespace_name name
- ,t1.block_size
- ,t1.contents type
- ,t1.STATUS
- ,t1.bigfile
- FROM dba_tablespaces t1
- ,v$tablespace t2
- WHERE t1.tablespace_name = t2.name
- )
- SELECT t.name tablespace
- ,t.type
- ,t.bigfile
- ,t.STATUS
- ,count(f.rfno) datafiles
- ,round(sum(f.file_size) * t.block_size / 1024 / 1024 / 1024, 2) AS TOTAL_GB
- ,round(sum(f.allocated_space) * t.block_size / 1024 / 1024 / 1024, 2) AS USED_GB
- ,round((sum(f.file_size) - sum(f.allocated_space)) * t.block_size / 1024 / 1024 / 1024, 2) AS FREE_GB
- ,round(sum(f.allocated_space) / sum(f.file_size) * 100, 2) AS USED_PCT
- ,round(sum(f.file_maxsize) * t.block_size / 1024 / 1024 / 1024, 2) AS MAXSIZE_GB
- ,round((sum(f.file_maxsize) - sum(f.file_size)) * t.block_size / 1024 / 1024 / 1024, 2) AS EXT_GB
- FROM v$filespace_usage f
- ,tbs t
- WHERE f.tablespace_id = t.ts#
- GROUP BY t.name
- ,t.type
- ,t.STATUS
- ,t.block_size
- ,t.bigfile
- ORDER BY USED_PCT DESC
- /
- host echo 'collect FSFI info'
- PROMPT ====Free Space Fragmentation Index info
- column FSFI format 999,99
- select tablespace_name,sqrt(max(blocks)/sum(blocks))*
- (100/sqrt(sqrt(count(blocks)))) FSFI
- from dba_free_space
- group by tablespace_name order by 1
- /
- host echo 'collect Data File Usage Info'
- PROMPT ====Data File Usage Info
- CLEAR COLUMNS BREAKS COMPUTES
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN tablespace FORMAT a18 HEADING 'Tablespace_Name'
- COLUMN filename FORMAT a75 HEADING 'File_name'
- COLUMN filesize FORMAT 9,999,999,999 HEADING 'Total_MB'
- COLUMN used FORMAT 9,999,999,999 HEADING 'Used_MB'
- COLUMN pct_used FORMAT 999 HEADING 'Pct_Used'
- COLUMN file_id FORMAT 9999 HEADING 'File_ID'
- COLUMN maxbytes FORMAT 9,999,999,999 HEADING 'Max_MB'
- COLUMN status FORMAT a10 HEADING 'Status'
- BREAK ON report on tablespace
- COMPUTE sum OF filesize ON report
- COMPUTE sum OF used ON report
- SELECT /*+ ordered */
- d.tablespace_name tablespace
- , d.file_name filename
- , d.file_id file_id
- , d.bytes/1024/1024 filesize
- , NVL((d.bytes - s.bytes), d.bytes)/1024/1024 used
- , TRUNC(((NVL((d.bytes - s.bytes) , d.bytes)) / d.bytes) * 100) pct_used
- , d.AUTOEXTENSIBLE autoextend
- FROM
- sys.dba_data_files d
- , ( select file_id, SUM(bytes) bytes
- from sys.dba_free_space
- GROUP BY file_id) s
- WHERE
- s.file_id (+)= d.file_id
- UNION
- SELECT
- d.tablespace_name tablespace
- , d.file_name filename
- , d.file_id file_id
- , d.bytes/1024/1024 filesize
- , NVL(t.bytes_cached, 0)/1024/1024 used
- , TRUNC((t.bytes_cached / d.bytes) * 100) pct_used
- , d.AUTOEXTENSIBLE autoextend
- FROM
- sys.dba_temp_files d
- , v$temp_extent_pool t
- WHERE
- t.file_id (+)= d.file_id
- /
- host echo 'collect Datafile Growth Info'
- PROMPT ====Datafile Growth
- CLEAR COLUMNS BREAKS COMPUTES
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN month FORMAT a7 HEADING 'Month'
- COLUMN growth FORMAT 999,999,999,999,999 HEADING 'Growth (MB)'
- BREAK ON report
- COMPUTE sum OF growth ON report
- SELECT
- TO_CHAR(creation_time, 'yyyy-MM') month
- , SUM(bytes/1024/1024) growth
- FROM sys.v_$datafile
- GROUP BY TO_CHAR(creation_time, 'yyyy-MM')
- ORDER BY TO_CHAR(creation_time, 'yyyy-MM')
- /
- host echo 'collect Big table histogram'
- PROMPT ====Big tables histogram
- SELECT count(CASE
- WHEN round(bytes / 1024 / 1024 / 1024, 0) <= 2
- THEN 1
- END) AS "0-2GB"
- ,count(CASE
- WHEN round(bytes / 1024 / 1024 / 1024, 0) > 2
- AND round(bytes / 1024 / 1024 / 1024, 0) <= 10
- THEN 1
- END) AS "2-10GB"
- ,count(CASE
- WHEN round(bytes / 1024 / 1024 / 1024, 0) > 10
- THEN 1
- END) AS ">10GB"
- FROM dba_segments
- WHERE OWNER NOT IN (
- 'sys'
- ,'system'
- )
- AND segment_type = 'TABLE'
- AND segment_name NOT IN (
- SELECT table_name
- FROM dba_tab_partitions
- )
- /
- host echo 'collect Big tables info'
- PROMPT ====Big tables details
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN segment_name FORMAT a30 HEADING 'Table Not Parted'
- COLUMN bytes FORMAT a65 HEADING 'Table Size(MB)'
- select s.owner,
- s.segment_name,
- round(s.bytes/1024/1024,0) mb
- from dba_segments s
- where s.segment_type='TABLE'
- and round(s.bytes/1024/1024,0)>10240
- and s.segment_name not in(select table_name from dba_part_tables)
- order by 3 desc
- /
- host echo 'collect Invalid Objects info'
- PROMPT ====Invalid Objects Info
- select count(*) in_valid_objs from dba_objects where status='INVALID';
- PROMPT ====Invalid Objects details Info
- CLEAR COLUMNS BREAKS COMPUTES
- compute sum of object_count on report
- select owner,
- object_type,
- count(*) object_count
- from dba_objects
- where status = 'INVALID'
- group by (owner, object_type)
- order by owner, object_type
- /
- host echo 'collect Table With High Degree'
- PROMPT ====Table With High Degree
- SELECT t.OWNER
- ,t.table_name
- ,t.degree
- ,t.TEMPORARY
- FROM dba_tables t
- WHERE trim(t.degree) NOT IN (
- '0'
- ,'1'
- ,'DEFAULT'
- )
- ORDER BY 1
- ,2
- /
- host echo 'collect Foreign Key Without Index'
- PROMPT ====Foreign Key Without Index
- SELECT c.OWNER
- ,c.table_name
- ,c.constraint_name fk_name
- ,l.column_name
- FROM dba_constraints c
- ,dba_cons_columns l
- ,dba_ind_columns i
- WHERE c.constraint_type = 'R'
- AND c.constraint_name = l.constraint_name
- AND c.OWNER = l.OWNER
- AND c.table_name = l.table_name
- AND l.table_name = i.table_name(+)
- AND l.column_name = i.column_name(+)
- AND i.index_name IS NULL
- AND c.owner not in('SYSTEM','SYS','ORDDATA')
- ORDER BY 1
- ,2
- /
- host echo 'collect Unuseable indexes info'
- PROMPT ====Unuseable indexes
- CLEAR COLUMNS BREAKS COMPUTES
- select owner,index_name,table_owner,table_name,status from dba_indexes where status='UNUSABLE'
- /
- host echo 'collect Table Has More Than 5 Indexes'
- PROMPT ====Table Has More Than 5 Indexes
- SELECT table_owner
- ,table_name
- ,count(index_name)
- FROM dba_indexes
- GROUP BY table_owner
- ,table_name
- HAVING count(index_name) > 5
- ORDER BY 1
- ,2
- ,3
- /
- host echo 'collect Table and Index With Different Owner'
- PROMPT ====Table and Index With Different Owner
- SELECT OWNER index_owner
- ,table_owner
- ,index_name
- ,table_name
- FROM dba_indexes
- WHERE OWNER <> table_owner
- /
- host echo 'collect Index With High Blevel/Degreer'
- PROMPT ====Index With High Blevel/Degree
- SELECT i.OWNER index_owner
- ,i.index_name
- ,i.index_type
- ,i.table_owner
- ,i.table_name
- ,i.uniqueness
- ,i.compression
- ,i.blevel
- ,i.degree
- FROM dba_indexes i
- WHERE blevel > 3
- OR trim(degree) NOT IN (
- '0'
- ,'1'
- ,'DEFAULT'
- )
- ORDER BY 1
- /
- host echo 'collect Unusable index'
- PROMPT ====Unusable index
- SELECT i.OWNER
- ,i.index_name
- ,i.index_type
- ,i.table_name
- ,NULL partition_name
- ,NULL subpartition_name
- ,i.STATUS
- FROM dba_indexes i
- WHERE STATUS = 'UNUSABLE'
- UNION ALL
- SELECT i.OWNER
- ,i.index_name
- ,i.index_type
- ,i.table_name
- ,p.partition_name
- ,NULL subpartition_name
- ,p.STATUS
- FROM dba_ind_partitions p
- ,dba_indexes i
- WHERE p.index_name = i.index_name
- AND p.STATUS = 'UNUSABLE'
- UNION ALL
- SELECT i.OWNER
- ,i.index_name
- ,i.index_type
- ,i.table_name
- ,s.partition_name
- ,s.subpartition_name
- ,s.STATUS
- FROM dba_ind_subpartitions s
- ,dba_indexes i
- WHERE s.index_name = i.index_name
- AND s.STATUS = 'UNUSABLE'
- ORDER BY 1
- ,4
- /
- host echo 'collect nologging tables'
- PROMPT ====nologging tables
- SELECT t.OWNER
- ,count(table_name) totalcnt
- FROM dba_tables t
- WHERE logging = 'NO'
- and owner not in('DBSNMP','DVSYS','MDSYS','ORDDATA','SYS','SYSMAN','SYSTEM','SYSMAN_MDS','SYSMAN_BIPLATFORM','GSMADMIN_INTERNAL','WMSYS','XDB')
- GROUP BY t.OWNER
- ORDER BY 1
- /
- host echo 'collect User Tables With Stale Stats count'
- PROMPT ====User Tables With Stale Stats count
- SELECT s.OWNER
- ,count(s.table_name) tablecnt
- FROM dba_tab_statistics s
- WHERE s.stale_stats = 'YES'
- AND s.table_name NOT IN (
- SELECT object_name
- FROM dba_recyclebin
- )
- AND s.stattype_locked IS NULL
- GROUP BY s.OWNER
- ORDER BY OWNER;
- host echo 'collect User Tables With Stale Stats details'
- PROMPT ====User Tables With Stale Stats details
- SELECT s.OWNER
- ,s.table_name
- ,s.partition_name
- ,s.subpartition_name
- ,s.object_type
- ,s.last_analyzed
- ,s.stattype_locked
- ,s.stale_stats
- FROM dba_tab_statistics s
- WHERE s.stale_stats = 'YES'
- AND s.stattype_locked IS NULL
- AND s.table_name NOT IN (
- SELECT object_name
- FROM dba_recyclebin
- )
- ORDER BY OWNER
- /
- host echo 'collect Application Sequences'
- PROMPT ====Application Sequences
- SELECT s.sequence_owner
- ,s.sequence_name
- ,s.min_value
- ,s.max_value
- ,s.increment_by
- ,s.cycle_flag
- ,s.order_flag
- ,s.cache_size
- ,s.last_number
- FROM dba_sequences s
- ORDER BY 2
- ,3
- /
- host echo 'collect corsurs usage info'
- PROMPT ====corsurs usage
- CLEAR COLUMNS BREAKS COMPUTES
- select
- 'session_cached_cursors' parameter,
- lpad(value, 5) value,
- decode(value, 0, ' n/a', to_char(100 * used / value, '999999999') || '%') usage
- from
- ( select
- max(s.value) used
- from
- v$statname n,
- v$sesstat s
- where
- n.name = 'session cursor cache count' and
- s.statistic# = n.statistic#
- ),
- ( select
- value
- from
- v$parameter
- where
- name = 'session_cached_cursors'
- )
- union all
- select
- 'open_cursors',
- lpad(value, 5),
- to_char(100 * used / value, '999999999') || '%'
- from
- ( select
- max(sum(s.value)) used
- from
- v$statname n,
- v$sesstat s
- where
- n.name in ('opened cursors current', 'session cursor cache count') and
- s.statistic# = n.statistic#
- group by
- s.sid
- ),
- ( select
- value
- from
- v$parameter
- where
- name = 'open_cursors'
- )
- select
- 'session_cached_cursors' parameter,
- lpad(value, 5) value,
- decode(value, 0, ' n/a', to_char(100 * used / value, '999999') || '%') usage
- from
- ( select
- max(s.value) used
- from
- v$statname n,
- v$sesstat s
- where
- n.name = 'session cursor cache count' and
- s.statistic# = n.statistic#
- ),
- ( select
- value
- from
- v$parameter
- where
- name = 'session_cached_cursors'
- )
- union all
- select
- 'open_cursors',
- lpad(value, 5),
- to_char(100 * used / value, '999999999999') || '%'
- from
- ( select
- max(sum(s.value)) used
- from
- v$statname n,
- v$sesstat s
- where
- n.name= 'opened cursors current' and
- s.statistic# = n.statistic#
- group by
- s.sid
- ),
- ( select
- value
- from
- v$parameter
- where
- name = 'open_cursors'
- )
- /
-
- host echo 'collect nonsys objs in system info'
- PROMPT ====Objects in system tablespace (no sys_user)
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN owner HEADING 'Owner'
- COLUMN count(*) HEADING 'Obj_numbers'
- select owner, count(*)
- from dba_segments
- where tablespace_name in ('SYSAUX')
- and owner not in ('MDSYS',
- 'OUTLN',
- 'CTXSYS',
- 'OLAPSYS',
- 'SYSTEM',
- 'EXFSYS',
- 'DBSNMP',
- 'ORDSYS',
- 'XDB',
- 'ORDDATA',
- 'SYS',
- 'WMSYS',
- 'APEX_030200',
- 'APEX_040200',
- 'AUDSYS',
- 'DVSYS',
- 'LBACSYS',
- 'TSMSYS',
- 'SYSMAN')
- group by owner
- union all
- select owner, count(*)
- from dba_segments
- where tablespace_name in ('SYSTEM')
- and owner not in ('MDSYS',
- 'OUTLN',
- 'CTXSYS',
- 'OLAPSYS',
- 'SYSTEM',
- 'EXFSYS',
- 'DBSNMP',
- 'ORDSYS',
- 'XDB',
- 'ORDDATA',
- 'SYS',
- 'WMSYS',
- 'APEX_030200',
- 'APEX_040200',
- 'AUDSYS',
- 'DVSYS',
- 'LBACSYS',
- 'TSMSYS',
- 'SYSMAN')
- group by owner
- /
- host echo 'collect recyclebin info'
- PROMPT ====Objects in recyclebin
- SELECT r.OWNER
- ,r.type
- ,count(*) count
- FROM dba_recyclebin r
- GROUP BY r.OWNER
- ,r.type
- /
-
- host echo 'collect auto task status'
- PROMPT ====Oracle auto task status
- SELECT a.client_name
- ,a.STATUS
- ,a.window_group
- FROM dba_autotask_client a
- /
- host echo 'collect segadv info'
- PROMPT ====segment advice
- SELECT tablespace_name
- ,segment_name
- ,segment_type
- ,partition_name
- ,recommendations c1
- FROM TABLE (dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE'))
- /
- host echo 'collect Schedulers Window'
- PROMPT ====Schedulers Window
- CLEAR COLUMNS BREAKS COMPUTES
- SELECT b.window_group_name
- ,a.window_name
- ,a.repeat_interval
- ,a.next_start_date
- ,a.duration
- FROM dba_scheduler_windows a
- ,dba_scheduler_wingroup_members b
- WHERE a.window_name = b.window_name
- ORDER BY 1
- /
- host echo 'collect Schedulers Jobs info'
- PROMPT ====Schedulers Jobs
- SELECT j.OWNER
- ,j.job_name
- ,j.schedule_name
- ,j.start_date
- ,j.end_date
- ,j.repeat_interval
- ,j.enabled
- ,j.STATE
- ,j.stop_on_window_close
- FROM dba_scheduler_jobs j
- ORDER BY job_name
- /
- host echo 'collect jobs info'
- PROMPT ====Oracle Jobs
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN job FORMAT 9999999 HEADING 'Job ID'
- COLUMN username FORMAT a20 HEADING 'User'
- COLUMN what FORMAT a60 HEADING 'What'
- COLUMN next_date FORMAT a20 HEADING 'Next Run Date'
- COLUMN interval FORMAT a30 HEADING 'Interval'
- COLUMN last_date FORMAT a20 HEADING 'Last Run Date'
- COLUMN failures HEADING 'Failures'
- COLUMN broken FORMAT a7 HEADING 'Broken?'
-
- SELECT
- job
- , log_user username
- , what
- , TO_CHAR(next_date, 'YYYYMMDD HH24:MI:SS') next_date
- , interval
- , TO_CHAR(last_date, 'YYYYMMDD HH24:MI:SS') last_date
- , failures
- , broken
- FROM
- dba_jobs
- /
- host echo 'collect Job history(last 10) info'
- PROMPT ====Job history(last 10)
- CLEAR COLUMNS BREAKS COMPUTES
- SELECT *
- FROM (
- SELECT j.client_name
- ,j.window_name
- ,j.window_start_time
- ,j.window_duration
- ,j.job_name
- ,j.job_status
- ,j.job_start_time
- ,j.job_duration
- ,j.job_error
- ,j.job_info
- ,row_number() OVER (
- PARTITION BY j.client_name ORDER BY j.job_start_time DESC
- ) rank
- FROM dba_autotask_job_history j
- )
- WHERE rank <= 10
- /
- host echo 'collect jobs info'
- PROMPT ====Oracle Jobs
- CLEAR COLUMNS BREAKS COMPUTES
- SELECT *
- FROM (
- SELECT log_date
- ,OWNER
- ,job_name
- ,STATUS
- ,req_start_date
- ,actual_start_date
- ,run_duration
- ,instance_id
- ,session_id
- ,slave_pid
- ,cpu_used
- ,error#
- ,additional_info
- ,row_number() OVER (
- PARTITION BY OWNER ORDER BY j.actual_start_date DESC
- ) rank
- FROM dba_scheduler_job_run_details j
- )
- WHERE rank <= 10
- /
- host echo 'collect Scheduler History info'
- PROMPT ====Scheduler History
- CLEAR COLUMNS BREAKS COMPUTES
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN username FORMAT a30 HEAD 'Username'
- COLUMN account_status FORMAT a17 HEAD 'Status'
- COLUMN expiry_date HEAD 'Expire Date'
- COLUMN default_tablespace FORMAT a28 HEAD 'Default Tablespace'
- COLUMN temporary_tablespace FORMAT a15 HEAD 'Temp Tablespace'
- COLUMN created HEAD 'Created On'
- COLUMN profile FORMAT a10 HEAD 'Profile'
- COLUMN sysdba FORMAT a6 HEAD 'SYSDBA'
- COLUMN sysoper FORMAT a7 HEAD 'SYSOPER'
- SELECT distinct
- a.username username
- , a.account_status account_status
- , TO_CHAR(a.expiry_date, 'mm/dd/yyyy HH24:MI:SS') expiry_date
- , a.default_tablespace default_tablespace
- , a.temporary_tablespace temporary_tablespace
- , TO_CHAR(a.created, 'mm/dd/yyyy HH24:MI:SS') created
- , a.profile profile
- , DECODE(p.sysdba,'TRUE', 'TRUE','') sysdba
- , DECODE(p.sysoper,'TRUE','TRUE','') sysoper
- FROM
- dba_users a
- , v$pwfile_users p
- WHERE
- p.username (+) = a.username
- ORDER BY username
- /
- host echo 'collect SYSDBA info'
- PROMPT ====SYSDBA Info
- select username,SYSDBA,SYSOPER,ACCOUNT_STATUS,AUTHENTICATION_TYPE from v$pwfile_users
- /
-
- host echo 'collect DBA info'
- PROMPT ====DBA Info
- COLUMN GRANTEE FORMAT a25 HEAD 'Username'
- COLUMN GRANTED_ROLE FORMAT a25 HEAD 'IS DBA'
- select * from dba_role_privs where granted_role='DBA' order by 1;
- PROMPT ====User with granted roles
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN USERNAME FORMAT a25 HEAD 'Username'
- COLUMN GRANTED_ROLE FORMAT a40 HEAD 'Granted Role'
- BREAK ON username
- select username,granted_role from dba_users u,dba_role_privs p where u.username=p.grantee order by 1
- /
- host echo 'collect profile info'
- PROMPT ====Profile
- COLUMN profile FORMAT a30 HEAD 'Profile'
- COLUMN resource_name FORMAT a25 HEAD 'Resource_name'
- COLUMN resource_type FORMAT a25 HEAD 'Resource_type'
- COLUMN limit FORMAT a40 HEAD 'Limit'
- break on profile
- select profile,resource_name,resource_type,limit from dba_profiles order by 1,2
- /
- host echo 'collect Resource_limit info'
- PROMPT ====Resource_limit
- COLUMN RESOURCE_NAME FORMAT a30 HEAD 'RESOURCE_NAME'
- COLUMN CURRENT_UTILIZATION HEAD 'CURRENT'
- COLUMN MAX_UTILIZATION HEAD 'MAX'
- COLUMN INITIAL_ALLOCATION FORMAT a40 HEAD 'INITIAL'
- COLUMN LIMIT_VALUE FORMAT a40 HEAD 'LIMIT'
- select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE from v$resource_limit
- /
- host echo 'collect User Data info'
- PROMPT ====User Data Summary
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN owner FORMAT a25 HEADING 'Username'
- COLUMN bytes FORMAT 999,999,999,999 HEADING 'MB'
- break on report
- COMPUTE sum OF mb ON report
- select owner,round(sum(bytes/1024/1024),0) mb from dba_segments group by owner order by 2 desc
- /
- host echo 'collect Database Links info'
- PROMPT ====Database Links
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN owner FORMAT a15 HEADING 'OWNER'
- COLUMN DB_LINK FORMAT a20 HEADING 'DL_VALUE'
- COLUMN username FORMAT a15 HEADING 'USER NAME'
- COLUMN host FORMAT a50 HEADING 'HOST'
- SELECT
- owner,username,db_link,host,created
- FROM
- dba_db_links
- ORDER BY 1
- /
- host echo 'collect directory info'
- PROMPT ====directory
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN owner FORMAT a10 HEADING 'Owner'
- COLUMN directory_name FORMAT a30 HEADING 'Directory Name'
- COLUMN directory_path FORMAT a85 HEADING 'Directory Path'
- SELECT
- owner
- , directory_name
- , directory_path
- FROM
- dba_directories
- ORDER BY
- owner
- , directory_name
- /
- host echo 'collect User Sessions Summary info'
- PROMPT ====User Sessions Summary Report
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN max_sess_allowed FORMAT 9,999,999 HEADING 'Max sessions allowed'
- COLUMN num_sessions FORMAT 9,999,999,999 HEADING 'Number of sessions'
- COLUMN pct_utl FORMAT a19 HEADING 'Percent Utilization'
- COLUMN username FORMAT a15 HEADING 'Oracle User'
- COLUMN num_user_sess FORMAT 9,999,999 HEADING 'Number of Logins'
- COLUMN count_a FORMAT 9,999,999 HEADING 'Active Logins'
- COLUMN count_i FORMAT 9,999,999 HEADING 'Inactive Logins'
-
- SELECT
- TO_NUMBER(a.value) max_sess_allowed
- , TO_NUMBER(count(*)) num_sessions
- , LPAD(ROUND((count(*)/a.value)*100,0) || '%', 19) pct_utl
- FROM
- v$session b
- , v$parameter a
- WHERE
- a.name = 'sessions'
- GROUP BY
- a.value
- /
- host echo 'collect awr settings info'
- PROMPT ====awr settings
- SELECT a.dbid
- ,a.snap_interval
- ,a.retention
- ,a.topnsql
- FROM dba_hist_wr_control a
- /
- host echo 'collect dbmemory info'
- PROMPT ====dbmemory info
- SELECT p.inst_id
- ,p.name
- ,round(p.value / 1024 / 1024 / 1024, 2) value
- FROM gv$system_parameter p
- WHERE p.name IN (
- 'memory_max_target'
- ,'memory_target'
- ,'sga_max_size'
- ,'sga_target'
- ,'pga_aggregate_target'
- ,'shard_pool_size'
- ,'shared_pool_reserved_size'
- ,'large_pool_size'
- ,'java_pool_size'
- ,'streams_pool_size'
- )
- /
- host echo 'collect SGA info'
- PROMPT ====SGA info
- SELECT instance_name
- ,name
- ,round(bytes / 1024 / 1024, 2) size_mb
- ,resizeable
- FROM gv$sgainfo s
- ,gv$instance i
- WHERE s.inst_id = i.inst_id
- ORDER BY 1
- ,3 DESC
- /
- host echo 'collect Top Components of Shared Pool'
- PROMPT ====Top Components of Shared Pool
- SELECT *
- FROM (
- SELECT a.inst_id
- ,a.pool
- ,a.name
- ,a.bytes
- FROM gv$sgastat a
- WHERE pool = 'shared pool'
- ORDER BY 1
- ,2
- ,4 DESC
- )
- WHERE rownum <= 10
- /
-
- host echo 'collect Undo parameters info'
- PROMPT ====Undo parameters
- SELECT inst_id
- ,name
- ,value
- FROM gv$parameter
- WHERE name IN (
- 'temp_undo_enabled'
- ,'undo_management'
- ,'undo_retention'
- ,'undo_tablespace'
- )
- UNION ALL
- SELECT NULL
- ,ksppinm name
- ,ksppstvl value
- FROM x$ksppi a
- ,x$ksppcv b
- WHERE a.indx = b.indx
- AND a.ksppinm IN (
- '_undo_autotune'
- ,'_highthreshold_undoretention'
- ,'_undotbs_stepdown_pcent'
- ,'_smu_debug_mode'
- )
- ORDER BY 1
- /
- host echo 'collect undo segment count info'
- PROMPT ====undo segment count
- SELECT r.instance_num inst
- ,r.OWNER
- ,r.tablespace_name
- ,r.STATUS
- ,count(r.segment_name) segcnt
- FROM dba_rollback_segs r
- GROUP BY r.instance_num
- ,r.OWNER
- ,r.tablespace_name
- ,r.STATUS
- ORDER BY 1
- ,2
- ,4
- /
- host echo 'collect undo extent count info'
- PROMPT ====undo extent count
- SELECT u.tablespace_name
- ,u.STATUS
- ,round(sum(u.bytes / 1024 / 1024), 2) total_mb
- FROM dba_undo_extents u
- GROUP BY u.tablespace_name
- ,u.STATUS
- ORDER BY 1
- ,2
- ,3
- /
- host echo 'collect undo extent usage details info'
- PROMPT ====undo extent usage details
- SELECT tablespace_name AS TABLESPACE
- ,segment_name
- ,nvl(sum(bytes), 0) AS ACTIVE_bytes
- ,00 unexp
- ,00 exp
- FROM dba_undo_extents
- WHERE STATUS = 'ACTIVE'
- GROUP BY tablespace_name
- ,segment_name
- UNION ALL
- SELECT tablespace_name
- ,segment_name
- ,00 act
- ,nvl(sum(bytes), 0) AS UNEXPIRED_bytes
- ,00 exp
- FROM dba_undo_extents
- WHERE STATUS = 'UNEXPIRED'
- GROUP BY tablespace_name
- ,segment_name
- UNION ALL
- SELECT tablespace_name
- ,segment_name
- ,00 act
- ,00 unexp
- ,nvl(sum(bytes), 0) AS EXPIRED_bytes
- FROM dba_undo_extents
- WHERE STATUS = 'EXPIRED'
- GROUP BY tablespace_name
- ,segment_name
- /
- host echo 'collect undo stats info'
- PROMPT ====undo stats
- SELECT *
- FROM v$undostat
- /
- host echo 'collect ASM Disk Groups info'
- PROMPT ====ASM Disk Groups
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN group_name FORMAT a25 HEAD 'Disk Group|Name'
- COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
- COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
- COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
- COLUMN state FORMAT a11 HEAD 'State'
- COLUMN type FORMAT a6 HEAD 'Type'
- COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
- COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
- COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
- BREAK ON report ON disk_group_name SKIP 1
- SELECT
- name group_name
- , sector_size sector_size
- , block_size block_size
- , allocation_unit_size allocation_unit_size
- , state state
- , type type
- , total_mb total_mb
- , (total_mb - free_mb) used_mb
- , ROUND((1- (free_mb / total_mb))*100, 2) pct_used
- FROM
- v$asm_diskgroup
- WHERE
- total_mb != 0
- ORDER BY
- name
- /
- host echo 'collect ASM Disks info'
- PROMPT ====ASM Disks
- CLEAR COLUMNS
- CLEAR BREAKS
- CLEAR COMPUTES
- COLUMN disk_group_name FORMAT a25 HEAD 'Disk Group Name'
- COLUMN disk_file_path FORMAT a20 HEAD 'Path'
- COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
- COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
- COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
- COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
- COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
- SELECT
- NVL(a.name, '[CANDIDATE]') disk_group_name
- , b.path disk_file_path
- , b.name disk_file_name
- , b.failgroup disk_file_fail_group
- , b.total_mb total_mb
- , (b.total_mb - b.free_mb) used_mb
- , ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
- FROM
- v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
- ORDER BY
- a.name
- /
- host echo 'collect logon session in 7 days'
- PROMPT ====logon session in 7 days
- with sysstat as (
- select a.dbid
- ,a.instance_number
- ,a.snap_id
- ,b.begin_interval_time
- ,sum(decode(a.stat_name,'logons current',a.value,0)) logon_current
- ,sum(decode(a.stat_name,'logons cumulative',a.value,0)) logon_totals
- from dba_hist_sysstat a,
- dba_hist_snapshot b
- where a.dbid=b.dbid
- and a.instance_number=b.instance_number
- and a.snap_id=b.snap_id
- and b.begin_interval_time >= trunc(sysdate-7)
- and b.begin_interval_time <= trunc(sysdate)
- and a.stat_name in ('logons current',
- 'logons cumulative')
- group by a.dbid,a.instance_number,a.snap_id,b.begin_interval_time),
- logoninfo as (
- select to_char(x.begin_interval_time,'MMDD_HH24MI') SNAP_TIME
- ,sum(decode(x.instance_number,1,x.logon_current,0)) logon_current_1
- ,sum(decode(x.instance_number,1,x.logon_totals,0)) logon_totals_1
- ,sum(decode(x.instance_number,1,y.value,0)) logon_limit_1
- ,sum(decode(x.instance_number,2,x.logon_current,0)) logon_current_2
- ,sum(decode(x.instance_number,2,x.logon_totals,0)) logon_totals_2
- ,sum(decode(x.instance_number,2,y.value,0)) logon_limit_2
- from sysstat x,
- dba_hist_parameter y
- where x.dbid=y.dbid
- and x.instance_number=y.instance_number
- and x.snap_id=y.snap_id
- and y.parameter_name='sessions'
- group by to_char(x.begin_interval_time,'MMDD_HH24MI')
- order by to_char(x.begin_interval_time,'MMDD_HH24MI'))
- select snap_time,
- logon_current_1,
- logon_limit_1,
- logon_current_2,
- logon_limit_2
- from (select snap_time
- ,logon_totals_1-lag(logon_totals_1) over (order by snap_time) logon_delta_1
- ,logon_totals_2-lag(logon_totals_2) over (order by snap_time) logon_delta_2
- ,logon_current_1 logon_current_1
- ,logon_current_2 logon_current_2
- ,logon_limit_1 logon_limit_1
- ,logon_limit_2 logon_limit_2
- ,rownum rn
- from logoninfo
- order by snap_time)
- where rn >1
- /
- host echo 'collect non-default parameter info'
- PROMPT ====parameter Info
- CLEAR COLUMNS BREAKS COMPUTES
- COLUMN pname FORMAT a45 HEADING 'Parameter Name'
- COLUMN value FORMAT a80 HEADING 'Value'
- COLUMN isdefault FORMAT a15 HEADING 'Is Default?'
- COLUMN issys_modifiable FORMAT a15 HEADING 'Is Dynamic?'
- SELECT
- DECODE( isdefault
- , 'FALSE'
- , SUBSTR(name,0,512)
- , SUBSTR(name,0,512) ) pname
- , DECODE( isdefault
- , 'FALSE'
- , SUBSTR(value,0,512)
- , SUBSTR(value,0,512) ) value
- , DECODE( isdefault
- , 'FALSE'
- , isdefault
- , isdefault ) isdefault
- , DECODE( isdefault
- , 'FALSE'
- , issys_modifiable
- , issys_modifiable ) issys_modifiable
- FROM
- v$parameter
- WHERE
- value is not null and
- isdefault='FALSE'
- order by 3,4
- /
- host echo 'collect top 3 cpu ela sql in etch SNAPSHOT'
- PROMPT ====top 3 cpu ela sql in etch SNAPSHOT
- SELECT snap_id
- ,sql_id
- ,execs
- ,cput
- ,cpu_rank
- ,elat
- ,ela_rank
- FROM (
- SELECT a.snap_id
- ,a.sql_id
- ,decode(sum(a.executions_delta), 0, 1, sum(a.executions_delta)) execs
- ,sum(a.cpu_time_delta) cput
- ,sum(sum(a.cpu_time_delta)) OVER (PARTITION BY a.snap_id) total_cput
- ,dense_rank() OVER (
- PARTITION BY a.snap_id ORDER BY sum(a.cpu_time_delta) DESC
- ) cpu_rank
- ,sum(a.elapsed_time_delta) elat
- ,sum(sum(a.elapsed_time_delta)) OVER (PARTITION BY a.snap_id) total_elat
- ,dense_rank() OVER (
- PARTITION BY a.snap_id ORDER BY sum(a.elapsed_time_delta) DESC
- ) ela_rank
- FROM dba_hist_sqlstat a
- ,dba_hist_snapshot b
- WHERE a.snap_id = b.snap_id
- AND b.begin_interval_time >= trunc(sysdate - 1)
- AND b.begin_interval_time <= trunc(sysdate)
- GROUP BY a.snap_id
- ,a.sql_id
- )
- WHERE cpu_rank <= 3
- OR ela_rank <= 3
- ORDER BY snap_id;
- host echo 'collect sqlid with full table scan info'
- PROMPT ====sqlid with full table scan
- SELECT snap_id
- ,sql_id
- ,sum(executions_delta) executions_count
- FROM dba_hist_sqlstat
- WHERE sql_id IN (
- SELECT DISTINCT sql_id
- FROM dba_hist_sql_plan
- WHERE operation = 'TABLE ACCESS'
- AND options = 'FULL'
- AND OBJECT_NAME NOT LIKE '%$'
- AND TIMESTAMP > trunc(sysdate - 1)
- )
- GROUP BY snap_id
- ,sql_id
- ORDER BY snap_id
- ,sql_id
- /
- Rem host echo 'collect Init. Parameter(Instance-wide) info'
- Rem PROMPT ====Parameter(Instance-wide)
- Rem
- Rem SELECT p.inst_id
- Rem ,p.name
- Rem ,p.value
- Rem ,p.isdefault
- Rem FROM gv$system_parameter p
- Rem WHERE isdefault = 'FALSE'
- Rem ORDER BY 1
- Rem ,2
- Rem /
- Rem
- Rem host echo 'collect Init. Parameter(Spfile) info'
- Rem PROMPT ====Parameter(Spfile)
- Rem
- Rem SELECT s.sid || '.' || s.name name
- Rem ,s.value
- Rem FROM gv$spparameter s
- Rem WHERE s.isspecified = 'TRUE'
- Rem /
- Rem
- Rem host echo 'collect Init. Parameter(all) info'
- Rem PROMPT ====Parameter(all)
- Rem
- Rem SELECT p.inst_id
- Rem ,p.name
- Rem ,p.value
- Rem ,p.isdefault
- Rem FROM gv$system_parameter p
- Rem ORDER BY 1
- Rem ,2
- Rem ,3
- Rem /
- Rem
- Rem host echo 'collect Init. Parameter(underscore) info'
- Rem PROMPT ====Parameter(underscore)
- Rem
- Rem SELECT ksppinm name
- Rem ,ksppstvl value
- Rem ,a.ksppdesc description
- Rem FROM x$ksppi a
- Rem ,x$ksppcv b
- Rem WHERE a.indx = b.indx
- Rem AND a.ksppinm LIKE '\_%' ESCAPE '\'
- Rem ORDER BY 1
- Rem ,2
- Rem /
-
- Rem host echo 'collect all parameter info'
- Rem PROMPT ====parameter all
- Rem select
- Rem /*+ use_hash(x y) */
- Rem x.inst_id INST_ID
- Rem , kspftctxpn P_NUM
- Rem , ksppinm P_NAME
- Rem , ksppity P_TYPE
- Rem , kspftctxdvl P_VALUE
- Rem , kspftctxdf IS_DEFAULT
- Rem , decode(bitand(ksppiflg/256,1), 1,'TRUE'
- Rem ,'FALSE') ISSES_MODIFIABLE
- Rem , decode(bitand(ksppiflg/65536,3), 1,'IMMEDIATE', 2,'DEFERRED'
- Rem , 3,'IMMEDIATE'
- Rem ,'FALSE') ISSYS_MODIFIABLE
- Rem , decode(bitand(ksppiflg/524288,1), 1,'TRUE'
- Rem ,'FALSE') ISPDB_MODIFIABLE
- Rem , decode(bitand(ksppiflg,4), 4,'FALSE'
- Rem , decode(bitand(ksppiflg/65536,3)
- Rem , 0, 'FALSE'
- Rem , 'TRUE')) ISINSTANCE_MODIFIABLE
- Rem , decode(bitand(kspftctxvf,7), 1,'MODIFIED', 4,'SYSTEM_MOD'
- Rem ,'FALSE') ISMODIFIED
- Rem , decode(bitand(kspftctxvf,2), 2,'TRUE'
- Rem ,'FALSE') ISADJUSTED
- Rem , decode(bitand(ksppilrmflg/64, 1)
- Rem , 1, 'TRUE'
- Rem , 'FALSE') ISDEPRECATED
- Rem , decode(bitand(ksppilrmflg/268435456, 1)
- Rem , 1, 'TRUE'
- Rem , 'FALSE') ISBASIC
- Rem , ksppdesc DESCRIPTION
- Rem , kspftctxvn ORDINAL
- Rem , kspftctxct UPDATE_COMMENT
- Rem , y.con_id CON_ID
- Rem from
- Rem x$ksppi x
- Rem , x$ksppcv2 y
- Rem where
- Rem (
- Rem (
- Rem x.indx+1
- Rem )
- Rem = kspftctxpn
- Rem )
- Rem /
- spool off
- SET MARKUP HTML OFF
- set termout on
- PROMPT OK! WellDone
- PROMPT HealthCheck Log was written to &host_name._&instname._&time..xls
- exit
复制代码
|
|