Starting SQLPlus.docx

1、Starting SQLPlusStarting 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 Usernam

2、e and PasswordWhen 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 neces

3、sary privileges 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 displaye

4、d in messages during 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 data

5、base. For example, 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 a

6、nd omitting the 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 e

7、xpired, you are 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.Cha

8、nging your PasswordIn 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

9、password of other 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.Rety

10、pe new password: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,

11、 if your password 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 i

12、n. Fill out the 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 Dat

13、abaseYou must connect 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,

14、and have compatible 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

15、 Connection Identifier 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

16、databases you use 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= (DESCRIP

17、TION= (ADDRESS=(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 t

18、nsnames.ora is not 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 n

19、et service nameSQLPLUS 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 ide

20、ntifier syntax like:(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) phras

21、e in place of the (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 th

22、e syntax:/host:port/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 connecti

23、on identifier can 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 databas

24、e. This is useful 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 conne

25、ct to a database 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 S

26、ervices Administrators 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. Af

27、ter SQL*Plus starts 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 a

28、n iSQL*Plus session, 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 th

29、e setting of SET 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

30、first understand 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

31、 systems expect 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 diff

32、er slightly):SQL*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, your password doe