Need Help Please - With Procedures

From: Viraj Luthra <viraj999_at_lycos.com>
Date: Wed, 28 Nov 2001 11:15:00 -0800
Message-ID: <F001.003CF70D.20011128110514@fatcity.com>

Hello all,

I have attached an sql file containing a set of sql's (6) of them, which gives me information regarding table fragmentation. What I need to do is instead of writing seperate sql's, I need to write a procedure, where in I pass the owner and table name and then the result comes out, as you will see in the last sql.

What I need is, if some one could please help in writing a procedure? How to put cursors and use all the information in different cursor variables etc.

Please help.

Thanks.

Rgds,

Raja

Accept owner prompt 'Enter Owner Name ' Accept tnm prompt 'Enter Table Name '

set echo off verify off heading off
set termout off

col val4 new_val hwm_blocks noprint
col val5 new_val above_hwm noprint
col val6 new_val row_chains noprint
col val7 new_val row_size noprint

col val7a new_val pct_used noprint
col val7b new_val pct_free noprint
col val8 new_val num_rows noprint
col val9 new_val row_chains_pct noprint

select  num_rows        val8,
        blocks          val4,
        empty_blocks    val5,
        chain_cnt       val6,
        avg_row_len     val7,
        pct_used        val7a,
        pct_free        val7b,
        100*chain_cnt/num_rows val9

from dba_tables
where table_name = upper('&tnm')
  and owner = upper('&owner');

col val9 new_val block_size noprint
select value val9
from v$parameter
where name = 'db_block_size';

col val10a new_val blocks_alloc noprint
col val10b new_val bytes_alloc noprint
col val10e new_val hwm_bytes noprint
col val10f new_val bytes_used noprint

select &hwm_blocks+&above_hwm val10a,
       (&hwm_blocks+&above_hwm)*&block_size/1024/1024 val10b,
       (&hwm_blocks*&block_size)/1024/1024 val10e,
       (&num_rows*&row_size)/1024/1024 val10f

from dual;

col val11a new_val blocks_pct_used noprint col val11b new_val bytes_pct_used noprint select 100*&hwm_blocks/&blocks_alloc val11a,

       100*&num_rows*&row_size/&hwm_bytes/1024/1024 val11b from dual;

col val12 new_val sf noprint
select count(*) val12
from dba_extents
where segment_name= upper('&tnm')
  and owner = upper('&owner');

set termout on
set echo off feedback off verify off

col bogus format 999,999,999 fold_after

select 'Owner              : '||'&owner' bogus,


       'Table name         : '||'&tnm' bogus,

       'pct_free           : '||&pct_free bogus,

       'pct_used           : '||&pct_used bogus,

       'Number of extents  : '||&sf||' <-- Segment Fragmentation' bogus,

       'Rows               : '||&num_rows bogus,

       'Row size           : '||&row_size bogus,

       'Rows frag:migration: '||&row_chains bogus,

       'Row % frag:migr.   : '||&row_chains_pct||'% <-- Row Fragmentation' bogus,

       'DB block size      : '||&block_size bogus,

       'Blocks alloc       : '||&blocks_alloc bogus,

       'Block HWM          : '||&hwm_blocks bogus,

       '% alloc used by HWM: '||&blocks_pct_used||'%' bogus,

       'MB alloc           : '||&bytes_alloc||'MB' bogus,

       'MB HWM             : '||&hwm_bytes||'MB' bogus,

       'MB used            : '||&bytes_used||'MB' bogus,

       '% HWM bytes used   : '||&bytes_pct_used||'% <-- Block Fragmentation' bogus

from   dual;

prompt *** The table &owner..&tnm must have been recently analyzed for accuracy
prompt *** You may need to ANALYZE TABLE &owner..&tnm DELETE STATISTICS

set echo on verify on heading on feedback on

Received on Wed Nov 28 2001 - 13:15:00 CST