What is Shared Pool in Oracle?

Shared memory area Shared Pool

The Shared Pool is made up of two performance-related memory structures:

    Row cache, discussed below.
    Cache "library" examined in the second advanced tutorial on oracle architecture.

Row cache.

When a user submits an SQL query, the server process extracts, during the analysis of the query, information stored in the dictionary tables (user account data, names of data files, names of table segments and indexes , extents locations, table descriptions and user privileges…).

This information is cached in the dictionary for reuse. During subsequent parse scans, the server process searches the dictionary cache for information to resolve object names and validate access.


Caching data dictionary information reduces response time to LMD statements (SELECT, INSERT, UPDATE, DELETE). A sufficient size of this cache contributes significantly to improving performance.

If the dictionary cache is of limited size, recursive calls slower than the queries made directly in the cache, will be operated by the server process on the database dictionary (disk access).

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is SGA in Oracle?

The SGA (System Global Area) in Oracle Instance

System Global Area (SGA) memory is memory shared by all server and background processes.


The SGA is made up of three mandatory components and three optional elements.

Mandatory components:

     Shared memory area
     Database buffer cache
     Redo log buffer

Optional items:

     LARGE POOL memory area
     Java memory area (Java Pool)
     Streams memory area (streams pool)

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is Oracle instance

What is Oracle instance?

An Oracle server is made up of an Oracle instance and an Oracle database.

An Oracle database can only be accessed through one instance. See our tutorial on managing an oracle instance to learn how to manipulate the associated database.

An Oracle server instance is made up of a SGA (Global Area System) shared memory structure and several background Oracle processes each with a specific role.


An instance can only open one database at a time.

A database can be opened by multiple instances using the RAC (Oracle Real Application Clusters) option for high availability and load balancing. (see diagram below)


In such a configuration, users waiting to be connected can be directed to the least loaded instance and in addition, if one instance fails, the other takes over for better database availability.

N.B: In Windows environment, each instance is associated with a service named OracleService “InstanceName”. This service must be started in order to start the instance.

When it starts up, the instance uses an initialization settings file.

An instance has a SID. This determines the parameter file used at STARTUP (spfile ”SID” .ora or spfile.ora or init ”SID” .ora in this order of priority, if Oracle does not find one it goes to the next).

The components of the instance (SGA + background processes) are described in the following sections.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

PGA memory in Oracle

What is the PGA memory

An additional memory structure called PGA (Global Area Program) is created for each logged in user.

PGA stores user session specific control information such as private areas for cursor processing, bind variables, session information, sort area ...

Each server process has its own private PGA memory which is exclusively reserved for it.

When the user process disconnects (end of session), the associated server process ends and the PGA memory is freed.

N.B: In a shared server configuration:

    a server process handles requests from multiple user processes.
    a global amount of PGA memory can be allocated to the Oracle instance through the PGA_AGGREGATE_TARGET initialization parameter.

Depending on the technical architecture of the application, the user process exists, either on the user's PC (in the case of a client / server application) or on the application server of the intermediate level (middle-tier) in the case of an n-tier application.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

Characteristics of Oracle Server

Oracle server

An Oracle server, a database management system, consists of an Oracle instance and an Oracle database.

The instance and the database together constitute an Oracle server.

The architecture of Oracle Server can be described in three phases:

    User login to the database
    Memory structures that are part of the Oracle instance
    Background processes that are part of the Oracle instance
    Physical structures of files forming the Oracle database.

User login to the database

Two processes allow a user to interact with the instance and ultimately with the database: the user process and the server process.


Each time a user runs an application, such as an application for human resources management, financial management, or just an SQL command, the client machine first launches a user process to establish a connection from the user to the Oracle instance.
The Oracle Listener Listening Process

The Oracle listener process is the primary server-side Oracle component that establishes the connection between client computers and an Oracle database. The listener can be seen as a big ear that listens for connection requests to Oracle services.

Theoretically, a server machine can host several Oracle databases and one listener and only one to allow a client to connect to the Oracle instance of his choice. The instance name is submitted by the client during the connection process (step 1).

Two cases are possible:

    A Listener process can serve multiple databases configured on the same server machine,
    Several listener can be configured on the same machine (for failover or load balancing purposes to support heavy loads of connection requests).

In a dedicated server configuration, the Listener starts a new server process for each client and gives it control of the client session. Each client connection is served by its own server process.

The diagram above corresponds to a dedicated server configuration and for a client / server application.

The connection process goes through the following steps:

    The customer contacts the Oracle listener by choosing the instance to which he wishes to connect (request for a service name).
    The listener starts a dedicated process called a server process
    The listener sends an acknowledgment to the client with the address of the dedicated server process
    The client establishes a connection with the dedicated server process
    The server process connects to the Oracle instance on behalf of the user process (creation of a user session)

It is the server process that connects to the Oracle instance to serve the user process throughout the client session.

The user process does not directly interact with the Oracle server. Rather, it is the server process that interacts with the Oracle server, responds to user requests and returns results to the user.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is Oracle Database?

Oracle Database

Oracle Database  is one product among others forming the Oracle  suite (Oracle Database, Oracle Application Server, Oracle Developer Suite, Oracle Enterprise Manager Grid Control…).

Oracle Database  is the Oracle database management system that allows multiple users to access data simultaneously while ensuring availability and disaster recovery mechanisms.

Be aware that Oracle Database refers to the entire Oracle software, while database has a specific meaning in the Oracle architecture.

To guarantee a high level of performance, Oracle maintains the database thanks to memory structures (in random access memory: RAM) and physical structures (on hard disk) and uses processes for the storage of data in memory or on disk .

Writing to disk is only performed when necessary and under conditions while Memory is used as much as possible since memory access is faster than disk access (performance gain).


In the event of a power outage, for example, Oracle will be able to recover well if data in memory, at the time of the incident, is not written to disk. This is the recovery of the instance managed by the SMON process discussed later in the oracle architecture.

An instance failure requires recovery, while a physical failure (such as a disk crash) requires a restore followed by a recovery.

Restoration and recovery are two different terms that should be clearly distinguished. The restoration is made from backup files and the recovery allows to reapply to the restored data files, the modifications already made on the base to find the consistent state of the database before the incident.


In the example in the figure, recovery at time t2 requires restoring the last available S1 backup and reimplementing any changes that occurred in the interval t1-t2.

To learn more about the components and basic concepts of the Oracle architecture, take look at our Expert DBA Team Club blog and more advance topics are available at this source.

What is Cursors in Oracle?

What is the Cursors?

A cursor is a pointer to a private SQL memory area allocated (in PGA memory) for processing an SQL statement. The cursor is used to process one by one the records (rows of tables) returned by the SQL statement in question.

Two types of cursors can be distinguished:

    Implicit Cursor: When a user issues an SQL command, Oracle generates a cursor for processing that command. This cursor created and managed by Oracle is called an implicit cursor.
    Explicit cursor: if you want to manage an SQL command within your PL / SQL code, you can explicitly create a cursor to process the rows returned by the command one by one.

Here is an example :

DECLARED
name emp.ename% TYPE;
salary emp.sal% TYPE;

CURSOR C1 IS SELECT ename, NVL (sal, 0) FROM emp;

BEGIN
OPEN C1;

LOOP
FETCH C1 INTO name, salary;
EXIT WHEN C1% NOTFOUND;
DBMS_OUTPUT.PUT_LINE (name || 'earns' || salary || 'dollars');
END LOOP;

CLOSE C1;
END;

Explanations:

CURSOR C1: used to declare a cursor with the name C1
name emp.ename% TYPE means that the variable name is of the same type as the ename column of the emp table (Another predefined type% ROWTYPE can be used to declare a record variable of the same type as the record of a table)
Open C1: open the cursor
Fetch: fetch the current record and load it into the name and salary variables and then advance the pointer to the next record
C1% NOTFOUND: returns TRUE if the cursor points beyond the last record. Other attributes of the cursor can be used:

    % FOUND,
    % ROWCOUNT which returns the number of rows returned by the SQL order of the cursor,
    % ISOPEN which returns true if the cursor is open.

Close C1: used to close the cursor and free the resources.

For advance topics of Oracle DBA tutorials and tips. Stay connected with our following blogs. Dba tips and Dbametrix.

Transaction in Oracle Database

The transactions

Managing transactions is at the heart of database processing. To allow multiple users to access it simultaneously, the DBMS must manage transactions with the minimum of conflicts while ensuring the consistency of the database.

A transaction is a logical unit made up of one or more SQL statements.

DML command 1
DML command 2
................................
................................
................................
DML command n

A transaction begins implicitly with the execution of the first SQL command and ends with a COMMIT to commit or a ROLLBACK to roll back the transaction.

The transaction continues executing SQL commands one at a time until one of the following occurs:

    COMMIT: Transaction encounters an explicit COMMIT statement, all changes made to the database up to this point are committed.

    ROLLBACK: Transaction encounters an explicit ROLLBACK statement, all changes made to the database up to this point are rolled back.

  DML instruction: If an DDL instruction is encountered such as CREATE, DROP, RENAME, or ALTER, Oracle first commits the current DML instructions for the transaction and then executes and commits the DDL instruction in question. This is an implied commit.

    Normal end of the calling program: if the program ends without errors, the transaction is committed implicitly. This is the case when you use the sqlplus tool, then you enter a series of DML commands in interactive mode, then you exit the session normally via the sqlplus "EXIT" command. In this case an implicit commit is applied to your transaction.

    Abnormal program termination: Such as a network cut or a program kill, the transaction is implicitly rolled back.


Learning Oracle DBA is not too short and quick guide. You should need to read several books and Oracle documentation for understanding of depth of each topics. You can get more tips and tutorials for Oracle database administration from our other resources like this or this.

PL/SQL For Oracle DBA

PL / SQL language

PL / SQL is a procedural language specific to Oracle which constitutes an extension to the SQL language (non-procedural language).

SQL commands can be embedded within PL / SQL code. These commands are called Embedded SQL Commands.

PL / SQL code can be used in program units stored in the database such as procedures, functions, and packages (groups of procedures and functions). These program units are said to be stored.

If these procedures are not stored in the database but incorporated into an application (development tools such as Oracle FORMS DEVELOPER and Oracle REPORT DEVELOPER), they are called application procedures.

PL / SQL code embedded in an application program unit or stored but not named is called an anonymous block. An anonymous block which does not have a name, therefore, cannot be stored in the database.

The advantage of a stored unit of programs is that it can be called by name from your application code and Oracle stores the result of the parse analysis of the unit in the database (persistent storage on disk). Considerable performance gains are generated as the parse output is made available and persistent for future reuse of unit code.

Example of PL / SQL code:

DECLARED
numerator NUMBER;
denominator NUMBER;
the_ratio NUMBER;
lower_limit CONSTANT NUMBER: = 0.72;
samp_num CONSTANT NUMBER: = 132;

BEGIN
SELECT x, y INTO numerator, denominator FROM result_table
WHERE sample_id = samp_num;

the_ratio: = numerator / denominator;

IF the_ratio> lower_limit THEN
INSERT INTO ratio VALUES (samp_num, the_ratio);

ELSE
INSERT INTO ratio VALUES (samp_num, -1);
END IF;

COMMIT;

EXCEPTION
WHEN ZERO_DIVIDE THEN
INSERT INTO ratio VALUES (samp_num, 0);
COMMIT;
WHEN OTHERS THEN
ROLLBACK;

END;

Explanations:

This PL / SQL code declares several variables and constants, then calculates a ratio value (the_ratio) for values ​​(numerator, denominator) selected from a table (result_table).

Very few times, Oracle DBA needs to address about PL/SQL because there are developers available for every company to utilize this PL/SQL for making forms and reports. Understanding PL/SQL is essential for Oracle database administrators. Stay connected with our blog for getting more advance knowledge of Oracle DBA tips and Tutorials.

SQL language for Oracle DBA

SQL language

SQL is a standard non-procedural language for relational database management systems (RDBMS)

Examples of SQL queries:

    SELECT ename FROM emp;
    SELECT last_name, department_id FROM employees;
    DELETE FROM department WHERE department_id IN (10, 30, 70);
    CREATE TABLE articles (article_id VARCHAR2 (15), designation VARCHAR2 (40));
    INSERT INTO articles VALUES (‘imp1’, laser printer ’);
    DROP TABLE articles;
    GRANT SELECT ON department TO scott;
    REVOKE DELETE ON department FROM scott;

The terms SELECT, FROM, DELETE, WHERE, IN, CREATE, TABLE, VARCHAR2, INSERT, INTO, VALUES, DROP, GRANT, TO, REVOKE, ON are words reserved for SQL

Oracle SQL statements are classified into categories:

  DML commands for Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE)
    DDL commands for Data Definition Language (CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE…).
        DDL commands are automatically validated: they generate an implicit commit.
    Control Commands of:
        Transactions (COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION)
        Sessions (ALTER SESSION)
        and System (ALTER SYSTEM)

SQL Recursive commands:

When an DDL command is issued, Oracle automatically issues an LMD command to modify the corresponding information in the data dictionary. This last command is called a recursive SQL command.

An DDL "CREATE TABLE" command, for example, issued by a user to create a table in their own schema, automatically generates another recursive LMD command (INSERT) in the SYS schema: data dictionary tables. In fact, the information on the structure of the newly created table must be inserted and available in the data dictionary.


Stay connected with ORAGEEK for advance Oracle DBA Tips with Dbametrix.

What is Oracle Data Dictionary?

Oracle data dictionary

In a database, all of the objects that belong to a user form what is called a user schema.

The main types of objects are tables, indexes, views, synonyms, sequences, program units stored in the database (procedures, functions, packages, triggers).

Only tables and indexes correspond to segments of data. Other objects only have a definition stored in the data dictionary.

A user schema is created and managed by it, while the data dictionary is created and managed by Oracle.

The data dictionary is a repository which belongs to the SYS super user and which stores the tables and views necessary for the proper functioning of the Oracle database. The DBA_TABLES view and the corresponding synonym USER_TABLES is an example of a table and dictionary view.

For example, if a user USER1 wants to display the structure of a TABLE1 table that he owns, he issues the "DESC TABLE1" command. The result of this command is obtained from the data dictionary.

This is because the TABLE1 table stores data and is owned by user USER1, while the structure of TABLE1 is stored elsewhere, in dictionary tables that are owned by user SYS. This information about the data forming the structure of TABLE1 is called metadata.

Dictionary tables are not directly accessible but dictionary views allow you to query them. In a session of the user USER1, USER_TABLES (synonymous with the DBA_TABLES view) is used to list the structure of all the tables of USER1.

In summary, the data dictionary stores metadata on tables, columns of tables, constraints, indexes, etc.

It can also store:

    user objects such as views, synonyms, sequences, procedures, functions, packages, triggers, etc.
    the names of the users, their privileges and roles… etc.

You can get advance level oracle DBA Tutorials from this source and read some articles from here

Availability of Job in Oracle DBA

Availability of Job in Oracle DBA

Now a day there is no existent of oracle DBA jobs in the market. Still, there are two ways to enter into this area along with achieving success in succeeding steps. There are different gateways for getting a job in DBA. One could expose to the oracle by learning it so that one could get a large amount of cash. A huge number of people are intending to become a DBA, but unfortunately due to the slow market of this sector, they have to get failure. 

But among all of them the person who gets the job in DBA, really the person has strong luck with him or her. But still, the person is not in a safe zone to be in the sector. Due to a huge number of fluctuations, there is a major possibility to lose the job. There is no guaranty of the person to stay permanently employed. The most experienced persons also face the same problem. Plenty of people have to be harassed and returned to their homes after being unemployed. One has to give a continuous flow of good performance to stay employed as a DBA.

Due to these reasons, government workers are advised to find people to make them eligible to become oracle DBA. For this, the government workers are paid well from the taxes that the people have paid. In this way, people are getting excited to get a job in their dreamed sector with secure and safe employment. 

The students who are getting the certificates from a community college situated in their locality also could get the job by their talent. Hence they have the real opportunity to get the job without having any previous experience in this sector. But still, they have to work too hard to stay permanently as a DBA. Oracle DBA Blog always helps to improve your knowledge base to get job very easily.

Free Oracle DBA Tutorials

During this critical situation of Noval Covid-19, people and companies prefer to stay at home. People work from home. Governments of many countries ask people to keep social distancing. Many companies, informed their employees to stay at home and work from home. In this situation, you need work from home but simultaneously you need to improve yourself in your skills and knowledge.

The Internet is the best tool to gain more and more knowledge and learn daily new things. 

Using the internet you can learn new technology and watch the latest news. In this situation, when you will be spending time learning something new and gain depth knowledge then Oracle DBA Tutorial is the best option. Without any money make a subscription or any charging fee if you get all kinds of database news, knowledge, and tutorials. It means we get the gold mine. We can spend lots of time gaining knowledge to improve our skills and keep up to date in the database domain.

Many people share their knowledge on the internet but only a few people share their thoughts and experience juice in blogs. People put their experience in blogging for improving other’s knowledge.

I was quarantined during this critical situation and during this period I surf the internet and spend my time. 

I went to many sites for gaining some more knowledge of database administration. When I came to Oracle DBA Tutorials then I was amazed to feel that I wasted my all-time searching everywhere. This blog contains all things about databases. It provides the latest news of database, Oracle DBA, Oracle DBA tips, MySQL tips, SQL server tips, Teradata articles, and lots more. This site also contains SQL tutorials and I learned a lot of things. Due to I am sharing herewith this resource. It is a single resource for gaining everything about database technology. Data science, data warehousing are also covered in this resource. 

I think everyone needs to stay connected with this kind of golden resource for getting all-time updated himself or herself with the latest database technology news.