Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email email@example.com
AW: How to track DB usage of user
From: Zabach, Elke (elke.zabachsap.com)
Date: Tue Sep 20 2005 - 06:44:43 CDT
Ralf Schneider wrote:
> Am Montag, 19. September 2005 17:28 schrieb Robert Klemme:
> > You could do reports like
> > select user, count(*)
> > from your_table
> > to see which user takes up how much space of the table. If you
> > combine this with information about the whole table's size you can
> > even calculate MB a certain user uses.
> But this would be quite inexact. Assume there are several VARCHAR
> may have 1000 characters, but the values stored are only a few
> long. The users would not understand why they reached their limit
> they entered only short texts.
> My idea was to use triggers for all the tables that calculate the
> currently modified record and store this value in a special table.
> be something like
> CREATE TRIGGER trg_update FOR <table> AFTER UPDATE EXECUTE (
> UPDATE users SET db_usage = db_usage - :OLD.size +
> :NEW.size WHERE user_id = :OLD.user_id;)
> Is there a way to dynamically calculate the size of a record? In this
> something like size (:NEW)? Or can I pass :NEW and :OLD to a dbproc?
> Best regards,
1. user-limits (as in Oracle) are able to calculate how much data is
stored in someones schema, but will never be able to see that column xyz
in table abc will be filled with userids/usernames and has to be taken
into account as well. --> even Oracle would not be able to add x bytes
for row 1 to user m's account and y bytes of the next row to someone
2. the statement 'select user, count(*)from your_table' will not do what
was described together with the statement.
The statement will result in the name of the current user and the number
of all rows in the table.
What could do would be
Select <userid-column>, count(*) from your_table group by
3. There is no way to see (with a function or so) the length needed for
storing the whole row in MaxDB.
4. I do not know how many users you have, if rows in your tables can
only be updated/deleted as the user which was given during insertion.
If this were the case I could think of having different tables (one for
each user --> size needed for storing can be calculated) and the
selection is done with a union for all of these tables.
5. The szenario that different people have to share one table, but are
limited to some amount of space, seems to me uncommon. May I ask for
I think that info stored in a table used by different people is really
needed, not existing just for fun and should not be limited to some
But, maybe I am wrong.
SAP Labs Berlin
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb