WORK OUT PRECISELY WHERE IN THE FILE THE EXTENT IS, IN TERMS OF HOW MANY BYTES INTO THE FILE IT BEGINS

5.

Work out precisely where in the file the extent is, in terms of how many bytes

into the file it begins. This requires finding out the tablespace’s block size.

Enter the block_id and tablespace_name returned by the query in Step 3 when

prompted.

select block_size * &block_id from dba_tablespaces

where tablespace_name='&tablespace_name';

The illustration that follows shows these steps, executed from SQL*Plus:

The illustration shows that the table exists in one extent that is 64KB large. This

extent is in the file /home/db11g/app/db11g/oradata/orcl/system01.dbf

and begins about 700MB into the file.

Two-Minute Drill

PA

R

T

I

Single-Instance Architecture

• An Oracle server is an instance connected to a database.

• An instance is a block of shared memory and a set of background processes.

• A database is a set of files on disk.

• A user session is a user process connected to a server process.

Instance Memory Structures

• The instance shared memory is the system global area (the SGA).

• A session’s private memory is its program global area (the PGA).

• The SGA consists of a number of substructures, some of which are required

(the database buffer cache, the log buffer, and the shared pool) and some of

which are optional (the large pool, the Java pool, and the Streams pool).

• The SGA structures can be dynamically resized and automatically managed,

with the exception of the log buffer.

Instance Process Structures

• Session server processes are launched on demand when users connect.

• Background processes are launched at instance startup and persist until

shutdown.

• Server processes read from the database; background processes write to the

database.

• Some background processes will always be present (in particular SMON,

PMON, DBWn, LGWR, CKPT, and MMON); others will run depending on

what options have been enabled.

Database Storage Structures

• There are three required file types in a database: the controlfile, the online

redo log files, and the datafiles.

• The controlfile stores integrity information and pointers to the rest of the

• The online redo logs store recent change vectors applied to the database.

• The datafiles store the data.

• External files include the parameter file, the password file, archive redo logs,

and the log and trace files.

• Logical data storage (segments) is abstracted from physical data storage

(datafiles) by tablespaces.

• A tablespace can consist of multiple datafiles.

• Segments consist of multiple extents, which consist of multiple Oracle blocks,

which consist of one or more operating system blocks.

• A segment can have extents in several datafiles.

Self Test