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
转载请注明原文地址: https://ju.6miu.com/read-968930.html
