我的数据库之路

set linesize 300
alter session set nls_date_format='yyyy-mm-dd hh24:mi';

spool d:\bpms—db_db_check.log

prompt --------------------1.数据库版本信息------------------------

select * from v$version;

prompt



prompt --------------------2.数据库字符集------------------------
column value format a15
select value from nls_database_parameters where parameter='NLS_CHARACTERSET';

prompt
prompt --------------------3.实例信息------------------------

column inst_id format 9999999
column iName format a8
column host_name format a15
column version format a10
column DBStatus format a8
column iRole format a18
select inst_id,instance_name iName,host_name,version,database_status DBStatus,
instance_role iRole,startup_time from gv$instance;


prompt
prompt --------------------4.数据库参数------------------------

column sysdate format a20
column name format a22
column value format a30
select sysdate, name, value
from v$parameter
where name in('db_name',
'instance_name',
'sga_max_size',
'db_cache_size',
'shared_pool_size',
'db_block_size',
'java_pool_size',
'large_pool_size',
'db_keep_cache_size',
'db_recycle_cache_size',
'db_8k_cache_size',
'db_16k_cache_size',
'db_32k_cache_size',
'db_4k_cache_size',
'db_2k_cache_size',
'log_buffer',
'pga_aggregate_target',
'workarea_size_policy',
'sort_area_size',
'cursor_sharing',
'processes',
'job_queue_processes',
'spfile',
'db_files',
'open_cursors',
'open_links')
order by 2;

prompt
prompt --------------------5.缓冲区命中率------------------------

column sysdate format a20
select sysdate, TO_NUMBER(round((1-pR.phy_read/lR.Log_read)*100,2)) Buf_Hit,phy_read,Log_read
from (select value phy_read
from v$sysstat t
where NAME in ('physical reads')) pR,
(select sum(value) Log_read
from v$sysstat t
where NAME in ('consistent gets', 'db block gets')) lR;

prompt
prompt --------------------6.共享池命中率------------------------

select sum(gets) gets,
sum(gethits) gethits,
round(sum(gethits)/sum(gets),2) * 100 "GetHitratio",
sum(pins) pins,
sum(pinhits) pinhits,
round(sum(pinhits)/sum(pins),2) * 100 "pinHitratio"
from v$librarycache t;

prompt
prompt --------------------7.排序区命中率------------------------

select sysdate,
round((1-b.VALUE /
decode((a.VALUE + b.VALUE), 0, 1, (a.VALUE + b.VALUE))) * 100,
2) SORT_HIT,
a.VALUE sorts_memory,
b.VALUE sorts_disk
from v$sysstat a, v$sysstat b
where a.NAME = 'sorts (memory)'
and b.NAME = 'sorts (disk)';

prompt
prompt --------------------8.重作日志文件信息------------------------

column member format a55
select l.thread#, l.group#, l.members, l.bytes, f.member
from v$log l, v$logfile f
where l.group# = f.group#;

prompt
prompt --------------------9.控制文件信息------------------------

column name format a55
select status,name from v$controlfile;

prompt
prompt -----------------10.查看表空间使用率--------------

column tablespace_name for a30
SELECT df.tablespace_name, COUNT(*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576) size_mb,
ROUND (SUM (free.BYTES) / 1048576, 2) free_mb,
ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2) used_mb,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free,
df.autoextensible
FROM dba_data_files df,
(SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free
WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name,df.autoextensible ORDER BY 7;

prompt
prompt -----------------11.异常的数据文件--------------

column tablespace_name for a30
column file_name for a30
column error for a30
select b.file_id,b.tablespace_name,b.file_name,a.online_status,a.error from v$recover_file a,dba_data_files b
where a.file#=b.file_id;

prompt
prompt --------------------12.无效索引信息------------------------

select owner,
index_name object_name,
'INDEX-' || index_type ||
decode(partitioned, 'NO', '', '-partitioned') object_type,
status,
sysdate - 100000 last_change
from dba_indexes t
where t.status not in ('VALID', 'N/A')
union all
select index_owner owner,
index_name object_name,
'INDEX-' || partition_name object_type,
status,
sysdate - 100000 last_change
from dba_ind_partitions t
where t.status not in ('USABLE');

prompt
prompt ----------13.可能需要rebuild的索引----------------------

column owner format a12
column table_owner format a20
column index_type format a10
column table_name format a20
SELECT t.owner,t.index_name,t.index_type,t.table_owner,t.table_name,
t.blevel FROM DBA_INDEXES T WHERE T.BLEVEL>= 3;

prompt
prompt ---------------14.资源限制与使用情况---------------

column RESOURCE_NAME format a24
column LIMIT_VALUE format a13
column CURRENT_UTILIZATION format 9999

select t.RESOURCE_NAME,t.CURRENT_UTILIZATION,t.MAX_UTILIZATION,t.LIMIT_VALUE
from v$resource_limit t;

prompt
prompt ---------------15.具有DBA角色的用户---------------

column GRANTEE format a18
column GRANTED_ROLE format a18
column account_status format a18
select t1.GRANTEE, t1.GRANTED_ROLE, t2.account_status, t2.created
from (select *
from dba_role_privs T
where granted_role = 'DBA'
and t.grantee not in ('SYS', 'SYSTEM')) t1,
(select * from dba_users) t2
where t1.GRANTEE = t2.username;

prompt
prompt ---------------16.归档参数值---------------

column name format a40
select name,value from v$parameter where name like 'log_archive_dest%';

prompt
prompt ---------------17.归档频率及次数---------------

column date1 format a10
select trunc(completion_time) as date1,
count(0) as cnt,
trunc(sum((blocks * block_size) / 1024 / 1024)) as mb
from v$archived_log
where rownum<10
group by trunc(completion_time) order by 1 desc;


spool off