--查看最近做过ddl操作的表
select *
from (select owner, object_name, OBJECT_TYPE, last_ddl_time
from dba_objects
where object_type in( 'TABLE','PROCEDURE','SYNONYM','INDEX','SEQUENCE','PACKAGE','PACKAGE BODY','VIEW','MATERIALIZED VIEW')
ORDER BY LAST_DDL_TIME DESC)
where rownum < 100;
--select * from dba_dependencies where name in('P_SB_GETSBQX_GGGZ','P_SB_CALC_TAX','P_SB_GETSBQX_GGGZ','P_SB_CALC_TAX')
---查看wait事件
select a.INST_ID, a.sid,b.spid , a.status, a.event, a.program, a.LOGON_TIME, a.blocking_instance blocl_inst, a.blocking_session block_sess
,a.osuser, a.machine, a.username, a.serial#, a.wait_class, a.sql_id, a.sql_trace, a.sql_trace_waits, 'alter system kill session '||''''||a.sid|| ','||a.serial#||'''' || ';'
from gv$session a, gv$process b
where a.paddr = b.addr and a.INST_ID=b.INST_ID and a.event not like 'SQL%' and a.wait_class <> 'Idle'
order by a.INST_ID,a.event ;
---查看活动会话执行时间
select A.USERNAME,a.SID,a.STATUS,a.SQL_ID, a.MACHINE, a.PROGRAM, b.EXECUTIONS,a.logon_time,a.LAST_CALL_ET,b.SQL_TEXT,b.SQL_FULLTEXT,a.event, 'alter system kill session '||''''||a.sid|| ','||a.serial#||'''' || ';'
from v$session a,v$sqlarea b
where a.SQL_ID=b.SQL_ID
and a.STATUS='ACTIVE'
and b.EXECUTIONS >=0
order by a.LAST_CALL_ET desc;
---失效索引
select t.owner,t.index_name,t.status,t.table_owner,t.table_name from dba_indexes t where t.status<>'VALID' and t.status<>'N/A';
---查看正在运行的job
select a.*,b.interval,b.WHAT from dba_jobs_running a ,dba_jobs b where a.JOB = b.JOB;
--查看系统会话总数
select a.total_1,b.inst_1,c.total_2,d.inst_2 from
(select count(*)total_1 from gv$session where inst_id='1') a,(select count(*) inst_1 from gv$session where inst_id='1'and status='ACTIVE')b,
(select count(*)total_2 from gv$session where inst_id='2') c,(select count(*) inst_2 from gv$session where inst_id='2'and status='ACTIVE')d;
---查看锁
SELECT DECODE(request,0,'Holder: ','Waiter: ')||inst_id , sid ,
id1, id2, lmode, request, type
FROM GV$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY id1, request;
---通过系统pid查找sql
select b.USERNAME,b.SID,b.SQL_ID,b.MACHINE,c.SQL_TEXT,c.SQL_FULLTEXT from v$process a,v$session b,v$sqlarea c
where a.ADDR=b.PADDR
and b.SQL_ID=c.SQL_ID
and a.SPID=660010
--查看当前会话的回滚数据
select used_ublk,a.USED_UREC from v$transaction a
where a.ADDR=(select taddr from v$session where sid=2239)
---查看表空间使用情况
SELECT D.TABLESPACE_NAME "表空间名",
t.extent_management,
t.segment_space_management,
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,
2),
'990.99') "使用比"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D,dba_tablespaces t
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and D.TABLESPACE_NAME =t.tablespace_name
ORDER BY 6 DESC;
--查看sid 查看sql
select s.USERNAME,s.SID||','||s.SERIAL#,'kill -9 '||p.SPID,s.STATUS,s.SQL_ID,s.STATE,s.EVENT,s.WAIT_CLASS,s.PROGRAM,s.MACHINE,s.SECONDS_IN_WAIT,s.LAST_CALL_ET,sq.SQL_TEXT ,sq.SQL_FULLTEXT,s.USERNAME from v$session s,v$sqlarea sq ,v$process p
where s.PADDR = p.ADDR and s.SQL_ID = sq.SQL_ID(+)
and s.SID = 1773 ;
--根据spid查看sql语句
select s.USERNAME ,s.SID,s.SERIAL#,'ps -ef|grep '||p.SPID,'kill -9 '||p.SPID,s.SQL_ID,s.STATUS,s.STATE,s.EVENT,s.WAIT_CLASS,s.PROGRAM,s.MACHINE,s.SECONDS_IN_WAIT,s.LAST_CALL_ET,sq.SQL_TEXT ,sq.SQL_FULLTEXT
from v$session s,v$sqlarea sq ,v$process p
where s.PADDR = p.ADDR and s.SQL_ID = sq.SQL_ID(+)
and p.spid = '2544592';
--杀job
select --'alter system kill session ' || '''' || b.SID || ',' || b.SERIAL# ||''';' sid,
'kill -9 ' || p.SPID ,b.STATUS
from v$session b, v$process p
where b.SID in (select t.SID From dba_jobs_running t)
and b.PADDR = p.ADDR;
--查看数据库中正在运行的存储过程
select * from v$db_object_cache where type='PROCEDURE'
----单节点锁等待 --请求
SELECT L.SESS,
S.STATUS,
S.SID || ',' || S.SERIAL# SID,
S.STATE,
S.EVENT,
S.WAIT_CLASS,
L.LMODE,
L.REQUEST,
L.TYPE,
S.STATUS,
P.SPID,
SQ.SQL_ID,
SQ.SQL_TEXT,
SYSDATE,
LAST_CALL_ET,
S.WAIT_TIME,
S.SECONDS_IN_WAIT,
S.USERNAME
FROM V$SESSION S,
V$SQLAREA SQ,
V$PROCESS P,
(SELECT DECODE(REQUEST, 0, 'Holder', 'Waiter') SESS,
SID,
ID1,
ID2,
LMODE,
REQUEST,
TYPE
FROM V$LOCK
WHERE (ID1, ID2, TYPE) IN
(SELECT ID1, ID2, TYPE FROM V$LOCK WHERE REQUEST > 0)) L
WHERE S.PADDR = P.ADDR
AND S.SQL_ID = SQ.SQL_ID(+)
AND L.SID = S.SID
ORDER BY L.ID1, L.REQUEST;
--索引选择性
select index_name ,last_analyzed,distinct_keys/num_rows from user_indexes where num_rows <>0;
select index_name,last_analyzed ,decode(num_rows,0,0,distinct_keys/num_rows) from user_indexes;
select * from user_indexes;
--查看最近做过ddl操作的表
select *
from (select owner, object_name, OBJECT_TYPE, last_ddl_time
from dba_objects
where object_type in( 'TABLE','PROCEDURE')
--and object_name='T_DA_NSRCWBBZB'
ORDER BY LAST_DDL_TIME DESC)
where rownum < 400;
--15 表示alter table ，25 表示alter procedure，9表示创建索引，17表示grant object，
select *
from v$active_session_history s
where sql_opcode in(15,17,9,25,33,43,60,79)
and SAMPLE_TIME > to_date('2013-11-21 14:00:00','yyyy-mm-dd hh24:mi:ss') and SAMPLE_TIME <to_date('2013-11-21 15:30:00','yyyy-mm-dd hh24:mi:ss')
select * from v$sql_plan a where a.PLAN_HASH_VALUE=4029203395
select * from dba_users where user_id=597;
select * from v$session where command=1
---对象依赖关系
select * from dba_dependencies where name='P_SB_GETSBQX_GGGZ'
--查看用户所拥有的权限
select * from dba_sys_privs where grantee='DB_USER_YD'
select * from dba_tab_privs where grantee='DB_USER_YD'
---查看执行时间长的sql
Select b.USERNAME,
b.SID,
a.SQL_ID,
a.SQL_TEXT,
a.sql_fulltext,
b.EVENT,
a.executions,
trunc(((a.cpu_time / a.executions) / 1000000)) c_time,
trunc(((a.ELAPSED_TIME / a.executions) / 1000000)) e_time,
trunc(cpu_time/1000000) cpu_time,
trunc(a.ELAPSED_TIME/1000000) ELAPSED_TIME ,
a.DISK_READS,
a.BUFFER_GETS,
b.MACHINE,
b.PROGRAM
From v$sqlarea a, v$session b
Where executions > 0
--And b.status = 'ACTIVE'
and a.SQL_ID = b.SQL_ID
-- and trunc(((a.cpu_time / a.executions) / 1000000))>5
Order By c_time desc,
a.BUFFER_GETS Desc,
a.EXECUTIONS desc,
a.sql_id;
---查看索引度
select owner,index_name,table_name,degree from dba_indexes where degree >'1';
select a.owner, a.index_name, a.table_name, a.degree, b.created,
'alter index ' || a.owner||'.'||a.index_name ||' noparallel;'
from dba_indexes a, dba_objects b
where a.index_name = b.OBJECT_NAME
and b.OBJECT_TYPE = 'INDEX'
and a.owner not in ('SYS','SYSTEM','SYSMAN')
AND A.DEGREE > '1'
order by a.degree desc
--回滚事务和undo段
select * from v$fast_start_transactions where state='RECOVERING'
select * from v$transaction;
select * from v$transaction WHERE XID IN (select XID from v$fast_start_transactions where state='RECOVERING')
--查看回滚段使用
select a.name, b.xacts, c.sid, c.serial#, d.sql_text
from v$rollname a,
v$rollstat b,
v$session c,
v$sqltext d,
v$transaction e
where a.usn = b.usn
and b.usn = e.xidusn
and c.taddr = e.addr
and c.sql_address = d.address
and c.sql_hash_value = d.hash_value
order by a.name, c.sid, d.piece;
select * from v$session where taddr in (select addr from v$transaction where status='')
select tablespace_name,segment_name,status from dba_rollback_segs;
---利用sqlsid查找绑定变量的值
select dbms_sqltune.extract_binds(bind_data)
from v$sql
where sql_id = 'far2q5b3d3hsh';
--内存中绑定变量
SELECT T.ADDRESS,
T.HASH_VALUE,
T.SQL_ID,
---T.CHILD_ADDRESS,
---- T.CHILD_NUMBER,
T.NAME,
T.POSITION,
T.LAST_CAPTURED,
T.VALUE_STRING,
T.DATATYPE_STRING,
T.CHARACTER_SID,
T.WAS_CAPTURED
FROM V$SQL_BIND_CAPTURE T
WHERE SQL_ID = '399yn889mxyd7';
--awr中绑定变量
SELECT T.SNAP_ID,
T.NAME,
T.POSITION,
T.VALUE_STRING,
T.LAST_CAPTURED,
T.WAS_CAPTURED
FROM DBA_HIST_SQLBIND T
WHERE SQL_ID = '6bx3ujngd6zkr'
and last_captured >to_date('2013-11-04 00:00:00','yyyy-mm-dd hh24:mi:ss')
and last_captured <to_date('2013-11-04 10:00:00','yyyy-mm-dd hh24:mi:ss')
---查询超过30万行的全表扫描查询/全分区
with t as
(select dt.owner || '.' || dt.table_name tablename, dt.num_rows, sp.SQL_ID
from v$sql_plan sp, dba_tables dt
where
sp.OPTIONS = 'FULL'
and sp.OPERATION = 'TABLE ACCESS'
and dt.owner = sp.OBJECT_OWNER
and dt.table_name = sp.OBJECT_NAME
and dt.num_rows > 300000),
t1 as
(select du.username, t.tablename, t.num_rows, t.sql_id
from v$active_session_history ash, dba_users du, t
where t.SQL_ID = ash.SQL_ID
and ash.USER_ID = du.user_id
and du.username not in ('SYS', 'SYSTEM')
union
select du.username, t.tablename, t.num_rows, t.sql_id
from dba_hist_active_sess_history dh, dba_users du, t
where t.SQL_ID = dh.SQL_ID
and dh.user_id = du.user_id
and du.username not in ('SYS', 'SYSTEM'))
select t1.username,t1.tablename, t1.num_rows, t1.sql_id, sq.SQL_TEXT
from t1, v$sqlarea sq
where t1.sql_id = sq.SQL_ID
order by t1.username,t1.num_rows desc
select sql_id from v$sql_plan where OPTIONS = 'ALL' and OPERATION = 'PARTITION RANGE' and rownum <100;
select * from v$sqlarea where sql_id in(select sql_id from v$sql_plan where OPTIONS = 'ALL' and OPERATION = 'PARTITION RANGE' and rownum <100);
---未使用绑定变量的sql语句
with force_matches as
(select force_matching_signature, --表明在force模式下，游标共享能够使用的语句（当前非共享）
count(*) cnt,
max(sql_id || child_number) max_sql_child,
dense_rank() over(order by count(*) desc) ranking
from v$sql
where force_matching_signature <> 0
and parsing_schema_name not in ('SYS','SYSTEM')
group by force_matching_signature
having count(*) > 100)
select parsing_schema_name schema, cnt, sql_id, sql_text
from v$sql
join force_matches
on (sql_id || child_number = max_sql_child)
--where ranking <= 20
order by 1, cnt desc;
---查看谁占用了undo表空间
select r.name 回滚段名,
rssize/1024/1024/1024 "size(g)",
s.sid,
s.serial#,
s.status,
s.sql_hash_value,
s.sql_address,
s.machine,
s.module,
substr(s.program,1,78) 操作程序,
r.usn,
hwmsize/1024/1024/1024,
shrinks,
xacts
from sys.v_$session s,
sys.v_$transaction t,
sys.v_$rollname r,
v$rollstat rs
where t.addr=s.taddr
and t.xidusn=r.usn
order by rssize desc;
---查看谁占用了temp表空间
select t.blocks*16 /1024/1024,
s.username,
s.schemaname,
t.tablespace,
t.segtype,
t.extents,
s.program,
s.osuser,
s.terminal,
s.sid,
s.serial#,
sql.sql_text
from v$sort_usage t,v$session s,v$sql sql
where t.session_addr=s.saddr
and t.sqladdr=sql.address
and t.sqlhash=sql.hash_value;
---PGA占用最多的进程
select p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
p.pga_alloc_mem,
s.username,
s.osuser,
s.program
from v$process p,v$session s
where s.paddr(+)=p.addr
and rownum <=10
order by p.pga_alloc_mem desc;
---登录时间最长的session
SELECT *
FROM (SELECT T.SID,
T2.SPID,
T.PROGRAM,
T.STATUS,
T.SQL_ID,
T.PREV_SQL_ID,
T.EVENT,
T.LOGON_TIME,
TRUNC(SYSDATE - LOGON_TIME)
FROM V$SESSION T, V$PROCESS T2
WHERE T.PADDR = T2.ADDR
AND T.TYPE <> 'BACKGROUND'
ORDER BY LOGON_TIME)
WHERE ROWNUM <= 20;
---逻辑最多的sql
select * from
(select sql_id,
sql_text,
s.executions,
s.last_load_time,
s.first_load_time,
s.disk_reads,
s.buffer_gets
from v$sql s
where s.buffer_gets >300
order by buffer_gets desc
)
where rownum <=20;
---物理读最多的sql
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
where s.disk_reads >300
order by disk_reads desc
)
where rownum <=20;
---执行次数最多的sql
select * from
(select b.USERNAME,
s.sql_id,
s.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,v$session b
where s.SQL_ID=b.SQL_ID
order by s.executions desc
)
where rownum <=200;
---检查是否有显著未释放高水平位的表
select table_name,blocks,num_rows
from dba_tables
where blocks/num_rows >=0.2 ---表示至少一个块要装5行数据，除非有long和clob或者varchar2(4000)
and num_rows is not null
and num_rows <>0
and blocks >=10000;
---检查CACHE小于20的序列
select t.sequence_owner,
t.sequence_name,
t.cache_size,
'alter sequence '||t.sequence_owner||'.'||t.sequence_name||'cache 1000;'
from dba_sequences t
where sequence_owner in('DB_SBZS')
and cache_size <=20;
select * from dba_sequences where sequence_owner='DB_SBZS' and rownum <100;
SELECT COUNT(*) FROM DBA_SEQUENCES WHERE SEQUENCE_OWNER NOT IN('SYS','SYSTEM')
Select db_sbzs.sq_zs_globalbusinessid.Nextval id From dual