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" <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
