Mit einem Skript die Datenbank dokumentieren

conn / as sysdba
set linesize 1000
set feedback off
set pagesize 1000
set colsep '|'
set verify off
set echo off
col host_name format a20
col file_name format a100
col member format a100
define spoolfolderandfile='c:\result.txt'
cl scr
spool &spoolfolderandfile

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*oracle-version:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select * from v$version;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*system-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select instance_name, host_name, to_char(startup_time,'dd.mm.yyyy hh24:mi:ss') as startup, archiver, log_switch_wait,logins from v$instance;
select dbid,name,created,resetlogs_time,log_mode, log_mode from v$database;
select * from v$sga;
select * from v$sga_dynamic_components;
select * from v$sga_dynamic_free_memory;
select * from v$sga_resize_ops;
select * from v$sysstat;
select * from v$system_event order by TIME_WAITED_MICRO desc;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*datenbank-groesse:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select (sum((bytes/1024)/1024)) as mbytes, (sum((user_bytes/1024)/1024)) as used_mbytes from dba_data_files;
select tablespace_name, (sum((bytes/1024)/1024)) as mbytes, (sum((user_bytes/1024)/1024)) as used_mbytes from dba_data_files group by tablespace_name;
select tablespace_name,file_name, (sum((bytes/1024)/1024)) as mbytes, (sum((user_bytes/1024)/1024)) as used_mbytes from dba_data_files group by tablespace_name,file_name;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*session-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select saddr,sid,serial#,username,command,ownerid,taddr,lockwait,status,server,schemaname,osuser,process,machine,terminal,program,module,action,client_info,row_wait_obj#,row_wait_file#,row_wait_block#,row_wait_row#,logon_time,client_identifier from v$session where username is not null and username not in('SYS','SYSTEM','DBSNMP','SYSMAN');
select * from v$session_longops;
select * from v$session_event where sid in (select sid from v$session where username is not null and username not in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by time_waited_micro desc;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*redolog-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select group#, sequence#, ((bytes/1024)/1024) as mbytes, members, archived, status from v$log;
select group#, status, member from v$logfile;
select * from(select to_char(first_time,'dd.mm.yyyy') as tag, count(*) as logswitch_anzahl
from v$log_history group by to_char(first_time,'dd.mm.yyyy')
order by to_date(to_char(first_time,'dd.mm.yyyy'),'dd.mm.yyyy') desc)
where rownum<=10;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*archiver-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
archive log list
col name format a100
select * from (select sequence#, archived, backup_count,deleted,completion_time,name from v$archived_log order by sequence# desc) where rownum<=30;
col name clear
show parameter log_arch

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*buffer-cache-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
show parameter statistics_level
show parameter db_cache_advice
show parameter _cache_size
show parameter cpu_count
show parameter db_writer_processes
show parameter dbwr_io_slaves
show parameter disk_asynch_io
show parameter db_file_multiblock_read_count
select name, block_size, size_for_estimate,size_factor,estd_physical_read_factor from v$db_cache_advice order by name,block_size, size_for_estimate;
select name, 1 - (physical_reads / (db_block_gets + consistent_gets)) hit_ratio
from v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0;
select statistic_name,object_name, value
from v$segment_statistics
where statistic_name = 'buffer busy waits' and
      value > 20000;
select * from ( select owner, object_name,object_type, statistic_name, sum(value)
from v$segment_statistics
where owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN')
group by owner, object_name, object_type,statistic_name
order by sum(value) desc)
where rownum col event format a25
select event,total_waits,time_waited,average_wait from v$system_event where event in('db file sequential read','db file parallel read','db file scattered read');
col event clear
set heading off

select lpad ('*',100,'*') || chr(13) || chr(10) || '*shared-pool-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
show parameter shared_pool
show parameter cursor_sharing

col namespace format a20
select namespace, gethitratio, pinhitratio,reloads,invalidations from v$librarycache;
col namespace clear
select parameter, type,getmisses,scanmisses from v$rowcache;
select shared_pool_size_for_estimate,shared_pool_size_factor,estd_lc_time_saved_factor,estd_lc_time_saved_factor from v$shared_pool_advice;
select plan_hash_value, count(*) as anzahl_der_sql_anweisungen
from v$sql
where parsing_schema_id not in
(Select user_id from dba_users where username in ('SYS','SYSTEM','DBSNMP','SYSMAN')) and plan_hash_value0
group by plan_hash_value
having count(*)>3
order by 2;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*pga-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
show parameter area_size
show parameter pga_agg
select * from v$pgastat;
select pga_target_for_estimate,pga_target_factor,estd_pga_cache_hit_percentage from v$pga_target_advice;
select sql_text,
       sum(onepass_executions) onepass_cnt,
       sum(multipasses_executions) mpass_cnt
from v$sql s, v$sql_workarea wa
where s.address = wa.address
group by sql_text
having sum(onepass_executions+multipasses_executions)>0;
select to_number(decode(sid, 65535, null, sid)) sid,
       operation_type                     operation,
       trunc(expected_size/1024)              esize,
       trunc(actual_mem_used/1024)              mem,
       trunc(max_mem_used/1024)              maxmem,
       number_passes                           pass,
       trunc(tempseg_size/1024)               tsize
from v$sql_workarea_active
order by 1,2;
select * from v$pga_target_advice_histogram;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*log-buffer-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
show parameter log_buff
show parameter fast_start_mttr
show parameter checkpoint
select r.value redo_buffer_allocation_retries, e.value redo_entries,
r.value/e.value*100 percentage
from v$sysstat r, v$sysstat e
where r.name = 'redo buffer allocation retries'
and e.name='redo entries';
select sid, event, seconds_in_wait, state
from v$session_wait
where event = 'log buffer space%';
col event format a40
select event, total_waits, time_waited, average_wait
from v$system_event
where event like 'log file switch completion%';
select event, total_waits, time_waited, average_wait
from v$system_event
where event like 'log file switch (arch%';
SELECT event, total_waits, time_waited, average_wait
FROM V$SYSTEM_EVENT
WHERE event like 'log file switch (check%';
col event clear
select 'ACTUAL_REDO_BLKS' parameter, ACTUAL_REDO_BLKS as Value from v$instance_recovery
union all
select 'TARGET_REDO_BLKS' parameter, TARGET_REDO_BLKS as Value from v$instance_recovery
union all
select 'LOG_FILE_SIZE_REDO_BLKS' parameter, LOG_FILE_SIZE_REDO_BLKS as Value from v$instance_recovery
union all
select 'LOG_CHKPT_TIMEOUT_REDO_BLKS' parameter, LOG_CHKPT_TIMEOUT_REDO_BLKS as Value from v$instance_recovery
union all
select 'LOG_CHKPT_INTERVAL_REDO_BLKS' parameter, LOG_CHKPT_INTERVAL_REDO_BLKS as Value from v$instance_recovery
union all
select 'TARGET_MTTR' parameter, TARGET_MTTR as Value from v$instance_recovery
union all
select 'ESTIMATED_MTTR' parameter, ESTIMATED_MTTR as Value from v$instance_recovery;

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*datei-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select * from dba_tablespaces;
col name format a100
select f.name,fs.* from v$filestat fs,v$datafile f where f.file#=fs.file#;
col name clear

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*undo-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
show parameter undo
select * from dba_tablespaces where contents='UNDO';
select tablespace_name, ((bytes/1024)/1024) as mbytes, ((user_bytes/1024)/1024) as used_mbytes from dba_data_files where tablespace_name like '%UNDO%';
SELECT (((((UR * (UPS * DBS)) + (DBS * 24))/1024)/1024)) AS UNDO_MIN_MB 
        FROM (SELECT value AS UR 
        FROM v$parameter 
        WHERE name = 'undo_retention'), 
        (SELECT (SUM(undoblks)/SUM 
              (((end_time-begin_time)*86400))) AS UPS 
            FROM v$undostat), 
            (SELECT value AS DBS 
            FROM v$parameter 
            WHERE name = 'db_block_size');
col sql_text format a200
col seconds format a20
select TO_CHAR(ELAPSED_TIME/1000000,999999990.00) as seconds,sql_text from v$sqlarea where COMMAND_TYPE=3 and ELAPSED_TIME=(select max(ELAPSED_TIME) from v$sqlarea where COMMAND_TYPE=3);
col sql_text clear
col seconds clear

set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*statistik-informationen:' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
show parameter optimizer
select * from aux_stats$;
select owner, count(*) tables_without_stats from dba_tables
where last_analyzed is null and owner not in('SYS','SYSTEM','DBSNMP','SYSMAN')
group by owner
order by tables_without_stats desc;
select owner, count(*) tables_with_old_stats from dba_tables
where months_between(sysdate,last_analyzed)>6 and owner not in('SYS','SYSTEM','DBSNMP','SYSMAN')
group by owner
order by tables_with_old_stats desc;
select owner, count(*) indexes_without_stats from dba_indexes
where last_analyzed is null and owner not in('SYS','SYSTEM','DBSNMP','SYSMAN')
group by owner
order by indexes_without_stats desc;
select owner, count(*) indexes_with_old_stats from dba_indexes
where months_between(sysdate,last_analyzed)>6 and owner not in('SYS','SYSTEM','DBSNMP','SYSMAN')
group by owner
order by indexes_with_old_stats desc;

col sql_text format a100
set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*top10-sql-cpu :' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select username, CPU_TIME, OPTIMIZER_MODE, ROWS_PROCESSED, SQL_TEXT from (select CPU_TIME, OPTIMIZER_MODE, ROWS_PROCESSED, PARSING_USER_ID, SQL_TEXT from v$sql where PARSING_USER_ID not in (select user_id from dba_users where username in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by CPU_TIME desc) ts ,dba_users du where ts.PARSING_USER_ID=du.user_id and rownum<=10
/
set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*top10-sql-elapsed-time :' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select username, ELAPSED_TIME, OPTIMIZER_MODE, ROWS_PROCESSED, SQL_TEXT from (select ELAPSED_TIME, OPTIMIZER_MODE, ROWS_PROCESSED, PARSING_USER_ID, SQL_TEXT from v$sql where PARSING_USER_ID not in (select user_id from dba_users where username in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by ELAPSED_TIME desc) ts ,dba_users du where ts.PARSING_USER_ID=du.user_id and rownum<=10
/
set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*top10-sql-disk-reads :' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select username, DISK_READS, OPTIMIZER_MODE, ROWS_PROCESSED, SQL_TEXT from (select DISK_READS, OPTIMIZER_MODE, ROWS_PROCESSED, PARSING_USER_ID, SQL_TEXT from v$sql where PARSING_USER_ID not in (select user_id from dba_users where username in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by DISK_READS desc) ts ,dba_users du where ts.PARSING_USER_ID=du.user_id and rownum<=10
/
set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*top10-sql-buffer-gets :' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select username, BUFFER_GETS, OPTIMIZER_MODE, ROWS_PROCESSED, SQL_TEXT from (select BUFFER_GETS, OPTIMIZER_MODE, ROWS_PROCESSED, PARSING_USER_ID, SQL_TEXT from v$sql where PARSING_USER_ID not in (select user_id from dba_users where username in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by BUFFER_GETS desc) ts ,dba_users du where ts.PARSING_USER_ID=du.user_id and rownum<=10
/
set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*top10-sql-buffer-sorts :' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select username, SORTS, OPTIMIZER_MODE, ROWS_PROCESSED, SQL_TEXT from (select SORTS, OPTIMIZER_MODE, ROWS_PROCESSED, PARSING_USER_ID, SQL_TEXT from v$sql where PARSING_USER_ID not in (select user_id from dba_users where username in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by SORTS desc) ts ,dba_users du where ts.PARSING_USER_ID=du.user_id and rownum<=10
/
set heading off
select lpad ('*',100,'*') || chr(13) || chr(10) || '*top10-sql-executions :' || chr(13) || chr(10) || lpad ('*',100,'*') || chr(13) || chr(10) from dual;
set heading on
select username, EXECUTIONS, OPTIMIZER_MODE, ROWS_PROCESSED, SQL_TEXT from (select EXECUTIONS, OPTIMIZER_MODE, ROWS_PROCESSED, PARSING_USER_ID, SQL_TEXT from v$sql where PARSING_USER_ID not in (select user_id from dba_users where username in('SYS','SYSTEM','DBSNMP','SYSMAN')) order by EXECUTIONS desc) ts ,dba_users du where ts.PARSING_USER_ID=du.user_id and rownum<=10
/
spool off
set linesize 100
set feedback on
set pagesize 200
set colsep ' '
set verify on
set echo on