How To Recreate SGA & PGA İn Oracle Database

SGA = also known as shared memory space. This is the area where data and control information generated on the Ram is stored when Oracle Instance is started. It is not allowed to be used by another application until the database is closed. When turned off, the memory space returns to the source system. This is the common area in the database that can be accessed by all user sessions. The server and background processes do not take place here, but they also allocate memory space.
We can display the components of SGA by querying the V $ SGASTAT view.

PGA = Unshared memory space. When a server process is started, the PGA memory space is allocated from the server physical memory and uses it until the process is finished.
With the V $ PGASTAT vie’i we can get information about the PGA area.

create pfile = ‘/ home / oracle / pfile_backup.ora’ from spfile;

create pfile = ‘D: \ pfile_backup.ora’ from spfile;

show parameter target;

show parameter sga;

show parameter pga;

show parameter spfile;

show parameter pfile;

select * from v $ sga;

select * from v $ sgainfo;

SELECT name, value
FROM V $ SYSSTAT
WHERE name IN (‘db block gets from cache’, ‘consistent gets from cache’,
‘physical reads cache’);

NAME TYPE VALUE

lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 32G
sga_target big integer 32G

create pfile from spfile;

File created.

alter system set sga_max_size = 15G scope = spfile;

The system has been changed.

alter system set sga_target = 15G scope = spfile;

The system has been changed.

alter system set pga_aggregate_target = 2048M scope = spfile;

The system has been changed.

show parameter pga

NAME TYPE VALUE

lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 32G
sga_target big integer 32G

SQL> show parameter pga

NAME TYPE VALUE

---

pga_aggregate_target big integer 8G
sweat sga

Auto Memory Management disabled parameters.

alter system set memory_max_target = 0;
alter system set memory_target = 0;

SELECT * FROM V_ $ SGA_TARGET_ADVICE;
SELECT * FROM V_ $ PGA_TARGET_ADVICE;

---

If the database is not open from the old pfile

startup pfile = ‘/ home / oracle / pfile_backup.ora’;
create spfile from pfile = ‘/ home / oracle / pfile_backup.ora’;

create pfile from spfile;

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

create pfile = ‘/ home / oracle / pfile_backup2.ora’ from spfile;