Control file Size

!hello

Can any on tell me how can i find the size of my CONTROLFILE .
Is there any view to find out.
Or I have to check by using OS command.

Using OS command will help as well...

For more info... Here

You may use record_size and records_total columns in v$controlfile_record_section to get the size of the controlfile. But for accurate size it is always advisable to see the size in OS.

Quote:

---

Originally posted by SANJAY_G
You may use record_size and records_total columns in v$controlfile_record_section to get the size of the controlfile. But for accurate size it is always advisable to see the size in OS.

---

Well Sanjay, IMHO those column values are in number (records) and not in bytes (size). So even if you want to calculate the controlfile size from the record_size or records_total, you cannot.

Quote:

---

Originally posted by adewri
Well Sanjay, IMHO those column values are in number (records) and not in bytes (size).

---

I think you may want to look at defination.

Quote:

---

V$CONTROLFILE_RECORD_SECTION
This view displays information about the control file record sections.

TYPE VARCHAR2(17) Identifies the type of record section: DATABASE, CKPT PROGRESS, REDO THREAD, REDO LOG, DATAFILE, FILENAME, TABLESPACE, LOG HISTORY, OFFLINE RANGE, ARCHIVED LOG, BACKUP SET, BACKUP PIECE, BACKUP DATAFILE, BACKUP REDOLOG, DATAFILE COPY, BACKUP CORRUPTION, COPY CORRUPTION, DELETED OBJECT, or PROXY COPY
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

---

Quote:

---

Originally posted by abhaysk
I think you may want to look at defination.

---

Hmmm I think i learned something new today :). BTW i was trying to addup those but its always different than the actual size at OS level, may be some more information is needed other that record_size to addup to the actual sie :). Or may be... Im not so good in mathematics..

Yes Amar, I too tried that before but it never gives you accurate size. Looking at OS size is the best way. :)

V$Controlfile_Record_Section, will give you actual size used in the allocted space by OS. So, with OS perspective this is not the size as the allocated size > the sum of the (sizes*used)

Quote:

---

Originally posted by abhaysk
V$Controlfile_Record_Section, will give you actual size used in the allocted space by OS. So, with OS perspective this is not the size as the allocated size > the sum of the (sizes*used)

---

Abhay,
FRom your previous post

Quote:

---

RECORDS_TOTAL NUMBER Number of records allocated for the section

---

I was using the no of records allocated and record size. But it always comes lesser that the actual cf size.

Quote:

---

Originally posted by SANJAY_G
Abhay,
I was using the no of records allocated and record size. But it always comes lesser that the actual cf size.

---

Yes sanjay, this is the reason why i didnt post that v$controlfile_record_section can be used as alternative to OS command.

Have to dig up some docs to see, if there are any other TYPE's which get recorded in Control File apart from listed types.

Abhay.

Dear Friends,

You can use this script to find the controlfile size,

set termout off
column value new_value BlkSz

select
value
from
sys.v_$parameter
where
name = 'db_block_size'
/
set termout on
set verify off

select
&BlkSz * (1 + 2 * sum(ceil(record_size * records_total / (&BlkSz - 24)))) "controlfile size"
from
sys.v_$controlfile_record_section

OutPut will look like this,

VALUE
----------------------
8192

1 row selected.


controlfile size
----------------
7806976

1 row selected.

This script returns blocksize also.

Bye,

UpeshP

Even this returns < os file size, however the ressult is pretty near.

could you please let us know, why is such cmplx cumputation required.