1 conn sys/123456@oracle01 as sysdba
2 column dest_name format a30
3 column destination format a20
4 column MEMBER format a45
5 column destination format a20
6 column TABLESPACE_NAME format a10
7 column FREE_RATE format a10
8 alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
9 set wrap off;
10 prompt **************************** 实 例 状 态 ************************************;
11 select instance_name,version,status,database_status from v$instance;
12 prompt **************************** 数 据 库 状 态 *************************************;
13 select name,log_mode,open_mode from v$database;
14 prompt **************************** 控 制 文 件 状 态 ***********************************;
15 column name format a50
16 select status,name from v$controlfile;
17 prompt **************************** 日 志 文 件 状 态 ***********************************;
18 select GROUP#,status,type,member from v$logfile;
19 prompt***************************** 归 档 目 的 地 状 态 *********************************;
20 select dest_name ,status,database_mode,destination from v$archive_dest_status where
21 dest_id in ('1','2');
22 set heading off;
23 prompt ************ 数 据 库 已 连 续 运 行 天 数*******************************************
24 select round(a.atime-b.startup_time)||' days ' from(select sysdate atime from dual)
25 a,v$instance b;
26 set heading on;
27 prompt***************************** 会 话 数 *************************************;
28 select sessions_current,sessions_highwater from v$license;
29 prompt**************************** active sessions count **************************;
30 select count(*) "Active session count" from v$session where status='ACTIVE';
31 prompt**************************** total sessions count **************************•;
32 select count(*) "Total session count" from v$session;
33 prompt**************************** top 30 big objects name ************************;
34 column OWNER format a10
35 column SEGMENT_NAME format a35
36 column SEGMENT_TYPE format a15
37 column SIZES format a10
38 SELECT * FROM
39 (
40 select OWNER, SEGMENT_NAME, SEGMENT_TYPE, round(BYTES / 1024 /
41 1024 / 1024,3)||'G' AS SIZES
42 from dba_segments
43 ORDER BY BYTES DESC)
44 WHERE ROWNUM<=30
45 ;
46 prompt***************************** WANGGOUuser data size ****************************;
47 select sum(bytes)/1024/1024/1024||'G' "User Data Size" from dba_segments where
48 owner='WANGOU';
49
50 prompt***************************** SUP data size ************************************;
51 select sum(bytes)/1024/1024/1024||'G' "User Data Size" from dba_segments where
52 owner='SUP';
53
54 prompt***************************** DB size ******************************************;
55 select sum(bytes)/1024/1024/1024||'G' "DB Size" from dba_segments;
56
57 prompt***************************** total tablespace size ****************************;
58 select sum(bytes)/1024/1024/1024||'G' "Total Tablespace Size" from
59 dba_data_files;
60
61 prompt***************************** last day archive log count ***********************;
62 select sequence#, completion_time from v$archived_log where completion_time>=
63 trunc(sysdate-1) and completion_time<= trunc(sysdate) and dest_id=1;
64
65 prompt********************** 表空间监控********************;
66 SELECT D.TABLESPACE_NAME,
67 SPACE "SUM_SPACE(M)",
68 BLOCKS "SUM_BLOCKS(K)",
69 SPACE - NVL (FREE_SPACE, 0) "USED_SPACE(M)",
70 ROUND( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2)
71 "USED_RATE(%)",
72 FREE_SPACE "FREE_SPACE(M)"
73 FROM ( SELECT TABLESPACE_NAME,
74 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
75 SUM (BLOCKS) BLOCKS
76 FROM DBA_DATA_FILES
77 GROUP BY TABLESPACE_NAME) D,
78 ( SELECT TABLESPACE_NAME,
79 ROUND (SUM (BYTES) / (1024 * 1024), 2)
80 FREE_SPACE
81 FROM DBA_FREE_SPACE
82 GROUP BY TABLESPACE_NAME) F
83 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
84 UNION ALL --如果有
85 SELECT D.TABLESPACE_NAME,
86 SPACE "SUM_SPACE(M)",
87 BLOCKS SUM_BLOCKS,
88 USED_SPACE "USED_SPACE(M)",
89 ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2)
90 "USED_RATE(%)",
91 NVL (FREE_SPACE, 0) "FREE_SPACE(M)"
92 FROM ( SELECT TABLESPACE_NAME,
93 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
94 SUM (BLOCKS) BLOCKS
95 FROM DBA_TEMP_FILES
96 GROUP BY TABLESPACE_NAME) D,
97 ( SELECT TABLESPACE_NAME,
98 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2)
99 USED_SPACE,
100 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2)
101 FREE_SPACE
102 FROM V$TEMP_SPACE_HEADER
103 GROUP BY TABLESPACE_NAME) F
104 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
105 ORDER BY 1;
106 prompt **************************** 表 空 间OFFLINE(显 示 为 空 正 常) ********************;
107 select tablespace_name ,status from dba_tablespaces where status='OFFLINE';
108 prompt **************************** SEQUENCE 同步数 *********************************;
109 select max(sequence#)from v$log_history;
110 conn sys/123456@oracle01 as sysdba
111 prompt **************************** 备库SEQUENCE 同步数 *****************************;
112 select max(sequence#)from v$log_history;
113 prompt **************************** 备库日志未应用(显 示 为 空 正 常) *******************;
114 select sequence#,applied from v$archived_log where applied='yes';
115 prompt **************************** 备库日志应用(显示最近十个日志) *****************;
116 select * from(select sequence#,applied from v$archived_log order by sequence# desc)
117 where rownum<=10;
118 set time on
119 disconnect
120 exit