经典SQL语句收集（ORACLE）
1、经典的查询语句
2、经典的字定义函数
3、经典的与业务相关的存储过程
等等



抛砖引玉：备注本人彩票迷
（有点缺陷）
create or replace function f_ssqrandom(priornum in integer,endnum in integer)
 return varchar2 
 is
 v_prior_rand integer :=0;
 v_end_rand integer;
 v_string string(32000):='红色球';
begin
for v in 1..5 loop
for v_count in 1..3 loop
  v_prior_rand:=floor(dbms_random.value(1,priornum));
  
  v_end_rand:=v_prior_rand;
  <<random>>
  v_prior_rand:=floor(dbms_random.value(1,priornum));
  if v_prior_rand!=v_end_rand then
  v_string:=v_string||','||v_prior_rand||','||v_end_rand;
  else
  goto random;
  end if;
  dbms_output.put_line(v_string);
  end loop;
  v_string:=v_string||' 蓝色球, '||floor(dbms_random.value(1,endnum))||' 红色球';
  end loop;
  return v_string;
 
end f_ssqrandom;

  希望不要恶意回帖 MARK 顶的大哥大姐 放心理就行 小弟心领了

------解决方案--------------------
查询新建用户
select username
from dba_users
 where username not in
('TEXT', 'RMAN_USER', 'TEST', 'SCOTT', 'TSMSYS', 'MDDATA', 'DIP',
'DBSNMP', 'SYSMAN', 'MDSYS', 'ORDSYS', 'EXFSYS', 'DMSYS', 'WMSYS',
'CTXSYS', 'ANONYMOUS', 'XDB', 'ORDPLUGINS', 'SI_INFORMTN_SCHEMA',
'OLAPSYS', 'MGMT_VIEW', 'SYS', 'SYSTEM', 'OUTLN');


查询那些用户，操纵了那些表造成了锁机 
SELECT s.username, 
decode(l.type,'TM','TABLE LOCK', 
'TX','ROW LOCK', 
NULL) LOCK_LEVEL, 
o.owner,o.object_name,o.object_type, 
s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser 
FROM v$session s,v$lock l,all_objects o 
WHERE l.sid = s.sid 
AND l.id1 = o.object_id(+) 
AND s.username is NOT Null 
其中 TM 为表锁定 TX 为行锁定


看锁阻塞的方法是 
SELECT (select username FROM v$session WHERE sid=a.sid) blocker, 
a.sid, 
'is blocking', 
(select username FROM v$session WHERE sid=b.sid) blockee, 
b.sid 
FROM v$lock a, v$lock b 
WHERE a.block = 1 
AND b.request > 0 
AND a.id1 = b.id1 
AND a.id2 = b.id2
------解决方案--------------------
比如：获取系统信息：
select
SYS_CONTEXT('USERENV','TERMINAL') terminal,
SYS_CONTEXT('USERENV','LANGUAGE') language,
SYS_CONTEXT('USERENV','SESSIONID') sessionid,
SYS_CONTEXT('USERENV','INSTANCE') instance,
SYS_CONTEXT('USERENV','ENTRYID') entryid,
SYS_CONTEXT('USERENV','ISDBA') isdba,
SYS_CONTEXT('USERENV','NLS_TERRITORY') nls_territory,
SYS_CONTEXT('USERENV','NLS_CURRENCY') nls_currency,
SYS_CONTEXT('USERENV','NLS_CALENDAR') nls_calendar,
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT') nls_date_format,
SYS_CONTEXT('USERENV','NLS_DATE_LANGUAGE') nls_date_language,
SYS_CONTEXT('USERENV','NLS_SORT') nls_sort,
SYS_CONTEXT('USERENV','CURRENT_USER') current_user,
SYS_CONTEXT('USERENV','CURRENT_USERID') current_userid,
SYS_CONTEXT('USERENV','SESSION_USER') session_user,
SYS_CONTEXT('USERENV','SESSION_USERID') session_userid,
SYS_CONTEXT('USERENV','PROXY_USER') proxy_user,
SYS_CONTEXT('USERENV','PROXY_USERID') proxy_userid,
SYS_CONTEXT('USERENV','DB_DOMAIN') db_domain,
SYS_CONTEXT('USERENV','DB_NAME') db_name,
SYS_CONTEXT('USERENV','HOST') host,
SYS_CONTEXT('USERENV','OS_USER') os_user,