Re: Need Help Please - With Procedures

From: Robert Chin <chinman_at_optonline.net>
Date: Wed, 28 Nov 2001 15:45:25 -0800
Message-ID: <F001.003CFA86.20011128134530@fatcity.com>

WOW ! all those new column names wth quirky names. Well here is the procdure. make sure you got the GRANTs right in order to compile it.
Also check to make sure I got the your_variable/my_variable match right. hth
robert chin

CREATE OR REPLACE PROCEDURE table_fragmentation_info ( v_table IN VARCHAR2,
  v_owner IN VARCHAR2
)
IS

v_num_rows dba_tables.num_rows%TYPE;
v_hwm_blocks dba_tables.blocks%TYPE;
v_above_hwm dba_tables.empty_blocks%TYPE;
v_row_chains dba_tables.chain_cnt%TYPE;
v_row_size dba_tables.avg_row_len%TYPE;
v_pct_used dba_tables.pct_used%TYPE;
v_pct_free dba_tables.pct_free%TYPE;
v_row_chains_pct NUMBER;
v_block_size v$parameter.value%TYPE;
v_blocks_alloc NUMBER;
v_bytes_alloc  NUMBER;
v_hwm_bytes    NUMBER;
v_bytes_used   NUMBER;
v_blocks_pct_used NUMBER;
v_bytes_pct_used  NUMBER;
v_sf INTEGER;

BEGIN select num_rows,

        blocks,
        empty_blocks,
        chain_cnt,
        avg_row_len,
        pct_used,
        pct_free,
        100*chain_cnt/num_rows
INTO
        v_num_rows,
        v_hwm_blocks,
        v_above_hwm,
        v_row_chains,
        v_row_size,
        v_pct_used,
        v_pct_free,
        v_row_chains_pct

    from dba_tables
   where table_name = upper(v_table)
     and owner = upper(v_owner);

---

select value INTO v_block_size
FROM v$parameter WHERE name = 'db_block_size';

---

v_blocks_alloc := (v_hwm_blocks + v_above_hwm);
v_bytes_alloc := (V_hwm_blocks + v_above_hwm)* v_block_size/1024/1024;
v_hwm_bytes := (v_hwm_blocks * v_block_size)/1024/1024;
v_bytes_used := (v_num_rows   * v_row_size)/1024/1024;
------------------------------------------

v_blocks_pct_used := 100 * v_hwm_blocks / v_blocks_alloc; v_bytes_pct_used := 100 * v_num_rows * v_row_size / v_hwm_bytes/1024/1024;

---

select count(*) INTO v_sf
from dba_extents
where segment_name= upper(v_table)
  and owner = upper(v_owner);

---

dbms_output.put_line('Owner              : '||v_owner);
dbms_output.put_line('Table name         : '||v_table);
dbms_output.put_line('pct_free           : '||v_pct_free);
dbms_output.put_line('pct_used           : '||v_pct_used);
dbms_output.put_line('Number of extents  : '||v_sf||' <-- Segment
Fragmentation');
dbms_output.put_line('Rows               : '||v_num_rows);
dbms_output.put_line('Row size           : '||v_row_size);

dbms_output.put_line('Rows frag:migration: '||v_row_chains); dbms_output.put_line('Row % frag:migr. : '||v_row_chains_pct||'% <-- Row Fragmentation');

dbms_output.put_line('DB block size      : '||v_block_size);
dbms_output.put_line('Blocks alloc       : '||v_blocks_alloc);
dbms_output.put_line('Block HWM          : '||v_hwm_blocks);
dbms_output.put_line('% alloc used by HWM: '||v_blocks_pct_used||'%');
dbms_output.put_line('MB alloc           : '||v_bytes_alloc||'MB');
dbms_output.put_line('MB HWM             : '||v_hwm_bytes||'MB');
dbms_output.put_line('MB used            : '||v_bytes_used||'MB');
dbms_output.put_line('% HWM bytes used   : '||v_bytes_pct_used||'% <-- Block

Fragmentation');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(CHR(10)||'Are you sure you entered FIRST TABLE NAME THEN OWNER NAME correctly ?');

END;
/

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, November 28, 2001 2:05 PM

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robert Chin
  INET: chinman_at_optonline.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Nov 28 2001 - 17:45:25 CST