table size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: table size

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    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

  2. #2
    Join Date
    Apr 2001
    Posts
    107
    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

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    Hi fabien

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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    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

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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 ....

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828

    Talking

    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...............

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width