parameter--数据库参数检查

spool /tmp/check_oracle_env.log
set echo on
set trimspool on
set line 5000 pagesize 5000
select action,version,comments from dba_registry_history;
col tablespace_name for a12
col file_name for a60
select tablespace_name, file_name ,bytes/1024/1024/1024 from dba_temp_files
union all
select tablespace_name,file_name,bytes/1024/1024/1024 from dba_data_files;
select tablespace_name, file_name ,autoextensible from dba_temp_files where tablespace_name like '%TEMP%'
union all
select tablespace_name,file_name,autoextensible from dba_data_files where tablespace_name like '%UNDO%';

select b.tablespace_name,
round(curr_size/1024/1024,2) curr_size_mb,
round(max_size_Gb,2) max_size_Gb,
nvl(round(free_size/1048576,2),0) free_size_mb,
nvl(round(free_size/curr_size*100,2),0)||'%' pct_free,
nvl(round((curr_size-free_size)/curr_size*100,2),100)||'%' pct_used
from (select tablespace_name,sum(bytes) free_size
from dba_free_space
group by tablespace_name) a,
(select tablespace_name,sum(bytes) curr_size,
sum(case
when MAXBYTES=0 then BYTES
else MAXBYTES end)/1024/1024/1024 max_size_Gb
from dba_data_files
group by tablespace_name) b
where
a.tablespace_name(+)=b.tablespace_name;

show parameter instance_name
show parameter instance_group
show parameter parallel_instance_group
show parameter parallel_force_local
show parameter memory
show parameter sga_
show parameter pga_
show parameter db_cache_size
show parameter shared_pool_size
show parameter log_buffer
show parameter session_cached_cursors
show parameter open_cursors
show parameter processes
show parameter db_files
show parameter undo_retention
show parameter recyclebin
show parameter large_pool_size
show parameter audit_trail
select * from dba_hist_wr_control;
archive log list;
show parameter archive
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,STATE,OS_MB,TOTAL_MB,FREE_MB,LABEL,PATH from v$asm_disk;
select name,total_mb,free_mb from v$asm_diskgroup;
select group#,thread#,members,blocksize,bytes/1024/1024 size_mb from v$log;
col member for a50
col is_recovery_dest_file for a21
select * from v$logfile;
select * from gv$cluster_interconnects;
select task_name,client_name,status,current_job_name from dba_autotask_task;
col value for a15;
col describ for a60;
col name for a50;
select x.ksppinm name, y.ksppstvl value, x.ksppdesc describ
from sys.x$ksppi x, sys.x$ksppcv y
where x.inst_id = userenv('instance')
and y.inst_id = userenv('instance')
and x.indx = y.indx
and x.ksppinm in ('_serial_direct_read',
'_gc_affinity_time',
'_gc_policy_time',
'_gc_undo_affinity',
'_optimizer_extended_cursor_sharing_rel',
'_optimizer_extended_cursor_sharing');
select * from v$controlfile;
select client_name,status from dba_autotask_client;
select profile,resource_name,resour

ce_type,limit from dba_profiles where resource_name in ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LIFE_TIME');
select sequence#,first_time,nexttime,round(((first_time-nexttime)*24)*60)||'m' diff
from (
select sequence#,first_time, lag(first_time) over(order by sequence#) nexttime
from v$log_history
where thread#=1
) order by sequence# desc;
select OCCUPANT_NAME,SPACE_USAGE_KBYTES/1024/1024 from V$SYSAUX_OCCUPANTS;
SELECT s.object_name from dba_objects s where s.object_name like '%OPTSTAT%' and s.object_type='TABLE';
spool off;