Some partitions in a partition table are unavailable, leading to invalid statistics collection.

When some partitions in a partition table are unavailable, the statistical information collection becomes invalid. A specific requirement is met by the customer. The tablespace of some partitions in the Partition Table is OFFLINE, and tb leads to an error when deleting the statistical information. The following example illustrates this problem: www.2cto.com 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; www.2cto.com Tablespace altered. SQL> exec dbms_stats.gather_table_stats (user, 't_ PART_READ ') BEGIN dbms_stats.gather_table_s Tats (user, 't_ PART_READ '); END; * ERROR at line 1: ORA-00376: file 6 cannot be read at this timeORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_7w8l5fz1 _. dbf 'ora-06512: at "SYS. DBMS_STATS ", line 23829ORA-06512: at" SYS. DBMS_STATS ", line 23880ORA-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, the collected results are 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 the following errors during statistics collection: SQL> exec dbms_stats.lock_partition_stats (user, 't_ PART_READ ', 'p1') www.2cto.com 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 tim EORA-01110: data file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_7w8l5fz1 _. dbf 'ora-06512: at "SYS. DBMS_STATS ", line 23829ORA-06512: at" SYS. DBMS_STATS ", line 23880ORA-06512: at line 1 www.2cto.com SQL> exec PARTITION (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. Partitions that may be 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.