Oracle DBA 常用脚本
以下脚本收集自网上，自己经常使用，有了互联网，有了网络，才让工作变得轻松。未能一一注明出处，还请见谅。
----监控数据库信息
SELECT to_char(A.DBID) DBID,DB_UNIQUE_NAME,TO_CHAR(CREATED, 'YYYY-MM-DD HH24:MI:SS') CREATED,OPEN_MODE,GUARD_STATUS, PLATFORM_NAME,LOG_MODE,FORCE_LOGGING,FLASHBACK_ON,DATABASE_ROLE,(SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_RDBMS_VERSION') NLS_RDBMS_VERSION,(SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_LANGUAGE') NLS_LANGUAGE,(SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET') AS NLS_CHARACTERSET,(SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER = 'NLS_LENGTH_SEMANTICS') NLS_LENGTH_SEMANTICS,(SELECT ROUND(SUM(BYTES)/1024/1024/1024,2) DFILECAP FROM DBA_DATA_FILES) DFILECAP,(SELECT ROUND(SUM(BYTES) / 1024 / 1024 / 1024, 2) DATACAP FROM DBA_SEGMENTS) DATACAP,SNAP_INTERVAL AWR_INTERVAL, RETENTION AWR_RETENTION, (SELECT TO_CHAR(MAX(END_TIME),'YYYY-MM-DD HH24:MI:SS') RMAN_LASTCOMPLETED FROM V$RMAN_STATUS WHERE STATUS = 'COMPLETED' AND OBJECT_TYPE LIKE 'DB FULL') RMAN_LASTCOMPLETED,DECODE((SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Real Application Clusters'), 'TRUE',1,0) AS IS_RAC FROM V$DATABASE A,DBA_HIST_WR_CONTROL X
WHERE A.DBID=X.DBID;
---监控实例信息
SELECT INSTANCE_NAME,HOST_NAME,TO_CHAR(STARTUP_TIME, 'YYYY-MM-DD HH24:MI:SS') AS STARTUP_TIME,STATUS,VERSION,LOGINS,ROUND(SYSDATE - STARTUP_TIME,2) AS RUNNING_TIME,DATABASE_STATUS,INSTANCE_ROLE,(SELECT D.VALUE FROM V$PARAMETER D WHERE D.NAME='processes') PROCESSES,(SELECT D.VALUE FROM V$PARAMETER D WHERE D.NAME='sessions') SESSIONS,(SELECT D.VALUE FROM V$PARAMETER D WHERE D.NAME='open_cursors') OPEN_CURSORS,(SELECT COUNT(1) FROM V$SESSION WHERE USERNAME IS NOT NULL) SESSION_COUNT,(SELECT COUNT(1) FROM V$SESSION WHERE USERNAME IS NOT NULL AND STATUS='ACTIVE') ACTIVE_SESSION FROM V$INSTANCE;
---用户
SELECT D.USERNAME,D.ACCOUNT_STATUS,D.LOCK_DATE,D.EXPIRY_DATE,D.DEFAULT_TABLESPACE,D.TEMPORARY_TABLESPACE,D.CREATED,X.LIMIT FROM DBA_USERS D,DBA_PROFILES X WHERE X.PROFILE=D.PROFILE AND X.RESOURCE_NAME='PASSWORD_GRACE_TIME' AND USERNAME NOT IN ('DBSNMP', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR', 'HR', 'APEX_PUBLIC_USER', 'OE', 'DIP', 'SH', 'IX', 'MDDATA', 'PM', 'BI', 'XS$NULL', 'ORACLE_OCM', 'SCOTT', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS', 'ORDPLUGINS', 'XDB', 'ANONYMOUS', 'CTXSYS', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS', 'WMSYS', 'EXFSYS', 'ORDSYS', 'MDSYS', 'FLOWS_FILES', 'MGMT_VIEW', 'OUTLN');
--业务表监控
select d.owner,
d.segment_name,
round(sum(d.BYTES / 1024 / 1024 / 1024), 2) sumcap
from dba_segments d
where d.segment_type in ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') and d.owner not in ('SYS','SYSTEM','SYSMAN','DBSNMP', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR', 'HR', 'APEX_PUBLIC_USER', 'OE', 'DIP', 'SH', 'IX', 'MDDATA', 'PM', 'BI', 'XS$NULL', 'ORACLE_OCM', 'SCOTT', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS', 'ORDPLUGINS', 'XDB', 'ANONYMOUS', 'CTXSYS', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS', 'WMSYS', 'EXFSYS', 'ORDSYS', 'MDSYS', 'FLOWS_FILES', 'MGMT_VIEW', 'OUTLN')
group by d.owner, d.segment_name;
---告警日志
select d.host_id,
host_address,
d.originating_timestamp,
d.indx,
d.inst_id,
CASE d.message_type
WHEN 1 THEN
'Unknown'
WHEN 2 THEN
'Incident Error'
WHEN 3 THEN
'Error'
WHEN 4 THEN
'Warning'
WHEN 5 THEN
'Notification'
WHEN 6 THEN
'Trace'
END AS message_type,
CASE d.message_level
WHEN 1 THEN
'Critical'
WHEN 2 THEN
'Severe'
WHEN 8 THEN
'Important'
WHEN 16 THEN
'Normal'
END AS message_level,
d.message_text
from X$DBGALERTEXT d
where d.COMPONENT_ID = 'rdbms'
and d.ORIGINATING_TIMESTAMP > SYSTIMESTAMP - INTERVAL '5' MINUTE;
--失效对象监控
select d.OWNER,d.OBJECT_NAME,d.OBJECT_ID,d.OBJECT_TYPE,d.CREATED,d.LAST_DDL_TIME,d.status from dba_objects d where d.OWNER not in ('DBSNMP', 'SPATIAL_WFS_ADMIN_USR', 'SPATIAL_CSW_ADMIN_USR', 'HR', 'APEX_PUBLIC_USER', 'OE', 'DIP', 'SH', 'IX', 'MDDATA', 'PM', 'BI', 'XS$NULL', 'ORACLE_OCM', 'SCOTT', 'OLAPSYS', 'SI_INFORMTN_SCHEMA', 'OWBSYS', 'ORDPLUGINS', 'XDB', 'ANONYMOUS', 'CTXSYS', 'ORDDATA', 'OWBSYS_AUDIT', 'APEX_030200', 'APPQOSSYS', 'WMSYS', 'EXFSYS', 'ORDSYS', 'MDSYS', 'FLOWS_FILES', 'MGMT_VIEW', 'OUTLN') and d.status<>'VALID';
---锁表监控
select distinct c.inst_id,
a.session_id,
c.serial#,
b.object_name,
b.owner,
a.os_user_name,
c.module,
round(c.seconds_in_wait / 60, 2) minutes,
'alter system kill session ''' || session_id || ',' ||
c.serial# || ''' immediate;',
(select 'kill -9 ' || e.spid
from gv$process e
where e.addr = c.paddr
and e.inst_id = c.inst_id),
c.sql_id sqlid,
(select d.sql_text
from gv$sqlarea d
where d.inst_id = c.inst_id
and d.sql_id = c.sql_id) sqlarea,
c.prev_sql_id pre_sqlid,
(select d.sql_text
from gv$sqlarea d
where d.inst_id = c.inst_id
and d.sql_id = c.prev_sql_id) pre_sqlarea
from gv$locked_object a, dba_objects b, gv$session c
where a.object_id = b.object_id
and a.inst_id = c.inst_id
and a.session_id = c.sid
and b.object_name = 'XXX';
---锁监控
select (select 'alter system kill session ''' || d.sid || ',' || d.serial# ||
''' immediate;'
from gv$session d
where d.sid = s.FINAL_BLOCKING_SESSION
and d.INST_ID = s.FINAL_BLOCKING_INSTANCE) kill_BLOCKING_SESSION_SQL,
'alter system kill session ''' || s.sid || ',' || s.serial# ||
''' immediate;',
s.FINAL_BLOCKING_SESSION_STATUS,
s.FINAL_BLOCKING_INSTANCE,
s.FINAL_BLOCKING_SESSION,
s.BLOCKING_SESSION_STATUS,
s.BLOCKING_INSTANCE,
s.BLOCKING_SESSION,
s.INST_ID,
s.pADDR,
s.SID,
s.STATUS,
s.SERIAL#,
s.USERNAME,
s.MACHINE,
s.TERMINAL,
s.PROGRAM,
s.ACTION,
s.PREV_SQL_ID,
s.EVENT,
s.WAIT_CLASS,
round(s.seconds_in_wait / 60, 2) minutes,
s.STATE,
s.SERVICE_NAME,
decode(lmode,
0,
'None',
1,
'Null',
2,
'Row-S',
3,
'Row-X',
4,
'Share',
5,
'S/Row-X',
6,
'Exclusive',
'Unknown') LockMode,
decode(request,
0,
'None',
1,
'Null',
2,
'Row-S',
3,
'Row-X',
4,
'Share',
5,
'S/Row-X',
6,
'Exclusive',
'Unknown') RequestMode,
ctime,
block
from gv$session s, gv$lock l
where s.SID = l.SID
and s.INST_ID = l.INST_ID
--and s.PROGRAM = 'plsqldev.exe'
--and s.USERNAME='XXX_OP'
and (request > 0 or block > 0)
and request <> 0
and s.SERVICE_NAME <> 'SYS$BACKGROUND'
and l.LMODE <> '4'
and s.blocking_session is not null
order by s.FINAL_BLOCKING_INSTANCE,
s.FINAL_BLOCKING_SESSION,
s.BLOCKING_SESSION;
---表空间监控
select d.tablespace_name "TBS_NAME",
d.contents "TYPE",
nvl(a.bytes / 1024 / 1024 / 1024, 0) "TOTAL(GB)",
nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024 / 1024 "USAGE(GB)",
nvl(f.bytes, 0) / 1024 / 1024 / 1024 "FREE(GB)",
nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0) "FREE PCT %",
nvl(a.ARTACAK, 0) / 1024 / 1024 / 1024 "EXTENSIBLE(GB)",
nvl(a.MAX_BYTES, 0) / 1024 / 1024 / 1024 "MAX_SIZE(GB)",
nvl((a.bytes - nvl(f.bytes, 0)) / (a.bytes + nvl(a.ARTACAK, 0)) * 100,
0) "USED PCT OF MAX %",
a.NO_AXF_NUM,
a.AXF_NUM
from sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(autoextensible, 'YES', maxbytes - bytes, 0)) ARTACAK,
count(decode(autoextensible, 'NO', 0)) NO_AXF_NUM,
count(decode(autoextensible, 'YES', 0)) AXF_NUM,
sum(decode(maxbytes, 0, BYTES, maxbytes)) MAX_BYTES
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes
from dba_free_space
group by tablespace_name) f
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = f.tablespace_name(+)
and not
(d.extent_management like 'LOCAL' and d.contents like 'TEMPORARY')
union all
select d.tablespace_name "TBS_NAME",
d.contents "TYPE",
nvl(a.bytes / 1024 / 1024 / 1024, 0) "TOTAL(GB)",
nvl(t.bytes, 0) / 1024 / 1024 / 1024 "USAGE(GB)",
nvl(a.bytes - nvl(t.bytes, 0), 0) / 1024 / 1024 / 1024 "FREE(GB)",
nvl(t.bytes / a.bytes * 100, 0) "FREE PCT %",
nvl(a.ARTACAK, 0) / 1024 / 1024 / 1024 "EXTENSIBLE(GB)",
nvl(a.MAX_BYTES, 0) / 1024 / 1024 / 1024 "MAX_SIZE(GB)",
nvl(t.bytes / (a.bytes + nvl(a.ARTACAK, 0)) * 100, 0) "USED PCT OF MAX %",
a.NO_AXF_NUM,
a.AXF_NUM
from sys.dba_tablespaces d,
(select tablespace_name,
sum(bytes) bytes,
sum(decode(autoextensible, 'YES', MAXbytes - bytes, 0)) ARTACAK,
count(decode(autoextensible, 'NO', 0)) NO_AXF_NUM,
count(decode(autoextensible, 'YES', 0)) AXF_NUM,
sum(decode(maxbytes, 0, BYTES, maxbytes)) MAX_BYTES
from dba_temp_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes_used) bytes
from v$temp_extent_pool
group by tablespace_name) t
where d.tablespace_name = a.tablespace_name(+)
and d.tablespace_name = t.tablespace_name(+)
and d.extent_management like 'LOCAL'
and d.contents like 'TEMPORARY%'
order by 3 desc;
---数据文件
SELECT file_id,
file_name,
tablespace_name,
autoextensible,status||'-'||online_status status,
bytes / 1073741824 as current_gb,
maxbytes / 1073741824 as max_gb
FROM dba_data_files
UNION
SELECT file_id,
file_name,
tablespace_name as ts_name,
autoextensible,status,
bytes / 1073741824 as cur_gb,
maxbytes / 1073741824 as max_gb
FROM dba_temp_files
ORDER BY tablespace_name, file_id, file_name;
select * from dba_temp_files;
select * from dba_data_files;
---asm文件
SELECT group_number,
name,
type,
total_mb,
free_mb,
hot_used_mb,
required_mirror_free_mb,
usable_file_mb,
offline_disks
FROM v$asm_diskgroup_stat;
---redo日志
SELECT t2.member,
t1.group#,
t1.thread#,
t1.sequence#,
t1.bytes / 1024 / 1024 AS SIZE_MB,
t1.status,
t1.archived,
t1.members
FROM v$log t1, v$logfile t2
WHERE t1.group# = t2.group#
ORDER BY thread#, group#;
--归档大小
SELECT lpad(to_char(first_time, 'yyyymmdd'), 12) "DATE",
trunc(sum(blocks * block_size) / 1024 / 1024) "SIZE(MB)",
count(*)
FROM v$archived_log
WHERE first_time > sysdate -1
AND creator = 'ARCH'
GROUP BY lpad(to_char(first_time, 'yyyymmdd'), 12)
ORDER BY 1;
---日志切换频率
SELECT SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) Day,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22 ,
SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23,
COUNT(*) TOTAL
FROM v$log_history a
WHERE first_time>=to_char(sysdate-1)
GROUP BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5)
ORDER BY SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'),1,5) DESC;
--日志切换
SELECT thread#,
sequence#,
to_char(first_time, 'MM/DD/RR HH24:MI:SS')
FROM v$log_history
WHERE /*thread# = 1
AND*/ first_time > sysdate - 1
ORDER BY first_time DESC;
--备份情况
SELECT session_key,
start_time,
end_time,
status,
time_taken_display tt
FROM v$rman_backup_job_details
WHERE start_time > sysdate -1
ORDER BY session_key;
select a.recid as recid,
decode(b.incremental_level,
'',
decode(backup_type, 'L', 'Archivelog', 'ControlFile'),
1,
'Incr-1',
0,
'Incr-0',
b.incremental_level) as lv_type,
b.controlfile_included as controlfile_included,
decode(a.status,
'A',
'AVAILABLE',
'D',
'DELETED',
'X',
'EXPIRED',
'ERROR') as status,
a.device_type as device_type,
a.start_time as start_time,
a.completion_time as completion_time,
a.elapsed_seconds as elapsed_seconds,
round(a.bytes / 1024 / 1024 / 1024,3) as bak_size,
a.compressed as compressed,
a.tag as tag,
1 as value
from v$backup_piece a, v$backup_set b
where a.set_stamp = b.set_stamp
and a.deleted = 'NO'
and a.set_count = b.set_count
and a.start_time > sysdate-8
order by a.completion_time desc
--最近三十个会话
SELECT *
FROM (SELECT t.sid,
t.serial#,
trunc(sysdate - logon_time) AS online_time,
t.PROGRAM,
t.status,
t.LOGON_TIME,
t.sql_id,
t.prev_sql_id,
t.event
FROM gv$session t
WHERE t.type <> 'BACKGROUND' AND program is not null
ORDER BY logon_time)
WHERE rownum <= 30;
--资源限制
SELECT *
FROM gv$resource_limit
WHERE trim(limit_value) != 'UNLIMITED';
--等待前20的会话
SELECT t.*, s.sid, s.serial#, s.machine, s.program, s.osuser
FROM (SELECT c.USERNAME,
a.event,
to_char(a.cnt) as seconds,
a.sql_id,
dbms_lob.substr(b.sql_fulltext, 100, 1) sqltext
FROM (SELECT rownum rn, t.*
FROM (SELECT decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu') Event,
s.sql_id,
s.user_id,
count(*) cnt
FROM v$active_session_history s
WHERE sample_time > sysdate - 1
GROUP BY s.user_id,
decode(s.session_state,
'WAITING',
s.event,
'Cpu + Wait For Cpu'),
s.sql_id
ORDER BY cnt DESC) t
WHERE rownum < 20) a,
v$sqlarea b,
dba_users c
WHERE a.sql_id = b.sql_id
AND a.user_id = c.user_id
ORDER BY cnt DESC) t,
v$session s
WHERE t.sql_id = s.sql_id(+);
---执行次数最多的语句
SELECT *
FROM (SELECT sql_id, sql_text, s.executions, s.last_load_time, s.first_load_time, s.disk_reads, s.buffer_gets, s.parse_calls
FROM v$sql s
ORDER BY s.executions DESC)
WHERE rownum <= 10;
--解析最多的语句
SELECT *
FROM (SELECT sql_id, sql_text, s.EXECUTIONS, s.LAST_LOAD_TIME, s.FIRST_LOAD_TIME, s.DISK_READS, s.BUFFER_GETS, s.PARSE_CALLS
FROM v$sql s
ORDER BY s.PARSE_CALLS DESC)
WHERE rownum <= 10;
---容灾语句
SELECT dest_name,
status,
database_mode,
destination
FROM v$archive_dest_status
WHERE dest_id in ('1','2');
SELECT m.thread#,
m.sequence#,
first_change#,
next_change#
FROM v$log_history m,
(SELECT thread#, max(sequence#) as sequence#
FROM v$log_history
GROUP BY thread#) t
WHERE m.thread# = t.thread#
AND m.sequence# = t.sequence#;
SELECT UNIQUE thread# AS thread,
MAX(sequence#) OVER (PARTITION BY thread#) AS last
FROM v$archived_log;
浙公网安备 33010602011771号