Oracle SQL*Plus: The Definitive Guide

Looking at Your Database

The SHOW command may be used to review information regarding the current state of your database. Use SHOW SGA to see the information about the size of your System Global Area, or SGA. With SHOW PARAMETER, you can look at the current settings for the database initialization parameters. SHOW LOGSOURCE and SHOW AUTORECOVERY give you information about archive logging and recovery.

Tip

Remember that the SHOW command options described in this chapter are new with release 8.1 of SQL*Plus, which was shipped with Oracle8i. In previous versions of Oracle, these SHOW commands were implemented only in Server Manager.

Looking at the SGA

The SHOW SGA command may be used to display information regarding the size of the System Global Area. The SGA is shared by all Oracle processes and contains the database buffer cache, the redo log buffer, shared SQL statements, and a number of other items. Here is an example of SHOW SGA being used:

SQL> SHOW SGA
Total System Global Area       5508612 bytes
Fixed Size                       45584 bytes
Variable Size                  4529140 bytes
Database Buffers                409600 bytes
Redo Buffers                    524288 bytes

The values returned by SHOW SGA are as follows:

Total System Global Area

The total maximum size of the SGA.

Fixed Size

This is the minimum size of the SGA, and is the size used when the SGA is first allocated.

Variable Size

This is the amount by which the SGA may expand.

Database Buffers

The number of bytes allocated for buffering of data blocks from the data files. Oracle tries to keep the ...