How to Size Oracle database/segment

I am trying to put together some numbers and calculation for Oracle's space handling. These old ways of calculating and over years Oracle has done significant changes in space management.
So my #s may not be 100% accurate but may give you an idea


Table Name: EPTORA1.H_STOCK_EXEC


Table size calculations:
DB Block Size = 8192 (8k)

With the above column datatypes each row would take around 27 bytes. Formula is Scale-precision/2+1 and for date type its 7
So A = 27

In each block, Oracle stores header information which takes around (141 bytes + 2x) where x is # of records in that block
B = 141 + 2x

At the table level, PCTFREE is 2%, space for updates.
C = 2% of 8192 = 163

So Space Available for data = 8192 - (A+B+C) = 8192 - (27+141+2x+163) = 7861 -2x

If we have x rows in one block

7861 -2x = 27x => x= 271 records approx

# of rows = 3380055613 as of 28th Jan

Space required by all rows (3380055613/x) blocks = (3380055613/271)*8192/1024/1024/1024 = 95 GB

In reality, Oracle allocated 97 GB as of 28th Jan

12:12:46 SYS@gptprd2 SQL> select last_analyzed, num_rows, round(blocks*8192/1024/1024/1024) size_gb from dba_tables where table_name = 'H_STOCK_EXEC';

LAST_ANALYZED NUM_ROWS SIZE_GB
-------------------- ---------- ----------
28-JAN-2011 22:04:36 3300080148 97

1 row selected.

This wastage can be due to last row not fitting in the remaining space and get moved to the next block.



Now lets calculate for Index.
Same stuff but only Indexed column. While considering the space for index we need to consider the precision. Index is on

COLUMN_NAME COLUMN_LENGTH
------------------------- -------------
H_STOCK_ENTRY_ID 22


A=22

In each block, Oracle stores header information which takes around (165 bytes). For index header is bit more
B = 165

At the table level, PCTFREE is 2%, space for updates.
C = 2% of 8192 = 163

Index has some additional header information like ROWID etc
D = 9

So Space Available for index data= 8192 - (A+B+C+D) = 8192 - (22+165+163+6) = 7836

# of rows = 3380055613 as of 28th Jan

# of blocks required for these many rows = 3380055613*28/7836

Index is B-Tree and branches has a 10% space overhead. so multiplying that

So the Index size in GB is 1.1*(3380055613*28/7836)*8192/1024/1024/1024 = 101 GB


16:12:20 SYS@gptprd2 SQL> select last_analyzed, num_rows, round(leaf_blocks*8192/1024/1024/1024) size_gb from dba_indexes where table_name = 'H_STOCK_EXEC';

LAST_ANALYZED NUM_ROWS SIZE_GB
-------------------- ---------- ----------
28-JAN-2011 22:05:07 3380055613 97

1 row selected.

How to drop corrupt or missing logfiles

When logfiles are not present in the actual locations. may have been dropped by accident.

16:33:41 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log'

I tried to add a new one, but it failed as controlfile had the information. Logs were UNUSED in v$log

Elapsed: 00:00:00.68
16:33:49 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;
alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' - file already part of database


UNUSED logfiles can be dropped straight

Elapsed: 00:00:00.10
16:34:24 SYS@eqdd011g SQL> alter database drop logfile group 1;

Database altered.

Elapsed: 00:00:00.38

Then added those files again

16:34:41 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;

Database altered.

Elapsed: 00:00:00.66

One more attempt to open the database
16:34:46 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log'


Elapsed: 00:00:00.13

2nd loggroup this time

16:34:49 SYS@eqdd011g SQL> alter database drop logfile group 2;

Database altered.

Elapsed: 00:00:00.33
16:34:58 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log' size 5m;

Database altered.

Elapsed: 00:00:00.55
16:35:09 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17

Log was current and unable to drop, it was not even archived since it was current

16:35:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance eqdd011g (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.05
16:35:20 SYS@eqdd011g SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open


Elapsed: 00:00:00.02

16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'


Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Database altered.

Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;

Database altered.

16:39:11 SYS@eqdd011g SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ------ ------------- --------------------
2 1 0 5242880 1 YES UNUSED 0
1 1 91 5242880 1 NO CURREN 3220382 24-SEP-2009 16:38:10

2 rows selected.

Elapsed: 00:00:00.06
16:39:28 SYS@eqdd011g SQL> alter database open;

Database altered.