hi masters,
i always wonder if i could get the exact size of data in a schema and exact size of a database with following queries..i will be thankful if anyone would tell me what goes wrong in the following query as it shows different size of data in schema..and any query to find exact size of my database (not files only data).....
SQL> ed
Wrote file afiedt.buf
1 select d.owner,sum(u.bytes)/1024/1024 "MB USED"
2 from dba_segments d,user_segments u
3 where d.tablespace_name=u.tablespace_name
4* group by d.owner
SQL> /
OWNER MB USED
------------------------------ ----------
MDSYS 9484.375
TSMSYS 303.5
DMSYS 303.5
OUTLN 4027
CTXSYS 5614.75
OLAPSYS 18892.875
*VD 503.375*
SYSTEM 132100.625
EXFSYS 4400.75
DBSNMP 1896.875
ORDSYS 607
OWNER MB USED
------------------------------ ----------
SYSMAN 57665
XDB 57133.875
SYS 543965.5
WMSYS 8346.25
15 rows selected.
SQL> select sum(bytes)/1024/1024
2 from dba_segments;
SUM(BYTES)/1024/1024
--------------------
1353.875
SQL> select owner,sum(bytes) from dba_segments
2 group by owner;
OWNER SUM(BYTES)
------------------------------ ----------
MDSYS 34013184
TSMSYS 262144
DMSYS 262144
OUTLN 524288
VD 519897088
CTXSYS 4849664
OLAPSYS 16318464
SYSTEM 23265280
EXFSYS 3801088
SCOTT 393216
DBSNMP 1638400
OWNER SUM(BYTES)
------------------------------ ----------
ORDSYS 524288
SYSMAN 54067200
XDB 50397184
SYS 702218240
WMSYS 7208960
16 rows selected.
i would like to know about the one ijn bold...
can we get exact size of database by querying
select sum(bytes)/1024/1024 "size in Mb" from dba_segments;
???
i know there are lots of site giving same queries.....but i trust oracle forums....
any help will be appreciable...
thanks and regards
VD
Edited by: vikrant dixit on Apr 9, 2009 12:06 AM