Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: how to find bytes used when inserting a row

Re: how to find bytes used when inserting a row

From: Vitaliy Mogilevskiy <vit100gain_at_earthlink.net>
Date: Mon, 12 Apr 1999 12:33:45 -0700
Message-ID: <37124A98.57882B03@earthlink.net>


You can use this script:


rem

rem     Script:     c_unused.sql
rem     Created:    Vitaliy Mogilevskiy (vit100gain_at_earthlink.net)
rem
rem     This script used to get the space usage (High-water mark)
rem     for TABLE, INDEX or CLUSTER
rem     The high-water mark for a table is the difference between the
rem     TOTAL_BLOCKS value and UNUSED_BYTES value returned by this
procedure call.
rem     The UNUSED_BLOCKS value represents the number of blocks above
rem     the high-water mark; the TOTAL_BLOCKS value reflects the total
rem     number of blocks allocated to the table
rem     After executing this script you can reclaim space from a TABLE
rem     For example:
rem     TOTAL_BLOCKS = 200
rem     UNUSED_BLOCKS = 100
rem     if the database BLOCK size = 4K, then 100 blocks--400K--could be
reclaimed
rem     if you want to leave 20 blocks within the table as unused space
above the
rem     high-water mark, you can alter the table, specifying that the
database
rem     keep 20 blocks--80K:
rem     SQL> alter table <TABLE_NAME> deallocate unused keep 80K;
rem
rem

set term on
set serveroutput on
set feedback on
set echo off

declare

var1                    number;
var2                    number;
var3                    number;
var4                    number;
var5                    number;
var6                    number;
var7                    number;

begin
dbms_space.unused_space(upper('&owner'),upper('&&object_name'),upper('&object_type'),

                                VAR1,VAR2,VAR3,VAR4,VAR5,VAR6,VAR7);

        dbms_output.put_line('OBJECT_NAME               =
&&object_name');
        dbms_output.put_line('--------------------------------------');
        dbms_output.put_line('TOTAL_BLOCKS              = '||VAR1);
        dbms_output.put_line('TOTAL_BYTES               = '||VAR2);
        dbms_output.put_line('UNUSED_BLOCKS             = '||VAR3);
        dbms_output.put_line('UNUSED_BYTES              = '||VAR4);
        dbms_output.put_line('LAST_USED_EXTENT_FILE_ID  = '||VAR5);
        dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||VAR6);
        dbms_output.put_line('LAST_USED_BLOCK           = '||VAR7);
end;
/
undefine object_name
undefine owner
undefine object_type

it will prompt you for OWNER, OBJECT_NAME, OBJECT_TYPE
+--------------------------------------------------------------------------+

| Vitaliy Mogilevskiy
| Senior Consultant
| CORE Technology Group, Inc.
| E-mail:    vit100gain_at_earthlink.net
| Web Page:  http://home.earthlink.net/~vit100gain/index.html

|            *** Free DBA Script Library at my Web Page ***
+--------------------------------------------------------------------------+

butun wrote:

> Hi
> i need to know the space occupied in bytes when i insert a new
> row in a table. Can anybody give be the SQL query which will
> be able to return the actual size in bytes occupied by the table.

>

> This way i could insert a 1000 rows and measure the size before and
> after to determine the space occupied by each inserted row.
>

> thanks

>
> Butun
Received on Mon Apr 12 1999 - 14:33:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US