Starting Command-Line SQL*Plus

2017-11-03 09:05:07

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).

Choosing Your Default Database

On Linux and Unix, when you're running SQL*Plus on the same machine that is the database server, you'll often use the oraenv utility to specify the (local) database to which you want to connect. For example, to set your default database to the db01 instance, specify the following:

oracle@gennick02:~> . oraenv ORACLE_SID = [prod] ? db01

You specify the database, which must be local to your machine, by typing its system identifier (SID) in response to the prompt. This example shows the Oracle SID being changed from prod to db01 . Subsequent invocations of SQL*Plus and other Oracle utilities will connect to db01 .

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 SettingsJonathanGennick> 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 SettingsJonathanGennick> 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 :

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 SettingsJonathanGennick> 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.

Using the NOLOG Option

Using the /NOLOG command-line option, you can start SQL*Plus without connecting to any database at all. If you're a DBA, you'll use that option often, as it's a common way to connect using the SYSDBA and SYSOPER roles. For example:

oracle@gennick02:~> sqlplus /nolog SQL*Plus: Release 10.1.0.2.0 - Production on Thu Apr 22 20:52:10 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. SQL> connect system as sysdba Enter password: Connected. SQL>

This example uses /NOLOG to start SQL*Plus without making a database connection. A CONNECT command is then issued to connect as the SYSTEM user in the SYSDBA role. Once connected, this user can perform administrative tasks such as shutting down or recovering the database.

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 information ”username and password in this example ”are 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.

Connecting as SYSDBA

Using /NOLOG isn't the only way to connect in the SYSDBA or SYSOPER roles. (See the earlier sidebar "Using the /NOLOG Option".) You can specify those roles from the command line by enclosing your connection information within quotes:

oracle@gennick02:~> sqlplus "/ AS SYSDBA" SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 9 20:19:52 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>

In SQL*Plus 10.1 and higher, it is no longer necessary to enclose / AS SYSDBA within quotes.

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

Choosing Your Default Database

Using the NOLOG Option

Connecting as SYSDBA

Категории