Monthly Archives: January 2012


Unix和Linux环境下可以直接使用,windows环境使用需要使用第三方的工具。 $ ./oerr ora 1555 01555, 00000, “snapshot too old: rollback segment number %s with name \”%s\” too small” // *Cause: rollback records needed by a reader for consistent read are // overwritten by other writers // *Action: If in Automatic Undo Management mode, increase undo_retention // setting. Otherwise, use larger rollback segments 参考资料: Viewing [...]

oracle中null在in、not in、exists操作中表现

1、null在in、not in操作符的左边时,结果都不是true SQL>select 1 from dual where null in (1,2,3,null); no rows selected SQL>select 1 from dual where null not in (1,2,3,null); no rows selected 2、使用not in时,如果操作符右边的集合中有null,其返回结果结果始终不是true SQL>select 1 from dual where 5 not in (1,2,3); 1 ———- 1 SQL>select 1 from dual where 5 not in (1,2,3,null); no rows selected 3、对包含null字段,not in和not exists操作不再等价,not exists返回结果更多,因为包含了null值 [...]


1. 创建物化视图相关的授权 GRANT CREATE MATERIALIZED VIEW TO adam; GRANT QUERY REWRITE TO adam; 2. 使用dmbs_mview.explain_mview查询无法快速刷新的原因 需要先创建表 mv_capabilities_table 脚本:$ORACLE_HOME/rdbms/admin/ultxmv.sql 具体用法: exec dbms_mview.explain_mview(mv=>’ekp.mv_org_person’,stmt_id=>’100′); select * from mv_capabilities_table t where t.capability_name like ‘REFRESH_FAST_AFTER%’ and statement_id = ’100′ dmbs_mview.explain_mview查询出无法快速刷新的原因: mv references PL/SQL function that maintains state, msnno 2083 查询语句中使用了函数wmsys.wm_concat inline view or subquery in FROM list not supported for [...]

iXML ABAP Objects的测试

参考资料:iXML ABAP Objects Jumpstart *&———————————————————————* *& Report Z_XML_LIB_TEST *& *&———————————————————————* *& *& *&———————————————————————* REPORT z_xml_lib_test. TYPE-POOLS: ixml. CLASS cl_ixml DEFINITION LOAD. DATA: g_ixml TYPE REF TO if_ixml. g_ixml = cl_ixml=>create( ). TYPES: BEGIN OF xml_line, data(256) TYPE x, END OF xml_line. DATA: xml_table TYPE TABLE OF xml_line, xml_table_size TYPE i, istream TYPE REF TO if_ixml_istream. [...]


测试对象是ADAM.T2,只有一个字段N,想增加一个varchar2(10)的name字段 SYS@orcl>desc adam.t2; Name Null? Type —————————————— N NUMBER SYS@orcl>select count(*) from t2; COUNT(*) ———- 5 确认ADAM.T2是否支持在线重定义 SYS@orcl>exec dbms_redefinition.can_redef_table(‘adam’,'t2′,dbms_redefinition.cons_use_rowid); PL/SQL procedure successfully completed. 创建用于交换表定义临时表ADAM.T5 SYS@orcl>create table adam.t5 as select * from adam.t2 where 1 = 0; Table created. SYS@orcl>alter table adam.t5 add (name varchar2(20)); Table altered. 执行在线重定义操作 SYS@orcl>exec dbms_redefinition.start_redef_table(‘adam’,'t2′,’t5′,’n n’,2); PL/SQL procedure successfully completed. Elapsed: [...]