Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: how to find bytes used when inserting a row
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
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;
+--------------------------------------------------------------------------+ | 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.
>
>