《StartingSQLPlus.docx》由会员分享，可在线阅读，更多相关《StartingSQLPlus.docx（25页珍藏版）》请在三一文库上搜索。
1、Starting SQL*PlusThis chapter describes how to start, login, and connect to a database, how to get help, and how to exit SQL*Plus.Specific topics discussed are: Login Username and Password Connecting to a Database Starting SQL*Plus Exiting SQL*Plus SQLPLUS Program SyntaxLogin Username and PasswordWh
2、en you start SQL*Plus, you need a username and password to login to an Oracle Database schema. Your username and password identify you as an authorized user of the Oracle Database schema.The database administrator (DBA) is responsible for creating your database account with the necessary privileges
3、and giving you the username and password that enables you to access your account.Default logins are created and you are prompted for associated passwords during Oracle Database installation. Some of the default login usernames created are: SYS SYSTEM HRLogins are created and displayed in messages du
4、ring Oracle Database installation.For further information about the default logins, see the Oracle Database Administrators Guide.Once you have logged in, you can connect under a different username with the SQL*Plus CONNECT command. The username and password must be valid for the database. For exampl
5、e, to connect the username TODD to the default database using the password FOX, you could enterCONNECT TODD/FOXIn the command-line interface, if you omit the username and password, SQL*Plus prompts you for them. You also have the option of typing only the username following CONNECT and omitting the
6、password (SQL*Plus then prompts for the password). Because CONNECT first disconnects you from your current database, you will be left unconnected to any database if you use an invalid username and password in your CONNECT command.If you log on or connect as a user whose account has expired, you are
7、prompted to change your password before you can connect.If an account is locked, a message is displayed and connection as this user is not permitted until the account is unlocked by your DBA.You can use the DISCONNECT command to disconnect from a database without leaving SQL*Plus.Changing your Passw
8、ordIn the command-line interface, you can change your password with the PASSWORD command. See PASSWORD.Changing Your Password in iSQL*PlusYou can change your Oracle Database account password in the Change Password screen. If you have logged in with DBA privileges, you can change the password of othe
9、r users. You access the Change Password screen from the Preferences screen.Description of the illustration prefs_password.gifUsername:Enter your Oracle Database account username.Old password:Enter your current Oracle Database account password.New password:Enter your new password.Retype new password:
10、Enter your new password again to make sure you have entered it correctly.ApplyClick the Apply button to change the password for your Oracle Database account.CancelClick the Cancel button to clear the screen without changing your password.Expired PasswordIn the command-line interface, if your passwor
11、d has expired, SQL*Plus prompts you to change it when you attempt to log in. You are logged in once you successfully change your password.Expired Password Screen in iSQL*PlusIf your password has expired, the Expired Password screen is automatically displayed when you attempt to log in. Fill out the
12、fields on the Expired Password screen as you would for the Change Password screen.You are logged in once you successfully change your password. If you click the Cancel button, you are returned to the Login screen.Description of the illustration password_expired.gifConnecting to a DatabaseYou must co
13、nnect to an Oracle Database (instance) before you can query or modify data in that database. You can connect to the default database and to other databases accessible through your network. To connect to another database over a network, both databases must have Oracle Net configured, and have compati
14、ble network drivers. You must enter either a connection identifier or a net service name to connect to a database other than the default.The connection identifier or net service name is entered: as an argument to the SQLPLUS Program Syntax when starting a command-line session. in the Connection Iden
15、tifier field in the iSQL*Plus Login Screen when starting iSQL*Plus. in the Host String field in the Log On dialog when Starting the Windows Graphical User Interface. as an argument to the CONNECT command from a current session.Net Service NameYour DBA is responsible for creating the databases you us
16、e and defining net service names for them in the tnsnames.ora file. In iSQL*Plus, your DBA can also restrict the databases available to those shown in a dropdown list of net service names.A net service name definition in the tnsnames.ora file has the syntax:net_service_name= (DESCRIPTION= (ADDRESS=(
17、PROTOCOL=tcp)(HOST=host)(PORT=port) )(CONNECT_DATA= (SERVICE_NAME=service_name) ) ) To use a net service name (alias), it must have an entry in the tnsnames.ora file on the machine running SQL*Plus, or for iSQL*Plus, the machine running the iSQL*Plus Application Server. An entry in tnsnames.ora is n
18、ot required if you use a connection identifier.Example 4-1 tnsnames.ora entry for the sales databaseSALES1 =(DESCRIPTION =(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521) )(CONNECT_DATA=(SERVICE_NAME=) ) )Example 4-2 Start a command-line session to the sales database using the net service nameS
19、QLPLUS hr/passwordSALES1See the Oracle Net Services Reference Guide and the Oracle Net Services Administrators Guide for more information about database connections and net service name definitions.Full Connection IdentifierDepending on your configuration, use the full connection identifier syntax l
20、ike:(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=port) )(CONNECT_DATA=(SERVICE_NAME=service_name) ) )You can optionally use the (INSTANCE_NAME=instance) phrase in place of the (SERVICE_NAME=service_name) phrase.When connecting to an Oracle8i database, use the (SID=name) phrase in place of th
21、e (SERVICE_NAME=service_name) phrase.Example 4-3 Full connection identifier for SALES1SQLPLUS hr/password(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521) )(CONNECT_DATA=(SERVICE_NAME=) ) )Easy Connection IdentifierThe easy or abbreviated connection identifier has the syntax:/host:p
22、ort/service_nameExample 4-4 Start a command-line session to the sales database using the easy connection identifiersqlplus hr/passwordsales-server:1521/Example 4-5 CONNECT to the sales database using the easy connection identifierconnect hr/passwordsales-server:1521/The easy connection identifier ca
23、n be used wherever you can use a full connection identifier, or a net service name. The easy syntax is less complex, and no tnsnames.ora entry is required.Connectionless Session with /NOLOGIn the command-line interface, it is possible to start SQL*Plus without connecting to a database. This is usefu
24、l for performing some database administration tasks, writing transportable scripts, or to use SQL*Plus editing commands to write or edit scripts.You use the /NOLOG argument to the SQLPLUS command to start a connectionless command-line session. After SQL*Plus has started you can connect to a database
25、 with the CONNECT command.Example 4-6 Start a connectionless SQL*Plus session with /NOLOGSQLPLUS /NOLOG Starting SQL*PlusIf you are connecting to a remote Oracle database, make sure your Oracle Net software is installed and working properly. For more information, see the Oracle Net Services Administ
26、rators Guide.When you start a SQL*Plus command-line or Windows GUI session, and after a CONNECT command in that session, the site profile, glogin.sql, and the user profile file, login.sql, are processed: After SQL*Plus starts and connects, and prior to displaying the first prompt. After SQL*Plus sta
27、rts and connects, and prior to running a script specified on the command-line. Prior to the first prompt when /NOLOG is specified on the command-line and no connection is made.The site profile file, glogin.sql is processed first, then the user profile file, login.sql.When you start an iSQL*Plus sess
28、ion, and after a CONNECT command in that session, the site profile, glogin.sql, is processed: After iSQL*Plus starts and connects. After iSQL*Plus starts and connects, and prior to running a script specified in a dynamic URL.Behavior in SQL*Plus 10.1 may be unexpected depending on the setting of SET
29、 SQLPLUSCOMPATIBILITY. For example, processing glogin.sql and login.sql after a CONNECT command only occurs with the default SQLPLUSCOMPATIBILITY setting of 10.1. For more information, see SET SQLPLUSCOMPATIBILITY x.y.z.Starting Command-line SQL*PlusTo begin using SQL*Plus, you must first understand
30、 how to start and stop SQL*Plus.Example 4-7 Starting SQL*PlusThis example shows you how to start SQL*Plus:1. Make sure that SQL*Plus has been installed on your computer.2. Log on to the operating system (if required).3. Enter the command, SQLPLUS, and press Return.Note:Some operating systems expect
31、you to enter commands in lowercase letters. If your system expects lowercase, enter the SQLPLUS command in lowercase.SQLPLUSSQL*Plus displays its version number, the current date, and copyright information, and prompts you for your username (the text displayed on your system may differ slightly):SQL
32、*Plus: Release 10.1.0.2.0 - Production on Thu Oct 5 16:29:01 2003(c) Copyright 1982, 2003 Oracle Corporation. All rights reserved.Enter user-name:4. Enter your username and press Return. SQL*Plus displays the prompt Enter password:.5. Enter your password and press Return again. For your protection,
33、your password does not appear on the screen.The process of entering your username and password is called logging in. SQL*Plus displays the version of Oracle Database to which you connected and the versions of available tools such as PL/SQL.Next, SQL*Plus displays the SQL*Plus command prompt:SQLThe S
34、QL*Plus command prompt indicates that SQL*Plus is ready to accept your commands.If SQL*Plus does not start, you should see a message to help you correct the problem.Shortcuts to Starting Command-line SQL*PlusWhen you start SQL*Plus, you can enter your username and password, separated by a slash (/),
35、 following the command SQLPLUS. For example, you can enterSQLPLUS HR/your_passwordand press Return.Getting Command-line HelpTo access command-line help for SQL*Plus commands, type HELP or ? followed by the command name at the SQL command prompt or in the iSQL*Plus Workspace Input area. See the HELP
36、command for more information. For example:HELP ACCEPTTo display a list of SQL*Plus commands, type HELP followed by either TOPICS or INDEX. HELP TOPICS displays a single column list of SQL*Plus commands. HELP INDEX displays a four column list of SQL*Plus commands which fits in a standard screen. For
37、example:HELP INDEXStarting the Windows Graphical User InterfaceThe graphical user interface can be started from the Windows menu, or from a Windows command prompt.Starting the GUI from the Windows Menu1. Select Programs in the Start menu. Then select Oracle - ORACLE_HOME, then Application Developmen
38、t, and click SQL Plus.The SQL*Plus window appears displaying the Log On dialog.Description of the illustration logon.gifEnter a valid user name and password. If you are connecting to a remote Oracle database, enter the Oracle Net connect identifier in the Host String field. To connect to the default
39、 database, leave the Host String field blank. See Easy Connection Identifier earlier for more information about configuring and using Oracle Net connect identifiers.2. Click OK.Starting the GUI from the Windows Command Prompt1. Select Command Prompt from Programs-Accessories in the Start menu. A Com
40、mand Prompt window appears.2. Enter3. C: SQLPLUSWThe SQL*Plus graphical user interface starts. You can optionally include your login username and password separated by a slash (/), and a database to connect to, for example:C: SQLPLUSW username/passwordconnect_identifierOtherwise enter the required i
41、nformation in the login dialog as described in Starting SQL*Plus earlier. The Oracle SQL*Plus application window appears.Description of the illustration wingui.gifStarting the iSQL*Plus Application ServerThe iSQL*Plus Application Server must be running on the middle tier before you can start an iSQL
42、*Plus session. A command-line utility and a Windows Service are supplied to start and stop iSQL*Plus on Windows.The iSQL*Plus Application Server is started by default during Oracle Database installation.To Start the iSQL*Plus Application Server on Unix1. Start a terminal session.2. Enter3. $ORACLE_H
43、OME/bin/isqlplusctl startThe iSQL*Plus Application Server is started.To Start the iSQL*Plus Application Server on Windows1. Select Services from the Start Programs Administrative Tools menu.2. Locate the iSQL*Plus Windows Service, OracleOracleHomeNameiSQL*Plus.3. Start the Windows Service.Alternativ
44、ely, you can start iSQL*Plus from a command prompt.To Start iSQL*Plus Application Server from a Command Prompt1. Start a command prompt session.2. Enter3. %ORACLE_HOME%binisqlplusctl startThe iSQL*Plus Application Server is started.To Test If the iSQL*Plus Application Server Has Started Correctly1.
45、Enter the iSQL*Plus URL in your web browser. The iSQL*Plus URL is in the form:2. http:/machine_name:5560/isqlplus/iSQL*Plus uses HTTP port 5560 by default. If iSQL*Plus is not available on port 5560, read the $ORACLE_HOME/install/portlist.ini file to find the port on which iSQL*Plus is running.3. En
46、ter one of the following URLs from a web browser on the machine running the iSQL*Plus Application Server if you do not know the iSQL*Plus URL:4. http:/127.0.0.1:5560/isqlplus/5. http:/localhost:5560/isqlplus/The iSQL*Plus Login screen should be displayed.6. Enter the same URL you used in step 2, wit
47、hout isqlplus/ if the iSQL*Plus Login screen was not displayed in step 2. This is to establish whether the OC4J instance has started correctly:7. http:/127.0.0.1:5560/8. http:/localhost:5560/The OC4J default page should be displayed.If the OC4J default page is not displayed, the iSQL*Plus Applicatio
48、n Server is not running. Also see Testing if the iSQL*Plus Application Server is Running.To Check the HTTP Port used by the iSQL*Plus Application ServerTo discover the HTTP port number used by the iSQL*Plus Application Server, search the $ORACLE_HOME/install/portlist.ini file on the Application Server. Also see Changing the iSQL*Plus Application Server Port in Use.Stop