Re: Table/index size and number of extents

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Wed, 29 Jul 1998 21:49:39 GMT
Message-ID: <01bdbb4a$97946220$a504fa80@mndnet>

Harsha,

I am not sure what you mean by (query), but you may be looking for this script. You may need to modify it to suit your needs. If this is not want you want, then you need to explain your problem little bit more. I don't believe you need to worry too much about extents and size for query, but you do to create tables and indexes.

set pause off termout off verify off wrap on set newpage 0 pagesize 58 linesize 80

column today            new_value     today     noprint
column time             new_value     time      noprint
 
select   to_char(sysdate, 'dd-MON-yyyy') today,
         to_char(sysdate, 'HH:MI:SS AM') time

  from dual
/

column object           format a26              heading 'OBJECT'
column file_id          format 9990             heading 'FILE|ID '
column block_id         format 999990           heading 'BLOCK|ID '
column blocks           format 999990           heading 'BLOCKS'
column bytes            format 9,999,999,999    heading 'BYTES'
 

spool x.x

ttitle today center 'EFM_HIST TABLESPACE FRAGMENTATION' -

   right 'Page ' format 990 sql.pno skip 1 -    time -
   skip 2 -
   analyze_efm_hist_tablespace_usage.sql skip 2

select   'freespace' owner, '       ' object,
         file_id, block_id, blocks, bytes         bytes

  from sys.dba_free_space
 where tablespace_name = 'EFM_HIST'
union
select substr(owner, 1, 15),

         substr(segment_name, 1, 31),
         file_id, block_id, blocks, bytes         bytes

  from sys.dba_extents
 where tablespace_name = 'EFM_HIST'
order by 3, 4
/
spool off
exit

Good luck !!!

suresh.bhat_at_mitchell-energy.com

Harsha Karan <Harsha.Karan_at_priority-health.com> wrote in article <35BF7CC4.2DE7_at_priority-health.com>...
> Hi
>
> Can anyone suggest a method to calculate the size and number of extents
> for ( a query )
>
> 1. an index
> 2. a table
>
> Thanks in advance
>
> Harsha
>
Received on Wed Jul 29 1998 - 16:49:39 CDT