Re: Estimating table size used and unused for sizing

From: Matt (zagnimp@aom.bt.co.uk)
Date: Thu Aug 30 2001 - 09:59:35 PDT

  • Next message: Yves Haegeman: "Re: [suse-oracle] oracle installation problems on suse7.2" 

    Message-ID: <3B8E70F7.255E1766@aom.bt.co.uk>
Date: Thu, 30 Aug 2001 17:59:35 +0100
From: Matt <zagnimp@aom.bt.co.uk>
Subject: Re: Estimating table size used and unused for sizing

    Hi

    Done it to a certain extent... (exc the pun).

    Now any ideas for calculating the best value for the extent ?
    (replace <TABLE_OWNER> with your own).

    select owner, segment_name, partition_name, segment_type, max_extents,
    nbr_extents,
           trunc(sum((nbr_extents/max_extents) * 100),2) as nbr_percent
        from
        (
    select s.owner as owner,
           s.segment_name as segment_name,
           s.partition_name as partition_name,
        s.segment_type as segment_type,
        s.max_extents as max_extents,
           count(distinct e.extent_id) as nbr_extents
    from dba_segments s, dba_extents e
    where s.segment_type <> 'CACHE'
    and e.owner = s.owner
    and e.owner ='<TABLE_OWNER>'
    and e.segment_type = s.segment_type
    and e.segment_name = s.segment_name
    and nvl(e.partition_name, '~') = nvl(s.partition_name, '~')
    group by s.owner, s.segment_name, s.partition_name, s.segment_type,
    s.max_extents
    having count (distinct e.extent_id) > (s.max_extents -505)
    )
    group by owner, segment_name, partition_name, segment_type, max_extents,
    nbr_extents

    Regards

    Matt

    Matt wrote:

    > Hi,
    >
    > I have this script any ideas on how it can be adapted
    > for table_names ?
    >
    > Regards
    >
    > Matt#
    >
    > COLUMN dummy NOPRINT
    > COLUMN pct_used FORMAT 999.9 HEADING "%|Used"
    > COLUMN name FORMAT a16 HEADING "Tablespace Name"
    > COLUMN Kbytes FORMAT 999,999,999 HEADING "KBytes"
    > COLUMN used FORMAT 999,999,999 HEADING "Used"
    > COLUMN free FORMAT 999,999,999 HEADING "Free"
    > COLUMN largest FORMAT 999,999,999 HEADING "Largest"
    > BREAK ON report
    > COMPUTE sum OF kbytes ON REPORT
    > COMPUTE sum OF free ON REPORT
    > COMPUTE sum OF used ON REPORT
    > SELECT
    > NVL(b.tablespace_name,nvl(a.tablespace_name,'UNKOWN')) name
    > , kbytes_alloc kbytes
    > , kbytes_alloc-NVL(kbytes_free,0) used
    > , NVL(kbytes_free,0) free
    > , ((kbytes_alloc-NVL(kbytes_free,0))/kbytes_alloc)*100 pct_used
    > , NVL(largest,0) largest
    > FROM ( SELECT SUM(bytes)/1024 Kbytes_free
    > , MAX(bytes)/1024 largest
    > , tablespace_name
    > FROM
    > -- sys.dba_free_space
    > sys.dba_segments
    > GROUP BY tablespace_name
    > ) a
    > , ( SELECT SUM(bytes)/1024 Kbytes_alloc
    > , tablespace_name
    > FROM sys.dba_data_files
    > GROUP BY tablespace_name
    > ) b
    > WHERE a.tablespace_name (+) = b.tablespace_name
    > /
    >
    > Matt wrote:
    >
    > > Hi,
    > >
    > > I am trying to estimate the space required for a table
    > > without running into max extents problem.
    > >
    > > I would like to see the results as
    > > Table_Name, Rows, Used_K, Remaining_K, Precent_Unused_K
    > > or to give an estimate of what size the table should be on expected
    > > number of entries (rows) perday without runing into max extents problems.
    > >
    > > The Script below gets initial extent sizes for dd segments
    > > and produces the calculation in K's but it does not
    > > solve the problem.
    > >
    > > Any ideas ?
    > >
    > > select
    > > s.segment_name,
    > > s.bytes / 1024 || 'K' suggest1,
    > > ceil(s.bytes / (10 * p.value)) * (p.value / 1024) || 'K' suggest2
    > > from
    > > sys.dba_segments s,
    > > sys.v_$parameter p
    > >
    > > Regards
    > >
    > > Matt

    This archive was generated by hypermail 2.1.0 : Thu Oct 11 2001 - 14:54:23 PDT