Re: Info for row-space-usage in a table

From: Mark D Powell <mark.powell_at_eds.com>
Date: 11 Jun 2002 13:56:32 -0700
Message-ID: <178d2795.0206111256.4997a266@posting.google.com>

Ralf Brunkhorst <eedrab_at_eed.ericsson.se> wrote in message news:<Pine.LNX.4.44.0206111417060.3769-100000_at_localhost.localdomain>...
> Hi,
>
> we have a DB for a complex tool called MATRIX.
> In the tool-db there is a table LXDESC_1C66A382, here some more info:
>
> Tablename blocks extents size rows
> ---------------------------------------------------------
> LXDESC_1C66A382 680704 267 5318MB 187318
>
> desc LXDESC_1C66A382
> Name Null? Type
> ----------------------------------------- -------- ---------------------
> LXOID NUMBER(38)
> LXKIND NUMBER(38)
> LXDESC LONG
>
> There was an inrease of 5000MB (from ~300MB to 5318MB) in 1 week in the table.
> The problem is, which rows take so much space, how can we find the rows, with
> the biggest space usage.
> With the analyse-package from oracle I get only info for average space-usage.
> Has someone an idea or a pl/sql-script?
> Many thanks for your help.

Too bad you are not using a LOB column because you could just use the get size function in the dbms_lob package. With a long I think you would need to read through the table and fetch the long column and display the size. I have this code I saved from a Tom Kyte post on the newsgroups a couple years back that I was going to study one day. Maybe it will help you.

---

piecewise fetching will do it... here is an example. You send showlong a query that selects 1 column (a long) and fetches 1 row (it'll only fetch the first row):

create or replace procedure showlong( p_query in varchar2 ) as

    l_cursor    integer default dbms_sql.open_cursor;
    l_n         number;
    l_long_val  varchar2(250);
    l_long_len  number;
    l_buflen    number := 250;
    l_curpos    number := 0;

begin

    dbms_sql.parse( l_cursor, p_query, dbms_sql.native );

    dbms_sql.define_column_long(l_cursor, 1);     l_n := dbms_sql.execute(l_cursor);

    if (dbms_sql.fetch_rows(l_cursor)>0)     then

        loop
            dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos
,
                                        l_long_val, l_long_len );
            l_curpos := l_curpos + l_long_len;
            dbms_output.put_line( l_long_val );
            exit when l_long_len = 0;
      end loop;

   end if;

   dbms_output.put_line( '====================' );

   dbms_output.put_line( 'Long was ' || l_curpos || ' bytes in length' );

   dbms_sql.close_cursor(l_cursor);
exception

   when others then

      if dbms_sql.is_open(l_cursor) then
         dbms_sql.close_cursor(l_cursor);
      end if;
      raise;

end showlong;
/

If you create it, then you can test it with:

SQL> begin
  2 showlong( 'select text from all_views where rownum = 1' );
  3 end;
  4 /
select OWNER, TABLE_NAME, TABLESPACE_NAME, CLUSTER_NAME, IOT_NAME,

     PCT_FREE, PCT_USED,
     INI_TRANS, MAX_TRANS,
     INITIAL_EXTENT, NEXT_EXTENT,
     MIN_EXTENTS, MAX_EXTENTS, PCT_INCREASE,
     FREELISTS, FREELIST_GROUPS, LOGGING,
     BACKED_UP, NUM_ROWS, BLOCKS, EMPTY_BLOCKS,
     AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN,
     AVG_SPACE_FREELIST_BLOCKS, NUM_FREELIST_BLOCKS,
     DEGREE, INSTANCES, CACHE, TABLE_LOCK, 
     SAMPLE_SIZE, LAST_ANALYZED, PARTITIONED,
     IOT_TYPE, NULL, NULL, TEMPORARY, NESTED,
     BUFFER_POOL

from all_tables
union all
select

"OWNER","TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","PCT_FREE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTENTS","MAX_EXTENTS","PCT_INCREASE
","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","AVG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","
PARTITIONED","IOT_TYPE","TABLE_TYPE_OWNER","TABLE_TYPE","TEMPORARY","NESTED","BUFFER_POOL"

from all_object_tables

---

Long was 1114 bytes in length

PL/SQL procedure successfully completed.  

Thomas Kyte
tkyte_at_us.oracle.com

---

HTH -- Mark D Powell -- Received on Tue Jun 11 2002 - 15:56:32 CDT