Find Oracle DB table size

To find the size of all the tables in a DB:

select owner, segment_name, sum(bytes)/1024/1024/1024 from dba_segments group by owner, segment_name order by sum(bytes)/1024/1024/1024 desc;

To find the size of a specific table in a DB:

select owner, segment_name, sum(bytes)/1024/1024/1024 from dba_segments where segment_name='&segment_name';

Find Oracle DB name as a normal user

During certain situations, we may be required to find the DB name as a normal user (which does not have 'sysdba' privilege or access on v$/dba views).

In such cases, the below queries will help us with the DB name along with the domain name

select ora_database_name from dual;

select * from global_name;

List of DB objects in a datafile

set pages 1000
set lines 500
col owner format a20
col segment_name format a50
select distinct a.file_id,a.owner, a.segment_name,a.segment_type
from dba_extents a, dba_data_files b
where a.file_id=b.file_id
and b.file_name='/data/orcl/sysaux01.dbf';

Using the above query, we can find the following data:

Ø Owner                     - Schema owner

Ø file_id                      - Datafile number

Ø Segment_name   - DB object name

Ø Segment_type     - DB object type