MyNotes

this is a common question most of the interviewer asked how to find the particular schema/owner size ? or  how to get the over all schema/owner size in database? Over all owner size Query SQL> select OWNER,Segment_type,sum(bytes)/ 1024/1024/1024 "SIZE_IN_GB" from dba_segments where segment_type='TABLE' group by owner,segment_type order by owner;     Note: if we want to get particular schema/user size along table only (exclude of index,view ) use segment_type='TABLE' otherwise just remove this segment_type     Particular schema/owner size Query   SQL> select OWNER,sum(bytes)/1024/1024/ 1024 "SIZE_IN_GB" from dba_segments group by owner order by owner;          

we can find out the database size in two types  1) Actual database  size  2) Over all database  Size 1)Actual database size  Query SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files; 2)Over all Database size Query Overall database size is the sum of used space plus free space i.e. the size of the data files, temp files, log files and the control files. You can find out the total database size using simple query. This sql gives the total size in GB.   select ( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) + ( select nvl(sum(bytes),0)/1024/1024/ 1024 temp_size from dba_temp_files ) + ( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) + ( select sum(BLOCK_SIZE*FILE_SIZE_BLKS) /1024/1024/1024  controlfile_size from v$controlfile) "Size in GB" from dual;

By using this query we can able to find the table size by below mentioned table format owner.tablename. Eg - hr.employees query: select segment_name,segment_type, sum(bytes/1024/1024/1024) GB from dba_segments where segment_name='&Your_Table_Name' group by segment_name,segment_type;