Monday, July 21, 2008

Find the size of the control files

Below is the SQL statement to find the size of the control files.


select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile where rownum = 1;

OR

select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile;


Eg:

SQL > select (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile where rownum = 1;

CONTROL_FILE_SIZE (MB)
----------------------
18.515625


SQL > select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS))/1024/1024 "CONTROL_FILE_SIZE (MB)" from sys.v_$controlfile;


CONTROL_FILE_SIZE (MB)
----------------------
18.515625

SQL > select distinct (BLOCK_SIZE * (1 + FILE_SIZE_BLKS)) "CONTROL_FILE_SIZE (BYTES)" from sys.v_$controlfile;

CONTROL_FILE_SIZE (BYTES)
-------------------------
19415040


$ ls -slrt [path_to_Controlfile]/cntrl0*
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl03.dbf
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl02.dbf
18984 -rw-rw---- 1 oracle dba 19415040 Jul 21 02:56
[path_to_Controlfile]/cntrl01.dbf


Note: 1 block needs to be added to the file_size_blks to allow for the file header.

No comments: