Oracle Database XE: Initializing and Connecting

In this third part of a five-part series on Oracle Database XE administration, you will learn about initialization parameters and how to connect to the database. This article is excerpted from chapter 28 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).

Initialization Parameters

When a database instance starts, the memory for the Oracle instance is allocated and one of two types of initialization parameter files is opened: either a text-based file called init<SID> .ora (known generically as init.ora or a PFILE), or a server parameter file (otherwise known as an SPFILE). The instance first looks for an SPFILE in the default location for the operating system (e.g., $ORACLE_HOME/dbs on Linux) as either spfile<SID> .ora or spfile.ora . If neither of these files exists, the instance looks for a PFILE with the name init<SID> .ora . Alternatively, the STARTUP command can explicitly specify a PFILE to use for startup. For a default installation of Oracle Database XE, the name of the SPFILE is spfileXE.ora .

Initialization parameter files, regardless of the format, specify parameters such as file locations for trace files, control files, filled redo log files, and so forth. They also set limits on the sizes of the various structures in the SGA discussed earlier in this chapter as well as how many users can connect to the database simultaneously.

As of Oracle Database 10g, and of course Oracle Database XE, Oracle categorizes the 258 initialization parameters (in Oracle Database XE) into two broad categories: basic and advanced. There are 29 basic parameters. Most databases should only need to have these adjusted, if at all, to run efficiently. The other advanced parameters only need to be adjusted when the Oracle documentation specifically calls for the adjustment under special circumstances. Many parameters are automatically adjusted based on the settings of other parameters. For example, the parameter SGA_TARGET , which specifies the total size of the SGA, automatically sizes five other parameters that control SGA memory areas: DB_CACHE_SIZE , SHARED_POOL_SIZE , LARGE_POOL_SIZE , JAVA_POOL_SIZE , and STREAMS_POOL_SIZE .

Until Oracle9i Database, using the init.ora file was the only way to specify initialization parameters for the instance. Although it is easy to edit with a text editor, it has some drawbacks. If a dynamic system parameter is changed at the command line with the ALTER SYSTEM command, the DBA must remember to change the init.ora file so that the new parameter value will be in effect the next time the instance is restarted.

An SPFILE makes parameter management easier and more effective for the DBA. If an SPFILE is in use for the running instance, any ALTER SYSTEM command that changes an initialization parameter can change the initialization parameter automatically in the SPFILE, change it only for the running instance, or both. No editing of the SPFILE is necessary or even possible without corrupting the SPFILE itself. A default installation of Oracle Database XE uses an SPFILE. Oracle best practices recommend using an SPFILE and backing it up whenever you make changes to an initialization parameter.

To view the values of all of the initialization parameters using the Oracle Database XE home page, click the Administration ? About Database icons. Next, select the Parameters checkbox and click the Go button. In the rare case where you need to change an initialization parameter, you can use the ALTER SYSTEM command using SQL*Plus or the SQL Commands page. Here is an example of querying the value of the PROCESSES system parameter, then increasing the number of processes and therefore the number of users that can connect to the database:

SQL> show parameter processes

NAME                        TYPE      VALUE
————————— ——— ——

aq_tm_processes             integer   0 db_writer_processes         integer   1 gcs_server_processes        integer   0 job_queue_processes         integer   4 log_archive_max_processes   integer   2
processes                integer  40 SQL> alter system set processes = 100; alter system set processes = 100
                
*

ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

SQL> alter system set processes=100 scope=spfile;

System altered.

SQL>

The first ALTER SYSTEM command failed because you cannot change the value for PROCESSES while the instance is running. Using the SCOPE=SPFILE option, the value for PROCESSES changes in the SPFILE only and will take effect the next time the database starts.

{mospagebreak title=Connecting to the Database} 

Now that you know the architecture of an Oracle database, your next task is to learn how to connect to the database. We’ll start with the command-line tool that has been around almost as long as the Oracle database itself: SQL*Plus. The Plus part of SQL*Plus defines the extra functionality beyond running a SQL statement and returning the results. Here are a few of the capabilities of SQL*Plus:

  1. Define headers and footers for text-based reports
  2. Rename columns for report output 
     
  3. Prompt users for a variable substitution when they run a script 
     
  4. Save query results to a file 
     
  5. Copy tables between databases using a single command 
     
  6. Retrieve column names from a table

Most of these SQL*Plus functions have long since been replaced by more elegant methods, as you will see throughout this chapter and the rest of the book. However, you never know when you might have to run a legacy SQL*Plus script that has not yet been rewritten for Oracle Database XE using Application Express.

We cover the other Oracle command-line and GUI utilities later in this chapter in the sections titled “Running SQL Commands Using the XE Home Page” and “Using Oracle-Supplied Utilities.” In this section, we show you how to run SQL*Plus from the command line in Linux and Windows as well as how to run SQL queries from the SQL Commands function accessible via the Oracle Database XE home page.

Since the SQL Commands functionality, available via the Oracle Database XE home page, automatically formats your SQL command output for a Web page, very few of the functions found in the SQL*Plus text-based tool remain in the SQL Commands tool. For example, you can still use the DESCRIBE command on a table (to display the columns and datatypes of a table), but you no longer have header and footer commands. For those situations where you want more precise control of your SQL output in a Web form, you use the Application Builder function from the Oracle Database XE home page.

Running SQL*Plus from the Command Line

Using SQL*Plus from the command line is slightly different depending on whether you’re using Linux or Windows. On Linux, you must define some environment variables using a login script or running a predefined script provided with your Oracle Database XE installation. In addition, we show you a couple different ways to start SQL*Plus in both the Linux and Windows environments.

Please check back next week for the fourth part of the series.

[gp-comments width="770" linklove="off" ]

chat