Establishing SQL*Net Sessions for Oracle Data Warehouses

Establishing SQL*Net Sessions

On systems running SQL*Net version 2.0, the session script can be used to query the number of dedicated and shared servers on the system. For example, Listing 9.2 shows an SQL*Plus script to view all sessions.

Listing 9.2  session.sql displays all connected sessions.

set echo off;
set termout on;
set linesize 80;
set pagesize 60;
set newpage 0;
ttitle "dbname Database|UNIX/Oracle Sessions";
spool /tmp/session
set heading off;
select 'Sessions on database '||substr(name,1,8) from v$database;
set heading on;
select
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
 where
b.paddr = a.addr
and type='USER'
order by spid;
ttitle off;
set heading off;
select 'To kill, enter SQLPLUS>  ALTER SYSTEM KILL SESSION',
''''||'SID, SER#'||''''||';' from dual;
spool off;

[oracle]ram2: sqlx session

Wed Sep 14                                                                    
                                                          Page    1
                                                    ram2db Database
                                               Sessions for SQL*Net

SERVER     Oracle user  O/S      User      Machine Program       
--------- -----------  ------  -------  ------------------------
DEDICATED   SYS        oracle  ram2    sqldba@ram2 (Pipe Two-Task)         

DEDICATED  OPS$REDDY   risdon  ram2    runform30@ram2 (Pipe Two-Task)      

DEDICATED  GLINT       jones   ram2    sqlplus@ram2 (Pipe Two-Task)        

DEDICATED  OPS$ORACLE  oracle  clt2    sqlplus@clt2 (TNS interface)        

DEDICATED  OPS$JOKE    joke    ram2    ?  @ram2 (TCP Two-Task)          

DEDICATED  OPS$WWRIGHT wwright ram2    runmenu50@ram2 (Pipe Two-Task)      

DEDICATED  OPS$ORACLE  oracle  ensc    sqlplus@ensc (TCP Two-Task)         

DEDICATED  SECTION144  OraUser         C:\PB3\PBSYS030.DLL                 

DEDICATED  OPS$ORACLE  oracle  ram2    sqlplus@ram2 (Pipe Two-Task)         

DEDICATED  OPS$JSTARR  jstarr  ram2    sqlforms30@ram2 (Pipe Two-Task)     

DEDICATED  OPS$WWRIGHT wwright ram2    RUN_USEX@ram2 (Pipe Two-Task)       

12 rows selected.

Here, we see each of the following four types of SQL*Net connections:

*     Pipe Two-task--Used for internal tasks (SQLPLUS /).

*     TNS Interface--Used when connection is made with a v2 service name (SQLPLUS /@ram2).

*     TCP Two-task--Used when connection is made with a v1 connect string (SQLPLUS /@t:ram2:ram2db).

*     PC Connection Task--Denoted by the PC DLL name (c:\pb3\pbsys030.dll = initiated via PowerBuilder DLL).

  < ===Please copy this stuff to summary Application Connection With SQL*Net

Now that we have seen how SQL*Net connection are made to Oracle, let’s take a look at how an application specifies a remote database.  Connections to remote databases can be made by specifying either service names or connect strings. Connect strings use the full connection. In the following example, the t: means a TCP/IP connection, host: is the name of the remote processor, and database: is the name of the databases on that processor:

*     Connect with a service name.
emp@my_db

*     Connect with a server connect string.

sqlplus /@t:host:database

Connect strings are stored in the DBA_DBLINKS table, and they are created with the CREATE DATABASE LINK command.

SQL*Net For Oracle Distributed Data Warehouses

SQL*Net can establish database communications in three ways: remote connection, remote request, or distributed request. A remote connection is the easiest way to make a database connection. The sending database simply makes a request by specifying a table name suffixed by @.  SQL*Net takes it from there, seamlessly accessing the remote database and returning the data to the initiating system. Communication is established by making a distributed request to a remote database. Within Oracle, @ specifies the remote database name, but the functionality of the @ operator depends upon where it is used. Here’s an example:

In this request, Scott is using Oracle SQL*Plus SQL command line interface to connect to the London database, and @london is the service name, as defined in the tnsnames.ora file. SQL*Net recognizes this as a remote connection and determines the appropriate linkage to establish communications with London. Internally, Oracle will check the tnsnames.ora file to ensure that london is a valid destination.

Now, observe another way of connecting to London from the same database. This is called a remote request:

Unlike a remote connection made directly from SQL*Plus, this remote request has Scott connecting to the local copy of SQL*Plus to specify the remote table (in this case, employee@london). In order for a remote request to work, a database link must define london. As mentioned earlier, a database link is a connection pathway to a remote database that specifies the service name of the remote database. Without the database link, the following request would fail:

This request will give you an error message that reads: ORA-02019: connection description for remote database not found. This message is received because of the way Oracle defines the @ operator. When entering an Oracle service such as SQL*Plus, the @ operator will go directly to the tnsnames.ora file to manage the request, while the @ operator from within an Oracle program specifies the use of a database link.

To make the code functional, you must define a database link that specifies the service name used to establish the connection. Note that the database link name and the service name are the same in this example, but the database link and the connect descriptor are not related in any way:

Let's take a closer look at the database link. In this simple example, no mention is made of the user ID used to establish the connection on the remote database. Because Scott is the user connecting to SQL*Plus, Scott will be the user ID when the remote connection is established to the London database. Therefore, Scott must have SELECT privileges against the employee table in London in order for the query to work properly. Scott's privileges on the initiating Oracle have no bearing on the success of the query.

Note: If you are using the Oracle Names facility, you must be sure that your database service names are the same as the global_databases_names and the DOMAIN init.ora parameter.

In cases where SELECT security is not an issue, you can enhance the database link syntax to include a remote connect description, as follows:

This way, all users who specify the london database link will connect as Scott1 and will have whatever privileges Scott1 has on the London system.

Once you establish a communications pathway to the remote database, it is often desirable to implement location transparency. In relational databases such as Oracle, you can obtain location transparency by creating database links to the remote database and then assigning a global synonym to the remote tables. The database link specifies a link name and an SQL*Net service name. You can create database links with a location suffix that is associated with a host name (in this example, london).

You can use database links to allow applications to point to other databases without altering the application code. For data warehousing applications, you can replicate a table on another machine and establish links to enable the application to point transparently to the new box containing the replicated table.

To see the links for a database, query the Oracle dictionary, as follows:

Keep in mind that SQL*Net bypasses all operating system connections when it connects to a database. All externally identified user accounts (that is, accounts without an Oracle password) will not be allowed in SQL*Net transactions unless the init.ora parameter is changed. The identified externally clause (OPS$) in Oracle version 6 allows the operating system to manage passwords, but because SQL*Net bypasses the operating system, impostor accounts can be created from other platforms. The result is that security can be bypassed. Consequently, Oracle now recommends forbidding externally identified accounts when using distributed connections, and the OPS$ features is seldom used in Oracle7 or Oracle8.

It is interesting to note that Oracle will allow you to create accounts with an OPS$ prefix. Therefore, the operating system can manage its passwords, while you also have passwords within Oracle. For example, assume the following user definition:

Assuming that Scott has logged onto the operating system, Scott could enter SQL*Plus either with or without a password, as follows: