巡检查询

该脚本的作用是在用户需要查询神通数据库相关信息时所使用，并且查询结果已生成result.txt文本形式 以方便用户及数据库维护人员对数据库信息进行相关了解

spool result.txt

---1.数据库版本
select '1.数据库版本';
SELECT VERSION();

---2.数据库信息
select '2.数据库信息';
SELECT * FROM  V_SYS_DATABASE_INFO;

---3.配置参数
select '3.配置参数';
show all;

---4.数据库占用的有效空间
select '4.数据库占用的有效空间';
select trunc(sum(size)/1024/1024)||'MB' from sys_class, v_segment_info where relid = oid;

---5.各个模式占用的有效空间
select '5.各个模式占用的有效空间';
select USENAME, sum(size) / 1024.0 / 1024 || 'M' total_space
  from sys_class, v_segment_info, sys_shadow
 where relid = oid
   and USESYSID = RELOWNER
 group by RELOWNER, USENAME;

---6.表空间
select '6.表空间';
SELECT TSNAME,TSINITSIZE,TSNEXTSIZE,TSPCTFREE,TSPCTUSED,TSFILL FROM SYS_TABLESPACE;

---7.数据文件
select '7.数据文件';
SELECT "FILE ID",
        B.TSNAME,
        a.path,
        a.size / 1024.0 / 1024 || 'M' current_SIZE,
        a.TOTALFREE / 1024.0 / 1024 || 'M' FREESIZE,
        ((1 - a.TOTALFREE * 1.0 / a.SIZE) * 100) "PCT_USED(%)",
        a.MAXSIZE max_size,
        A.NEXT / 1024.0 / 1024 || 'M' NEXTSISE,
        a."CREATION TIME"
   FROM V_SYS_DATAFILE_INFO a, SYS_TABLESPACE b
  WHERE NAME = tsname
  order by size desc;


---8.日志文件
select '8.日志文件';
SELECT * FROM v_sys_loggroup_info;

---9.用户表数目
select '9.用户表数目';
select count(*) total_table_num from sys_class where RELNAMESPACE != 11 and RELKIND = 'r';

---10.用户索引数目
select '10.用户索引数目';
select count(*) total_index_num from sys_class where RELNAMESPACE != 11 and RELKIND = 'i';

---11.用户视图数目
select '11.用户视图数目';
select count(*) total_view_num from sys_class where RELNAMESPACE != 11 and RELKIND = 'v';

---12.用户存储过程数目
select '12.用户存储过程数目';
SELECT count(*) total_procedure_num FROM SYS_PROC WHERE PRONAMESPACE != 11;

---13.用户对象信息
select '13.用户对象信息';
select USENAME,
       relname,
       current_size,
       RELPAGES,
       RELTUPLES
  from (select USENAME,
               relname,
               trunc(size / 1024.0 / 1024 + 0.005, 2) current_size,
               RELPAGES,
               trunc(RELTUPLES + 0.5) RELTUPLES
          from sys_class, v_segment_info, sys_shadow
         where RELOWNER = USESYSID
           and relid = oid
           and RELNAMESPACE != 11
           and RELSID != 0) order by RELTUPLES desc limit 30;


---14.IO情况
select '14.IO情况';
select BUFFERREAD,BUFFERWRITE,DATAREAD,DATAWRITE from my_storage_stats;

---15.当前session连接数
select '15.当前session连接数';
select count(*) as "连接数" from V_SYS_SESSIONS;

---16.当前表占用的磁盘空间
select '16.当前表占用的磁盘空间';
select trunc(sum(b.SIZE)/1024/1024,2)||' MB' from sys_class a,v_segment_info b where RELSID = SEGID AND RELNAMESPACE != 11 and RELKIND = 'r';

---17.当前索引占用的磁盘空间
select '17.当前索引占用的磁盘空间';
select trunc(sum(b.SIZE)/1024/1024,2)||' MB' from sys_class a,v_segment_info b where RELSID = SEGID AND RELNAMESPACE != 11 and RELKIND = 'i';