Some partitions in the Partition Table are unavailable, leading to invalid statistics collection.

According to the specific requirements of a customer, the tablespace of some partitions in the Partition Table is OFFLINE, and tb leads to an error when deleting the statistics.

 

 

The following example illustrates the problem:

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.www.2cto.com

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

If the tablespace is read-only, it does not affect the collection of statistical information of tables or partitions on the tablespace, because the collection process only reads and the collected result information is written to the SYSTEM tablespace.

However, if the tablespace in which the partition is located is OFFLINE, an error is reported during statistics collection.

There is a very simple way to solve this problem, that is, to lock the statistical information of the partitions affected by OFFLINE, so that Oracle will skip the locked partition when collecting statistics, this method can avoid errors during statistics collection:

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.

Even after the partition is locked, an error is still reported when you try to collect statistics. This is because Oracle collects table-level statistics in addition to the statistics on the partition by default, in this case, partitions affected by OFFLINE are also read.

The solution is to specify the collection granularity as a partition when collecting statistics and not to collect GLOBAL information on the table.