oracle 日常检查

1,Shmmax 是核心参数中最重要的参数之一，用于定义单个共享内存段的最大值，shmmax 设置应该足够大，能在一个共享内存段下容纳下整个的SGA ,
         设置的过低可能会导致需要创建多个共享内存段，这样可能导致系统性能的下降.
         一般设置shmmax >=SGA (32Bit 系统是否支持到1.7G 以上SGA 需要注意) 。如果是64Bit 的Linux 操作系统，shmmax 设置为大于SGA_MAX_SIZE 即可。
         Ipcs -sa 可以看到共享内存段个数
2,

1,检查磁盘空间使用情况
select tablespace_name,count(*) chunks,max(bytes)/1024/1024 max_chunk,sum(bytes)/1024/1024 tatal_space from dba_free_space group by tablespace_name;
2,检查是否有失效的索引
select tablespace_name,count(*) chunks,max(bytes)/1024/1024 max_chunk,sum(bytes)/1024/1024 tatal_space from dba_free_space group by tablespace_name;
3，检查是否有无效的对象
col object_name format a25;
select object_name,object_type,owner,status from dba_objects where status !='VALID' and owner not in ('SYS','SYSTEM')

select owner,object_name,object_type from dba_objects where status='INVALID';
4，检查sequence使用
set linesize 120
select sequence_owner,sequence_name,min_value,max_value,increment_by,last_number,cache_size,cycle_flag from dba_sequences；

5,检查是否有运行失败的job
col what format a20;
select job,this_date,this_sec,next_date,next_sec,failures,what from dba_jobs where failures !=0 or failures is not null；
6，检查SGA的使用情况
select * from v$sgastat;
select pool,count(pool),sum(bytes)/1024/1024 from v$sgastat group by pool;

7，检查回滚段使用情况
select n.name,wraps,extends,shrinks,optsize,waits,xacts,aveactive,hwmsize from v$rollstat r, v$rollname n where r.usn=n.usn;
8，检查是否有用户的缺省表空间和临时表空间设置为SYSTEM表空间
select username,default_tablespace,temporary_tablespace from dba_users;
9,检查数据文件的自动增长是否关闭
select file_name,autoextensible from dba_data_files where autoextensible='SYS';

10,检查表空间的使用情况
select a.tablespace_name,round((total-free)/total,3)*100 pecent from (select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name) a,(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) b where a.tablespace_name=b.tablespace_name;
11，检查剩余表空间
select tablespace_name,sum(blocks) as free_blk,trunc(sum(bytes)/(1024*1024)) as free_m,max(bytes)/(1024) as big_chunk_k,count(*) as num_chunks from dba_free_space group by tablespace_name;

12,检查不起作用的约束
select owner,constraint_name,table_name,constraint_type,status from dba_constraints where status='DISABLED' and constraint_type='p';
13,检查无效的trigger
 select owner,trigger_name,table_name,status from dba_triggers where status='DISABLED';