Home arrow Site Administration arrow Oracle Database XE: Initializing and Connecting

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).

  1. Oracle Database XE: Initializing and Connecting
  2. Connecting to the Database
By: Apress Publishing
Rating: starstarstarstarstar / 1
October 07, 2010

print this article



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 asinit.oraor 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/dbson Linux) as eitherspfile<SID>.oraorspfile.ora. If neither of these files exists, the instance looks for a PFILE with the nameinit<SID>.ora. Alternatively, theSTARTUPcommand can explicitly specify a PFILE to use for startup. For a default installation of Oracle Database XE, the name of the SPFILE isspfileXE.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 parameterSGA_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, andSTREAMS_POOL_SIZE.

Until Oracle9i Database, using theinit.orafile 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 theALTER SYSTEMcommand, the DBA must remember to change theinit.orafile 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, anyALTER SYSTEMcommand 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 theALTER SYSTEMcommand using SQL*Plus or the SQL Commands page. Here is an example of querying the value of thePROCESSESsystem 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.


The firstALTER SYSTEMcommand failed because you cannot change the value forPROCESSESwhile the instance is running. Using theSCOPE=SPFILEoption, the value forPROCESSES changes in the SPFILE only and will take effect the next time the database starts.

>>> More Site Administration Articles          >>> More By Apress Publishing

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Coding: Not Just for Developers
- To Support or Not Support IE?
- Administration: Networking OSX and Win 7
- DotNetNuke Gets Social
- Integrating MailChimp with Joomla: Creating ...
- Integrating MailChimp with Joomla: List Mana...
- Integrating MailChimp with Joomla: Building ...
- Integrating MailChimp with Joomla
- More Top WordPress Plugins for Social Media
- Optimizing Security: SSH Public Key Authenti...
- Patches and Rejects in Software Configuratio...
- Configuring a CVS Server
- Managing Code and Teams for Cross-Platform S...
- Software Configuration Management
- Back Up a Joomla Site with Akeeba Backup

Developer Shed Affiliates


Dev Shed Tutorial Topics: