如何通过APEX实现标准化运维sql.txt
免费下载
APEX 演示 SQL:
实例基本信息：
select instance_name INSTANCE_NAME ,instance_number instance_num ,thread#
thread_num , host_name HOST_NAME , version VERSION ,TO_CHAR(startup_time,'yyyy-
mm-dd HH24:MI:SS') STARTUP_TIME , ROUND(TO_CHAR(SYSDATE-startup_time), 2)
uptime_day ,decode(parallel,'NO','NO','YES') parallel ,status instance_status
,decode(logins,'ALLOWED','NO','YES') logins , DECODE( archiver , 'FAILED' ,
'FAILED' , 'STOPPED' , 'NO' , 'YES') archiver FROM gv$instance
表空间：
SELECT D.TABLESPACE_NAME, autoextensible, SPACE ||'M' "SUM_SPACE(M)",
to_char(BLOCKS) "SUM_BLOCKS", SPACE - NVL (FREE_SPACE, 0) ||'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2)||'%' "USED_RATE(%)",
FREE_SPACE ||'M' "FREE_SPACE(M)" , B.BIGFILE FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS) BLOCKS,
AUTOEXTENSIBLE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ,autoextensible) D,
( SELECT TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE FROM
DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F , dba_tablespaces B WHERE
D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.TABLESPACE_NAME =
B.TABLESPACE_NAME(+) UNION ALL SELECT D.TABLESPACE_NAME, ' ', SPACE ||'M'
"SUM_SPACE(M)", to_char(BLOCKS) SUM_BLOCKS, USED_SPACE ||'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) ||'%' "USED_RATE(%)", NVL
(FREE_SPACE, 0) ||'M' "FREE_SPACE(M)" , 'NO' BIGFILE FROM ( SELECT
TABLESPACE_NAME, ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, SUM (BLOCKS)
BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, ( SELECT
TABLESPACE_NAME, ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, ROUND
(SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP
BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) ORDER BY 1
数据文件：
select d.file_id, d.file_name, d.tablespace_name, round(d.bytes / 1024 / 1024 /
1024, 2) "Used(G)", d.AUTOEXTENSIBLE, round(d.MAXBYTES / 1024 / 1024 / 1024, 2)
"maxsixe(G)" from dba_data_files d where 1=1 union all select d.FILE_ID,
d.file_name, d.tablespace_name, round(d.bytes / 1024 / 1024 / 1024, 2)
"Used(G)", d.AUTOEXTENSIBLE, round(d.bytes / 1024 / 1024 / 1024, 2) "Used(G)"
from dba_temp_files d where 1=1
redo 日志：
select * from ( SELECT i.instance_name instance, i.status , i.thread# thread,
f.group# groupno, f.MEMBER MEMBER, f.TYPE redo_file_type, DECODE (l.status,
'CURRENT', l.status, l.status) log_status, l.bytes/1024/1024 MB, l.archived
archived FROM gv$logfile f, gv$log l, gv$instance i WHERE f.group# = l.group#
AND l.thread# = i.thread# AND i.inst_id = f.inst_id AND f.inst_id = l.inst_id
union all SELECT i.instance_name instance, i.status , i.thread# thread, f.group#
groupno, f.MEMBER MEMBER, f.TYPE redo_file_type, DECODE (l.status, 'CURRENT',
l.status, l.status) log_status, l.bytes/1024/1024 MB, l.archived archived FROM
gv$logfile f, gv$standby_log l, gv$instance i WHERE f.group# = l.group# AND
l.thread# = i.thread# AND i.inst_id = f.inst_id AND f.inst_id = l.inst_id )
ORDER BY redo_file_type, instance, groupno, MEMBER
归档日志：
select INST_ID,
substr(to_char(FIRST_TIME,'YYYY/MM/DD DY'),1,15) day,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'00',1,0))) d_0,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'01',1,0))) d_1,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'02',1,0))) d_2,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'03',1,0))) d_3,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'04',1,0))) d_4,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'05',1,0))) d_5,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'06',1,0))) d_6,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'07',1,0))) d_7,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'08',1,0))) d_8,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'09',1,0))) d_9,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'10',1,0))) d_10,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'11',1,0))) d_11,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'12',1,0))) d_12,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'13',1,0))) d_13,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'14',1,0))) d_14,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'15',1,0))) d_15,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'16',1,0))) d_16,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'17',1,0))) d_17,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'18',1,0))) d_18,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'19',1,0))) d_19,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0)),0,'-',sum(de
code(substr(to_char(FIRST_TIME,'HH24'),1,2),'20',1,0))) d_20,
decode(sum(decode(substr(to_char(FIRST_TIME,'HH24'),1,2),'21',1,0)),0,'-',sum(de
of 4
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
文档被以下合辑收录
评论