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

3.1 Starting iSQL*Plus

Starting i SQL*Plus and connecting to a database
is relatively easy. The most difficult part is to remember the URL to
type into your web browser.

3.1.1 Starting the iSQL*Plus Server

If you're the DBA, you may need to worry about
starting the i SQL*Plus server. The concept is
similar to starting an Oracle Net listener. The
i SQL*Plus server is what clients running web
browsers connect to when they enter the
i SQL*Plus URL into their web browsers. Figure 3-1 illustrates.

To start the i SQL*Plus server in Oracle Database
10 g on a Unix or Linux system, log in as the
Oracle software owner and issue the isqlplusctl
start command:

oracle@gennick02:~> isqlplusctl start
iSQL*Plus 10.1.0.2.0
Copyright (c) 2004 Oracle. All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.

Once you've started i SQL*Plus
on your server, clients will be able to freely connect and use
i SQL*Plus via their web browsers. You can use
the isqlplusctl stop command to shut down the
i SQL*Plus server, although
you'll normally want to leave
i SQL*Plus running for as long as your database
is open.

To start Oracle Database
10 g 's
i SQL*Plus server under Windows, go to the Services control
panel, look for the Oracle service with a name ending in
"iSQL*Plus", and start that
service.

You can also use isqlplusctl start to start the i SQL*Plus server under Windows. Issue the command from the Windows command prompt. You may need to leave open the command-prompt window from which you issue the command, but I did not find that to be the case when running Oracle Database 10 g under Windows XP. You may want to investigate the use of the Windows start command, as in start isqlplusctl start .

In Oracle9 i Database, iSQL*Plus starts and stops
along with the HTTP Server. Under Unix, use the commands
apachectl start and apachectl
stop. Under Windows, use Apache -k
start and Apache -k shutdown.

If you're using the Oracle9 i Database version of i SQL*Plus, and you start the HTTP Server while logged in as the Oracle software owner, then any user connecting through i SQL*Plus will be able to connect as SYSDBA. Thus, you should start HTTP Server as some other user.

3.1.2 Using a Supported Browser

To connect to a database through i SQL*Plus, you
need to be using a web browser that supports HTML 4.0 Transitional or
higher. Oracle's i SQL*Plus
documentation specifically mentions Netscape Navigator 4.7 or later,
and Microsoft Internet Explorer 5.0 or later. These are the browsers
Oracle officially supports. However, you should be able to use any
reasonably current browser. I've had good luck using
the Opera web browser Version 7.23, Mozilla 1.4, and Konqueror 3.0.3.

3.1.3 Connecting to a Database

You start i SQL*Plus in your web browser by
entering a URL. In Oracle Database 10 g , the
default URL takes the following form:

http://server.domain.com:5560/isqlplus Thus, to connect to i SQL*Plus running on the
database server named , you
would enter the following URL:

http://:5560/isqlplus 5560 is the default port number used by
i SQL*Plus in Oracle Database
10 g . Your DBA may have changed that port number.
If you have doubts about the URL, ask your DBA. If you
are the DBA, see the sidebar
"Which Port to Use?".

Upon entering the correct URL, your browser will display the
i SQL*Plus login page shown in Figure 3-2, which has been filled out to connect to a
database through the net service name db01. From
here, you have two choices:

You can enter only a username and password to connect to the default
database. Be aware that this default database is the one specified
for i SQL*Plus on the server. If you have
specified a default database at the client level, that has no effect
here.

You can also enter a connect identifier , usually
a net service name, to connect to some other database. If you use a
net service name, that net service name is resolved on the
i SQL*Plus server, not on your local machine.

The term connect identifier is synonymous with connect string . Oracle uses connect string in some of its documentation but uses connect identifier on the i SQL*Plus login page.

After entering your login information, click the Login button.
You'll be logged in to your database and presented
with the i SQL*Plus Workspace page shown in Figure 3-3.

Several things are worth noticing about this page:

The biggest element on the pageyou can't miss
itis the text box into which you type SQL statements, SQL*Plus
commands, and PL/SQL blocks.

Just above and to the right of the text box is a recap of your login
information, minus the password.

There is a Logout button that you might mistake as being part of the
page header. To log out, click the round icon with the key at the
upper right of the page.

For the most part, operation of i SQL*Plus is
fairly simple. Type a command, statement, or block into the text box
and click the Execute button.

3.1.4 Connecting as an Administrator

In Oracle Database 10 g , you can use a special
URL to connect to i SQL*Plus in the SYSDBA or
SYSOPER administrative
roles.
You can then proceed to
use i SQL*Plus for administrative tasks such as
starting and stopping your database.

When you connect to i SQL*Plus as a DBA, you must
authenticate twice, as in the following process:

Enter a special URL that brings up an i SQL*Plus
DBA login dialog.

Enter a username and password to identify yourself to
i SQL*Plus as a DBA. This username/password has
meaning only to i SQL*Plus and is unrelated to
your database username/password.

i SQL*Plus takes you to a special version of the
login page that includes a pull-down menu for choosing between SYSDBA
and SYSOPER.

Authenticate to your database using your database username/password.

Before you can use i SQL*Plus as SYSDBA or
SYSOPER, you must create a special username and password to use in
the first step. This username and password is tied to the specific
instance of i SQL*Plus to which you connect.
Thus, if you connect to i SQL*Plus on

3.1.4.1 Creating iSQL*Plus DBA users

To authenticate you as a DBA, i SQL*Plus uses a
Java Authentication and
Authorization Service (JAAS) provider named Java
AuthoriZatioN , which Oracle calls JAZN. You
must create a username/password within JAZN in order to use
i SQL*Plus as SYSDBA or SYSOPER.

You can store i SQL*Plus DBA usernames and passwords within the Oracle Internet Directory. For details on doing that, refer to the chapter "Configuring SQL*Plus" in the Oracle SQL*Plus User's Guide and Reference (Oracle Corporation).

To create a JAZN user for i SQL*Plus, use the
JAZN shell. Follow these steps:

Log into your database server as the Oracle software owner, or as a
user having the DBA role.

Create a JAVA_HOME environment variable:

oracle@gennick02:~> export JAVA_HOME=$ORACLE_HOME/jdk You must invoke the JAZN utility from the correct directory. If you
fail to do so, this process will also fail. Set your current working
directory as follows:

oracle@gennick02:~> cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus Invoke the JAZN shell for the "iSQL*Plus
DBA" realm as the admin user
using the following, rather horrendous command. Type it as one, long
line. Type it exactly as shown, but supply your own password if
you've changed it from the default of
welcome:

oracle@gennick02:/oracle10g/product/10gr1/oc4j/j2ee/isqlplus/application-
deployments/isqlplus> $JAVA_HOME/bin/java -Djava.security.properties=$ORACLE_
HOME/sqlplus/admin/iplus/provider -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -user
"iSQL*Plus DBA/admin" -password welcome -shell Issue an adduser command to create a new,
i SQL*Plus DBA user. The following example
creates a user named dude with a password of
secret:

JAZN:> adduser "iSQL*Plus DBA" dude secret Grant the webDba role to the user you just
created:

JAZN:> grantrole webDba "iSQL*Plus DBA" dude If this is your first time using the JAZN shell, change the
admin password to something other than the
default. The following example changes the admin
password from welcome to
secret:

JAZN:> setpasswd "iSQL*Plus DBA" admin welcome secret Exit the JAZN utility:

JAZN:> exit In order for i SQL*Plus to see the new JAZN user
you've created, you'll need to
bounce i SQL*Plus by issuing the command
isqlplusctl stop followed by
isqlplusctl start.

3.1.4.2 Authenticating to iSQL*Plus as a DBA

To access the DBA
login page, append Figure 3-4.

Enter the username and password you created from the JAZN shell, and
you should be taken to the login page shown in Figure 3-5. This login page will have an additional
field, a pull-down menu from which you can choose to connect as
SYSDBA or SYSOPER.

Upon logging in to your database as SYSDBA or SYSOPER,
you'll be taken to the same
i SQL*Plus workspace page shown in Figure 3-3. The difference is that you will be able to
execute commands such as STARTUP and SHUTDOWN.

Upon logging out of an i SQL*Plus DBA session, be sure to close your browser, unless you plan to immediately connect again in either the SYSDBA or SYSOPER role. Once you authenticate to i SQL*Plus as a DBA (as in Figure 3-4), you remain recognized as a DBA, and you retain the ability to choose SYSDBA and SYSOPER as long as your browser remains open.

3.1.5 Ending a Session

Figure 3-6 illustrates two ways to end an
i SQL*Plus session. You can click the Logout
button in the upper right of the page, or you can issue the
SQL*Plus
DISCONNECT command.

Clicking the Logout button returns you to the login page. Issuing the
DISCONNECT command leaves you in a position to enter further SQL*Plus
commands but neither SQL statements nor PL/SQL blocks. Frankly, I
don't see much value in being able to issue SQL*Plus
commands in i SQL*Plus while not connected to a
database.

You can follow a DISCONNECT command with a CONNECT command to create
a new database connection. However, if you're going
to do that, you might as well enter the CONNECT command to begin with
and skip the DISCONNECT step.