As you learned in Chapter 2, "Installing and Using Personal Oracle7," Personal Oracle7 for Windows 95 provides two versions of SQL*Net--;the middleware product that uses a particular networking protocol to establish an Oracle connection between a client application and an Oracle database server. Oracle Corporation is phasing out the older version, SQL*Net version 1. Its replacement--;SQL*Net version 2--;uses protocol adapters to support a variety of networking protocols.
For the rest of this chapter, the term SQL*Net refers to SQL*Net version 2. Also, assume that you are going to use the TCP/IP protocol--;especially since TCP/IP is the protocol used on the Internet. Also, SQL*Net allows the Oracle7 server to utilize the MultiThreaded Server, which means that multiple Oracle connections can share a single server process. The MultiThreaded Server has the beneficial effect of reducing the number of processes on the machine hosting the Oracle7 server.
Note
To use the distributed database features discussed in this chapter, you'll need to have access to a remote Oracle database. This access could be via a LAN/WAN connection or via a dial-up network connection through an Internet service provider (ISP). Although SQL*Net supports protocols other than TCP/IP, this chapter focuses on the use of the TCP/IP protocol.
When you establish a remote connection to an Oracle database, an application program on a client machine--;for example, SQL*Plus on a Windows 95 PC--;connects to a remote computer (or host) on which an Oracle database server resides. For this connection to occur, the Windows 95 client must have SQL*Net installed and the host machine must have an Oracle program called the SQL*Net Listener running. The SQL*Net Listener listens for and processes connection requests.
Aside from being an interesting technical achievement, connecting to remote databases enables you to
Before you explore these powerful possibilities, you need to know how to create a database alias and establish a database connection.
SQL*Net uses several configuration files to specify legitimate database aliases. You can find these files in C:\ORAWIN95\NETWORK\ADMIN. Making entries in each of these files can be tricky, so Personal Oracle7 provides the utility program SQL*Net Easy Configuration to help you. This utility gives you an almost foolproof way to create a new database alias or modify an existing one.
You can invoke SQL*Net Easy Configuration from the Oracle for Windows 95 folder.
The first window you see looks much like the Oracle Installer. After a few moments,
you'll see a window with five radio buttons. (See Figure 30.1.)
Figure 30.1. The SQL*Net Easy Configuration
options.
The next section explains how to add a database alias.
To add a database alias, click the Add Database Alias radio button and then click
OK. Then enter a meaningful name for the database to which you want to connect in
the Choose Database Alias dialog box as shown in Figure 30.2. The name cannot already
exist, and it cannot include spaces. Click OK.
Figure 30.2. Choosing the database alias.
After you enter a valid name for the database alias, the Choose TCP/IP Host Name
and Database Instance dialog box appears. (See Figure 30.3.)
Figure 30.3. Specifying the TCP/IP host
name and database instance.
Enter the host name or IP address of the machine on which the Oracle database resides
in the TCP/IP Host Name text box. Speak with the database administrator responsible
for the Oracle database that you plan to use to determine whether you should use
a host name (such as bigserver.galactic_corp.com) or an IP address (such
as 199.188.177.1). Enter the correct database instance in the Database
Instance text box. ORCL is the default instance that is created during an Oracle
installation. However, the host machine may have more than one Oracle instance so
ORCL may not be the correct entry--;or there may not be an instance named ORCL.
Click OK. Then click OK again to add the database alias to the program when prompted
to do so in the Confirm Adding Database Alias dialog box. (See Figure 30.4.)
Figure 30.4. Confirming the addition
of a new database alias.
To modify a database alias, click the Modify Database Alias radio button and then
click OK. Double-click the database alias that you wish to modify in the Modify Database
Alias window. (See Figure 30.5.)
Figure 30.5. Selecting a database alias
to modify.
Use the Enter Modification Information dialog box to change the database alias, the
host name, or the database instance. (See Figure 30.6.)
Figure 30.6. Changing the definition
of a database alias.
Once you make the desired changes to the database alias, click OK. Confirm your changes
when prompted and click OK. The SQL*Net Easy Configuration program will modify the
configuration files, thereby changing the database alias.
To delete a database alias, click the Delete Database Alias radio button and then
click OK. Use the Delete Database Alias window to delete a database alias. (See Figure
30.7.)
Figure 30.7. Selecting a database alias
to delete.
Double-click the database alias that you want to delete. The Confirm Deleting Database
Alias dialog box window asks you to confirm your deletion request. (Notice that the
default response setting is No.) To delete the database alias, click the Yes radio
button and then click OK. The SQL*Net Easy Configuration program will delete all
references to the database alias from the configuration files.
To view the configuration of a database alias, click the View Database Alias radio
button and then click OK. In the Choose Database Alias window, double-click the database
alias that you wish to access. A Configuration Information window displays the parameters
for the database alias that was selected. (See Figure 30.8.)
Figure 30.8. Displaying the configuration
information for a database alias.
When you are finished changing database aliases, you can exit the SQL*Net Easy Configuration
program by selecting the Exit SQL*Net Easy Configuration radio button and then clicking
OK.
As I explained in Chapter 3, "Personal Oracle7 for Windows 95: Database Administration Tools," a database connection is defined by specifying a username, the password for the user, and a database alias or connect string. A database alias identifies the machine on which the Oracle database resides and the Oracle instance on that machine to which the connection should be made.
For example, suppose you want to establish an Oracle connection to the corporate server located in Madagascar. (Assume that the networking protocol is TCP/IP.) You must specify the machine on which the Oracle database resides; its name is Antananarivo_Server. You must also specify which Oracle instance to connect to on Antananarivo_Server--;recall that more than one Oracle instance may be running on that machine.
When you start the Navigator, the Main window will display any database connections
that have been defined. Initially, these database connections will not be connected--;indicated
by the icon that looks like two disconnected plugs. You can examine the properties
of a database connection by right-clicking Properties. A single tab folder labeled
General will appear. (See Figure 30.9.)
Figure 30.9. Viewing the properties
of a database connection.
NoteThe Connect text box on the General properties folder might be confusing to some users. It really contains the database alias that is created with the SQL*Net Easy Configuration program. Be aware that the terms connect, connect string, and database alias are used interchangeably within Personal Oracle7.
Among other things, you will see the username and database alias that are used when the database connection is invoked. The objects that are available to you through the database connection are the same objects that you would see if you had a local connection on the remote server. The next few sections examine some of the folders that exist for a remote connection.
If you click the Table folder, you will see a list of the tables and their owners in the right frame of the Main window. If you can't see the full name of tables with very long names, simply slide the right edge of the Name column heading to the right with the mouse.
If you select a table and right-click, the pop-up menu displays four enabled options:
You'll see these same options if you select a table in the local database. For
example, if you select Open, another window will appear, displaying the contents
of the selected table. (See Figure 30.10.)
Figure 30.10. Looking at the contents
of a table in a remote database.
Your ability to view, change, delete, or add to the table in the remote database
depends your object privileges. Obviously, if you are the owner of the table, you
have full privileges--;you can make any changes that you want to make.
However, suppose another Oracle user owns the table, and you have been granted
select privileges on the table. You will still be able to insert or update rows in
the window. When you close the window, the Navigator asks if you want to commit the
insert. (See Figure 30.11.)
Figure 30.11. The Navigator asking the
user if a commit should be performed.
If you choose Yes, the Navigator tells you that you don't have sufficient privileges
to insert the row. (See Figure 30.12.)
Figure 30.12. The Navigator indicates
that the user does not have the necessary privilege.
If you right-click after you have inserted a row in the window, you'll see a pop-up
menu with five items:
Now suppose that you have not been granted the update privilege on the table. The behavior of the Navigator is the same. In the table window, you can still modify the table. Again, when you close window, the Navigator asks if you want to commit the changes. If you select Yes, the Navigator displays the same error message, indicating that you do not have the necessary privilege.
If you right-click after you have changed a row in the window, you'll see a pop-up menu with six items:
Again, the table privileges are enforced by the remote database. If you try to delete a row without having the delete privilege on the remote table, the Navigator displays an error message containing the same error code that appears in Figure 30.12--;Oracle error ORA-01031.
Database links and snapshots are covered in major sections later in this chapter. The most important thing to remember is that these folders display the database links and snapshots that exist in the remote database, not in the local database.
If you do not have the Database Administrator (DBA) role in the remote database specified for the database connection, you will not see a Role folder.
When you are ready to create a database connection, you can either select File
| New | Database Connection from the menu or you can click the Database Connections
folder and right-click New. In either case, you will see a Create Database Connection
dialog box in which you specify the name of the database connection, the Oracle username,
the password for the Oracle username, and the connect string. (See Figure 30.13.)
Figure 30.13. Specifying username, password,
and connect string for a new database connection.
Enter a name for the database connection--;the name does not have to be the same
as the database alias that it references. Enter the name of the Oracle account to
which the connection should be made. Enter the password for the Oracle account. Enter
the connect string to be used for the database connection. You may click the down
arrow to view the existing database aliases. Alternatively, you can create a new
database alias by clicking Configure to open the SQL*Net Easy Configuration program.
Notice the Save Password? check box in the Create Database Connection dialog box. If it is checked, you will not be prompted for the Oracle password when an attempt is made to connect with this database connection. If it is not checked, the Navigator will prompt you for the Oracle account password when attempting to connect.
With the Navigator, connecting to a remote database is as simple as double-clicking the icon for the database connection you wish to use. Once the connection is established, the icon will be transformed into a pair of connected plugs. To disconnect a connected database connection, right-click Disconnect and the icon will return to two unconnected plugs.
Deleting a database connection is not at all the same as disconnecting
the connection. Disconnecting is temporary; when you disconnect a database connection,
you can still use it in the future. Deleting is permanent; when you delete a database
connection, the database connection is no longer available. To delete a database
connection, select the database connection that you want to delete. Then you can
either select Edit | Delete from the top menu or right-click and select Delete. The
Navigator will display a warning message, asking you to confirm that you really want
to delete the database connection. (See Figure 30.14.)
Figure 30.14. Confirmation message when
deleting a database connection.
You can think of a database link as an abbreviation for a remote database connection--;an abbreviation that can be used in a SQL statement. A database link specifies a username, password, and database alias. The benefit of a database link is that it allows you to retrieve or modify data in a remote database without disconnecting from the Oracle database to which you are currently connected.
To create a database link with the Navigator, click the Database Link folder and right-click New. Specify the following parameters in the Create Database Link dialog box. (See Figure 30.15.)
Figure 30.15. Creating a database link
with the Navigator.
Once the link has been created, it appears on the right side of the Main window.
If you created a public database link, the owner will be listed as PUBLIC. If you
created a private database link, the owner of the database link will correspond to
the username that you specified when creating it.
If you want to see the properties of a database link, select the database link
and right-click Properties. A window containing all of the properties for the database
link will appear. (See Figure 30.16.)
Figure 30.16. Displaying the properties
of a database link.
To delete a database link with the Navigator, select the database link and right-click Delete. The Navigator will ask you to confirm that you really want to delete the database link.
At times you may prefer to use SQL*Plus to perform a task--;especially if you want to group a large number of SQL statements into a single script. The syntax for creating a database link in SQL*Plus is
create database link database-link connect to username identified by password using 'database-alias';
where database-link is the name of the database link
username is the Oracle username to be used when connecting with the database link
password is the password of the Oracle user username
database-alias is the database alias, created with the SQL*Net Easy Configuration program, that points to the remote database
In the following example, Jim Helmholtz decides to use SQL*Plus to create a database link that will allow him to reference the tables owned by FRAYED_WIRES on the Antananarivo server:
SQL> create database link fw_mad 2 connect to frayed_wires identified by helmholtz 3 using 'ant_server'; Database link created.
To drop a database link with SQL*Plus, use the following syntax:
drop database link database-link;
where database-link is the name of the database link.
For example, if Jim Helmholtz decides that he doesn't want to use a database link, he simply uses the statement in SQL*Plus:
SQL> drop database link fw_mad; Database link dropped.
Now that you know how to create a database link, you are ready to learn how to use one. In general, wherever you reference a table in a SQL statement, you can add @<db_link> to the table name where db_link is the name of an existing database link. For example, suppose that the database link fw_mad exists. The first thing that Jim Helmholtz can do is see what tables are owned by the FRAYED_WIRES Oracle account on the remote database:
SQL> select table_name 2 from user_tables@fw_mad; TABLE_NAME ------------------------------ INSTRUMENT
Helmholtz decides that he wants to look at the contents of the Instrument table:
SQL> describe instrument@fw_mad Name Null? Type ------------------------------- -------- ---- INSTRUMENT_NAME VARCHAR2(30) INSTRUMENT_TYPE VARCHAR2(10) VALUE NUMBER SQL> select instrument_name, instrument_type, value 2 from instrument@fw_mad; INSTRUMENT_NAME INSTRUMENT VALUE ------------------------------ ---------- --------- VIOLIN STRING 1000 TRUMPET BRASS 500 CLARINET WOODWIND 600
If he wanted to modify a row in the remote table, he could enter the following command via SQL*Plus:
SQL> update instrument@fw_mad 2 set value = 1500 3 where 4 instrument_name = 'VIOLIN'; 1 row updated. SQL> select instrument_name, instrument_type, value 2 from instrument@fw_mad 3 where 4 instrument_name = 'VIOLIN'; INSTRUMENT_NAME INSTRUMENT VALUE ------------------------------ ---------- --------- VIOLIN STRING 1500
NoteYou will probably notice a dip in response time when you use a database link--;especially if you're using a dial-up connection. Of course, the response time you experience will depend on the speed of your connection.
A distributed query is a SELECT statement that joins tables from two or more databases. For instance, from a Personal Oracle7 database, you can join two tables--;one from the local database and the other from a remote database. The table in the remote database is referenced with a database link.
Now suppose that Jim Helmholtz has an Instrument table in his Personal Oracle7 database. He also knows that an Instrument table resides on his server in Madagascar. If he wants to know all the instruments that are currently being repaired--;both locally and in Madagascar--;he can issue the following statement in SQL*Plus:
SQL> select instrument_name, instrument_type, value 2 from instrument; INSTRUMENT_NAME INSTRUMENT VALUE ------------------------------ ---------- --------- CELLO STRING 750 TUBA BRASS 700 SQL> select instrument_name, instrument_type, value 2 from instrument@fw_mad; INSTRUMENT_NAME INSTRUMENT VALUE ------------------------------ ---------- --------- VIOLIN STRING 1500 TRUMPET BRASS 500 CLARINET WOODWIND 600 SQL> select instrument_name, instrument_type, value 2 from instrument 3 union 4 select instrument_name, instrument_type, value 5 from instrument@fw_mad; INSTRUMENT_NAME INSTRUMENT VALUE ------------------------------ ---------- --------- CELLO STRING 750 CLARINET WOODWIND 600 TRUMPET BRASS 500 TUBA BRASS 700 VIOLIN STRING 1500
Sometimes, you might not want to use the database link notation to reference a table in a remote database--;or you might not want other users to know that a remote table is being referenced. In this case, you can create a synonym. For instance, if Jim Helmholtz wants to hide the fact that the Instrument table is located on a remote server, he can use SQL*Plus to create a public synonym:
SQL> create public synonym repair_instrument for 2 instrument@fw_mad; Synonym created. SQL> desc repair_instrument Name Null? Type ------------------------------- -------- ---- INSTRUMENT_NAME VARCHAR2(30) INSTRUMENT_TYPE VARCHAR2(10) VALUE NUMBER
Now, other users can use the synonym repair_instrument to access the Instrument table on the remote server.
A snapshot is a local copy of a table that exists in a remote Oracle database. A snapshot can be either read-only or updatable. Not surprisingly, the contents of a read-only snapshot cannot be modified, and the contents of an updatable snapshot can be modified. However, an updatable snapshot requires the configuration of Symmetric Replication in the remote Oracle database, a subject that is beyond the scope of this book.
Since most users read a table more frequently than they modify it, a snapshot can reduce network traffic by providing users with a fairly current local copy of a table. In other words, a user can read the snapshot and modify the remote table. A snapshot is most appropriate for a remote table that changes infrequently.
If you select the Snapshot folder and right-click New in an attempt to create
a new snapshot, the Navigator tells you that the correct way to create a new
snapshot is to drag the table from a remote database (that is currently connected)
and drop it into the Snapshot folder. You'll notice that the Navigator allows you
drop the table into the Snapshot folder only. Figure 30.17 shows the window that
Navigator displays when creating a snapshot of the Instrument table from the frayed_wires_at_madagascar
database connection.
Figure 30.17. Creating a snapshot of
the Instrument table.
Notice that you have a choice for the snapshot type: either updatable or read-only.
The default snapshot type is updatable. However, be aware that updatable snapshots
require that the server be configured for Symmetric Replication.
The settings in the Next Refresh group box control both the refresh option and frequency for the snapshot. The default setting for both hours and minutes is 0. If hours and minutes are both equal to 0, then the snapshot will not be automatically refreshed at a specified frequency. If hours and minutes are equal to some other value, then the combination of the specified hours and minutes will be used as the refresh frequency for the snapshot.
If an Oracle account for the Oracle user that owns the data on the remote database
doesn't exist in the local database, the Navigator will create the user and prompt
you to supply a password. (See Figure 30.18.) This user will be the owner of the
snapshot.
Figure 30.18. The Navigator prompts
for a password for the snapshot owner.
For instance, if Jim Helmholtz creates a read-only snapshot of the Instrument table
and specifies the refresh frequency as 0 hours and 2 minutes, then
the snapshot of the Instrument table will be refreshed every 2 minutes.
To look at the contents of a snapshot with the Navigator, choose the snapshot
that you want to look at and right-click Open. You can inspect the values in each
column. (See Figure 30.19.)
Figure 30.19. Viewing the contents of
a snapshot with the Navigator.
If you want to force a refresh of a snapshot with the Navigator, select the snapshot and right-click Refresh.
NoteA snapshot returns only the committed changes in the remote table. In other words, if an Oracle user inserts a row in the remote table, you will not see the new row until the user performs a commit--; regardless of the refresh frequency.
To examine the properties of a snapshot with the Navigator, choose the snapshot that you want to examine and right-click Properties. The Navigator displays a window containing the snapshot properties. In the group box labeled Refresh, you'll notice that the refresh date is specified as sysdate + 2/1440. This statement signifies that the snapshot will be refreshed at sysdate (which represents now) plus a fraction (2/1440). (The fractional portion is in units of days, and a day has 1,440 minutes.) Also, in the box labeled Query, you will see the SELECT statement that was used to construct the snapshot.
To delete a snapshot with the Navigator, choose the snapshot that you want to delete and right-click Delete. The Navigator will ask you to confirm that you really want to delete the snapshot.
You can also create a snapshot with SQL*Plus. You will need to be connected as an Oracle user who has the DBA role or the specific privileges to create a snapshot. The syntax for a simplified form of the statement is
create snapshot snapshot-name [refresh start with start-date next next-date] as query
where snapshot-name is the name of the snapshot (subject to the usual Oracle naming restrictions)
start-date is the starting date/time for the initial snapshot of the remote table
next-date is the next date/time for refreshing the snapshot
query is a SELECT statement that references a table in a remote database via a database link
For example, Jim Helmholtz creates a snapshot with a refresh frequency of one hour with this statement:
SQL> create snapshot mad_instrument 2 refresh start with sysdate next sysdate + 1/24 3 as 4 select instrument_name, instrument_type, value 5 from instrument@fw_mad; Snapshot created.
If you are trying to connect to a remote database, you may experience a number of Oracle errors. Here are some of the more common error situations:
If the Navigator displays this error, you have probably supplied an invalid username or password for that user. Verify that the username and password are correct. Also, verify that the username and password are correct for the database alias that you specified. You may have the correct username and password but for the wrong database alias.
SQL*Net was unable to connect to the host that you specified for the database alias in the database connection for any of several reasons:
The Navigator--;actually SQL*Net--;is telling you that the Oracle instance that you specified for the database alias cannot be found on that server. This error message does provide a useful piece of information: SQL*Net was able to connect to the server. Check your database alias to verify that the instance name is correct; confirm the name with the responsible DBA.
SQL*Net was unable to connect to the host. If you are using a dial-up connection, verify that you are actually connected. Otherwise, check with your network administrator or ISP.
This chapter focuses on the following key concepts:
