Calculating size of Oracle Database

select decode(least(sum(bytes), 1000000000),
1000000000, ROUND(sum(bytes)/1000000000) || 'GB',
ROUND(sum(bytes)/1000000) || 'MB') as database_size
from dba_segments;

The above query has the nice effect of displaying the size of the database in a human readable format.

DATABASE_SIZE
------------------------------------------
151GB

1 rows selected

Oracle SQL Schema and Text Index Size

I recently had to determine the size of my schema and the size of the text indexes used in that schema. These SQL statements got me the results:

SQL> select sum(bytes) from dba_segments where owner='SCHEMA_NAME' and SEGMENT_NAME like '%$%';

SUM(BYTES)
----------
6663307264

SQL> select sum(bytes) from dba_segments where owner='SCHEMA_NAME';

SUM(BYTES)
----------
9464446976