In Oracle : Oracle Physical Database Structure

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

Oracle DBA

Real Application Cluster (RAC) Maintenance tasks Backup and Recovery Database Architecture Replication Oracle Performance Tuning Oracle DBA - Other articles Oracle scripts/ Selects for DBA Oracle Errors (ORA-nnnnn)

The last articles in the site (HOT)

The most visited articles in the site

In-Oracle.com  -> Oracle DBA -> Oracle Database Architecture

-> Oracle Physical Database Structure

 

 Oracle Physical Database Structure

 

 

Questions:

  1. Which are the Oracle Physical Database Structure Components ?

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

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

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

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

  6. How could I know which are the DATA files of the database and their location ?

  7. How could I know which are the TEMP files of the database and their location ?

  8. How could I know which are the CONTROL files of the database and their location ?

  9. How could I know which are the LOG files of the database and their location ?

1.  Which are the Oracle Physical Database Structure Components ?

 

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

Optional: archivelog files, password file

 

 

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.  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>';

 

5.  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. 

 

 

6.  How could I know which are the DATA files of the database and their location ?

 

SELECT FILE_NAME FROM dba_data_files;

 

 

7.  How could I know which are the TEMP files of the database and their location ?

 

SELECT FILE_NAME FROM dba_temp_files;

 

 

8.  How could I know which are the CONTROL files of the database and their location ?

 

SELECT NAME FROM v$controlfile;

 

 

9.  How could I know which are the LOG files of the database and their location ?

 

select MEMBER from v$logfile;

 

 

In-Oracle.com  -> Oracle DBA -> Oracle Database Architecture

-> Oracle Physical Database Structure

Oracle Applications EBS 11i, R12      Oracle Database      Data Warehouse & BI      Oracle Middleware      PL/SQL      Linux ...

 

     Copyright (c) 2011-2015  www.in-oracle.com  |  Disclaimer: The views expressed on this web site are my own and do not reflect the views of Oracle Corporation. You may use the information from this site only at your risk.