estimating controlfile size via sql

[Axr2]

This is of no particular consequence. Just curiosity, thats all.

Why the large difference in controlfile size between the sql and actual size on OS? Am I not comparing apples to apples?

SQL> select sum(RECORD_SIZE*RECORDS_TOTAL) from V$CONTROLFILE_RECORD_SECTION;

SUM(RECORD_SIZE*RECORDS_USED)
-----------------------------
16140224

SQL> !ls -l /oradata/dev/control01.ctl
-rw-rw---- 1 oracle oinstall 32530432 Nov 23 16:17 /oradata/dev/control01.ctl

[kris123]

Hi Kuya Axe,

Just a wild guess...the fisrt is logical and the latter is physical
Just like counting rows in a table and compare it to the size of the
tables data_file.

[Akila]

somethg to do with the header information OS add to the original data

I think so, not sure

[Markelenkov]

Steve Adams:

"...
Recoverability for changes to the other controlfile records sections is provided by maintaining all the information in duplicate. Each logical block is represented by two physical blocks. One contains the current information, and the other contains either an old copy of the information, or a pending version that is yet to be committed. To keep track of which physical copy of each logical block contains the current information, Oracle maintains a block version bitmap with the database information entry in the first record section of the controlfile..."

[Axr2]

Originally posted by Markelenkov
Steve Adams:

"...
Recoverability for changes to the other controlfile records sections is provided by maintaining all the information in duplicate. Each logical block is represented by two physical blocks. One contains the current information, and the other contains either an old copy of the information, or a pending version that is yet to be committed. To keep track of which physical copy of each logical block contains the current information, Oracle maintains a block version bitmap with the database information entry in the first record section of the controlfile..."

Thanks!! That explains it!! The physical size is always just a tad more than twice sum(RECORD_SIZE*RECORDS_TOTAL). Makes sense now.

[Markelenkov]

http://www.ixora.com.au/q+a/0102/02112927.htm

[Axr2]

Originally posted by Markelenkov
http://www.ixora.com.au/q+a/0102/02112927.htm

Thanks again for a relevant answer.