oracle常用运维sql语句

fteair

查询asm磁盘组空间使用率

set feedback off
set linesize 120
col grp_name for a10
col grp_num for a10
col used_rate for a10

select 'grp'||group_number grp_num,name grp_name,type,round(total_mb/1024,2) total_gb,round(free_mb/1024,2) free_gb,round(REQUIRED_MIRROR_FREE_MB/1024,2) req_mir_free_gb,round(USABLE_FILE_MB/1024,2) usable_gb,round((total_mb-free_mb)/total_mb*100,2)||'%' used_rate from v$asm_diskgroup_stat;

fteair

查询DBA角色包含哪些权限

col grantee for a10
col privilege for a40
set pagesize 100
select * from dba_sys_privs where grantee='DBA';

fteair

查询隐含参数

select x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf, 7),
1,
'MODIFIED',
4,
'SYSTEM_MOD',
'FALSE') ismod,
decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
from sys.x$ksppi x, sys.x$ksppcv y
where x.inst_id = userenv('Instance')
and y.inst_id = userenv('Instance')
and x.indx = y.indx
and x.ksppinm like '%_optimizer_cbqt_no_size_restriction%'
order by translate(x.ksppinm, ' _', ' ');

caixuqad

fteair

查询latch情况

col name for a30
col gets for 9999999999999999
col misses for 9999999999999999
col immediate_gets for 999999999999999

select * from (select name,gets,misses,immediate_gets,immediate_misses,sleeps from v$latch order by misses desc) where rownum<10;

fteair

查询ash

select sql_id,event,count(*) from v$active_session_history where sql_id is not null and event is not null and sample_time between to_date('20161125150000','yyyy-mm-dd hh24:mi:ss') and to_date('20161125160000','yyyy-mm-dd hh24:mi:ss') group by sql_id,event order by count(*) desc;

fteair

查询大事务以及对应的sql

col xid for a20
col schemaname for a20
col sql_text for a40
set linesize 150
set pagesize 100

select s.sid,s.serial#,s.status,t.start_time, t.xidusn||'.'||t.xidslot||'.'||t.xidsqn xid,
s.username,decode(s.sql_id,null,s.prev_sql_id,s.sql_id) sqlid
from v$transaction t, v$session s
where s.saddr = t.ses_addr
order by t.start_time;



select s.sid,s.serial#,s.status,t.start_time, t.xidusn||'.'||t.xidslot||'.'||t.xidsqn xid,
s.username,decode(s.sql_id,null,s.prev_sql_id,s.sql_id) sqlid,l.sql_text
from v$transaction t, v$session s,v$sql l
where s.saddr = t.ses_addr and l.sql_id=s.sql_id
order by t.start_time;

fteair

查询pga和uga使用量

select a.name,b.value from v$statname a,v$sesstat b where a.statistic#=b.statistic# and b.sid=(select sid from v$mystat where rownum=1) and (a.name like '%ga %' or a.name like '%direct temp%')
union all
select 'total: '||a.name,sum(b.value) from v$statname a,v$sesstat b,v$session c where a.statistic#=b.statistic# and (a.name like '%ga %' or a.name like '%direct temp%') and b.sid=c.sid and c.username is not null group by 'total: '||a.name;

张兴康

fteair 发表于 2017-6-27 15:44
闪回查询

SELECT * FROM  

caixuqad