[sql] 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?

This question is related to sql oracle oracle11g

The answer is


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.


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;

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


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.


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;

Questions with sql tag:

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database Export result set on Dbeaver to CSV How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? #1273 – Unknown collation: ‘utf8mb4_unicode_520_ci’ MySQL Error: : 'Access denied for user 'root'@'localhost' SQL Server IF EXISTS THEN 1 ELSE 2 How to add a boolean datatype column to an existing table in sql? Presto SQL - Converting a date string to date format What is the meaning of <> in mysql query? Change Date Format(DD/MM/YYYY) in SQL SELECT Statement Convert timestamp to date in Oracle SQL #1292 - Incorrect date value: '0000-00-00' Postgresql tables exists, but getting "relation does not exist" when querying SQL query to check if a name begins and ends with a vowel Find the number of employees in each department - SQL Oracle Error in MySQL when setting default value for DATE or DATETIME Drop view if exists Could not find server 'server name' in sys.servers. SQL Server 2014 How to create a Date in SQL Server given the Day, Month and Year as Integers TypeError: tuple indices must be integers, not str Select Rows with id having even number SELECT list is not in GROUP BY clause and contains nonaggregated column IN vs ANY operator in PostgreSQL How to insert date values into table Error related to only_full_group_by when executing a query in MySql How to select the first row of each group? Connecting to Microsoft SQL server using Python eloquent laravel: How to get a row count from a ->get() How to execute raw queries with Laravel 5.1? In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function Rebuild all indexes in a Database SQL: Two select statements in one query DB2 SQL error sqlcode=-104 sqlstate=42601 What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types How to run .sql file in Oracle SQL developer tool to import database? Concatenate columns in Apache Spark DataFrame How Stuff and 'For Xml Path' work in SQL Server? Fatal error: Call to a member function query() on null

Questions with oracle tag:

concat yesterdays date with a specific time ORA-28001: The password has expired how to modify the size of a column How to create a blank/empty column with SELECT query in oracle? Find the number of employees in each department - SQL Oracle Query to display all tablespaces in a database and datafiles When or Why to use a "SET DEFINE OFF" in Oracle Database How to insert date values into table error: ORA-65096: invalid common user or role name in oracle In Oracle SQL: How do you insert the current date + time into a table? Extract number from string with Oracle function How to run .sql file in Oracle SQL developer tool to import database? How to kill all active and inactive oracle sessions for user What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? How to subtract hours from a date in Oracle so it affects the day also Why does Oracle not find oci.dll? Oracle SQL - DATE greater than statement How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 How do I view the Explain Plan in Oracle Sql developer? Getting Error - ORA-01858: a non-numeric character was found where a numeric was expected SQLPLUS error:ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA How to change Oracle default data pump directory to import dumpfile? How can you tell if a value is not numeric in Oracle? Why do I get PLS-00302: component must be declared when it exists? Split function in oracle to comma separated values with automatic sequence DATEDIFF function in Oracle How can I solve ORA-00911: invalid character error? sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory Windows cannot find 'http:/.127.0.0.1:%HTTPPORT%/apex/f?p=4950'. Make sure you typed the name correctly, and then try again How do I resolve this "ORA-01109: database not open" error? ORA-01653: unable to extend table by in tablespace ORA-06512 How to Select Top 100 rows in Oracle? How to determine tables size in Oracle Split String by delimiter position using oracle SQL ORA-28000: the account is locked error getting frequently Oracle Add 1 hour in SQL Oracle listener not running and won't start PLS-00428: an INTO clause is expected in this SELECT statement ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM: How to extend? What is the max size of VARCHAR2 in PL/SQL and SQL? How to configure Glassfish Server in Eclipse manually Oracle query to identify columns having special characters The listener supports no services ORA-00907: missing right parenthesis NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle Partition - Error ORA14400 - inserted partition key does not map to any partition Equivalent function for DATEADD() in Oracle Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection? BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

Questions with oracle11g tag:

Convert timestamp to date in Oracle SQL Query to display all tablespaces in a database and datafiles Oracle Installer:[INS-13001] Environment does not meet minimum requirements Forgot Oracle username and password, how to retrieve? Extract number from string with Oracle function How to solve : SQL Error: ORA-00604: error occurred at recursive SQL level 1 Add days Oracle SQL How to determine tables size in Oracle ORA-28000: the account is locked error getting frequently Oracle listener not running and won't start How to calculate difference between two dates in oracle 11g SQL PLS-00428: an INTO clause is expected in this SELECT statement Getting error in console : Failed to load resource: net::ERR_CONNECTION_RESET The listener supports no services NLS_NUMERIC_CHARACTERS setting for decimal copy from one database to another using oracle sql developer - connection failed Oracle SqlDeveloper JDK path Oracle SQL Developer: Failure - Test failed: The Network Adapter could not establish the connection? BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed Default Values to Stored Procedure in Oracle How to call Oracle MD5 hash function? How to find the users list in oracle 11g db? How to find schema name in Oracle ? when you are connected in sql session using read only user Nth max salary in Oracle How to echo text during SQL script execution in SQLPLUS PL/SQL ORA-01422: exact fetch returns more than requested number of rows Could not load file or assembly "Oracle.DataAccess" or one of its dependencies How to create a new schema/new user in Oracle Database 11g? How to move table from one tablespace to another in oracle 11g The network adapter could not establish the connection - Oracle 11g Oracle SQL query for Date format How to update cursor limit for ORA-01000: maximum open cursors exceed SQL Error: ORA-00942 table or view does not exist Grant SELECT on multiple tables oracle how to determine size of tablespace oracle 11g ORA-01017 Invalid Username/Password when connecting to 11g database from 9i client Difference between number and integer datatype in oracle dictionary views Is it possible to use "return" in stored procedure? IO Error: The Network Adapter could not establish the connection Error while trying to retrieve text for error ORA-01019 How to get the last row of an Oracle a table Display names of all constraints for a table in Oracle SQL java.sql.SQLException: Missing IN or OUT parameter at index:: 1 Oracle pl-sql escape character (for a " ' ") MAX(DATE) - SQL ORACLE SQL to add column and comment in table in single command Using SQL LOADER in Oracle to import CSV file ORA-12514 TNS:listener does not currently know of service requested in connect descriptor How can I select from list of values in Oracle Timestamp conversion in Oracle for YYYY-MM-DD HH:MM:SS format