Re: Schema Size

Hi,
Thanx for your answer. It is very useful, and simple solution, In the mean time I have just discovered different solution to that problem, I will just post it here maybe it will be useful to someone else.

SQL> SELECT tablespace_name,
  2 Sum(bytes)/1024/1024 AS total_size_mb   3 FROM dba_segments
  4 WHERE owner = 'Owner_name'
  5 GROUP BY owner, tablespace_name
  6 /

TABLESPACE_NAME                TOTAL_SIZE_MB
------------------------------ -------------
INDX                                 118,375
RBS                                     18,5
USERS                                189,625

SQL> SELECT owner,
  2 Sum(bytes)/1024/1024 AS total_size_mb   3 FROM dba_segments
  4 WHERE owner = Upper('Owner_name')
  5 GROUP BY owner;

OWNER                          TOTAL_SIZE_MB
------------------------------ -------------
OWNER_NAME                             326,5

By the way I have another question, regarding the dba_ts_quotas view, in that view I can see only few schemas, not all schemas created on my Oracle Istance. Do you maybe know why this is happening? Altough, I'm able to query every single schema with user_ts_quotas view(of course when I'm logged in as that user/schema). ?????????

Regards,
Ermin

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA702E385FE_at_lnewton.leeds.lfs.co.uk>...
> Two simple ways :
>
> select * from dba_ts_quotas where username = 'WHATEVER';
>
> or
>
> log in to the schema in question and do :
>
> select * from user_ts_quotas;
>
>
> Both times you get the quota allowed and space actually used in each
> tablespace in blocks and bytes.
>
>
> Of course, the space used figure may well not be the actual anmount of
> data in all the tables/indexes/etc. This is because the INITIAL extent
> size will be allocated (as will MINEXTENTS) and the space will be marked
> as allocated, even though you don't yet have any data in the table.
>
> HTH
>
> Cheers,
> Norman.
>
> -------------------------------------
> Norman Dunbar
> Database/Unix administrator
> Lynx Financial Systems Ltd.
> mailto:Norman.Dunbar_at_LFS.co.uk
> Tel: 0113 289 6265
> Fax: 0113 289 3146
> URL: http://www.Lynx-FS.com
> -------------------------------------
>
>
> -----Original Message-----
> From: ermin_at_dzinic.com (Ermin) [mailto:ermin_at_dzinic.com]
> Posted At: Wednesday, November 06, 2002 1:12 PM
> Posted To: server
> Conversation: Schema Size
> Subject: Schema Size
>
>
> Hi,
> How can I find out the size of the current schema?
>
> Thanx in Advance,
> Ermin
Received on Thu Nov 07 2002 - 03:01:06 CST