Calculating table space used????

[murraywasley]

Hi there,

I am trying to develop a method of querying a table and calculating the
actual kb of information returned.

For example, a query that returns 540 matches is 100kb of data.

I need to do this so I can calculate and limit the amount of table space one
user can use in my database.

Has anyone done this kind of thing or could someone point me in the right
direct.

Thank you in advance for your help.

Best Regards
Murray Wasley

[svnfightsvn]

Depends on what database you are using:

for oracle:

Try:

$query = "SELECT Tablespace_name,
       Free_space, (total_space-Free_space) as Used_space, total_space
FROM
  (select tablespace_name, sum(bytes/1024/1024) as Free_Space
     from sys.dba_free_space
    group by tablespace_name
  ) Free,
  (select b.name,  sum(bytes/1024/1024) as Total_Space
     from sys.v_$datafile a, sys.v_$tablespace B
    where a.ts# = b.ts#
    group by b.name
  ) Total
WHERE Free.Tablespace_name = Total.name
order by tablespace_name";

$stmt = do_ora_stmt($q);

while (OCIFetchInto($stmt,$r))
{
print round($r['USED_SPACE']/$r['TOTAL_SPACE']." bytes";
}

[jweissig]

for mySQL use

mysql> SHOW TABLE STATUS FROM table_name;

where "table_name" is the name of the table you want the information for 🙂

• Justin