• 常用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='testISD-2D6BG2X';
    Annie?Zhang
    testISD-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='testISD-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='testISD-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';

  • 相关阅读:
    【Python】列表推导式
    【产品吐槽】阿里云堡垒机,没有那么优秀
    python之路第五天
    python之路第四天
    python之路第三天
    python之路第二天
    python之路第一天
    Flink中Periodic水印和Punctuated水印实现原理(源码分析)
    Flink的Job启动JobManager端(源码分析)
    Flink的Job启动Driver端(源码分析)
  • 原文地址:https://www.cnblogs.com/feiyun8616/p/6281381.html
Copyright © 2020-2023  润新知