oracle 表分区用完了,dbms_stats.lock_table_stats对于没有统计信息的表分区同样有效...

常见的分区表DDL如 split partition、add partition都会生成没有统计信息的表分区table partition，长期以来我对dbms_stats.lock_table_stats有一个错误的认识，即对于没有统计信息的分区，LOCK_STATS并不生效。 实际测试发现锁表统计信息对没有统计信息的分区同样有效，结果就是自动收集统计信息的作业不会收集这些实际没有统计信息的分区：

CREATE TABLE MacleanV

nologging

partition by range(object_id)

(partition p1 values less than (99999) tablespace users,

partition p2 values less than (maxvalue) tablespace users)

as select * from dba_objects where rownum=0;

SQL> insert into macleanV select * from dba_objects;

51029 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEANV');

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from dba_tables where table_name='MACLEANV';

NUM_ROWS BLOCKS

---------- ----------

51029 748

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME NUM_ROWS BLOCKS

------------------------------ ---------- ----------

P1 51029 748

P2 0 0

SQL> exec dbms_stats.lock_table_stats('SYS','MACLEANV');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('SYS','MACLEANV');

BEGIN dbms_stats.gather_table_stats('SYS','MACLEANV'); END;

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 15027

ORA-06512: at "SYS.DBMS_STATS", line 15049

ORA-06512: at line 1

SQL> alter table macleanv split partition p1 at (20001) into (partition p3, partition p4);

Table altered.

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME NUM_ROWS BLOCKS

------------------------------ ---------- ----------

P4

P2 0 0

P3

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC;

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME NUM_ROWS BLOCKS

------------------------------ ---------- ----------

P2 0 0

P3

P4

SQL> exec DBMS_STATS.GATHER_DATABASE_STATS;

PL/SQL procedure successfully completed.

SQL> select partition_name,num_rows,blocks from dba_tab_partitions where table_name='MACLEANV';

PARTITION_NAME NUM_ROWS BLOCKS

------------------------------ ---------- ----------

P2 0 0

P3

P4

使用DBMS_STATS.LOCK_TABLE_STATS锁定相关统计信息

语句为：

EXECUTE DBMS_STATS.LOCK_TABLE_STATS ('owner name', 'table name');

查询锁定状态

SELECT stattype_locked FROM dba_tab_statistics

当锁定表的统计信息后，这个表相关的对象的统计信息也被锁定，比如列信息、直方图、索引的统计信息。

在锁定前，请在适当时刻对表的统计信息进行收集，并确认当前的统计信息是合适的。

在锁定后，请用刷新shared pool或grant/revoke等方法将表相关的SQL失效。

更多DBMS_STATS.LOCK_TABLE_STATS信息可以参考：

Preserving Statistics using DBMS_STATS.LOCK_TABLE_STATS [ID 283890.1]

ORA-7445 [kdapprv] Running dbms_stats.gather_table_stats or dbms_stats.lock_table_stats [ID 333377.1]

FAQ: Automatic Statistics Collection Jobs (10g and 11g) [ID 1233203.1]