常用sql
http://www.sosdb.com/tech/oracle_rac/56bbs2.htm
===========================================================Schema Change:
sqlplus system/oracle123@"192.168.4.75:15021/etmsprod"
select * from dba_tab_privs where owner like 'ETMS%' and grantee not like 'ETMS%';
select * from dba_tab_privs where owner not like 'ETMS%' and grantee like 'ETMS%';
col owner for a10
col referenced_owner for a10
col referenced_name for a20
col referenced_link_name for a20
select owner,type,name,referenced_owner,referenced_type,referenced_name from dba_dependencies where owner like 'ETMS%' order by 1,2,3
select owner,type,name,referenced_owner,referenced_type,referenced_name from dba_dependencies where referenced_owner like 'ETMS%' order by 1,2,3
col ddl format a100
set long 10000 longc 10000
select dbms_metadata.get_ddl('TRIGGER',trigger_name,'ETMSDATA') ||';' "DDL" from dba_triggers
where owner='ETMSDATA';
===========================================================
1. Select sql_text,sql_id from v$sql where sql_text like ‘% EPP_ORDER_CNTR drop%’;
确认当前执行的语句SQLID
2. Select sid,serial#,event,p1,p2 from v$session where sql_id=’xxx’;
正在执行该SQL的session，也就是当前执行drop的session的等待事件状态
1. Select sql_text,sql_id from v$sql where sql_text like ‘% EPP_ORDER_CNTR drop%’;
确认当前执行的语句SQLID
2. Select sid,serial#,event,p1,p2 from v$session where sql_id=’xxx’;
正在执行该SQL的session，也就是当前执行drop的session的等待事件状态
SQL> select sid,username,event,blocking_session,seconds_in_wait, wait_time from v$session where state in ('WAITING') and wait_class != 'Idle';
SQL> select waiting_session,holding_session from dba_waiters;
select sql_text,hash_value from v$sqltext where sql_text like '%EPP_ORDER_CNTR drop column charge_code%' order by piece;
select s.sid,s.serial#,s.sql_hash_value,s.status,w.event,w.p1,w.p2 from v$session s,v$session_wait w where w.sid=s.sid and s.sql_hash_value=&hash_value;
select 'blocker('||wb.holding_session||':'||sb.username||')-sql:'||qb.sql_text blockers,
'waiter ('||wb.waiting_session||':'||sw.username||')-sql:'||qw.sql_text waiters
from dba_waiters wb,
v$session sb,
v$session sw,
v$sqlarea qb,
v$sqlarea qw
where wb.holding_session=sb.sid
and wb.waiting_session=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and wb.mode_held<>'None'
select p.spid pid,
s.sid,
s.SERIAL#,
s.username,
w.event,
w.p1,
w.P1TEXT,
w.p2,
w.P2TEXT,
w.p3,
w.P3TEXT,
sq.SQL_TEXT,
w.WAIT_TIME,
w.SECONDS_IN_WAIT,
w.STATE
from v$session_wait w, v$session s, v$process p, v$sql sq
where event not like 'SQL%' and w.sid = s.sid and s.paddr = p.addr and
s.SQL_ADDRESS = sq.ADDRESS and s.SQL_HASH_VALUE = sq.HASH_VALUE
/死锁检测
sqlplus system/oracle123@\"192.168.2.166:15022/amsprod\"
select object_name,machine,s.sid,s.serial# from v$locked_object k, dba_objects o,v$session s where k.object_id=o.object_id and k.session_id=s.sid;
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
0, 'None',
1, 'NULL',
2, 'ROW-S (SS)',
3, 'ROW-X (SX)',
4, 'SHARE',
5, 'S/ROW-X (SSX)',
6, 'EXCLUSIVE',
TO_CHAR (wk.request)
) mode_requested,
TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
1, 'Blocking', /**//* This lock blocks other processes */
2, 'Global', /**//* This lock is global, so we can't tell */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND wk.id2(+) = hk.id2
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username <> 'SYSTEM')
AND (bs.username <> 'SYS')
ORDER BY 1;
/
SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#,l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID;
/
select username,lockwait,status,machine,program from v$session where sid in (select session_id from v$locked_object);
select sql_text from v$sql where hash_value in (select sql_hash_value from v$session where sid in (select session_id from v$locked_object));
/
mcs慢语句捕捉：
alter session set nls_date_format = 'YYYY-MON-DD HH24:MI:SS';
set pagesize 0
set linesize 1000
col xxx for 99999
select sid, serial#,osuser,machine,terminal,program from v$session
where machine='test\ISD-2D6BG2X';
Annie?Zhang
test\ISD-2D6BG2X
ISD-2D6BG2X
plsqldev.exe
SET LINESIZE 100
SET PAGESIZE 50000
COL USERNAME FOR A10
COL machine FOR A15
COL sql_text FOR A50
select
substr(s.username,1,18) username,
s.sid,s.serial#,s.machine,y.sql_text
from v$session s,v$process p,v$transaction t,v$rollstat r,v$rollname n,v$sql y
where s.paddr = p.addr
and s.taddr = t.addr (+)
and t.xidusn = r.usn (+)
and r.usn = n.usn (+)
and s.username is not null
and s.sql_address=y.address
and s.sid=&sid
order by s.sid,s.serial#,s.username,s.status
mcsuat 15023 192.168.195.55 testmcsu
select S.logon_time,S.sid,S.serial#,S.terminal,S.program,T.piece,T.sql_text
from v$session S,v$sqltext T
where T.address=S.sql_address
and S.machine='testmcsu' and S.username='MCSUSR'
order by S.logon_time,S.sid,T.piece
/
select S.logon_time,S.sid,S.serial#,S.terminal,S.program,T.piece,T.sql_text
from v$session S,v$sqltext T
where T.sql_id=S.sql_id
and machine='test\ISD-2D6BG2X'
order by S.logon_time,S.sid,T.piece
/
---
select S.logon_time,S.sid,S.serial#,S.terminal,S.program,T.piece,T.sql_text
from V$SESSION S,V$SQLTEXT T
where T.sql_id=S.prev_sql_id
and machine='test\ISD-2D6BG2X'
order by S.LOGON_TIME,S.sid,T.piece
/
select S.LOGON_TIME,S.sid,S.SERIAL#,S.TERMINAL,S.program,T.piece,T.SQL_TEXT
from v$session S,v$sqltext T
where T.ADDRESS=S.PREV_SQL_ADDR
and S.machine='testmcsu' and S.username='MCSUSR'
order by S.LOGON_TIME,S.sid,T.piece
/
-----格式调整
SQL> alter session set nls_date_format = 'YYYY-MON-DD HH24:MI:SS';
SQL> set pagesize 0
SQL> set linesize 1000
select S.logon_time,S.sid,S.serial#,T.piece,T.sql_text
from v$session S,v$sqltext T
where T.address=S.sql_address
and S.machine='testmcsu' and S.username='MCSUSR' and S.logon_time > to_date('2015-JUL-27 13:40:00','YYYY-MON-DD HH24:MI:SS')
order by S.logon_time,S.sid,T.piece
/
select S.logon_time,S.sid,S.serial#,T.piece,T.sql_text
from v$session S,v$sqltext T
where T.sql_id=S.prev_sql_id
and S.machine='testmcsu' and S.username='MCSUSR' and S.logon_time > to_date('2015-JUL-27 13:40:00','YYYY-MON-DD HH24:MI:SS')
order by S.logon_time,S.sid,T.piece
/历史执行语句
select S.session_id,S.program,S.machine,S.sample_time,T.last_active_time,T.sql_text
from v$active_session_history S, v$sqlstats T
where T.sql_id = S.sql_id
and upper(T.sql_text) like upper('%INSERT%UNITCONFIG%')
/
---典型格式
select
'create or replace synonym '|| o.object_name ||' for &v_syn_owner..'||o.object_name||';'
from all_objects o
where o.owner = upper('&v_syn_owner')
and user != upper('&v_syn_owner')
and o.object_type in ('TABLE', 'VIEW', 'SEQUENCE', 'PACKAGE','PROCEDURE','FUNCTION')
and not exists
(select null
from user_synonyms s
where s.table_owner = o.owner
and s.table_name = o.object_name)
order by o.object_name
/
select TO_CHAR(CREATED,'YYYY-MON-DD') time, object_type, COUNT(*) from DBA_OBJECTS
where OWNER='MVO'
group by TO_CHAR(CREATED,'YYYY-MON-DD'), OBJECT_TYPE
order by TO_CHAR(CREATED,'YYYY-MON-DD') desc
/
select * from table(dbms_xplan.display_cursor('3gbpb4x7s4r4n',0,'runstats_last'));
select * from dba_tables where table_name='TASK_HANDLING_RESEARCH';
