THE DBA VIEWS LIST EVERY APPROPRIATE OBJECT IN THE DATABASE

13. þ B. The DBA views list every appropriate object in the database.ý A, C, and D. A is wrong because this will list only the tables the current user has permissions on. C is wrong because it will list only the tables owned by SYS. D is wrong because this is the view that lists all the dynamic performance views, not all tables.

CHAPTER 4

Oracle Networking

Exam Objectives

In this chapter you will learn to• 052.5.1 Configure and Manage the Oracle Network• 052.5.2 Use the Oracle Shared Server Architecture

133

Networking is an integral part of the client-server database architecture that is fundamental to all modern relational databases. The Oracle database had the potential for client-server computing from the beginning (version 1, released in 1978, made a separation between the Oracle code and the user code), but it was only with version 4 in 1984 that Oracle introduced interoperability between PC and server. True client-server support came with version 5, in 1986. This chapter introduces the Oracle Net services. Oracle Net was previously known as Sqlnet, and you will still hear many DBAs refer to it as such.The default Oracle Net configuration is dedicated server. In a dedicated server environment, each user process is connected to its own server process. An alternative is shared server, where a number of user processes make use of a pool of server processes that are shared by all the sessions. Generally speaking, DBAs have been reluctant to use shared server, but there are indications that Oracle Corporation would like more sites to move to it, and certainly knowledge of the shared server architecture is vital for the OCP examination.

Configure and Manage the Oracle Network

Oracle Net is the enabling technology for Oracle’s client-server architecture. It is the mechanism for establishing sessions against a database instance. There are several tools that can be used for setting up and administering Oracle Net, though it can be done with nothing more than a text editor. Whatever tool is used, the end result is a set of files that control a process (the database listener, which launches server processes in response to connection requests) and that define the means by which a user process will locate the listener.

Oracle Net and the Client-Server Paradigm

There are many layers between the user and the database. In the Oracle environment, no user ever has direct access to the database—nor does the process that the user is running. Client-server architecture guarantees that all access to data is controlled by the server.A user interacts with a user process: this is the software that is run on their local terminal. For example, it could be Microsoft Access plus an ODBC driver on a Windows PC; it could be something written in C and linked with the Oracle Call Interface (or OCI) libraries; it could even be your old friend SQL*Plus. Whatever it is, the purpose of the user process is to prompt the user to enter information that the process can use to generate SQL statements. In the case of SQL*Plus, the process merely waits for you to type something in—a more sophisticated user process will present a proper data entry screen, will validate your input, and then when you click the Submit button will construct the statement and send it off to the server process.The server process runs on the database server machine and executes the SQL it receives from the user process. This is your basic client-server split: a user process generating SQL, that a server process executes. The execution of a SQL statement goes through four stages: parse, bind, execute, and fetch. In the parse phase your server process works out what the statement actually means, and how best to execute it. PART IParsing involves interaction with the shared pool of the instance: shared pool memory structures are used to convert the SQL into something that is actually executable. In the bind phase, any variables are expanded to literal values. Then the execute phase will require more use of the instance’s SGA, and possibly of the database. During the execution of a statement, data in the database buffer cache will be read or updated and changes written to the redo log buffer, but if the relevant blocks are not in the database buffer cache, your server process will read them from the datafiles. This is the only point in the execution of a statement where the database itself is involved. And finally, the fetch phase of the execution cycle is where the server process sends the result set generated by the statement’s execution back to the user process, which should then format it for display.Oracle Net provides the mechanism for launching a server process to execute code on behalf of a user process. This is referred to as establishing a session. Thereafter, Oracle Net is responsible for maintaining the session: transmitting SQL from the user process to the server process, and fetching results from the server process back to the user process.Figure 4-1 shows the various components of a session. A user interacts with a user process; a user process interacts with a server process, via Oracle Net; a server process interacts with the instance; and the instance, via its background processes, interacts with the database. The client-server split is between the user process generating SQL and the server process executing it. This split will usually be physical as well as logical: there will commonly be a local area network between the machines hosting the user processes and the machine hosting the server processes. But it is quite possible for this link to be over a wide area network, or conversely to run the user processes on the server machine. Oracle Net is responsible for establishing a session, and then for the ongoing communication between the user process and the server process.

Figure 4-1

The database is protected from users by several layers of segregation.