Oracle OCP笔记(33)DBA查看系统常用脚本
-- Parameter
SELECT name,value,description,isdefault,isses_modifiable,issys_modifiable FROM v$parameter; -- 可以在Session级修改的参数(alter session)
SELECT name,value,description,isdefault,isses_modifiable,issys_modifiable FROM v$parameter2; -- 可以在Session级修改的参数(alter session) - LIST的值分开, 一行变多行数据
SELECT name,value,description,isdefault,isses_modifiable,issys_modifiable FROM v$system_parameter; -- 实例级的参数(alter system)
SELECT name,value,description,isdefault,isses_modifiable,issys_modifiable FROM v$system_parameter2; -- 实例级的参数(alter system) - LIST的值分开, 一行变多行数据
SELECT name,value FROM v$spparameter; -- 保存在spfile中的参数值(scope=both或者spfile).
-- Parameters
SELECT substr(name, 0, 512) name,
DECODE(type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Filename', ' ') type,
substr(value, 0, 512) value,
ISDEFAULT,
ISSYS_MODIFIABLE,
substr(description,0,512) description
FROM v$parameter
ORDER BY name;
-- Spfile Parameters
SELECT UNIQUE sp.name, sp.sid,
DECODE(p.type, 1, 'Boolean', 2, 'String', 3, 'Integer', 4, 'Filename', ' ') type,
sp.value, p.issys_modifiable, p.description
FROM v$spparameter sp, v$parameter p
WHERE sp.name = p.name ORDER BY sp.name, sp.sid;
-- Spfile file
SELECT name,value FROM v$parameter WHERE name = 'spfile';
SELECT * FROM v$instance; -- 实例信息
SELECT name,log_mode,open_mode from v$database; -- 数据库信息
SELECT banner FROM v$version WHERE BANNER LIKE '%Oracle%'; -- 版本信息
SELECT value FROM v$parameter WHERE name = 'db_block_size'; -- db block size
SELECT * FROM v$archive_dest ORDER BY dest_id; -- 归档日志位置
SELECT service_id, name, network_name FROM v$services; -- 服务名
-- Lock: 查看锁定的会话和锁定对象(阻塞的会话)
select s.sid,s.serial#,s.lockwait,s.status,s.schemaname,s.machine,s.terminal,s.osuser,s.program,s.module,s.action,s.logon_time,
l.oracle_username,l.os_user_name,l.locked_mode,o.owner,o.object_name,o.object_type,o.status,
'alter system kill session '||''''|| s.sid||','||s.serial#||''''||';' kill_session_sql
from v$session s,
v$locked_object l,
dba_objects o
where s.sid = l.session_id
and l.object_id = o.object_id
and s.sid in (select holding_session from dba_waiters);
-- Option,数据库组件
SELECT *
FROM v$option
WHERE value='TRUE'
and (parameter like 'OLAP' or parameter like 'Oracle Data Mining'
or parameter like 'Oracle Label Security' or parameter like 'Partitioning'
or parameter like 'Real Application Clusters' or parameter like 'Spatial');
-- SGA & PGA
SELECT name, value FROM v$sga;
SELECT name, value FROM v$pgastat;
-- SGA share pool advice
SELECT name,value FROM v$parameter WHERE name = 'shared_pool_size'; -- SGA自动管理shared_pool_size=0
select name,value from v$parameter where name = 'sga_target'; -- SGA自动管理需设置参数sga_target
SELECT value FROM v$parameter WHERE name = 'statistics_level';
SELECT shared_pool_size_for_estimate, shared_pool_size_factor, estd_lc_time_saved, estd_lc_time_saved_factor
FROM v$shared_pool_advice
ORDER BY shared_pool_size_for_estimate;
-- SGA db cache, 数据缓冲区命中率
SELECT 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio"
FROM v$sysstat physical,
v$sysstat direct,
v$sysstat lobs,
v$sysstat logical
WHERE physical.name = 'physical reads'
AND direct.name='physical reads direct'
AND lobs.name='physical reads direct (lob)'
AND logical.name='session logical reads';
-- SGA db cache advice
SELECT name, value FROM v$parameter WHERE name = 'db_block_size'; -- db block size
SELECT name, value FROM v$parameter WHERE name like 'db%cache_size'; -- SGA自动管理db%cache_size=0
select name,value from v$parameter where name = 'sga_target'; -- SGA自动管理需设置参数sga_target
SELECT value FROM v$parameter WHERE name = 'db_block_buffers';
SELECT value FROM v$parameter WHERE name = 'db_cache_advice'; -- advice
SELECT value FROM v$parameter WHERE name = 'statistics_level';
SELECT name, block_size, size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
FROM v$db_cache_advice
WHERE block_size = 8192;
-- SGA advice
select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
-- PGA advice
SELECT value FROM v$parameter WHERE name = 'statistics_level';
SELECT round(pga_target_for_estimate/1024/1024) pga_target_for_estimate_mb, estd_pga_cache_hit_percentage, estd_overalloc_count, pga_target_factor
FROM v$pga_target_advice ORDER BY 1;
-- Auto memory advice(SGA + PGA)
select memory_size,memory_size_factor,estd_db_time from v$memory_target_advice;
-- MTTR advice (平均恢复时间)
SELECT estimated_mttr FROM v$instance_recovery;
SELECT value FROM v$parameter WHERE name = 'statistics_level';
SELECT mttr_target_for_estimate, dirty_limit, estd_cache_writes, estd_cache_write_factor,
estd_total_writes, estd_total_write_factor, estd_total_ios, estd_total_io_factor
FROM v$mttr_target_advice
ORDER BY mttr_target_for_estimate;
-- Undo tablespace (撤消表空间)
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO' ORDER BY 1;
SELECT * FROM sys.dba_data_files WHERE tablespace_name = 'UNDOTBS1';
SELECT max(maxconcurrency), max(undoblks), avg(undoblks) FROM v$undostat;
-- Session
select case when r.session_id is not NULL and r.status = 'SUSPENDED' then 'Suspended'
when max(decode(s.serial#,l.serial#,1,0)) = 1 then 'Long Operations'
else 'Regular'
end operation,
s.sid,s.status, s.username, RTRIM(s.osuser), b.spid,
s.machine, s.program, s.resource_consumer_group, r.timeout
from v$session s, v$session_longops l, v$process b, dba_resumable r
where (s.sid = l.sid(+))
and (s.sid = r.session_id(+))
and s.paddr=b.addr
group by r.session_id, r.error_number, r.status, s.sid,s.status, s.username,
s.osuser, b.spid, s.machine, s.program,s.resource_consumer_group, r.timeout;
-- Resource Group
SELECT DISTINCT consumer_group "Consumer Group", mandatory "Mandatory", comments "Description" FROM
sys.dba_rsrc_consumer_groups ORDER BY 1
-- Resource Plan
SELECT plan "Plan", mandatory "Mandatory", comments "Description" FROM sys.dba_rsrc_plans ORDER BY 1
-- Tablespace
SELECT tablespace_name, status, contents, logging, extent_management, block_size,segment_space_management--, bigfile
FROM sys.dba_tablespaces ORDER BY 1;
-- Tablespace used information
SELECT d.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),2) "Used (%)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) 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.status "Status",
d.tablespace_name "Name",
d.contents "Type",
d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') "Used (M)",
ROUND(NVL(t.bytes / a.bytes * 100, 0),2) "Used (%)"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) 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 2;
-- Tablespace datafile
SELECT /*+ use_hash(d v) */ d.tablespace_name, d.file_name, v.status,
TO_CHAR((d.bytes / 1024 / 1024), '99999990.000') "Size (M)",
NVL(TO_CHAR(((d.bytes - s.bytes) / 1024 / 1024), '99999990.000'),
TO_CHAR((d.bytes / 1024 / 1024), '99999990.000')) "Used (M)",
d.file_id, d.autoextensible, d.increment_by, d.maxblocks
FROM sys.dba_data_files d,
v$datafile v,
(SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id) s
WHERE (s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
ORDER BY d.tablespace_name, d.file_name;
-- Tablespace extents
SELECT tablespace_name,(initial_extent / 1024) initial_extent_kb, (next_extent / 1024) next_extent_kb, min_extents, max_extents, pct_increase,
(min_extlen / 1024) min_extlen_kb, extent_management, allocation_type
FROM sys.dba_tablespaces
ORDER BY tablespace_name;
-- Property: Character set, Default_temp_tablepsace, ...
SELECT * FROM sys.props$;
SELECT name,value$ FROM sys.props$ WHERE name = 'DEFAULT_TEMP_TABLESPACE';
-- Datafile used
SELECT /*+ ordered no_merge(v) */ v.status "Status", d.file_name "Name", d.tablespace_name "Tablespace",
TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99999990.000') "Size (M)",
TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0))/1024/1024, 0),'99999999.999') || '/' ||
TO_CHAR(NVL(d.bytes/1024/1024, 0),'99999999.999') || '/' || NVL(d.autoextensible, 'NO') "Used",
TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / d.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_data_files d,
v$datafile v,
(SELECT file_id, SUM(bytes) bytes FROM sys.dba_free_space GROUP BY file_id) s
WHERE (s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
UNION ALL
SELECT /*+ ordered no_merge(v) */ v.status "Status", d.file_name "Name", d.tablespace_name "Tablespace",
TO_CHAR(NVL(d.bytes / 1024 / 1024, 0), '99999990.000') "Size (M)",
TO_CHAR(NVL(t.bytes_cached/1024/1024, 0),'99999999.999') || '/' ||
TO_CHAR(NVL(d.bytes/1024/1024, 0),'99999999.999') || '/' || NVL(d.autoextensible, 'NO') "Used",
TO_CHAR(NVL(t.bytes_cached / d.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_temp_files d,
v$temp_extent_pool t,
v$tempfile v
WHERE (t.file_id (+)= d.file_id)
AND (d.file_id = v.file#);
-- Rollback segment (普通表空间9i开始已不建议使用Rollback segment,仅用于SYSTEM)
SELECT r.status "Status", r.segment_name "Name", r.tablespace_name "Tablespace", s.extents "Extents",
TO_CHAR((s.bytes / 1024 / 1024), '99999990.000') "Size" ,segment_type
FROM sys.dba_rollback_segs r, sys.dba_segments s
WHERE r.segment_name = s.segment_name
AND s.segment_type = 'ROLLBACK';
-- Log group
SELECT NLS_INITCAP(status) "Status", group# "Group", members "# of Members", NLS_INITCAP(archived) "Archived",
(bytes / 1024) "Size (K)", sequence# "Sequence" , first_change# "First Change#"
FROM v$log
ORDER BY group#;
-- Log file
SELECT group#, status, type, member, is_recovery_dest_file,con_id FROM v$logfile ORDER BY group#;
-- Archived log
SELECT name "Name",sequence# "Sequence", thread# "Thread", backup_count "Backup Count",
first_change# "First Change#", first_time "First Time", completion_time "Archival Time", deleted
FROM v$archived_log
WHERE deleted='NO'
ORDER BY sequence#;
-- PL/SQL程序源代码
select text from dba_source where owner = <'owner_name'> and name = <'object_name'> order by line