ITPub博客

一: nvlselect nvl(字段名,0) from 表名;解释》如果字段的值为null,则用0代替。二：nvl2select nvl2(字段名，'A'，'B'）from 表名解释》如果字段值不为null，输出A，反之输出B。三：lnnvllnnvl只能用于where子句中；表达式的操作符号不能包含 AND, OR, BETWEEN。select * from 表名 where lnnvl(字段名>值)解释：如果lnnvl表达式中的结果为true，输出false，反之输出true四：nullif select nullif(字段A，字段B）from 表名解释》如果字段A等于字段B，输出null，反之输出字段A。 refs:http://blog.sina.com.cn/s/blog_5edcfe6a0100jpna

ref:http://hi.baidu.com/javashmily/blog/item/426e80ee644c0f06fcfa3c75.htmlsummary:LISTAGG Analystic Function in 11g Release 2SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees FROM emp GROUP BY deptno;[@more@]

ORACLE批量更新四种方法比较文章出处：飞诺网http://dev.firnow.com/course/7_databases/oracle/oraclejs/20071120/87132_2.html[@more@]

select t.rank, t.Name from t_menu_item t; 10 CLARK 10 KING 10 MILLER 20 ADAMS 20 FORD 20 JONES 20 SCOTT 20 SMITH 30 ALLEN 30 BLAKE 30 JAMES 30 MARTIN 30 TURNER 30 WARD -------------------------------- 我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果 select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank; DEPTNO ENAME ------ ---------- 10 CLARK, KING, MILLER 20 ADAMS, FORD, JONES, SCOTT, SMITH 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARDPS:1.WMSYS.WM_CONCA

refs:http://www.ningoo.net/html/2008/get_top_n_cpu_sql_in_realtime.html[@more@]

V$SQL 中PARSING_USER_ID,PARSING_SCHEMA_ID,PARSING_SCHEMA_NAME这三个字段之间的关系：假如当前用户是HLDBA默认情况下，当前SCHEMA_ID与USER_ID相同，SCHEMA_NAME就是当前用户名(HLDBA)。但是当执行如下语句后，SCHEMA_ID 和SCHEMA_NAME就会发生改变alter session set current_schema="SALESMGMT" ;之后，USER_ID=(HLDBA USERID) , SCHEMA_ID,SCHEMA_NAME 皆属于SALESMGMT用户对应的ID和名字。[@more@]

Oracle Sequence Cache 参数说明http://blog.csdn.net/tianlesoftware/archive/2010/11/08/5995051.aspx[@more@]

Oracle常见SQL分页实现方案介绍http://blog.sina.com.cn/s/blog_4d8a2c970100fjv5.html[@more@]

临时表空间消除的方法http://weiranjie.itpub.net/post/26925/479789[@more@]

_b_tree_bitmap_plans参数http://blog.csdn.net/dinanzhang/archive/2008/05/09/2421978.aspx[@more@]

free unused space under HWM.prerequisites: enable table row movement.1, shrink space.ALTER TABLE T2 SHRINK SPACE ;2, move [tablespace].ALTER TABLE T2 MOVE ;-- sample code:SELECT BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME='T2' ;--384, 512ALTER TABLE T2 ALLOCATE EXTENT (SIZE 1M) ;exec dbms_stats.gather_table_stats(user,'t2')DELETE T2 WHERE DEPTNO=10 ;ALTER TABLE T2 DEALLOCATE UNUSED ;ALTER TABLE T2 ENABLE ROW MOVEMENT;-- blocks in user_tables indicate the HWM block.select TABLE_NAME, BLOCKS,NUM_