Instance and SGA

The database stores data in files on the hard disk and allows access to those

files via the operating system. To enable efficient operation

Oracle uses shared memory available to all users

A database called the Global Shared Area (SGA)

Area). The Oracle instance is the above-mentioned background processes and

The allocated global SGA shared area. The user connects to the database

The data does not download data directly from the database file. SQL command

the user sends to the appropriate buffer in the SGA structure, a

Then, after processing and analyzing, blocks are taken from the data file to

SGA area. Only then will the information be redirected to the user. IN

The case where the data referenced by the user is already in the domain

SGA, the download phase of the file is omitted and the information is immediately returned,the user. Oracle relational database uses the standard language

SQL queries, and has built-in internal procedural language

PL / SQL stored procedures. Since version 8i as the language for creating stored procedures

Oracle databases can also use Java.


Sessions and transactions

Client architecture – Oracle database server for many users

Simultaneous access to the same database. Read operations of the same data by many

At the same time, they do not cause conflicts or inconsistencies. Otherwise, it is

has to modify the same data. Then they could occur

inconsistencies. In order to preserve the integrity of the data at the time of sampling

The modification introduced a transaction mechanism, as an entity, under which

Users perform their operations in the database. In conflict situations the system

Database management (DBMS) serializes the operations of various transactions in such a way,

No inconsistencies in data. Moment of connecting user to base

Data is also the beginning of a user’s session. It lasts until the moment

Finishing work with the database. In parallel, one user may have open access

more than one session. Within one session, the user can execute one or more

more transactions, one by one. A transaction is a unit of user interaction with the database

and consists of individual operations performed in the database. User

It executes its transactions either by the SQL commands that are directed

directly to the database management system (DBMS), or indirectly – using

Previously prepared database applications, referring to the DBMS.

Data lock mechanism

Transactions performing read operations do not affect each other, their actions

do not collide. The problem occurs in the case of changing operations

At the same time, the same data in the database. If two users simultaneously change two

Different objects in the database do not cause conflicts. If the two operations

will deal with the same object, resulting in a conflict situation. To avoid

Deadlock caused by such situations Oracle has developed a complex mechanism

locking objects. In case when the first transaction starts

The second user can read the data just modified by

First, but can not change them until the first transaction ends

user. The second user’s request will be queued as pending


users and diagrams

When a database user creates an object, it becomes its owner at the same time.

These objects are created in the so-called user schema, ie logical space

Database. User schema is created automatically when defining

The user has a unique name that is identical to the username.

The schema name is used to indicate the objects of the database

owner of the user. Applies to another object

Object names must be unique within the schema. Two users can

Have an object with the same name in your schemas, but they will vary

to cancel them. If Kowalski and Nowak are in their schemes

Objects called Goods can be appealed to them (assuming they have to do so)

permission) by adding a friend’s name in front of the object’s name.

User referring to his object calls him:


Database objects

In the Oracle database, there are many different objects to store

The data itself and functions supporting the management of this data.

All objects are created by database users. The user must have

However, the permission granted to create the object type. In Oracle

You can define such objects as:

Tables  – are used to store data.

Indexes  – are data structures that increase the speed of execution of operations on


Temporary tables  – Used to store the data needed for a time

transaction or one session and to assist in the management of such data:

Views  – This is a logical structure, a virtual table computed on the fly, determined by

SQL query allows access to a subset of columns and rows of a table or table.

Sequences  – Sequence is a database object that generates consecutive numbers. Sequences are

Used primarily to create further value for artificial keys


Trigger  – this is a procedure that is performed automatically as a response to certain

events in the database table.


A table is an unordered collection of records of the same type. This field

a single indivisible element of information, comprising:

Names   – identifying field,

Type  – specifies the set of values that this field can accept,

It sc  – information which is stored in the field.

Record  – an ordered set of different fields.

Column  – a collection of fields of the same type within the scope of all records



Global SGA memory area

SGA (System Global Area) is a memory buffer allocated at startup of an instance. Store information shared by different database processes. stores:

  • Copies of disk data (in the data buffer in SGA)
  • Journal log repetition
  • Execution plans for SQL queries

In general, everything that is needed is a database to operate on and must be at hand (in memory available to the server).


In general, the SGA is divided into several parts.

Data buffer – data that we query, not always have to read from the disk. If someone had previously accessed data that was not in this buffer, the data had to be read from the disk and then dumped into the data buffer. This way, when the next person requests the same data, they will no longer have to read them from the disk, as they will be available in the data buffer. The time of read operation from memory is incomparably shorter than the time of read operation from disk. It also works the other way. Changed or added data is not immediately packaged to disk, they are changed first in the data buffer, so they are faster. Of course, at some point, it writes to disk a special process (DBWR). Notice that not all data can be stored here even because of the capacity of such a buffer. Data is written to disk in three cases. Cyclically, every 3 seconds (DBWR) is written to the disk. If the buffer overflows, the most recently used data is deleted. If any data already in the buffer is used again, it goes to the beginning of the list. This is what makes us a server process. This is called an algorithm LRU  (Last Used Used).

Data is also written to the disk when the duplicate log files end and the data needs to be cleared. DBWR checks if there are still any data to save on the disk, if it is written, and then cleaned up the duplicate log files.

Log repetition log –  all data changes are logged. Even if the changed data has not been permanently saved to disk, we are able to restore it. There are special repetitive log files in which any changes are saved, but the information before it hits it goes to the repetition log buffer.

Library Buffer – If we ask a SQL query for an instance, this query must first be checked for syntax, also if we have the appropriate permissions to read / write specific data. Oracle must also figure out how to execute the command so that it ends as soon as possible. For this purpose, he invents several plans to execute the query and then chooses the most optimal one. If every time Oracle had to perform the same operations for the same query, it would be a waste of time. So he does it the first time, then he just throws it into the library buffer. This means that if another user does the same query, Oracle will reach the library buffer where the query plan has already been developed, whether the query is syntactically correct and does not need to execute it all from the beginning.

Dictionary cache  – There are special dictionaries in Oracle that store objects in the database, which depend on them, and who has access to them. These data need to be used frequently, in fact, every time a query is made. Therefore, these dictionaries are loaded into memory, which makes them faster to access.

There are also other buffs in SGA:

JAVA_POOL –  a buffer used to store Java code. In Oracle we can also store Java classes that need some memory during the execution – this is what the JAVA_POOL buffer is used for.

LARGE_POOL  – An optional buffer that is used for large disk operations such as data backup.

STREAMS_POOL  – A special buffer used for database replication.

The default data buffer has one part –  DEFAULT . If necessary we can still divide it into  KEEP,  RECYCLE , and  nK BUFFER .

KEEP Buffer – Used to store objects that we want to permanently store in memory, so that they are not pushed out by new data blocks. For example, if a table is used frequently, we want to always have access to it. In this case, we need to create such a buffer and specify that this table is to be stored in it.

Buffer RECYCLE  – We can load objects that we use sporadically and we do not want to occupy a valuable place in the DEFAULT buffer. Here we also have to enable the use of this buffer and specify that the object is to be stored in it.

Buffer nK – Buffer for storing objects of non-standard size blocks. We will not be here to deal with it.

PGA memory area

Since the server processes to perform some operations for us also need some amount of memory, the PGA area is allocated to each of them. This is a special dedicated piece of memory in which the server process can:

  • Sort data
  • Hold information about the user session
  • Keep information about variables, arrays, cursors, data processed by the process.

Of course, at the end of a user session, this buffer is no longer needed and the memory allocated to it is released.

Physical structure of the base

Spitfire file – This is a startup parameter file. It provides information on how much space can allocate an instance, how much space is allocated to SGA, PGA and individual buffers, and other parameters such as the number of concurrent sessions etc .. Spfile is hooked up when the instance goes to mount mode. The spfile file contains, among others. The locations of the alert_log file, the control file, and the backup space. Information about the location of the control file (or a few – we may have more) is necessary to pass the instance to MOUNT mode when the control files are mounted.

The parameters saved in the spfile file can be modified in two ways. The first is to use the alter system command.

alter system set parameter = scope value = memory;

Change the parameter to the database restart. Changing the parameter will only affect the currently running instance, after rebooting the value will be set to fix.

alter system set parameter = scope value = spfile;

Change the parameter from the database restart. Executing a command with the scope = spfile parameter will make the parameter change only in the spfile file. Since this file is read at startup, the change will take effect the next time the instance is started.

alter system set parameter = scope value = both;

Change the parameter for the current startup and restart.

alter system set parameter = value;

without scope by default scope = both.


The second way to change the parameters is to create a Pfile file based on Spfile and

Manually modify the file. After modifying Pfile, run the instance with its use to verify that the base will stand up completely with the use of

new parameters. If the database started correctly we create Spfile based on Pfile.



Check file – stores very relevant information about the database,

including the location of the data files, the DBID of the database, and the last SCN. By default, this file also serves as a backup repository (location and content information, does not contain backups itself):). In Express Edition we have one control file, two versions of Standards and Enterprise, though both are on the same disk. Both are mirrored copies of themselves. Because these files are so sensitive, it is important for XE to duplicate the control file, and for Standard and Enterprise, make sure that these files are located on different disks.

The alert log file –  contains the most important information about what is happening in the database, status messages, errors, damages. All of the alter database administrator and alter system commands are stored there. If you want to know its location on the disk, use the command show parameter background_dump_dest. In the directory whose address will be returned after this command, there should be a file called alert_SID.log. The SID should be located in our database. For example, the alert for the CRM database log will be called alert_CRM.log.


Duplicate log files – files that store information about all changes to the database. Each database must have at least two duplicate log files because they operate alternately. When one of the files is full, the next entries are placed in the second.

Archived Replicate Log Files – By default, repetitive log files are overwritten when they are full, and previous content is lost. However, we have the ability to preserve change information using the archivelog mode. In this mode, before copying the duplicate log files, copies are made of archives that are stored together with the backups. By default, the database is in noarchivelog (archivelogs are not produced). Archivelogs are needed if you want to make hot backups. They are also necessary if you want to play the base just before the crash. This is possible because archivelogs have a history of what was happening in the database. In the event of a failure, the playback is performed in such a way that the status of the backup data files is restored first, Then the changes are made from the time the backup was made to just before the archivelog crash. To go to archivelog mode we put the base first.


Then we raise the base to mount mode, where we can enable the archivelog generation by using the alter database archivelog command. At the end, we go back to open mode.


Data files -plics in which all data stored in the database is stored. One data file can only be assigned to one tablespace, but a single tablespace can consist of multiple files. Data files can automatically increase their size when they run out of space, if the administrator makes use of the autoextend command.