Saturday, August 25, 2018

Sql query for calculating the size of the current database, schema and tables

The size of the database is the space the files physically consume on disk.
Below is the query to get the size:

select sum(bytes)/1024/1024 size_in_mb from dba_data_files;

We can get the total space used with the below query:
select sum(bytes)/1024/1024 size_in_mb from dba_segments;

We can get the space used by individual schema with the below query:
select owner, sum(bytes)/1024/1024 Size_MB from dba_segments group by owner;

SQL query to check table size in Oracle database:
SELECT segment_name, segment_type, bytes/1024/1024 MB
FROM user_segments WHERE segment_type='TABLE' AND segment_name='Your Table Name';

SQL query to get top 10 tables by size in Oracle database:
select * from (select owner, segment_name, bytes/1024/1024 MB from dba_segments where
segment_type = 'TABLE' order by bytes/1024/1024 desc) where rownum <= 10;

No comments:

Post a Comment

  Opatch reports 'Cyclic Dependency Detected' error when patching ODI   Issue: When applying a Patch Set Update (PSU) to WebLogic Se...