View the temporary tablespace, undo tablespace, SGA, and PGA sizes of the Oracle System

1) CheckOracleSystem temporary tablespace size:

Select sum (bytes)/1024/1024 "Temp size (m)" from dba_temp_files where tablespace_name = 'temp ';

 

2) QueryUndoTablespace size

Select sum (bytes)/1024/1024 "Current undo size (m)" from dba_data_files where tablespace_name = 'undotbs1 ';

 

3) QuerySGAAndPGA:

SQL> show parameter SGA;

Obtain the values of sga_max_size and sga_target.

SQL> show parameter PGA;

Obtain the pga_aggregate_target value.

 

Reference value:

8 GB memory for on-site servers, 4 GB for SGA and 1 GB for PGA

The on-site server has 16 GB memory, SGA is set to 10 Gb, and PGA is set to 2 GB.

On-Site Server is 32 GB memory, SGA is set to 20 GB, and PGA is set to 4 GB

 

Modification method:

Pfile:

Sga_max_size = 4G

Sga_target = 4G

Pga_aggregate_target = 1g

 

Spfile:

SQL> alter system set sga_max_size = 4G scope = spfile;

SQL> alter system set sga_target = 4G scope = spfile;

SQL> alter system set pga_aggregate_target = 1g scope = spfile;