经典oracle语句（特辑一）

1、查询新建用户
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');

2、查询那些用户，操纵了那些表造成了锁机
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 为行锁定 

3、看锁阻塞的方法是
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

4、获取系统信息：
  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,
          SYS_CONTEXT('USERENV','IP_ADDRESS') ip_address,
          SYS_CONTEXT('USERENV','NETWORK_PROTOCOL') network_protocol,
          SYS_CONTEXT('USERENV','BG_JOB_ID') bg_job_id,
          SYS_CONTEXT('USERENV','FG_JOB_ID') fg_job_id,
          SYS_CONTEXT('USERENV','AUTHENTICATION_TYPE')
authentication_type,
          SYS_CONTEXT('USERENV','AUTHENTICATION_DATA')
authentication_data
  from dual

5、Oracle计算时间差表达式
--获取两时间的相差天数
select ceil((To_date('2008-05-02 00:00:00', 'yyyy-mm-dd hh24-mi-ss') -
            To_date('2008-04-30 23:59:59', 'yyyy-mm-dd hh24-mi-ss'))) 相差天数
  FROM DUAL;
--获取两时间月份差
select (EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd'))) * 12 +
       EXTRACT(month FROM to_date('2008-05-01','yyyy-mm-dd')) - EXTRACT(month FROM to_date('2008-04-30','yyyy-mm-dd')) months
from dual;
--获取两时间年份差
select EXTRACT(year FROM to_date('2009-05-01','yyyy-mm-dd')) - EXTRACT(year FROM to_date('2008-04-30','yyyy-mm-dd')) years from dual;

6、--Insert multiple rows into different tables with a single statement:
INSERT ALL
   WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
   WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
   WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
   ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
SELECT type, val FROM source_tab;

7、--产生1~99行数据，少于一位则补0
SELECT lpad(ROWNUM,2,'0') FROM dual CONNECT BY ROWNUM <=99;

8、跟踪当前对话下用户的SQL脚本
select sql_text from v$sqltext_with_newlines where (hash_value,address)
in (select sql_hash_value,sql_address from v$session where sid=&sid)
order by address,piece;
SID 由这得到
select sid,machine from v$session;

9、删除重复记录
delete from dumpy_part a
where a.rowid <> (select min(b.rowid) from dumpy_part b where a.id = b.id)

10、若干语句
select decode(1,1,'YES','NO')  from dual;
select a.* from a,b where instr(a.MSGSTR,b.SSTR)>0;
select * from emp where eid not like '[0-8][0-8][0-12]';
select username,default_tablespace from user_users;
select * from table for update;

11、把select出来的结果导到一个文本文件中
SQL>SPOOL　C:/ABCD.TXT;
SQL>select *　from table;
SQL >spool off;

12、估算SQL执行的I/O数  
SQL>SET AUTOTRACE ON ;
SQL>SELECT * FROM TABLE;
OR
SQL>SELECT *　FROM　v$filestat ;
可以查看IO数