Hello everybody,

I'm trying to make some scripts to get the growing behavior of my database, and then planning some manteniance tasks, and for that I would like to know how to get an accurate row size for the tables in Baan.

The thing is that when I look the tables in Sql*Plus, I can only see the size for a field if it's char type (example: T$CREG CHAR(3)), but if the field is number type, there's no reference to the size, just NUMBER.

The Baan's report "Count number of record per table" brings a column: (Number of record / row size) * 1000 ...Is it the real row size?

I'll really apreciate any advice in this matter.

Thanks in advance.

Eric

You can try the Oracle SQL code -

rem Table Sizes section

column Bytes heading BytesMB format 999,999,999,999
column tablespace_name heading 'Tablespace Name' format a16
column segment_name heading 'Table Name' format a35
column owner heading 'Owner' format a10

prompt ########################################################################
prompt
prompt Table Sizes Report
prompt

col tablespace_name format a20;
col segment_name format a30;
col owner format a8;
set linesiz 132;
set pagesiz 60;

BREAK ON owner ;
COMPUTE SUM OF bytes on owner;

select owner,tablespace_name,segment_name,sum((bytes/1024/1024)) Bytes
from sys.dba_extents
where owner in 'BAAN' and segment_type='TABLE'
group by tablespace_name,owner,segment_name
order by owner,tablespace_name,segment_name, bytes

Hello,

Thanks for your answer. With this script I actually have the size of the tables and the whole database; but, it's is possible to get the long of one record for any give tables? This is what I'm looking for. With this information I can calculate and planning the storage parameters for some of the biggest tables.

If you have any suggestion, I'll be really grateful.

Greetings!

You can add a where condition -

where owner in 'BAAN' and segment_type='TABLE'
and table_name = 'T&1' ;

You can use it in a shell script and &1 (in upper case) is the parameter to the SQL script or you can consider using just one shell script and change that script to prompt and accept parameters.

You have the pieces here and you can tailor to your requirements.

Hello!

I'll try with your suggestion and get the info I need. I'll let you know about it.

Thank you very much for your support!

Eric

If you are in the habit of analyzing your tables, then you can use the tables USER_TABLES or ALL_TABLES. It contains not only average row length ,but also row counts. The analyze command updates the statistics of those tables.

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Thank you Gilbert !

The info in this table is what I'm really looking for. :)

Eric