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

Ralf,

Don't forget to 'set serveroutput on'.

-Mark

Ralf Brunckhorst <Ralf.Brunckhorst_at_eed.ericsson.se> wrote in message news:<aeusqb$d22$1_at_aken.eed.ericsson.se>...
> On Tue, 11 Jun 2002 22:56:32 +0200, Mark D Powell wrote:
>
> > 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 --
>
> Thanks for your info Mark, but see below:
> SQL> desc showlong
> PROCEDURE showlong
> Argument Name Type In/Out Default?
> ------------------------------ ----------------------- ------ --------
> P_QUERY VARCHAR2 IN
>
> SQL> begin
> 2 showlong( 'select text from all_views where rownum = 1');
> 3 end;
> 4 /
>
> PL/SQL procedure successfully completed.
>
> I get no result.
> Whats wrong?
Received on Sat Jun 22 2002 - 08:28:04 CDT