ORACLE 数据库,两个表,分别设置了alter table keep cache,但是其中一个表 dba_tables 仍然显示为default Buffer_pool ,另一个表显示为 Keep。
dba_tables 显示为default Buffer_pool 的是分区表,分区表的话,需要通过 DBA_PART_TABLES 视图查询
创建普通表,并配置 keep cache
SQL> create table t_keep_cache(i int);
Table created.
SQL> alter table t_keep_cache storage(buffer_pool keep) cache;
Table altered.
SQL> select table_name,cache,buffer_pool from dba_tables where table_name='T_KEEP_CACHE';
TABLE_NAME CACHE BUFFER_
------------------------------ -------------------- -------
T_KEEP_CACHE Y KEEP
可以看到通过 dba_tables 视图看到,BUFFER_POOL 显示为 KEEP,符合预期。
创建分区表,并配置 keep cache
CREATE TABLE T_KEEP_PART_CACHE(
ID NUMBER,
NAME VARCHAR2(50),
CREATE_DATE DATE
)
PARTITION BY RANGE (CREATE_DATE)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2023-06', 'YYYY-MM'))
);
alter table t_keep_part_cache storage(buffer_pool keep) cache;
select table_name,cache,buffer_pool from dba_tables where table_name in ('T_KEEP_CACHE','T_KEEP_PART_CACHE');
TABLE_NAME CACHE BUFFER_
------------------------------ -------------------- -------
T_KEEP_CACHE Y KEEP
T_KEEP_PART_CACHE Y DEFAULT
可以看到,分区表,通过 dba_tables 视图查看,仍然是 DEFAULT。
通过 DBA_PART_TABLES 视图查询确认:
SQL> select table_name,DEF_BUFFER_POOL from DBA_PART_TABLES where table_name='T_KEEP_PART_CACHE' ;
TABLE_NAME DEF_BUF
------------------------------ -------
T_KEEP_PART_CACHE KEEP
知识总结
分区表的信息通常是通过DBA_PART开头的视图进行查询。