oracle控制文件的大小,控制文件大小的疑问

在一个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 到底有什么关系呢？