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?

This might help: http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle — Andreas Dolk, Mar 12 '12 at 16:50

score 57 · Answer 1 · answered Mar 12 '12 at 16:50

57

You probably want

SELECT sum(bytes)
  FROM dba_segments
 WHERE owner = <<owner of schema>>

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 '12 at 16:50

Justin Cave

227,342
24
367
384

Is there anyway to change the size of a schema? – Mardin Yadegar Dec 13 '16 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. – Justin Cave Dec 17 '16 at 15:03

biology.info · Answer 2 · 2016-07-28T10:48:42.440

16

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='<schema's owner>'
group by
   segment_type;

For all schemas

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

edited Jul 28 '16 at 10:48

answered Jul 28 '16 at 10:29

biology.info

3,500
2
28
39

can you do this for all ORacle schemas – Peter Penzov Jul 28 '16 at 10:30
yeap just remove `WHERE` clause ;) and add a `GROUP BY` owner – biology.info Jul 28 '16 at 10:36

score 3 · Answer 3 · edited Aug 21 '15 at 17:16

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.

score 3 · Answer 4 · answered Mar 12 '12 at 16:57

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. http://www.dba-oracle.com/t_script_oracle_table_size.htm for more options

answered Mar 12 '12 at 16:57

Rostislav Matl

4,294
4
29
53

This does not take into account various overhead used in storing the data such as indexes and block header space. – Mark Stewart Jun 24 '16 at 19:09

score 0 · Answer 5 · edited Feb 12 '19 at 18:43

0

SELECT DS.TABLESPACE_NAME, SEGMENT_NAME, ROUND(SUM(DS.BYTES) / (1024 * 1024)) AS MB
    FROM DBA_SEGMENTS DS
    WHERE SEGMENT_NAME IN (SELECT TABLE_NAME FROM DBA_TABLES) AND SEGMENT_NAME='YOUR_TABLE_NAME'
    GROUP BY DS.TABLESPACE_NAME, SEGMENT_NAME;

edited Feb 12 '19 at 18:43

Blue

22,608
7
62
92

answered Feb 12 '19 at 18:37

Dilip Paudel

19
3

4

Thank you for this code snippet, which might provide some limited, immediate help. A [proper explanation would greatly improve its long-term value](//meta.stackexchange.com/q/114762/206345) by showing _why_ this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Blue Feb 12 '19 at 18:43

How to display Oracle schema size with SQL query?

5 Answers5

Linked

Related