摘要：
SELECT * FROM V$SESSION WHERE PREV_SQL_ID='dakpd8k8juxz0'select * from v$sql where sql_text like '%SELECT ORG.ORGID AS ORGID, ORG%' and last_load_time >'2013-07-04/19:00:00'SELECT * FROM V$SQL_BIND_CAPTURE WHERE SQL_ID ='dakpd8k8juxz0' 阅读全文
摘要：
select a.table_name user1_table, a.column_name user1_column, b.table_name , b.column_name user2_column from (select table_name, column_name from dba_tab_columns where owner= 'USER1' ) a, (select table_name, column_name from dba_tab_columns where owner= 'USER2' ) b where a.table_name( 阅读全文
摘要：
分区表推荐用： EXEC dbms_stats.gather_table_stats(ownname => 'SCHEMA', tabname => 'TABLE_NAME', cascade => TRUE, degree => number of cpu, estimate_percent => 100); 一般的表（tom高效设计中的方法）： analyze table table_name compute statistics for table for all indexes for all indexed columns 阅读全文
摘要：
select [分组的字段],ltrim(max(sys_connect_by_path([要合并的字段], ',')), ',') [要合并的字段]from (select [分组的字段],[要合并的字段],row_number()over(partition by [分组的字段] order by [要合并的字段]) rnfrom 表名)start with rn=1connect by prior rn=rn-1 and prior [分组的字段]=[分组的字段]group by [分组的字段]order by [分组的字段]; 阅读全文
摘要：
SYS_CONTEXT('USERENV', 'OS_USER'), machine, sys_context('USERENV', 'IP_ADDRESS'), 阅读全文
摘要：
ORA_01555 snapshot too old: rollback segment number string with name "string" too small原因可分为以下情形：A. 回滚段太少/太小数据库中有太多的事务修改数据并提交, 就会发生已提交事务曾使用的空间被重用, 从而造成一个延续时间长的查询所请求的数据已经不在回滚段中.解决方法: 创建更多的回滚段, 为回滚段设置较大的EXTENT以及较大的MINEXTENTSB. 回滚段被破坏由于回滚段被破坏, 造成事务无法将修改前的内容(read-consistent snapshot) 放入回滚段, 也会 阅读全文
摘要：
1.SELECT * FROM V$DB_OBJECT_CACHE WHERE name='NCCM_MEDICAL_USERSTATS2' AND LOCKS!='0'; 2.select SID from V$ACCESS WHERE object='NCCM_MEDICAL_USERSTATS2';3.SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID='135'; 4.alter system kill session '135,50430' immediat 阅读全文
摘要：
刚看了一下10g的快速刷新限制条件，明确说明了包含分析函数的物化视图是无法快速刷新的：General Restrictions on Fast RefreshThe defining query of the materialized view is restricted as follows:The materialized view must not contain references to non-repeating expressions like SYSDATE and ROWNUM.The materialized view must not contain references 阅读全文
摘要：
优化前：update table1 aset co1 =(select co2 from table2 bwhere b.co3=a.co3)where exists =(select1 from table2 bwhere b.co3=a.co3 )优化后:update (select a.co1,b.co2 from sa.table1 a , sa.table2 b where b.co3=a.co3 ) set co1= co2b.co3=a.co3必须为唯一 阅读全文
摘要：
exception oracle error sqlcode value condition no_data_found ora-01403 +100 select into 语句没有符合条件的记录返回 too_many_rows ora-01422 -1422 select into 语句符合条件的记录有多条返回 dup_val_on_index ora-00001 -1 对于数据库表中的某一列，该列已经被限制为唯一索引，程序试图存储两个重复的值 value_error ora-06502 -6502 在转换字符类型，截取... 阅读全文