ORA-12801: error signaled in parallel query server P005

qq69033472

昨天有个分区表，交换了30多个分区（将业务表30多个分区交换到历史表，然后删除业务表这30多个分区），然后今天早上收集统计信息的时候报错了，请教各位，这个需要重建索引吗？为什么收集统计信息的时候会报错？请各位指教，alterlog和trace信息如下：

alertlog：
Mon Mar 03 22:46:36 CST 2014
GATHER_STATS_JOB encountered errors.  Check the trace file.
Mon Mar 03 22:46:36 CST 2014
Errors in file /Data/apps/oracle/admin/detail/bdump/detail_j003_5334.trc:
ORA-12801: error signaled in parallel query server P005
ORA-01502: index 'REPORTUSER.IDX_PROVINCE_DAY' or partition of such index is in unusable state

/Data/apps/oracle/admin/detail/bdump/detail_j003_5334.trc信息：
/Data/apps/oracle/admin/detail/bdump/detail_j003_5334.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /Data/apps/oracle/product/10.2.0/db_1
System name:    Linux
Node name:      localhost.localdomain
Release:        2.6.18-348.el5
Version:        #1 SMP Tue Jan 8 17:53:53 EST 2013
Machine:        x86_64
Instance name: detail
Redo thread mounted by this instance: 1
Oracle process number: 30
Unix process pid: 5334, image: oracle@localhost.localdomain (J003)

*** 2014-03-03 22:46:36.917
*** ACTION NAME:(GATHER_STATS_JOB) 2014-03-03 22:46:36.895
*** MODULE NAME:(DBMS_SCHEDULER) 2014-03-03 22:46:36.895
*** SERVICE NAME:(SYS$USERS) 2014-03-03 22:46:36.895
*** SESSION ID:(134.941) 2014-03-03 22:46:36.895
ORA-12801: error signaled in parallel query server P005
ORA-01502: index 'REPORTUSER.IDX_PROVINCE_DAY' or partition of such index is in unusable state
*** 2014-03-03 22:46:36.917
GATHER_STATS_JOB: GATHER_TABLE_STATS('"REPORTUSER"','"STATIC_AGG_PROVINCE_DAY"','""', ...)
ORA-12801: error signaled in parallel query server P005
ORA-01502: index 'REPORTUSER.IDX_PROVINCE_DAY' or partition of such index is in unusable state

harryzhang

正在收集所有的统计信息的过程中，索引失效了？
看看alert.log失效的时间戳呢。

qq69033472

不是吧，中午的时候，有报失效的信息，收集是在凌晨的，还有，local索引，为什么会exchange、drop索引之后，失效呢？
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:25:06 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:27:48 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:28:14 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:28:32 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:29:00 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:29:22 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:29:36 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:33:46 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:34:42 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:34:51 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:35:01 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable
Mon Mar 03 11:35:14 CST 2014
Some indexes or index [sub]partitions of table REPORTUSER.STATIC_AGG_PROVINCE_DAY have been marked unusable

Maclean Liu(刘相兵

具体是如何 exchange  和drop 的给出命令和对象DDL

qq69033472

Maclean Liu(刘相兵 发表于 2014-3-4 11:42
具体是如何 exchange  和drop 的给出命令和对象DDL

大概就是这个，写个一个procedure，我是手动改v_part_day变量一天一天交换，然后删除的
CREATE OR REPLACE procedure pro_parttable_auto
as
v_table_name varchar2(100);
v_part_day varchar2(100);
TYPE type_array_str IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR(32);
v_array_table type_array_str;

begin
  v_array_table('STATIC_AGG_HOUR') := 'PART_AGGHOUR_';
  v_array_table('STATIC_AGG_PROVINCE_DAY') := 'PART_PROV_';
  v_part_day := to_char(sysdate-90,'yyyymmdd');
  v_table_name := v_array_table.FIRST;

  execute immediate 'alter table STATIC_AGG_HOUR exchange partition PART_AGGHOUR_'||v_part_day||' with table EXCHANGE_HOUR_TEMP without validation';
  execute immediate 'alter table HIST_AGG_HOUR_2013 exchange partition HIST_HOUR_'||v_part_day||' with table EXCHANGE_HOUR_TEMP without validation';

  execute immediate 'alter table STATIC_AGG_PROVINCE_DAY exchange partition PART_PROV_'||v_part_day||' with table EXCHANGE_PRODAY_TEMP without validation';
  execute immediate 'alter table HIST_PROVINCE_DAY_2013 exchange partition HIST_PRODAY_'||v_part_day||' with table EXCHANGE_PRODAY_TEMP without validation';

  commit;

   loop
     exit when v_table_name is null;
     begin
       EXECUTE IMMEDIATE ('ALTER TABLE ' || v_table_name || ' DROP PARTITION ' || v_array_table(v_table_name) || v_part_day);
        insert into a_droppart_log values(sysdate, v_array_table(v_table_name)||v_part_day);
        commit;
     end;
     v_table_name := v_array_table.next(v_table_name);
   end loop;
end;
/

Maclean Liu(刘相兵

alter table STATIC_AGG_PROVINCE_DAY exchange partition PART_PROV_'||v_part_day||' with table EXCHANGE_PRODAY_TEMP without validation



对于 exchange partition且希望维护索引的 语法如下：

ALTER TABLE sales EXCHANGE PARTITION SALES_Q4_2003 with
table SALES_TMP INCLUDING INDEXES WITH VALIDATION UPDATE GLOBAL INDEXES

qq69033472

Maclean Liu(刘相兵 发表于 2014-3-4 12:20
alter table STATIC_AGG_PROVINCE_DAY exchange partition PART_PROV_'||v_part_day||' with table EXCHANG ...

索引类型是local分区索引，exchange也需要update global indexes吗？

SQL> select index_name,table_name,locality from user_part_indexes;

INDEX_NAME                     TABLE_NAME                     LOCALI
------------------------------ ------------------------------ ------
IDX_PROVINCE_DAY               STATIC_AGG_PROVINCE_DAY        LOCAL

Maclean Liu(刘相兵

注意 6楼 红字的2部分：

http://docs.oracle.com/cd/E11882 ... 3/part_admin002.htm

INCLUDING | EXCLUDING INDEXES Specify INCLUDING INDEXES if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table). Specify EXCLUDING INDEXES if you want all index partitions or subpartitions corresponding to the partition and all the regular indexes and index partitions on the exchanged table to be marked UNUSABLE. If you omit this clause, then the default is EXCLUDING INDEXES.