Oracle Physical Database Structure (Q & A) 

1.  How could I know the real size of a particular database (on the disks) ?

  SELECT sum(a.log_space+b.data_space+c.tempspace) "Total_DB_Size (G)"

     FROM

        (select round(sum(bytes/1024/1024/1024),2) data_space from dba_data_files ) b,

        (select round(sum(bytes*members/1024/1024/1024),2) log_space from v$log ) a,

        (select nvl(round(sum(bytes/1024/1024/1024),2),0) tempspace from dba_temp_files) c;

            NOTE: However, the temporary space and log space could be not added. If we want we can add also the space for the archive logs.

2.  How could I know in which file a particular table is stored ?

SELECT distinct f.FILE_NAME

FROM dba_extents e,

           dba_data_files f

WHERE f.file_id = e.file_id

and e.OWNER = '<Schema_Name>'

and e.SEGMENT_NAME = '<Table_Name>';

3.  How could I know the real size of a particular table (on the disks) ?

SELECT round(SUM(BYTES)/1024/1024,2) "Table Size(M)"

FROM DBA_EXTENTS

WHERE OWNER = '<Schema_Name>'

     AND SEGMENT_NAME = '<Table_Name>';

4.  Which are the Oracle Physical Database Structure Components ?

Mandatory: data files, redo log files, control files, parameter files

Optional: archivelog files, password file

5.  How could I know the real size of a particular schema (on the disks) ?

  SELECT round(SUM(bytes)/1024/1024,2) AS "Schema Size(Mb)"

  FROM DBA_SEGMENTS

  WHERE owner = '<Schema_Name>';

More information about  this subject ( Oracle Physical Database Structure ) you can get from  www.in-oracle.com