Hi

How do i check storage used by a particular table , i.e:
tfgld418 ?
I'am using Oracle8i

Pls advice
Thank you and have a nice day

Yours in service
dhnish

If you are looking for a basic info, you can use the tools functions -

db.nr.rows() multiply it by db.row.length()

If you like more info on Oracle database end then you need to use SQL scripting-

Count Table Space Used

Description
This script calculates the actual space used by a table (and not just the space used by all the extents).

This script is useful for sizing estimates where the initial sizes have been overestimated

Parameters
1 - Table Owner
2 - Table Name

SQL Source
REM Copyright (C) Think Forward.com 1998- 2004. All rights reserved.
set verify off
spool size_&&1..lst
set serverout on size 1000000

declare
sql_stmt varchar2(1024);
block_count number;

cursor tab is
select owner, table_name,blocks
from dba_tables
where owner=upper('&&1') and table_name like UPPER('%&2%');

l_db_block_size NUMBER;

begin

dbms_output.put_line('Displaying Actual Space Used for schema &&1 ');
dbms_output.put_line('Log file to Check_counts2.lst ....');
dbms_output.put_line('....');

SELECT value INTO l_db_block_size
FROM v$parameter WHERE name='db_block_size';

FOR tab_rec IN tab LOOP

BEGIN

sql_stmt:='select count(distinct( dbms_rowid.rowid_block_number(rowid) )) from '
||tab_rec.owner||'.' ||tab_rec.table_name;

EXECUTE IMMEDIATE sql_stmt INTO block_count;

dbms_output.put_line(rpad(tab_rec.table_name,30)
||' '||TO_CHAR(block_count*l_db_block_size)/1024/1024||' Mb'
||' ('||TO_CHAR(block_count*l_db_block_size)/1024||' Kb)'
||' Analyze:'
||TO_CHAR(tab_rec.blocks*l_db_block_size)/1024/1024||' Mb'
||' ('||TO_CHAR(tab_rec.blocks*l_db_block_size)/1024||' Kb)'
);

exception when others then
dbms_output.put_line('Error counting rows for table '||tab_rec.table_name||' '||SQLERRM);

END;

end loop;

end;
/

spool off

Man that's complicated... how about

select bytes from dba_segments where table_name like 'TTFGLD410%'

That will show you the amount of space "in bytes" the table
takes up in the database..

Dave

its working fine,but am I could not see the tableor view dba_segments in system/manger.

Jack -

That was for Oracle.. Are you SQL2000?

If you're oracle and system can't see the dba_* views then something's messed up!

Dave

You got to have privileges to see the dba_segments view in oracle.

If you have a DBA role you should be able to view this view.

-Jalandhar

What abt SQL server 2000? How can I know the size of a particular table??

rgds,
Jack