How to display Oracle schema size with SQL query?

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?

7,966 18 18 gold badges 90 90 silver badges 174 174 bronze badges asked Mar 12, 2012 at 16:46 Peter Penzov Peter Penzov 1,378 147 147 gold badges 486 486 silver badges 880 880 bronze badges This might help: stackoverflow.com/questions/264914/… Commented Mar 12, 2012 at 16:50

5 Answers 5

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.

answered Mar 12, 2012 at 16:50 Justin Cave Justin Cave 231k 25 25 gold badges 374 374 silver badges 390 390 bronze badges Is there anyway to change the size of a schema? Commented Dec 13, 2016 at 16:23

@MardinYadegar - If you change the size of any of the objects in the schema y forcing them to add or remove a segment, you'll change the size of the schema.

Commented Dec 17, 2016 at 15:03

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;

answered Jul 28, 2016 at 10:29 biology.info biology.info 3,548 2 2 gold badges 29 29 silver badges 40 40 bronze badges can you do this for all ORacle schemas Commented Jul 28, 2016 at 10:30 yeap just remove WHERE clause ;) and add a GROUP BY owner Commented Jul 28, 2016 at 10:36

SELECT table_name as Table_Name, row_cnt as Row_Count, SUM(mb) as Size_MB FROM (SELECT in_tbl.table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from ' ||ut.table_name)),'/ROWSET/ROW/C')) AS row_cnt , mb FROM (SELECT CASE WHEN lob_tables IS NULL THEN table_name WHEN lob_tables IS NOT NULL THEN lob_tables END AS table_name , mb FROM (SELECT ul.table_name AS lob_tables, us.segment_name AS table_name , us.bytes/1024/1024 MB FROM user_segments us LEFT JOIN user_lobs ul ON us.segment_name = ul.segment_name ) ) in_tbl INNER JOIN user_tables ut ON in_tbl.table_name = ut.table_name ) GROUP BY table_name, row_cnt ORDER BY 3 DESC;``

Above query will give, Table_name, Row_count, Size_in_MB(includes lob column size) of specific user.