CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Peter Penzov
Peter Penzov

Reputation: 1658

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?

Upvotes: 30

Views: 263433

Answers (5)

Dilip Paudel
Dilip Paudel

Reputation: 39

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;

Upvotes: 0

biology.info
biology.info

Reputation: 3569

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;

Upvotes: 19

Bhaskar
Bhaskar

Reputation: 41

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.

Upvotes: 4

Rostislav Matl
Rostislav Matl

Reputation: 4543

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

Upvotes: 3

Justin Cave
Justin Cave

Reputation: 231651

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.

Upvotes: 58

Related Questions