比较经典的SQL、PLSQL收集

来源：互联网发布：地图数据采集编辑：程序博客网时间：2024/06/11 09:43

--PL/SQL显示哪个用户(会话SID以及serial)通过什么工具执行了什么操作

declare    x number;begin    for x in    ( select username||'('||sid||','||serial#||                ') ospid = ' ||  process ||                ' program = ' || program username,             to_char(LOGON_TIME,' Day HH24:MI') logon_time,             to_char(sysdate,' Day HH24:MI') current_time,             sql_address, LAST_CALL_ET        from v$session       where status = 'ACTIVE'         and rawtohex(sql_address) <> '00'         and username is not null order by last_call_et )    loop        for y in ( select max(decode(piece,0,sql_text,null)) ||                          max(decode(piece,1,sql_text,null)) ||                          max(decode(piece,2,sql_text,null)) ||                          max(decode(piece,3,sql_text,null))                               sql_text                     from v$sqltext_with_newlines                    where address = x.sql_address                      and piece < 4)        loop            if ( y.sql_text not like '%listener.get_cmd%' and                 y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')            then                dbms_output.put_line( '--------------------' );                dbms_output.put_line( x.username );                dbms_output.put_line( x.logon_time || ' ' ||                                      x.current_time||                                      ' last et = ' ||                                      x.LAST_CALL_ET);                dbms_output.put_line(                          substr( y.sql_text, 1, 250 ) );            end if;        end loop;    end loop;end;column username format a15 word_wrappedcolumn module format a15 word_wrappedcolumn action format a15 word_wrappedcolumn client_info format a30 word_wrappedselect username||'('||sid||','||serial#||')' username,       module,       action,       client_infofrom v$sessionwhere module||action||client_info is not null;

--取得两个字符串从第几个字符开始不同。。。

select min(level)   from dual  where nvl(substr('字串1',level,1),' ')<>nvl(substr('字串2',level,1),' ') connect by level<=200;

--求两个日期相差多少天、多少小时、多少分、多少秒

SELECT EXTRACT(DAY               FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -                    TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))               DAY(9) TO SECOND) || '天'||       EXTRACT(HOUR               FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -                    TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))               DAY(9) TO SECOND) || '时'||       EXTRACT(MINUTE               FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -                    TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))               DAY(9) TO SECOND) || '分钟'||       EXTRACT(SECOND               FROM(TO_DATE('2012-12-31 12:11:12', 'YYYY-MM-DD HH24:MI:SS') -                    TO_DATE('2012-12-11 11:11:12', 'YYYY-MM-DD HH24:MI:SS'))               DAY(9) TO SECOND) || '秒'  FROM DUAL

--求指定月内哪天在日历表是对应哪周.

如:2013.04.01----2013.04.06为第一周,依次类推.

如果直接使用TO_CHAR(SYSDATE,'W')得到的周是按每个月的前7天为第一周的,这样和日历表上的有点出入.

select to_date('2013-04-01', 'yyyy-MM-dd') + level - 1,       CEIL((to_char(to_date('2013-04-01', 'yyyy-MM-dd'), 'd') + LEVEL - 1) / 7) 周  from dualconnect by level <= 30;

--表T11有A,B两个字段,A字段为空,B字段是26个字母无序排列--要求更新A列,使得B='A'时,A=1;B='B'时,A=2;B='C'时,A=3...UPDATE t11 c   SET a =       (select rn          from (select rank() over(order by b) rn, b from t11) d         where d.b = c.b);

--SQL完成字符串拆分，拆分逗号分隔的字符串

12:08:39 SYS@orcl> SELECT substr(col12:08:43   2               ,DECODE(LEVEL, 1, 1, instr(col, ',', 1, LEVEL - 1) + 1)12:08:43   3               ,DECODE(instr(col, ',', 1, LEVEL)12:08:43   4                      ,012:08:43   5                      ,LENGTH(COL) + 112:08:43   6                      ,instr(col, ',', 1, LEVEL)) -12:08:43   7                DECODE(LEVEL, 1, 1, instr(col, ',', 1, LEVEL - 1) + 1)) col12:08:43   8    FROM (SELECT '&a' col FROM dual)12:08:43   9  CONNECT BY LEVEL <= length(translate(col, ',' || col, ',')) + 1;输入 a 的值:  aa,bb,cc,dd,ee,ffCOL----------------------------------aabbccddeeff已选择6行。已用时间:  00: 00: 00.00

随机取表中第N条记录

select * from 表 sample(10) where rownum = 1;