控制檔案大小的疑問

在一個9i資料庫後臺看到一條報警日誌,說控制檔案已滿,而這個控制檔案是放在一個250M大小的裸裝置上,到底這個控制檔案有多大? 它會增長到多大? 10g還好計算,9i怎麼辦?[@more@]

-- v$controlfile_record_section

-- oracle 9i 和 oracle10g 中的v$controlfile_record_section 差異

-- 在9i中有23種型別的記錄
select type from v$controlfile_record_section;

TYPE
--------------------
ARCHIVED LOG
BACKUP CORRUPTION
BACKUP DATAFILE
BACKUP PIECE
BACKUP REDOLOG
BACKUP SET
BACKUP SPFILE
CKPT PROGRESS
COPY CORRUPTION
DATABASE
DATABASE INCARNATION
DATAFILE
DATAFILE COPY
DELETED OBJECT
FILENAME
LOG HISTORY
OFFLINE RANGE
PROXY COPY
REDO LOG
REDO THREAD
RMAN CONFIGURATION
TABLESPACE
TEMPORARY FILENAME

-- 在10g中有34種型別
ARCHIVED LOG
BACKUP CORRUPTION
BACKUP DATAFILE
BACKUP PIECE
BACKUP REDOLOG
BACKUP SET
BACKUP SPFILE
CKPT PROGRESS
COPY CORRUPTION
DATABASE
DATABASE INCARNATION
DATAFILE
DATAFILE COPY
DATAFILE HISTORY
DELETED OBJECT
FILENAME
FLASHBACK LOG
GUARANTEED RESTORE POINT
INSTANCE SPACE RESERVATION
LOG HISTORY
MTTR
OFFLINE RANGE
PROXY COPY
RECOVERY DESTINATION
REDO LOG
REDO THREAD
REMOVABLE RECOVERY FILES
RESTORE POINT
RMAN CONFIGURATION
RMAN STATUS
STANDBY DATABASE MATRIX
TABLESPACE
TEMPORARY FILENAME
THREAD INSTANCE NAME MAPPING

------------------
-- 增加的11項內容如下：
DATAFILE HISTORY
FLASHBACK LOG
GUARANTEED RESTORE POINT
INSTANCE SPACE RESERVATION
MTTR
RECOVERY DESTINATION
REMOVABLE RECOVERY FILES
RESTORE POINT
RMAN STATUS
STANDBY DATABASE MATRIX
THREAD INSTANCE NAME MAPPING
------------------
-- v$controlfile_record_section 各列的含義
RECORD_SIZE NUMBER --Record size in bytes
RECORDS_TOTAL NUMBER --Number of records allocated for the section
RECORDS_USED NUMBER --Number of records used in the section
FIRST_INDEX NUMBER --Index (position) of the first record
LAST_INDEX NUMBER --Index of the last record
LAST_RECID NUMBER --Record ID of the last record

------------------
-- 控制檔案的大小
SQL> col name for a40
SQL> select * from v$controlfile;

STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ------------------------------ --- ---------- --------------
/oracle/oradata/mynewdb/ora_control1 NO 16384 380

SQL>
SQL> select 16384*380 from dual;

16384*380
----------
6225920

SQL> host ls -l /oracle/oradata/mynewdb/ora_co*
-rw-r----- 1 oracle dba 6242304 Feb 21 11:45 /oracle/oradata/mynewdb/ora_control1

SQL> select 6242304-6225920 from dual;

6242304-6225920
---------------
16384

SQL>
-- 小結：在10g v$controlfile檢視中有blocksize 和 file size blks 兩列
-- 透過blocksize*(file size blks + 1) 可以計算出當前控制檔案大小。

-- 9i 中的情況又如何呢？
SQL> select * from v$controlfile_record_section
2 ;

TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED
-------------------- ----------- ------------- ------------
DATABASE 316 1 1
CKPT PROGRESS 4084 4 0
REDO THREAD 228 1 1
REDO LOG 72 5 3
DATAFILE 428 100 11
FILENAME 268 116 16
TABLESPACE 68 100 13
TEMPORARY FILENAME 56 100 2
RMAN CONFIGURATION 1108 50 1
LOG HISTORY 36 226 226
OFFLINE RANGE 180 136 0
ARCHIVED LOG 328 24 24
BACKUP SET 40 204 204
BACKUP PIECE 480 204 204
BACKUP DATAFILE 116 211 211
BACKUP REDOLOG 76 107 107
DATAFILE COPY 404 202 0
BACKUP CORRUPTION 44 185 0
COPY CORRUPTION 40 204 0
DELETED OBJECT 20 408 408
PROXY COPY 596 219 0
BACKUP SPFILE 36 226 200
DATABASE INCARNATION 56 145 1

23 rows selected.

-- (RECORD_SIZE * RECORDS_TOTAL )/8192 取整 求和
TYPE RECORD_SIZE RECORDS_TOTAL RECORDS_USED RECORDS_TOTAL2 RECORDS_USED2 tt8k tt8k-int tt4k tt4k-int
-------------------- ----------- ------------- ------------
DATABASE 316 1 1 316 316 0.038574219 1 0.077148438 1
CKPT PROGRESS 4084 4 0 16336 0 1.994140625 2 3.98828125 4
REDO THREAD 228 1 1 228 228 0.027832031 1 0.055664063 1
REDO LOG 72 5 3 360 216 0.043945313 1 0.087890625 1
DATAFILE 428 100 11 42800 4708 5.224609375 6 10.44921875 11
FILENAME 268 116 16 31088 4288 3.794921875 4 7.58984375 8
TABLESPACE 68 100 13 6800 884 0.830078125 1 1.66015625 2
TEMPORARY FILENAME 56 100 2 5600 112 0.68359375 1 1.3671875 2
RMAN CONFIGURATION 1108 50 1 55400 1108 6.762695313 7 13.52539063 14
LOG HISTORY 36 226 226 8136 8136 0.993164063 1 1.986328125 2
OFFLINE RANGE 180 136 0 24480 0 2.98828125 3 5.9765625 6
ARCHIVED LOG 328 24 24 7872 7872 0.9609375 1 1.921875 2
BACKUP SET 40 204 204 8160 8160 0.99609375 1 1.9921875 2
BACKUP PIECE 480 204 204 97920 97920 11.953125 12 23.90625 24
BACKUP DATAFILE 116 211 211 24476 24476 2.987792969 3 5.975585938 6
BACKUP REDOLOG 76 107 107 8132 8132 0.992675781 1 1.985351563 2
DATAFILE COPY 404 202 0 81608 0 9.961914063 10 19.92382813 20
BACKUP CORRUPTION 44 185 0 8140 0 0.993652344 1 1.987304688 2
COPY CORRUPTION 40 204 0 8160 0 0.99609375 1 1.9921875 2
DELETED OBJECT 20 408 408 8160 8160 0.99609375 1 1.9921875 2
PROXY COPY 596 219 0 130524 0 15.93310547 16 31.86621094 32
BACKUP SPFILE 36 226 200 8136 7200 0.993164063 1 1.986328125 2
DATABASE INCARNATION 56 145 1 8120 56 0.991210938 1 1.982421875 2
77 150

-- 控制檔案總共要分配77個大小為8K的塊
-- 或者
-- 控制檔案總共要分配150個大小為4K的塊
SQL> host ls -l /oracle/oradata/ptdb/control01.ctl
-rw-r----- 1 oracle dba 1302528 Feb 21 11:36 /oracle/oradata/ptdb/control01.ctl

-- 控制檔案總共要分配150個大小為4K的塊 到作業系統要分配150個資料塊(8k) 還缺少9個8k的塊做什麼了？
-- 問題： v$controlfile_record_section.RECORDS_TOTAL 與 v$controlfile.FILE_SIZE_BLKS 到底有什麼關係呢？