oracle 之数据库核查

clear columns breaks
set feed off wrap off verify off head off trim off
set pagesize 20
set linesize 100
--set newpage 0
set term off
set heading off
set TTITLE  ON

column dat1 new_value filename;
select to_char(sysdate,'yyyy-mm-dd hh24') dat1 from dual; 
spool c:&&filename..txt

                                     prompt *************************************************
                                     prompt **************数据库相关主要核查信息*********************
                                     prompt *************************************************

select  '实例名称:' || name from v$database;

select '历史最高会话数：' || sessions_highwater from v$license;

select '当前连接数:' || count(*)  from v$process;

select '当前进程数:' || value  from v$parameter where name = 'processes';

Select '并发连接数:' || count(*)  from v$session where status = 'ACTIVE';

select '版本信息:' || BANNER  from v$version where rownum<2;

set heading on

column username justify left format a14;
column 用户名 justify left format a14;

column count(username)  format a8;
column 用户连接的最大数 format a8;

select username 用户名, to_char(count(username)) 用户连接的最大数
  from v$session
 where username is not null
 group by username;

column destination format a32;
column 存储路径 format a32;

select status 状态,destination 存储路径 from v$archive_dest;

column username format a16;
column 用户 format a16;

column tablespace_name format a16;
column 表空间名称 format a16;

col to_char(max_bytes / 1024 / 1024 || 'M') format a6;
column 限额 format a6;

select username 用户,
       tablespace_name 表空间名称,
       case max_bytes
         when -1 then
          '-1'
         else
          to_char(max_bytes / 1024 / 1024 || 'M')
       end as "限额"
  from dba_ts_quotas
 order by username;

col username format a6;
select * from v$pwfile_users;

column b  justify right format a6;

col ff.s format a12;
col 表空间名称 format a12;

col ff.b format a8;
col 分配空间 format a12;

col (ff.b - fr.b) format a16;
col 已使用空间 format a16;

col fr.b format a12;
col 空闲空间 format a12;

col round((ff.b - fr.b) / ff.b * 100) || '% ' format a8;
col 已使用百分比 format a8;

select ff.s 表空间名称,
       to_char(ff.b) 分配空间,
       to_char((ff.b - fr.b)) 已使用空间,
       to_char(fr.b) 空闲空间,
       to_char(round((ff.b - fr.b) / ff.b * 100) || '% ') 已使用百分比
  from (select tablespace_name s, sum(bytes) / 1024 / 1024 b
          from dba_data_files
         group by tablespace_name) ff,
       (select tablespace_name s, sum(bytes) / 1024 / 1024 b
          from dba_free_space
         group by tablespace_name) fr
 where ff.s = fr.s;

column file_name format a36;
column 文件存储路径 format a36;

column tablespace_name format a18;
column 表空间名称 format a18;

select file_name 文件存储路径, tablespace_name 表空间名称
  from dba_data_files;

column username format a18;
column 用户名称 format a18;

column decode(lock_date, null, 'unlocked', 'locked') format a8;
column 锁定状态  format a8;

select username 用户名称,
       decode(lock_date, null, 'unlocked', 'locked') 锁定状态
  from dba_users;

column increment_by justify left format a12;
column tablespace_name format a18;
column 表空间名称 format a18;

column autoextensible format a6;
column increment_by format a8;

select tablespace_name 表空间名称, autoextensible, to_char(increment_by)
  from dba_data_files;

column grantee format a8;
column 用户 format a8;

column granted_role format a6;
column 权限类型 format a6;

select grantee 用户, granted_role 权限类型
  from dba_role_privs
 where granted_role = 'DBA';

prompt 以下是检查是否有失效的索引分析数据：

select index_name "索引",
       owner  "属主",
       table_name  "表名",
       tablespace_name  "表空间"
  from dba_indexes
 where owner not in ('SYS','SYSTEM')
   and status != 'VALID'
 order by owner;

prompt 以下检查是否有失效的触发器、视图、存储过程、函数的信息：

select object_name  "对象",
       object_type  "类型",
       owner        "属主",
       status       "状态"
  from dba_objects
 where status !='VALID'
   and owner not in ('SYS','SYSTEM')
   and object_type in   ('TRIGGER','VIEW','PROCEDURE','FUNCTION')
 order by owner,object_type;

 

prompt 检查是否存在运行失败的JOB的信息：

 

select job,
       log_user "属主",
       to_char(last_date,'yyyy-mm-dd hh24:mi:ss') "上次执行时间",
       to_char(next_date,'yyyy-mm-dd hh24:mi:ss') "下次执行时间",
       failures  "失败次数",
       what  "内容"
from dba_jobs
where failures !=0 or failures is not null;

spool off;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/20976446/viewspace-674391/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/20976446/viewspace-674391/