Oracle Database Architecture Physical and Logical

Database is combination of physical and logical storage structure. pictorial view will look like below figure.

DBA will be able see both views but for developer physical view is unknown and it won't affect its functionality as it whether it is moved to re-organized.

we can see there is one dotted line between datafile and segment, because its non -direct relationship as tablespace contains many segments and same is made up of many datafiles.

Logical View

Tablespace: Tablespace is collection of number of segments which may contain one or more datafiles depending on type of tablespace i.e. normal or smallfile will have number of datafiles and bigfile tablesapce will have single datafile with huge maxsize. tablespace names are unique in database.

we can query the tablespace names using dba_tablespaces view

SQL> select distinct tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSAUX

TEMP

USERS

SYSTEM

UNDO

Segment: This entity represent any database objects say table, index that store data and hence required space in tablespace. Many segments will combine in single tablespace.

Extent: Managing space one block at a time is difficult task so oracle blocks are grouped together to form extent. Extent is consecutively numbered Oracle Blocks within on datafile. Every segment is consisting of number of extents.

Oracle Block: This is smallest logical unit in the oracle database. size of the oracle block is fixed for a tablespace, you can say it is fixed across all tablespace in a database, generally it is 8 KB. There could be number of rows in single block, but when session request for row it will fetch whole block from disk into db buffer cache, similarly when row or column is changed in db buffer cache, the DBWn will write whole block back into datafile from which it came, overwrites original one.

Size is defined by DB_BLOCK_SIZE which can never be modified once database is created.

Physical view:

Datafile: This is physical entity made up of number of operation system blocks. structure between datafile and operating system block totally depends on operating system file system.

for example -

MS-DOS FAT file system- file size upto 4GB only.

NTFS (windows), ext3(Linux)- no practical limit

Raw devices /ASM : popularity of raw devices is getting lesser day by day due to manageability issue and automatic storage management.

Operating System Block : unit of I/O for file system.  operations wise it looks same as oracle block, if process wants only one byte from disk but I/O will have to read while os block. default operating system block size is 512B to 1KB depending on devices used.

 

Identifying physical location of segments extents

First commands create new table techon_user.techon_tb

second query shows it has just one extent (number 0), it’s in file 5, size is 8 block long starting from block number 97, size of extent is 64KB,

Third query allocates another extent to segment. next query shows new extent number 1 though first is not full. 

Last query shows physical location of file id 5..

Comment below in case additional information is required.