[Oracle OCP 052-1-2] 如何把表放到Keep Buffer Pool/Recycle Buffer Pool

假设scoot用户下有一张名为TTT的表，此表使用频度很高，但又容易被默认缓冲区踢出去，该怎么做呢？
我们可以将此表放到Keep Buffer Pool里

概念简介
Keep Buffer Pool： 其作用是缓存那些需要经常查询但又容易被默认缓冲区置换出去的对象
Recycle Buffer Pool ： 用于存储临时使用的，不经常使用的较大的对象

核心SQL

1. select table_name from all_tables where owner=‘SCOTT’; （or select table_name from user_tables;）查看用户下的表
2. alter table TTT storage(buffer_pool keep);将目标表放到Keep Pool
   alter table TTT storage(buffer_pool recycle);将目标表放到Recycle Pool
3. select segment_name,buffer_pool from dba_segments where segment_name=‘TTT’; 确认是否放成功了

实验举例

scott@ORCL>select table_name from all_tables where owner='SCOTT';

TABLE_NAME
------------------------------
DEPT
EMP
SALGRADE
T
TTT
T1
T2
GLOBAL
BONUS

9 rows selected.

scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>
scott@ORCL>select table_name from user_tables;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
GLOBAL
TTT
T
T1
T2

9 rows selected.

scott@ORCL>


scott@ORCL>select * from TTT;

        ID
----------
         1

scott@ORCL>

scott@ORCL>alter table TTT storage(buffer_pool keep);

Table altered.
scott@ORCL>select segment_name,buffer_pool from dba_segments where segment_name='TTT';

SEGMENT_NAME                                                                      BUFFER_
--------------------------------------------------------------------------------- -------
TTT                                                                               KEEP