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

Perfect order of ODI export and import

  Smart Export / Import is the recommended way to achieve such a migration. This is the easiest way to make sure all the dependencies are ta...