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?
--
/Ralf
Received on Fri Jun 21 2002 - 04:48:58 CDT
