2.3 Connecting to an Infrastructure

This section illustrates the Oracle infrastructure to which you connect. The first part, illustrated in Figure 2-4, discusses the point-to-point connection you have with Oracle in a dedicated-server environment. Second, we discuss the instance infrastructure, illustrated in Figure 2-5. This is all tied together with a discussion surrounding Figure 2-6.

Figure 2-4. Oracle Dedicated-Server Mode.

Figure 2-5. Database Instance.

Figure 2-6. Database Instance with Connections.

When you invoke SQL*Plus you start an interactive program, or process, that will idle, waiting for you to type a command ”SQL*Plus spends most of its time in an idle state waiting for you to type in a SQL statement. Once you establish a connection, there are actually two processes: one is the SQL*Plus program to which you are interfacing, and the other is an Oracle process to which you have a point-to-point connection. This connectivity is illustrated in the Figure 2-4 where we see SQL*Plus as a separate process, dedicated to servicing the SQL statements that you type ”this other process is Oracle.

Figure 2-4 depicts three users each running SQL*Plus. Each is running a command line SQL application that has a dedicated connection to an Oracle process. For each of these point-to-point scenarios, the partnering Oracle process begins to run when the user invokes SQL*Plus with a username and password.

When SQL*Plus accepts a username, password, and host string, it attempts to connect to an Oracle database instance. Once the connection is made and the username and password are validated , the separate Oracle process is established on behalf of that SQL*Plus session.

Database instance is the term used to describe the real-time processing framework of a running database. This framework consists of Oracle background processes that communicate with each other through shared memory.

Prior to any users connected into Oracle, we have a database instance that is open , and which graphically (see Figure 2-5) means we have three main components :

1. Numerous background processes.

2. A large chunk of shared memory called the System Global Area (SGA), which can range upward from 25M.

3. Disk space, which consists of many types of files. We refer to the complete set of files on disk as "the database."

Figure 2-5 illustrates a database instance that is an open database to which users can connect. Figure 2-5 just shows the database instance and database files ”no users are connected in this illustration.

Keep in mind that Figure 2-5 is a generalization of the Oracle architecture and it is generalized so as to not cloud this issue of connectivity with the complex framework of an Oracle instance. The Oracle architecture is a topic usually reserved for books on Oracle database administration.

Assume we have an Oracle instance open and no users are connected. First, we start SQL*Plus as an interactive program that initially runs as a stand-alone program. It is stand-alone until you give it an Oracle username, password and host string ”then there is some activity. SQL*Plus then sends a connection request to a specific Oracle process, the Oracle listener. The Oracle listener is a host process, or Windows service, dedicated to servicing inbound connection requests . If the username and password are valid, the listener sends back a successful return code. A dedicated Oracle process is invoked, on behalf of the end user, and we have the environment illustrated in Figure 2-6. The listener is no longer involved ”it only services the establishment of new incoming connections.

The "Background Oracle processes" and the SGA, in Figure 2-5, make up the processing framework of an open database ”this we call the instance. The datafiles, which include a variety of Oracle files serving different purposes, are what we refer to as "the database." Your manager could say, "Make a copy of this database and send it to California." You would make copies of all the datafiles, send them to California, and copy the files onto a server where Oracle was installed. Once that was done, you could "bring up" this new instance.

The discussion surrounding Figures 2-4, 2-5, and 2-6 is designed to crystallize what happens during a database connection. Even though this is a chapter on SQL*Plus, the mechanics of connecting to Oracle are the same for any application ”the underlying connection process of a .Net program to Oracle, or a JSP application using JDBC to Oracle, is identical.

There are variations to the architecture just described. For example, Oracle has an operating environment called Multi-Threaded Server (MTS) where users share Oracle processes. If you look at Figure 2-4 you might wonder about the load on a server once you approach hundreds of concurrent connections. The MTS environment is an architecture optimized for systems servicing many concurrent end users.

Once you have launched SQL*Plus and are connected to a database instance, you can submit SQL statements for processing. You can disconnect, in which case your point-to-point database connection no longer exists, and from this point you can then reconnect to the same database, connect to another database, or exit the SQL*Plus desktop application altogether.