Monthly Archives: January 2012
使用oerr命令查看oracle错误信息
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: [...]
Feed订阅