常用sql

Oracle数据库管理与优化技巧

最新推荐文章于 2025-07-19 15:12:22 发布

weixin_33754065

最新推荐文章于 2025-07-19 15:12:22 发布

阅读量96

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/feiyun8616/p/6281381.html

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
/