Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

2.1 Starting Command-Line SQL*Plus

You'd think that starting SQL*Plus and connecting to
a database would be a simple affair to explain, but it
isn't. There are many permutations available for
entering your username and password, and for specifying the target
database. You've seen a couple of them already in
Chapter 1. I won't cover every
possibility in this section, only those permutations that are most
useful.

2.1.1 Connecting to a Default Database

Perhaps the simplest way to start SQL*Plus is to issue the
sqlplus command
and let SQL*Plus prompt you for your username and password:

oracle@gennick02:~> sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 20:17:47 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: gennick
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

This approach works well if you're connecting to a
default database, usually running on the same machine that you are
logged into. SQL*Plus does not echo your password to the display,
protecting you from those who would steal your password by looking
over your shoulder as you type.

Prior to Oracle8 i Database, SQL*Plus executables under Windows were named plus80 (Oracle8), plus73 (Oracle7.3), plus72 (Oracle7.2), and plus71 (Oracle7.1).

2.1.2 Connecting to a Remote Database

To connect to a remote database, you must supply a
connect
string as part of your login. The
connect string specifies the target database to which you wish to
connect, and can take on several forms. Commonly, your DBA will
configure what is called a net service name for
you to use in connecting to a remote database. However, SQL*Plus
won't prompt for this net service name. How then, do
you enter it?

One way to enter a connect string is type it after your username,
separating the two values with an at sign (@) character:

C:\Documents and Settings\JonathanGennick>sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 20:28:21 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: gennick@db01
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

In this example, db01 is the net service name
defined by my DBA. It happens to be defined in a file known as
tnsnames.ora, and its definition looks like
this:

DB01 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gennick02.gennick.com)(PORT
= 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = db01.gennick.com)
)
) If for some reason you don't have a net service name
defined, aren't able to define one, and desperately
need to connect to a remote database anyway, and you happen to know
all the relevant connection information, you can provide your
connection details in the tnsnames.ora format:

C:\Documents and Settings\JonathanGennick>sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 20:38:58 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: gennick@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS =
(PROTOCOL =
CP)(HOST = gennick02.gennick.com)(PORT = 1521)))(CONNECT_DATA =
(SERVICE_NAME =
db01.gennick.com)))
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

The connect string in this example is (DESCRIPTION . . .
db01.gennick.com))). Truly, you would need to be desperate,
and a bit of an Oracle networking wizard, to remember and use such
convoluted syntax. However, bear in mind that the
tnsnames.ora syntax was never really designed
for interactive use.

Fortunately, for those of us who are challenged by the task of
matching up so many parentheses, SQL*Plus in Oracle Database
10 g recognizes a much simpler syntax, at least
for TCP/IP connections. This syntax is called the easy
connection identifier . Here is the general format of this
simplified connect string:

//host:port/service You should be able to use this syntax as follows:

C:\Documents and Settings\JonathanGennick>sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 20:38:58 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: gennick@//gennick02.gennick.com:1521/db01.gennick.com
...

Unfortunately, in Oracle Database 10 g Release 1
there is a bug that prevents this syntax from working. One workaround
is to append the connect string after your password. If you could see
your password when typing, that workaround would look like this:

C:\Documents and Settings\JonathanGennick>sqlplus
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 20:50:35 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter user-name: gennick
Enter password: secret@//gennick02.gennick.com:1521/db01.gennick.com
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

SQL*Plus does not echo characters typed in response to the password
prompt, making this workaround rather difficult to manage. One
workaround for that issue is to first invoke
SQL*Plus without logging in, which you do by specifying the /NOLOG
option, and then issuing a CONNECT command with all the necessary
login and connection information:

C:\>sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 9 18:52:01 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
SQL> CONNECT gennick/secret@//gennick02.gennick.com:1521/db01.gennick.com
Connected.
SQL>

Of the previous two alternatives, this last approach is best on Unix
and Linux systems because it ensures that your password is not
visible to anyone executing a ps command (which
displays commands and arguments used to start running programs).

Read more about CONNECT later, in Section 2.3.

2.1.3 Specifying Login Details on the Command Line

I've always found it easiest to type my login
information on the SQL*Plus command line, thus avoiding the entire
prompt/response process. I want to get to that
SQL> prompt just as fast as I can. To that end,
you can specify your username and password on the command line as
follows:

oracle@gennick02:~> sqlplus gennick/secret
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 21:13:47 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

This is nice. But nothing good comes without its price. You must be
aware of two problems when providing login details on the command
line. One rather obvious problem is that your login
informationusername and password in this exampleare
visible to onlookers until you scroll that information off the
screen. (Be aware, too, of the ability to scroll back, and of the
possibility under Unix/Linux that your commands may be recorded in a
shell history file.) Another, less obvious problem is that some Unix
and Linux systems make your entire command line, password and all,
visible to any system user who happens to issue a
ps command. The following example comes from a
Solaris system (thanks Tom!). The first command reported is the
ps command that is executing, while the second
is an invocation of SQL*Plus clearly showing the username and
password of scott/tigertkyte.

scott@ORA817DEV> !ps -auxww | grep sqlplus
tkyte 22046 0.3 0.1 1512 1264 pts/1 S 13:23:05 0:00 -usr/bin/csh
-c ps -auxww | grep sqlplus
tkyte 22035 0.2 0.4 9824 5952 pts/1 S 13:22:57 0:00 sqlplus
scott/tigertkyte
22054 0.0 0.1 984 768 pts/1 S 13:23:05 0:00 grep sqlplus To play it safe, you can provide just your username on the command
line, and let SQL*Plus prompt you for your password. That way, your
password is never displayed, nor will it show up in any
ps process listing or shell history file:

oracle@gennick02:~> sqlplus gennick
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 21 21:24:45 2004
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

If you're connecting to a remote database, you can
specify username, password, and connect string, all on the command
line, as follows:

sqlplus gennick/secret@db01 Better perhaps, specify only your username and connect string,
leaving SQL*Plus to prompt for your password:

sqlplus gennick@db01 This approach even works when using the rather complex
tnsnames.ora format:

sqlplus gennick@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST =
gennick02.gennick.com)(PORT = 1521)))(CONNECT_DATA = (SERVICE_NAME =
db01.gennick.com)))
Remember that easy connection identifier syntax from the previous
section? Unfortunately, to use it on the command line, you must also
specify the password on the command line. The following will not
work:

sqlplus gennick@//gennick02.gennick.com:1521/db01.gennick.com However, the following will work:

sqlplus gennick/secret@//gennick02.gennick.com:1521/db01.gennick.com This command works because the password, secret in
this example, is given on the command line as part of the
sqlplus command. That shouldn't
be necessary, and with any luck Oracle will fix the problem in a
future release.

A potentially useful program to hide Unix command-line
arguments can be found at
http://www.orafaq.com/scripts/c_src/hide.txt.