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,
SYS_CONTEXT('USERENV','EXTERNAL_NAME') external_name,