How to display Oracle schema size with SQL query?

Question:

I have a Oracle schema with 70+ tables. I want to create simple page which can display the HDD space occupied by the tables. How I can get this value with SQL query?

P.S And how I can get the Oracle architecture version?

Solution 1:

You probably want

SELECT sum(bytes)
FROM dba_segments
WHERE owner = <>
If you are logged in as the schema owner, you can also

SELECT SUM(bytes)
FROM user_segments
That will give you the space allocated to the objects owned by the user in whatever tablespaces they are in. There may be empty space allocated to the tables that is counted as allocated by these queries.

Solution 2:

If you just want to calculate the schema size without tablespace free space and indexes :

select
sum(bytes)/1024/1024 as size_in_mega,
segment_type
from
dba_segments
where
owner=''
group by
segment_type;
For all schemas

select
sum(bytes)/1024/1024 as size_in_mega, owner
from
dba_segments
group by
owner;
Solution 3:

select T.TABLE_NAME, T.TABLESPACE_NAME, t.avg_row_len*t.num_rows from dba_tables t
order by T.TABLE_NAME asc
See e.g. sqlerrors for more options