RECYCLE池的设置也很简单，设置DB_RECYCLE_CACHE_SIZE的值大于0，再将对象的BUFFER_POOL参数设置为RECYCLE就可以了。
SQL> SHOW PARAMETER DB_%E%_CACHE_SIZE
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
db_keep_cache_size big integer 134217728
db_recycle_cache_size big integer 0
SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE = 0;
System altered.
SQL> ALTER SYSTEM SET DB_RECYCLE_CACHE_SIZE = 134217728;
System altered.
创建测试用表：
SQL> CREATE TABLE T STORAGE(BUFFER_POOL RECYCLE) AS
2 SELECT * FROM DBA_SOURCE
3 UNION ALL
4 SELECT * FROM DBA_SOURCE;
Table created.
SQL> CREATE TABLE T2 STORAGE(BUFFER_POOL RECYCLE) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> CREATE TABLE T3 STORAGE(BUFFER_POOL RECYCLE) AS SELECT * FROM DBA_SOURCE;
Table created.
SQL> COL SEGMENT_NAME FORMAT A30
SQL> SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 M
2 FROM USER_SEGMENTS
3 WHERE SEGMENT_NAME IN ('T', 'T2', 'T3')
4 GROUP BY SEGMENT_NAME;
SEGMENT_NAME M
------------------------------ ----------
T 152
T2 80
T3 80
T表的大小超过RECYCLE池的大小，而T2和T3的大小都小于RECYCLE的大小。
下面看看RECYCLE池的CACHE特点：
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 1
T2 xcur 1
T3 xcur 1
SQL> SET AUTOT ON STAT
SQL> SELECT COUNT(*) FROM T2;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 1
T2 xcur 4829
T3 xcur 1
SQL> SELECT COUNT(*) FROM T3;
COUNT(*)
----------
167011
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4839 consistent gets
4828 physical reads
0 redo size
381 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT OBJECT_NAME, A.STATUS, COUNT(*)
2 FROM V$BH A, USER_OBJECTS B
3 WHERE A.OBJD = B.OBJECT_ID
4 AND OBJECT_NAME IN ('T', 'T2', 'T3')
5 GROUP BY OBJECT_NAME, A.STATUS;
OBJECT_NAME STATU COUNT(*)
------------------------------ ----- ----------
T xcur 1
T2 xcur 4829
T3 xcur 3266
