一个客户碰到的具体需求,分区表中有些分区所在的表空间被OFFLINE,tb导致在删除统计信息时报错。
下面通过例子来说明这个问题:
SQL> create table t_part_read (id number)
2 partition by range (id)
3 (partition p1 values less than (10) tablespace ts1,
4 partition p2 values less than (20) tablespace ts2,
5 partition pmax values less than (maxvalue) tablespace users);
Table created.
SQL> insert into t_part_read select rownum from tab;
54 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user, 'T_PART_READ')
PL/SQL procedure successfully completed.
SQL> alter tablespace ts1 read only;
Tablespace altered.
SQL> exec dbms_stats.gather_table_stats(user, 'T_PART_READ')
PL/SQL procedure successfully completed.
SQL> alter tablespace ts1 offline;
Tablespace altered.
SQL> exec dbms_stats.gather_table_stats(user, 'T_PART_READ')
BEGIN dbms_stats.gather_table_stats(user, 'T_PART_READ'); END;
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_7w8l5fz1_.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
如果将表空间只读,并不会影响到表空间上的表或分区的统计信息的收集,因为收集过程只是读取,而收集的结果信息是写到SYSTEM表空间的。
但是如果分区所在的表空间处于OFFLINE状态,那么在统计信息收集的过程中就会报错。
有一个很简单的方法可以解决这个问题,就是将被OFFLINE影响的分区的统计信息锁定,这样Oracle在收集统计信息时就会跳过锁定的分区,通过这个办法就可以避免统计信息收集过程中的报错:
SQL> exec dbms_stats.lock_partition_stats(user, 'T_PART_READ', 'P1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user, 'T_PART_READ')
BEGIN dbms_stats.gather_table_stats(user, 'T_PART_READ'); END;
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_7w8l5fz1_.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
SQL> exec dbms_stats.gather_table_stats(user, 'T_PART_READ', granularity => 'PARTITION')
PL/SQL procedure successfully completed.
即使锁定分区后,尝试收集统计信息仍然报错,这是因为Oracle默认除了要收集分区上的统计信息以外,还要收集表级的统计信息,而这就会造成被OFFLINE影响的分区也要被读取。
解决方法就是在收集统计信息的时候指定收集的粒度是分区,不收集表上的GLOBAL信息。