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.
Figure 3-1. iSQL*Plus architecture
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.
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 .
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:
Thus, to connect to i SQL*Plus running on the database server named gennick01. gennick .com , you would enter the following URL:
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:
Figure 3-2. The iSQL*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.
Figure 3-3. The iSQL*Plus Workspace
Several things are worth noticing about this 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.
Which Port to Use?
As I mentioned, the hardest part of using i SQL*Plus might be remembering, or figuring out, the correct URL and which port to use. The following information might be helpful:
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:
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 gennick01.gennick.com to reach a database on gennick02.gennick.com, you must have an i SQL*Plus DBA password on gennick01.gennick.com.
18.104.22.168 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.
To create a JAZN user for i SQL*Plus, use the JAZN shell. Follow these steps:
oracle@gennick02:~> export JAVA_HOME=$ORACLE_HOME/jdk
oracle@gennick02:~> cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus
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
JAZN:> adduser "iSQL*Plus DBA" dude secret
JAZN:> grantrole webDba "iSQL*Plus DBA" dude
JAZN:> setpasswd "iSQL*Plus DBA" admin welcome secret
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 .
22.214.171.124 Authenticating to iSQL*Plus as a DBA
To access the DBA login page, append /dba to the URL you normally use to invoke iSQL*Plus. For example, if you normally use:
then to log in as a DBA, you would use:
Entering the DBA URL should result in the login dialog shown in Figure 3-4.
Figure 3-4. Authenticating to iSQL*Plus as a DBA
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.
Figure 3-5. Logging in to Oracle as SYSDBA
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.
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.
Figure 3-6. Disconnecting from an iSQL*Plus session
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.
Introduction to SQL*Plus
A Lightning SQL Tutorial
Generating Reports with SQL*Plus
Creating HTML Reports
Writing SQL*Plus Scripts
Extracting and Loading Data
Exploring Your Database
Tuning and Timing
The Product User Profile
Customizing Your SQL*Plus Environment
Appendix A. SQL*Plus Command Reference
Appendix B. SQL*Plus Format Elements
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
Authors: Jonathan Gennick