Oracle Database Discussions

RDBMS Version: 11.2.0.3

Platform : Oracle Linux 6.2

The below query gives the size of schemas. But if there are any big tables in these schemas which can be shrunk (ie. High water mark  being reset) then actual size of these schemas will be less than shown below.

Is there a way to find the size of a schema  as if HWM in all the tables in it were reset ?

SQL> select owner, sum(bytes/power(1024,3)) gb from dba_segments group by owner order by gb desc;

OWNER                                  GB

------------------------------ ----------

SBTOEL                         1798.49951

MIGPHS                         228.772522

SYS                            22.4576416

MIGSTATIC                      4.79718018

MIGRFS                         2.39648438

SYSTEM                         .523681641

SYSMAN                         .277893066

.

.

.