[转帖]OLAP:ORA-12801,ORA-01410_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 6001 | 回复: 0   主题: [转帖]OLAP:ORA-12801,ORA-01410        下一篇 
derek
注册用户
等级:中校
经验:1550
发帖:209
精华:0
注册:2011-7-21
状态:离线
发送短消息息给derek 加好友    发送短消息息给derek 发消息
发表于: IP:您无权察看 2011-8-1 21:31:23 | [全部帖] [楼主帖] 楼主

OLAP系统,报表出错:
ORA-12801: 并行查询服务器 P002 中发出错误信号
ORA-01410: 无效的 ROWID
初步诊断是查询时候访问的index的rowid所指向的rowid找不到对应行
本着凡事先问metalink的原则,发现metalink给出了一些解答,感觉最靠谱的如下(id:734513.1):

Cause
The issue can occur when a index block delete is not completed.
ROWID's are found in the index block leaf.
Solution
~. Run the explain plan on the table with the update statement to fnd the offending index.
~. Run the following to find the index statement:
set long 100000
select dbms_metadata.get_ddl('INDEX','','') from dual;
~. Drop the index
~. Recreate the index.
The update should run without error
NOTE: The index must be dropped and recreated. An online rebuild will not fix the bad ROWID
3). In essence, It appears that an Index re-build could well resolve this issue.
4). If not then, please take this issue up with Oracle Database Technical Support as this does not seem to be an issue with Informatica or with Oracle Business Intelligence.


需要删除查询锁用到的index,并重新创建,rebulid online还不行。
因为是olap,并发用户访问量少,所以就大刀阔斧的开搞:
1)得到执行计划,找出索引

explain plan for  select ...
select * from table(DBMS_XPLAN.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            |  Name                     | Rows  | Bytes |TempSpc| Cost  | Pstart| Pstop |  TQ    |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                           |   100 | 31900 |       |   465K|       |       |        |      |            |
|*  1 |  VIEW                                |                           |   100 | 31900 |       |   465K|       |       |        |      |            |
|*  2 |   COUNT STOPKEY                      |                           |       |       |       |       |       |       |        |      |            |
|   3 |    VIEW                              |                           |  9571 |  2860K|       |   465K|       |       | 86,06  | P->S | QC (ORDER) |
|*  4 |     SORT ORDER BY STOPKEY            |                           |  9571 |  6000K|       |   465K|       |       | 86,06  | PCWP |            |
|*  5 |      SORT ORDER BY STOPKEY           |                           |   100 | 30600 |       |       |       |       | 86,05  | P->P | RANGE      |
|   6 |       SORT GROUP BY                  |                           |  9571 |  6000K|       |   465K|       |       | 86,05  | PCWP |            |
|   7 |        SORT GROUP BY                 |                           |  9571 |  6000K|       |   465K|       |       | 86,04  | P->P | HASH       |
|*  8 |         HASH JOIN                    |                           |  9571 |  6000K|       |   465K|       |       | 86,04  | PCWP |            |
|   9 |          NESTED LOOPS                |                           |     1 |   318 |       | 29166 |       |       | 86,03  | P->P | HASH       |
|  10 |           NESTED LOOPS               |                           |    36 | 10188 |       | 29164 |       |       | 86,03  | PCWP |            |
|  11 |            NESTED LOOPS              |                           |   718 |   189K|       | 29164 |       |       | 86,03  | PCWP |            |
|* 12 |             HASH JOIN                |                           | 71818 |    16M|       | 29164 |       |       | 86,03  | PCWP |            |
|* 13 |              TABLE ACCESS FULL       | STAT_DIM_AREA             |     1 |    22 |       |     2 |       |       | 86,00  | S->P | BROADCAST  |
|* 14 |              HASH JOIN               |                           |  7181K|  1547M|       | 29162 |       |       | 86,03  | PCWP |            |
|* 15 |               TABLE ACCESS FULL      | STAT_DIM_CITY             |     1 |    88 |       |     2 |       |       | 86,01  | S->P | BROADCAST  |
|  16 |               PARTITION LIST ALL     |                           |       |       |       |       |     1 |   131 | 86,03  | PCWP |            |
|  17 |                TABLE ACCESS FULL     | STAT_FACT_WATCHPART       |   718M|    92G|       | 29160 |     1 |   131 | 86,03  | PCWP |            |
|  18 |             INLIST ITERATOR          |                           |       |       |       |       |       |       | 86,03  | PCWP |            |
|* 19 |              INDEX UNIQUE SCAN       | PK_STAT_DIM_CPSP          |     1 |    22 |       |       |       |       | 86,03  | PCWP |            |
|* 20 |            INDEX UNIQUE SCAN         | PK_STAT_DIM_DATE          |     1 |    13 |       |       |       |       | 86,03  | PCWP |            |
|* 21 |           TABLE ACCESS BY INDEX ROWID| STAT_DIM_CONTENT_TYPE     |     1 |    35 |       |     1 |       |       | 86,03  | PCWP |            |
|* 22 |            INDEX UNIQUE SCAN         | PK_STAT_DIM_CONTENT_TYPE  |   100 |       |       |       |       |       | 86,03  | PCWP |            |
|  23 |          VIEW                        | STAT_DIM_CONTENT          |  2665K|   823M|       |   436K|       |       | 86,02  | S->P | HASH       |
|  24 |           SORT UNIQUE                |                           |  2665K|  1878M|  4152M|   436K|       |       |        |      |            |
|  25 |            UNION-ALL                 |                           |       |       |       |       |       |       |        |      |            |
|  26 |             TABLE ACCESS FULL        | STAT_TOTAL_VODPROG        |  1762K|  1258M|       |  2077 |       |       |        |      |            |
|  27 |             TABLE ACCESS FULL        | STAT_TOTAL_TVOD           |   887K|   613M|       |  1047 |       |       |        |      |            |
|  28 |             TABLE ACCESS FULL        | STAT_TOTAL_SUBJECT        | 10129 |  3857K|       |    14 |       |       |        |      |            |
|  29 |             TABLE ACCESS FULL        | STAT_TOTAL_CHANNEL        |  4901 |  1866K|       |     7 |       |       |        |      |            |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_001"."ROWNUM_">0)
2 - filter(ROWNUM<=100)
4 - filter(ROWNUM<=100)
5 - filter(ROWNUM<=100)
8 - access("STAT_FACT_WATCHPART"."CONTENT_CODE"="STAT_DIM_CONTENT"."CONTENT_CODE")
12 - access("STAT_FACT_WATCHPART"."AREA_CODE"="STAT_DIM_AREA"."AREA_CODE")
13 - filter("STAT_DIM_AREA"."AREA_CODE"='1')
14 - access("STAT_FACT_WATCHPART"."CITY_CODE"="STAT_DIM_CITY"."CITY_CODE")
15 - filter("STAT_DIM_CITY"."CITY_NAME"='??oo')
19 - access("STAT_DIM_CPSP"."CP_CODE"='HBDJ' OR "STAT_DIM_CPSP"."CP_CODE"='cp001')
filter("STAT_FACT_WATCHPART"."CP_CODE"="STAT_DIM_CPSP"."CP_CODE")
20 - access("STAT_FACT_WATCHPART"."DATE_ID"="STAT_DIM_DATE"."DATE_ID")
filter("STAT_DIM_DATE"."DATE_ID">=20110701)
21 - filter("STAT_DIM_CONTENT_TYPE"."CTYPE_NAME"='TV')
22 - access("STAT_FACT_WATCHPART"."CONTENT_TYPE"="STAT_DIM_CONTENT_TYPE"."CONTENT_TYPE")
Note: cpu costing is off
ò?????56DD?£
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT ptimizer=CHOOSE
1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'


2)删除重建:
用到了三个:

select dbms_metadata.get_ddl('INDEX','PK_STAT_DIM_CPSP','ZXDBM_890') from dual;
select dbms_metadata.get_ddl('INDEX','PK_STAT_DIM_DATE','ZXDBM_890') from dual;
select dbms_metadata.get_ddl('INDEX','PK_STAT_DIM_CONTENT_TYPE','ZXDBM_890') from dual;


3)由于是主键,删除的时候报错ORA-02429

使用alter table STAT_DIM_CONTENT_TYPE drop PRIMARY KEY;删除主键

4)测试
和谐




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论