How to display Oracle schema size with SQL query?

sql oracle oracle11g
Peter Penzov picture Peter Penzov · Mar 12, 2012 · Viewed 197.5k times · Source

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?

Answer

Justin Cave picture Justin Cave · Mar 12, 2012

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.

Related questions

How do I do top 1 in Oracle?

How do I do the following? select top 1 Fname from MyTbl In Oracle 11g?

Read More
Display names of all constraints for a table in Oracle SQL

I have defined a name for each of the constraint for the multiple tables that I have created in Oracle SQL. The problem is that to drop a constraint for the column of a particular table I need to know …

Read More
Timestamp conversion in Oracle for YYYY-MM-DD HH:MM:SS format

I'm trying to insert records with YYYY-MM-DD HH:MM:SS format into Oracle from Netezza, but I'm getting invalid date type. How can I accomplish this one? CREATE TABLE AM_PROGRAM_TUNING_EVENT_TMP1 ( ST TIMESTAMP, ET TIMESTAMP, MAS_DIV_…

Read More