table size

Hi

I have empty tables for which the structure is
defined. I want to know
What is the max. bytes a row of a table would
occupy?

regards
hrishy

Hello,

Try this for tables:

select c.OWNER W_OWNER2,
c.TABLE_NAME W_TABLE_NAME2,count(*) NB_COL2,
sum(decode(substr(c.DATA_TYPE,1,1),
'N',trunc((nvl(c.DATA_PRECISION,0)+1)/2,0)+1,
'D',7,nvl(c.DATA_LENGTH,0))) W_REC_MAX_SIZE
from SYS.DBA_TAB_COLUMNS c, SYS.DBA_TABLES t
where c.OWNER not in ('SYS','SYSTEM','PATROL')
and c.table_name=t.table_name
and c.owner=t.owner
group by c.OWNER, c.TABLE_NAME
having sum(decode(substr(c.DATA_TYPE,1,1),
'N',trunc((nvl(c.DATA_PRECISION,0)+1)/2,0)+1,
'D',7,nvl(c.DATA_LENGTH,0)))>0
order by c.OWNER,c.TABLE_NAME;

and this for Index:
select OWNER,I.TABLE_NAME, I.INDEX_NAME,
count(distinct(i.column_name)) COLUMNS,
sum(decode(substr(DATA_TYPE,1,1),
'N',trunc((nvl(DATA_PRECISION,0)+1)/2,0)+1,
'D',7,nvl(DATA_LENGTH,0))) LENGTH
from SYS.DBA_IND_COLUMNS I, SYS.DBA_TAB_COLUMNS T
where OWNER not in ('SYS','SYSTEM','PATROL')
and t.table_name=i.table_name
and t.column_name = i.column_name
and t.owner = i.index_owner
group by OWNER,I.TABLE_NAME,I.INDEX_NAME
having sum(decode(substr(DATA_TYPE,1,1),
'N',trunc((nvl(DATA_PRECISION,0)+1)/2,0)+1,
'D',7,nvl(DATA_LENGTH,0)))>0
order by OWNER,I.TABLE_NAME,I.INDEX_NAME;

(It won't work for column types like 'long, blob, etc...). To retrieve those types of 'exotic' columns:
select OWNER,TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from dba_tab_columns
where data_type not in ('CHAR','DATE','NUMBER','VARCHAR2')
and OWNER not in ('SYS','SYSTEM','PATROL');

Hope it helps :)

Cheers

Fabien

Hi fabien

thankx a lot pal..............that works.............

Just a tiny addition/correction to a nice queries Fabien provided:

When calculating tha max possible length of a column of type NUMBER there is an error if number datatype precision is undefined (unrestricted, that is if you defind a column like "num_col NUMBER"). Fabiens query returns 1 for such a column, but it should return 22. To correct this change the following line

'N',trunc((nvl(c.DATA_PRECISION,0)+1)/2,0)+1,

with this one

'N',nvl(trunc((c.DATA_PRECISION+1)/2+1),c.DATA_LENGTH),

both for tables and for indexes.

Additionaly, when calculating "maximum row length" for indexes (obviously only for leaf blocks rows) one should add the 10 bytes per row that is occupied by the ROWID.

Hi Jurij

what can i say i am already a fan of yours .well i was wundering wheather you are planning to write a book on Oracle DBA how to's


regards
hrishy

As I'm kind of lazy for writing I made up the following plan:

I'll wait until Chris's book comes out, then after a few months I'll ask him how much money have he made with it. If his answer will be at least 5-figure $ number (I'm sure he'll answer honestly as he is all-around good guy), I'll quit my job for a while and start writing.

Just kiding .... ;)

Hi jurij

you seem to be a very interesting guy .do u have a aol messenger pal ..............my id is hrishy4u@aol.com.........
by the jurij think about the fame...............you could perhaphs be alan cox of oracle world..............on the money front i dunno hwo much u will make but yep on the fan front how about a few millions...............