Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 28

In Chapter 27, you installed the Oracle database component and performed the basic configuration steps to round out your PHP environment. In this chapter, we’ll cover an overview of the Oracle
architecture and how to use several Oracle-supplied utilities, and give you a whirlwind tour of the other tools available at the Oracle Database XE home page.
While your focus as a PHP/Oracle developer is primarily as, well, a developer, your DBA duties will most likely take a small fraction of the time you spend with Oracle Database XE. However, in an environment where you are using Oracle Database XE, you are most likely wearing many different hats, and one of them is the DBA hat. As a result, you need to know the basics of being an Oracle DBA when the need arises.
You’ll see some of the tools we introduce in this chapter covered in more depth in Chapter 29. These tools, such as SQL Commands in the Oracle Database XE Web interface or SQL*Plus on the command line are useful regardless of whether you’re a developer, a database administrator, or a casual user who needs an occasional ad hoc query against the company’s sales history database.

Understanding the Oracle Architecture

As they say, you don’t have to know how a car’s antilock brakes work to drive a car, and you don’t need to be able to design a cell phone to call someone on a cell phone. The same could be said about the architecture of Oracle Database XE: you don’t necessarily need to know how Oracle stores data blocks on disk, but knowing the general disk and memory architecture model goes a long way to help you design and use the database efficiently. You also need to know the terminology surrounding Oracle components: tablespaces, datafiles, segments, and extents. Even if you’re only an occasional database administrator for your Oracle Database XE installation, the architectural overview in the next few sections will help you be an effective application developer as well.
An Oracle server contains both a database and an instance. The database consists of the files on
disk. These files store the data itself; the state of the database, in a small, most likely replicated file called the control file; and changes to the database’s data, in files called redo log files. The instance refers to the Oracle memory processes and memory structures that reside in your server’s memory and access the database in the disk files. This distinction becomes more obvious when you are using Real Application Clusters (RAC), which is two or more Oracle instances sharing one database for performance, scalability, or availability.
If you are still convinced that the database will never need any manual tuning (which may very well be the case with Oracle Database XE!), or your database storage needs will be relatively static over time, feel free to skip ahead to the section “Connecting to the Database.”

481

Oracle Storage Structures

It’s important to distinguish the logical database storage structure from the physical database structure. As with most computing paradigms, the logical version hides the implementation of the paradigm in the physical implementation, either to make application development easier or to help commu- nicate the architectural details to managers who are not involved in the technical aspects of a database computing environment on a daily basis.
From a database perspective, then, the logical database structures represent components such as tables, indexes, and views—what you see from a user’s or a developer’s point of view. The physical database structures, on the other hand, are the underlying storage methods on the disk file system including the physical files that compose the database.
Don’t worry if the next few sections seem a bit dry; you won’t need them to get into the trenches with Oracle. Most, if not all, of the storage and memory structures are tuned quite well by Oracle anyway; see Chapter 38 for more details. Skip to the section titled “Connecting to the Database” and come back here if and when you’re curious as to what’s going on under the hood.

Logical Storage Structures

The Oracle database is divided into increasingly smaller logical units to manage, store, and retrieve data efficiently and quickly. Figure 28-1 shows the relationships between the logical structures in an Oracle database: tablespaces, segments, extents, and blocks.

Figure 28-1. Oracle Database XE logical storage structures

The logical storage management of the database’s data is independent of the physical storage of the database’s physical files on disk. This makes it possible for changes you make to the physical structures to be transparent to the database user or developer at the logical level.

Tablespaces

A tablespace is the highest-level logical object in the database. A database consists of one or more tablespaces. A tablespace will frequently group together similar objects, such as tables, for a specific business area, a specific function, or a specific application. You can reorganize a particular tablespace or back it up with minimal impact to other users whose data may be changing at the same moment in other tablespaces in the database.
All Oracle databases require at least two tablespaces: the SYSTEM tablespace and the SYSAUX tablespace. Having more than just the SYSTEM and SYSAUX tablespaces is highly recommended when creating a database; a default installation of Oracle Database XE includes five tablespaces. In the illustration of logical structures in Figure 28-1, you can see the five default tablespaces: SYSTEM, SYSAUX, TEMP, USERS, and UNDO.

Segments

A tablespace is further broken down into segments. A database segment is a type of object that a user typically works with, such as a table or an index. The USERS tablespace in Figure 28-1 consists of five segments, which could be tables, indexes, and so forth. It’s important to note that this is the logical representation of these objects; the physical representation of these objects in the operating system files will most likely not match the logical representation. For example, extents 1 and 2 in segment 3 will most likely not be adjacent on disk and may even be in separate datafiles. We discuss datafiles in the section titled “Physical Storage Structures.”

Extents

The next-lowest logical grouping in a database is the extent. A segment groups one or more extents allocated for a specific type of object in the database. Segment 3 in Figure 28-1 consists of four extents. Note that an extent cannot cross segment boundaries. Also, a segment, and subsequently an extent, cannot cross tablespace boundaries.

Database Blocks

The most granular logical object in a database is the database block (also known as an Oracle block), the smallest unit of storage in an Oracle database. Every database block in a tablespace has the same number of bytes. Starting with Oracle9i, different tablespaces within a database can have database blocks with different sizes. Typically, one or more rows of a table will reside in a database block, although very long rows may span several database blocks.
A database block can have a size of 2KB, 4KB, 8KB, 16KB, or 32KB. Once any tablespace, including the SYSTEM and SYSAUX tablespaces, is created with a given block size, it cannot be changed. If you want the tablespace to have a larger or smaller block size, you need to create a new tablespace with the new block size, move the objects from the old tablespace to the new tablespace, and then drop the old tablespace.

Schemas

A schema is another logical structure that can classify or group database objects. A schema has a one-to-one correspondence with a user account in the Oracle database, although you may create a schema to hold only objects that other database users reference. For example, in Figure 28-1, the HR schema may own segments 1 and 3, while the RJB schema may own segment 2. HR and RJB are both

user accounts and schemas. Segments 1 and 3 may be the tables HR.EMPLOYEES and HR.DEPARTMENTS, while segment 2 may be the index RJB.PK_ACCT_INFO_IX.
A schema is not directly related to a tablespace or any other logical storage structure; the objects that belong to a schema may reside in many different tablespaces. Conversely, a tablespace may hold objects for many different schemas. A schema is a good way to group objects in the database for purposes of security, manageability, and access control.

Physical Storage Structures

From the perspective of building queries, developing applications, and running reports, regular users, managers, and developers don’t need to know much about the underlying physical structure of the database on disk. However, even a part-time DBA does need to understand these database structures. For example, you need to know where the database’s datafiles reside on disk and how to best optimize their placement when performance becomes an issue.
The physical structure of the Oracle database consists of datafiles, redo log files, and control files. On a day-to-day basis, the DBA will deal most often with the datafiles, since this is where all of the user and system objects, such as tables and indexes, are stored. Figure 28-2 shows the physical structure and its relationship to the Oracle memory and logical storage structures.

Figure 28-2. Oracle Database XE physical storage structures

Datafiles

The datafiles in a database contain all of the database data that the users of the database add, delete, update, and retrieve. A single datafile is an operating system file on a server’s disk drive. This disk may be local to the server or a drive on a shared storage array. Each datafile belongs to only one tablespace; a tablespace can have many datafiles associated with it.

There are seven physical datafiles in the database in Figure 28-2. There are two for the SYSTEM tablespace; one for the SYSAUX tablespace; one for the TEMP tablespace; two for the USERS tablespace; and one for the UNDO tablespace.

Redo Log Files

The Oracle mechanism to recover from an instance failure or a media failure uses redo log files. When users or system processes make changes to the database, such as updates to data or creating or dropping database objects, the changes are recorded to the redo log files first. A database has at least two redo log files. Oracle best practices recommend that you store multiple copies of the redo log files on different disks; Oracle automatically keeps the multiple copies in sync. If the instance fails, any unrecorded changes to database blocks not yet written to the datafiles are retrieved from the redo log files and written to the datafiles when the instance starts again; this process is known as instance recovery. By default, Oracle does not mirror the redo log files on different disks; we’ll show you how to do that in Chapter 40. For development, the default redo log configuration is sufficient.

Control Files

The control file maintains the metadata for the physical structure of the entire database. It stores the name of the database, the names and locations of the tablespaces in the database, the locations of the redo log files, information about the last backup of each tablespace in the database, and much more. Because of the importance of this file, Oracle best practices recommend that you keep a copy of the control file on at least three different physical disks. As with the redo log files, Oracle keeps
all copies of the control file in sync automatically. We’ll show you how to move the control files to different disks in Chapter 40.
The control file and redo log file contents do not map directly to any database objects, but their contents and status are available to the DBA by accessing virtual tables called data dictionary views, which are owned by the SYS schema. We cover data dictionary views and many other types of views in Chapter 34.

Oracle Memory Structures

The server memory allocated to Oracle includes the following types of data:

• User reading and writing activity (adding, modifying, deleting)

• SQL and PL/SQL commands

• Stored procedures and functions

• Information about database objects

• Transaction information

• Oracle program executables

This information is stored in two major memory areas: the System Global Area (SGA) and the Program Global Area (PGA). These areas are shown in Figure 28-3. The Oracle program executables are stored in the Software Code Area (not shown in the diagram).
The overall memory allocated to Oracle falls into two broad categories: shared memory and nonshared memory. The SGA and the Software Code Area are shared among all database users. The PGA is considered nonshared. There is one dedicated PGA allocated for each user connected to the database.

Figure 28-3. Oracle database memory structures

System Global Area

The SGA is the memory area that all connected database users share. The SGA itself is broken down into many areas. In the following sections we discuss the areas that hold cached data blocks from database tables, recently executed SQL statements, and information on recent structural and data changes in the database. These areas are the database buffer cache, the shared pool, and the redo log buffer, respectively.

Database Buffer Cache

The database buffer cache holds copies of database blocks that have been recently read from or written to the database datafiles. The data cached here primarily includes table and index data, along with data that supports ROLLBACK statements. We cover the ROLLBACK statement and transaction processing in Chapter 32.

Shared Pool

The shared pool contains recently used SQL and PL/SQL statements (stored procedures and func- tions). It also contains data from system tables (the data dictionary tables), such as character set information, tuning statistics, and security information. Because Oracle frequently caches objects such as PL/SQL stored functions in the shared pool, another user or process that needs the same stored functions can benefit from the performance improvement because the stored function is already in the shared pool.

Redo Log Buffer

The redo log buffer keeps the most recent information regarding changes to the database resulting from SQL statements. The Oracle background processes write these blocks initially to the online redo log files, which can be used to recover, or redo, all recent changes to the database after a failure, as we mentioned earlier.

Program Global Area

The PGA belongs to one user process or connection to the database and is therefore considered nonsharable. It contains information specific to the session, and it can include sort space and infor- mation on the state of any SQL or PL/SQL statements that are currently active by the connection.

Software Code Area

The Software Code Area is a shared area containing the Oracle program code (binary executables). Multiple database instances running against the same or different databases can share this code;
as a result, it saves a significant amount of memory on the server. Now that you’ve heard about the Software Code Area, you will probably never hear about it again; it’s a static area of memory that only changes size when you install a new version of Oracle. It’s truly a “set it and forget it” situation. If you meet the overall Oracle memory requirements, the program code uses a relatively insignificant amount of memory compared to the SGA and the PGA.

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 initial- ization 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 specifi- cally 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 param- eters 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.

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:

• Define headers and footers for text-based reports

• Rename columns for report output

• Prompt users for a variable substitution when they run a script

• Save query results to a file

• Copy tables between databases using a single command

• 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, auto- matically 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.

Using SQL*Plus on Linux

To run SQL*Plus, or any Oracle-supplied command, you must have several environment variables set. You can set these environment variables automatically at login by editing your login script, or you can use an environment variable script built when you installed Oracle Database XE. If you want to use your login script to set these variables, you need to define the variables listed in Table 28-1. For Oracle Database XE, most of these variables have required values.
When you install Oracle Database XE, the installer creates two scripts that define the environ-
ment variables for you. You can reference these scripts with the source command in your login script, or run it directly from the command line. This script in Listing 28-1 is for the Bourne, Korn, or Bash shells. The script for the C shell is similar but is named oracle_env.csh instead.

Table 28-1. Required Linux Environment Variables

Environment Variable Description Required Value

ORACLE_SID Oracle instance ID XE

ORACLE_HOME Oracle software home directory

PATH Search path for executables and shell scripts

/usr/lib/oracle/xe/app/oracle/product/
10.2.0/server

$ORACLE_HOME/bin:${PATH} (C shell) or
$ORACLE_HOME/bin:$PATH (Bourne, Korn, or
Bash shells)

NLS_LANG Language and territory Defaults to AMERICAN_AMERICA.US7ASCII

LD_LIBRARY_PATH Search path for shared libraries

SQLPATH Search path for *.sql scripts, separated by colons

$ORACLE_HOME/lib:$LD_LIBRARY_PATH

$ORACLE_HOME/sqlplus/admin

Listing 28-1. Environment Variable Script /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/
oracle_env.sh

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server export ORACLE_HOME
ORACLE_SID=XE export ORACLE_SID NLS_LANG=`$ORACLE_HOME/bin/nls_lang.sh` export NLS_LANG PATH=$ORACLE_HOME/bin:$PATH
export PATH
if [ $?LD_LIBRARY_PATH ]
then

else fi

LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH LD_LIBRARY_PATH=$ORACLE_HOME/lib

export LD_LIBRARY_PATH

Regardless of how you defined your environment variables, you’re finally ready to start a SQL*Plus session. At the Linux command-line prompt, type sqlplus, and you will see the SQL> prompt after you enter your username and password. In this example, you connect to the database with the SYSTEM user account after starting SQL*Plus, and then query the table structure of the employees table in the HR schema:

[oracle@phpxe ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 13 20:42:22 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> describe hr.employees
Name Null? Type
----------------------------------------- -------- ---------------------------- EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)

SQL>

Alternatively, you can authenticate your user account on the command line as follows:

[oracle@phpxe ~]$ sqlplus system/yourpassword

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 13 20:42:37 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL>

SQL*Plus is also available from the Linux KDE and Gnome desktop environments. For KDE,

click the K menu (or the red hat on Red Hat Linux) ➤ Oracle Database 10g Express Edition ➤ Run SQL Command Line, and you’ll get a SQL prompt in a terminal window, as shown in Figure 28-4. Using this method, you must authenticate using the CONNECT command.

Figure 28-4. A SQL*Plus window from the KDE start menu

For the Gnome desktop environment, you follow a similar procedure. Click Applications Menu ➤ Oracle Database 10g Express Edition ➤ Run SQL Command Line to get a SQL prompt in a terminal window.

Using SQL*Plus on Windows

The required environment variables are automatically set in the Windows registry when you install Oracle Database XE. Therefore, all Oracle applications in the Windows start menu or at a command- line prompt will run fine without defining these variables yourself.
The first way to access SQL*Plus in a Windows environment is to click Start ➤ All Programs ➤
Oracle Database 10g Express Edition ➤ Run SQL Command Line. You must use the CONNECT command to establish a connection, the same as for the Linux environment. It is certainly beneficial to your training efforts to have Oracle applications launch and otherwise behave in the same way regardless of the operating system platform.
Alternatively, you can launch SQL*Plus by clicking Start ➤ Run and entering sqlplus in the Run
window. SQL*Plus prompts you for the username and password just as it does when you run SQLPLUS in a Linux terminal window. To bypass the prompts, you can type sqlplus system/yourpassword in the Run window.

Running SQL Commands Using the XE Home Page

Regardless of whether you are using Linux or Windows, running SQL commands using the GUI follows the same procedure. Figure 28-5 shows the Oracle Database XE home page for the account RJB (you created this account in Figure 27-8 in Chapter 27). Note that for any account other than SYS, SYSTEM, and other privileged accounts created during installation, you see the Application Builder icon on the Oracle Database XE home page. We’ll tell you more about the icons on the home page
later in this chapter in the section “Using Oracle-Supplied Utilities.”

■Note Accounts with system privileges can access the Application Builder menu as long as the account is not
SYS or SYSTEM.

To execute SQL commands, use the SQL Commands function in the Oracle Database XE Web environment. Click the SQL icon shown in Figure 28-5, then click the SQL Commands icon on the next page to see the SQL Commands page shown in Figure 28-6. Type this command and click the Run button:

select * from hr.employees;

The SQL Commands window returns all rows from the table HR.EMPLOYEES and formats them using a best-fit method depending on the datatypes and the size of the columns. You can use the scroll bars at the bottom of the browser window to see the remaining columns in the query result.
Alternatively, you can use the down arrow on the right side of each icon to navigate directly to the desired destination page.

Figure 28-5. The Oracle Database XE home page for the user RJB

Figure 28-6. The SQL Commands page

Starting and Stopping Oracle Database XE

When you install Oracle Database XE, one of your options is to start the database automatically when the operating system starts. When you shut down your server, the shutdown process runs scripts to automatically shut down the database as well. Whether you start the database automati- cally using this method, or start it manually using the menu commands or SQL*Plus, there are situations where you need to stop the database manually without shutting down your server. For example, you may want to move some of your tablespaces from one disk drive to another, or you may want to
change some system parameters that can only be changed after you shut down the database.

■Note Under Linux, Oracle starts up automatically when the parameter ORACLE_DBENABLED is set to TRUE in
the file /etc/sysconfig/oracle-xe. Under Windows operating systems, the installer sets the autostart option by setting the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_XE\ORA_XE_AUTOSTART to TRUE.

Starting Oracle Database XE

The manual startup and shutdown procedures are identical on Linux and Windows. You can use SQL*Plus from the command line, the SQL Command GUI equivalent from the Windows or Linux start menu, or the Start Database and Stop Database menu items in the Windows or Linux start menu. The following shows how to start up SQL*Plus from the command line if you did not configure Oracle to start up automatically on your Linux server:

[oracle@phpxe ~]$ sqlplus system as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 14 00:17:53 2006
Copyright (c) 1982, 2005, Oracle. All rights reserved. Enter password:
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 79695676 bytes Database Buffers 62914560 bytes Redo Buffers 2932736 bytes Database mounted.
Database opened.
SQL>

Notice the keywords AS SYSDBA in the SQLPLUS command. Because the database is down, you cannot authenticate your user account using the database. AS SYSDBA authenticates your user account with a password file stored in the Oracle directory structure. The password you supply is the same password you use to connect when the database is up. Oracle automatically keeps the passwords stored in the database in sync with the passwords stored in the password file for user accounts that you grant the SYSDBA privilege. We discuss user privileges, security, and roles in greater detail in Chapter 30.

■Note The password file is located in %ORACLE_HOME%\server\database\PWDXE.ora on Windows and
$ORACLE_HOME/dbs/orapwXE on Linux.

If you are logged into the server using the user account you used to install the Oracle software, you can start up the database by authenticating with the operating system. You need not specify a user account or a password. To use operating system authentication to start up the database, you use the / keyword as follows:

[oracle@phpxe ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 14 00:18:20 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 79695676 bytes Database Buffers 62914560 bytes Redo Buffers 2932736 bytes Database mounted.
Database opened.

SQL>

Notice that you specify neither a username nor a password, since the authentication takes place

when you log into the operating system account that owns the Oracle software.
If you are using Windows XP as your host operating system, you don’t need to see the command line. Click Start ➤ All Programs ➤ Oracle Database 10g Express Edition ➤ Start Database to, as you might expect, start the database. A DOS command window will appear to confirm that you started
the database successfully.

Stopping Oracle Database XE

Ideally, you want all users logged off before you shut down the database. If you cannot contact the users that are still logged in and do not have time to disconnect each session manually using the Monitor Sessions function under the Administration icon on the Oracle Database XE home page, you can still shut down the database quickly with the SHUTDOWN IMMEDIATE command. This command performs the following operations:

• Prevents any new connections

• Prevents any new transactions from starting

• Rolls back any uncommitted transactions

• Immediately disconnects all users and applications

To shut down the database, connect to the database using the command SQLPLUS / AS SYSDBA, and use the SHUTDOWN IMMEDIATE command:

SQL> shutdown immediate Database closed. Database dismounted.
ORACLE instance shut down. SQL> exit
If the SHUTDOWN command does not respond after several minutes, you can force a shutdown. The database may not be responding for a number of reasons, including a background process that is no longer responding, a corrupted datafile, or a network failure. Use the SHUTDOWN ABORT command to force a shutdown:
SQL> shutdown abort
ORACLE instance shut down. SQL>
Since the database is in an inconsistent state, Oracle recommends that you start up the database to perform a recovery process, and then shut down the database gracefully using the SHUTDOWN IMMEDIATE command:

SQL> startup
ORACLE instance started.

Total System Global Area 146800640 bytes Fixed Size 1257668 bytes Variable Size 79695676 bytes Database Buffers 62914560 bytes Redo Buffers 2932736 bytes Database mounted.
Database opened.
SQL> shutdown immediate Database closed. Database dismounted.
ORACLE instance shut down. SQL>

If you are using Windows XP as your host operating system, you don’t need to see the command line in this case either. Click Start ➤ All Programs ➤ Oracle Database 10g Express Edition ➤ Stop Database to stop the database. A DOS command window will appear to confirm that you stopped the
database successfully.

Using Oracle-Supplied Utilities

You can navigate to all Oracle-supplied tools from the home page. Figure 28-7 shows the first- and second-level navigation hierarchy for the tools. In the following sections, we give you a brief over- view of the tools and where we will cover these tools in more depth later in this book.

Figure 28-7. Oracle Database XE home page tool menu hierarchy

Administration

The following are the tools available when you click the Administration icon:

Storage: Monitor the amount of disk storage your applications use. You can have up to 4GB of user data, and up to 1GB of system data, for a total of 5GB. The system data does not include space allocated for temporary and rollback storage.

Memory: Monitor main memory, similar to the Storage tool. The sum of all memory structures allocated for Oracle cannot exceed 1GB.

Database Users: Search, create, modify, and drop user accounts. Grant and revoke privileges and roles for user accounts.

Monitor: Monitor user and system connections as well as disconnect users, system statistics, most frequently executed SQL statements, and SQL statements that have been running for more than six seconds (wall clock time).

About Database: View version information, system settings, language settings, Common Gateway Interface (CGI) environment settings, and system parameters. We provide an over- view of system parameters earlier in this chapter in the section “Initialization Parameters.”

Object Browser

The Object Browser icon, as the name implies, makes it easy for you to view the characteristics of all types of database objects: tables, views, indexes, packages, procedures, functions, and so forth. In addition, you can create these objects on the same page.

SQL

From the SQL icon, you can run ad hoc queries, manage SQL scripts (more than one SQL command), and build queries with multiple tables using a graphical representation of the tables and their columns.

Utilities

The Utilities icon accounts for more than half of the tools available in the Web interface. When you click this icon, you see four new icons: Data Load/Unload, Generate DDL, Object Reports, and Recycle Bin.

Data Load/Unload

It’s unlikely that you have a homogeneous environment where all of your data is stored in an Oracle database (and no other brand of database) and no one uses spreadsheets or text files. As a result, you need the capability to import and export data in a variety of formats. On the Data Load/Unload page, you can load data into the database from text files, spreadsheets, or XML documents. To transfer the data in your database to another site that, for example, can only accept text or XML files for import, you can export one or more of your database tables into a number of text formats and XML. We cover importing and exporting Oracle data in more depth in Chapter 39.

Generate DDL

You may have a need to create your tables in another database that is not directly accessible from your database. You can use the Generate DDL icon to export the data-definition language (DDL) commands for one or more object types in a selected schema. You can selectively include or exclude generating the commands to create tables, indexes, functions, views, synonyms, and so forth.

Object Reports

The Object Reports icon facilitates reporting on all types of database objects: tables, PL/SQL compo- nents, security objects, and data dictionary views. We dive into views, both user views and data dictionary views, in Chapter 34. The reporting tools available don’t merely list the columns and datatypes of tables; they give you an easy way to identify objects that may need your attention. For example, you can retrieve a list of tables without primary keys and indexes. Tables without primary keys or indexes will most likely cause some kind of performance problem in your database, espe- cially if the table is large or your users access only a small subset of the rows in a table in their queries.

Recycle Bin

The database recycle bin operates much like the Recycle Bin on a Windows or Linux desktop: the object is logically deleted but still resides somewhere on disk if there is enough disk space available to maintain some of the deleted objects. The Recycle Bin icon provides you with the capability to browse and restore dropped database objects or to empty the recycle bin.

Application Builder

As the name implies, the Application Builder makes it easy for your developers to create Web-enabled applications that use the database for the application’s data. Clicking the down arrow next to the Application Builder icon gives you access to three sample applications that cover most of the key features available with Application Builder. The entire Oracle Database XE Web application environ- ment is an Application Builder application.

■Note The Application Builder icon shows up for all users except for SYS and SYSTEM, even if the user has been granted system privileges.

Troubleshooting in Oracle

Most of your database troubleshooting techniques are available at the Oracle Database XE home page, where you can browse and monitor the following:

• Database connections

• System statistics

• Frequently running SQL

• Long-running SQL

In addition, you can run reports against database tables to see which of those tables may be performing poorly because they either don’t have enough indexes, have too many indexes, or don’t even have a primary key.
One useful tool in the administrator’s toolbox is not available using the Web interface: the data-
base’s alert log. The alert log file is a text file containing messages about the state of the database. It contains entries about significant database events, such as database startup and shutdown informa- tion, nondefault initialization parameters, and other error conditions ranging from warning messages to fatal errors that cause a database failure. In addition, all ALTER SYSTEM commands appear in the alert log.
The alert log is a good place to look in situations where your database does not start or suddenly fails. Other log files known as trace files in the same directory as the alert log may also help determine the issue when the database is having problems.
The location and name of the alert log differ on the Windows and Linux platforms. On Windows the alert log file is stored in %ORACLE_BASE%\admin\XE\bdump\alert_xe.log; on Linux it is in $ORACLE_BASE/ admin/XE/bdump/alert_XE.log.
Here is a sample of the alert log in a Windows environment:

Dump file d:\oraclexe\app\oracle\admin\xe\bdump\alert_xe.log
Wed Jul 12 20:37:34 2006
ORACLE V10.2.0.1.0 - Production vsnsta=0 vsnsql=14 vsnxtr=3
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:3144M/3583M, Ph+PgF:6753M/7002M, VA:1945M/2047M Wed Jul 12 20:37:34 2006
Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST Autotune of undo retention is turned on.
IMODE=BR ILAT =10
LICENSE_MAX_USERS = 0
SYS auditing is disabled
ksdpec: called for event 13740 prior to event group initialization
Starting up ORACLE RDBMS Version: 10.2.0.1.0.

System parameters with non-default values:
sessions = 49
__shared_pool_size = 201326592
__large_pool_size = 8388608
__java_pool_size = 4194304
__streams_pool_size = 0 spfile =
D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DBS\SPFILEXE.ORA
sga_target = 805306368
control_files = D:\ORACLEXE\ORADATA\XE\CONTROL.DBF
__db_cache_size = 587202560 compatible = 10.2.0.1.0
db_recovery_file_dest = D:\OracleXE\app\oracle\flash_recovery_area db_recovery_file_dest_size= 10737418240
undo_management = AUTO undo_tablespace = UNDO remote_login_passwordfile= EXCLUSIVE
dispatchers = (PROTOCOL=TCP) (SERVICE=XEXDB)
shared_servers = 4 job_queue_processes = 4
audit_file_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\ADUMP background_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\BDUMP user_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP core_dump_dest = D:\ORACLEXE\APP\ORACLE\ADMIN\XE\CDUMP db_name = XE
open_cursors = 300 os_authent_prefix = pga_aggregate_target = 268435456

Even though the alert log file grows slowly, it does grow without limit, so you should rename the log file on a weekly or monthly basis to make it easy to browse previous alert log entries. After you rename the alert log file, Oracle automatically creates a new alert log file the next time it needs to record an event or error message.

Summary

Oracle Database XE, with its intuitive Web interface, makes it even easier to be a developer. The same can be said for those times when you must wear your DBA hat; most, if not all, functions you need to perform as a DBA are available within the Web interface.
In this chapter, we gave you a high-level look at the Oracle database architecture, from logical to physical, as well as how to stop and start the database while your server is still running. Next, we showed you how to run SQL commands from both the Web interface and the command line. Many of the tasks you will perform as a DBA involve typing slightly unintuitive commands when something doesn’t seem to be working right in your PHP development environment.
In the next chapter, we shift gears a bit and present some of the other ways to interact with
Oracle Database XE from a developer’s and a user’s point of view. In addition, we’ll show you how to download and install the Oracle Database XE client software and connect to an Oracle Database XE instance from another server.