Starting SQLPlus.docx

Starting SQLPlus.docx

《Starting SQLPlus.docx》由会员分享，可在线阅读，更多相关《Starting SQLPlus.docx（19页珍藏版）》请在冰豆网上搜索。

StartingSQLPlus

StartingSQL*Plus

Thischapterdescribeshowtostart,login,andconnecttoadatabase,howtogethelp,andhowtoexitSQL*Plus.

Specifictopicsdiscussedare:

∙LoginUsernameandPassword

∙ConnectingtoaDatabase

∙StartingSQL*Plus

∙ExitingSQL*Plus

∙SQLPLUSProgramSyntax

LoginUsernameandPassword

WhenyoustartSQL*Plus,youneedausernameandpasswordtologintoanOracleDatabaseschema.YourusernameandpasswordidentifyyouasanauthorizeduseroftheOracleDatabaseschema.

Thedatabaseadministrator（DBA）isresponsibleforcreatingyourdatabaseaccountwiththenecessaryprivilegesandgivingyoutheusernameandpasswordthatenablesyoutoaccessyouraccount.

DefaultloginsarecreatedandyouarepromptedforassociatedpasswordsduringOracleDatabaseinstallation.Someofthedefaultloginusernamescreatedare:

∙SYS

∙SYSTEM

∙HR

LoginsarecreatedanddisplayedinmessagesduringOracleDatabaseinstallation.

Forfurtherinformationaboutthedefaultlogins,seetheOracleDatabaseAdministrator'sGuide.

Onceyouhaveloggedin,youcanconnectunderadifferentusernamewiththeSQL*PlusCONNECTcommand.Theusernameandpasswordmustbevalidforthedatabase.Forexample,toconnecttheusernameTODDtothedefaultdatabaseusingthepasswordFOX,youcouldenter

CONNECTTODD/FOX

Inthecommand-lineinterface,ifyouomittheusernameandpassword,SQL*Pluspromptsyouforthem.YoualsohavetheoptionoftypingonlytheusernamefollowingCONNECTandomittingthepassword（SQL*Plusthenpromptsforthepassword）.BecauseCONNECTfirstdisconnectsyoufromyourcurrentdatabase,youwillbeleftunconnectedtoanydatabaseifyouuseaninvalidusernameandpasswordinyourCONNECTcommand.

Ifyoulogonorconnectasauserwhoseaccounthasexpired,youarepromptedtochangeyourpasswordbeforeyoucanconnect.

Ifanaccountislocked,amessageisdisplayedandconnectionasthisuserisnotpermitteduntiltheaccountisunlockedbyyourDBA.

YoucanusetheDISCONNECTcommandtodisconnectfromadatabasewithoutleavingSQL*Plus.

ChangingyourPassword

Inthecommand-lineinterface,youcanchangeyourpasswordwiththePASSWORDcommand.See"PASSWORD".

ChangingYourPasswordiniSQL*Plus

YoucanchangeyourOracleDatabaseaccountpasswordintheChangePasswordscreen.IfyouhaveloggedinwithDBAprivileges,youcanchangethepasswordofotherusers.YouaccesstheChangePasswordscreenfromthePreferencesscreen.

Descriptionoftheillustrationprefs_password.gif

Username:

EnteryourOracleDatabaseaccountusername.

Oldpassword:

EnteryourcurrentOracleDatabaseaccountpassword.

Newpassword:

Enteryournewpassword.

Retypenewpassword:

Enteryournewpasswordagaintomakesureyouhaveentereditcorrectly.

Apply

ClicktheApplybuttontochangethepasswordforyourOracleDatabaseaccount.

Cancel

ClicktheCancelbuttontoclearthescreenwithoutchangingyourpassword.

ExpiredPassword

Inthecommand-lineinterface,ifyourpasswordhasexpired,SQL*Pluspromptsyoutochangeitwhenyouattempttologin.Youareloggedinonceyousuccessfullychangeyourpassword.

ExpiredPasswordScreeniniSQL*Plus

Ifyourpasswordhasexpired,theExpiredPasswordscreenisautomaticallydisplayedwhenyouattempttologin.FilloutthefieldsontheExpiredPasswordscreenasyouwouldfortheChangePasswordscreen.

Youareloggedinonceyousuccessfullychangeyourpassword.IfyouclicktheCancelbutton,youarereturnedtotheLoginscreen.

Descriptionoftheillustrationpassword_expired.gif

ConnectingtoaDatabase

YoumustconnecttoanOracleDatabase（instance）beforeyoucanqueryormodifydatainthatdatabase.Youcanconnecttothedefaultdatabaseandtootherdatabasesaccessiblethroughyournetwork.Toconnecttoanotherdatabaseoveranetwork,bothdatabasesmusthaveOracleNetconfigured,andhavecompatiblenetworkdrivers.Youmustentereitheraconnectionidentifieroranetservicenametoconnecttoadatabaseotherthanthedefault.

Theconnectionidentifierornetservicenameisentered:

∙asanargumenttotheSQLPLUSProgramSyntaxwhenstartingacommand-linesession.

∙intheConnectionIdentifierfieldintheiSQL*PlusLoginScreenwhenstartingiSQL*Plus.

∙intheHostStringfieldintheLogOndialogwhenStartingtheWindowsGraphicalUserInterface.

∙asanargumenttotheCONNECTcommandfromacurrentsession.

NetServiceName

YourDBAisresponsibleforcreatingthedatabasesyouuseanddefiningnetservicenamesfortheminthetnsnames.orafile.IniSQL*Plus,yourDBAcanalsorestrictthedatabasesavailabletothoseshowninadropdownlistofnetservicenames.

Anetservicenamedefinitioninthetnsnames.orafilehasthesyntax:

net_service_name=

（DESCRIPTION=

（ADDRESS=（PROTOCOL=tcp）（HOST=host）（PORT=port））

（CONNECT_DATA=

（SERVICE_NAME=service_name）））

Touseanetservicename（alias）,itmusthaveanentryinthetnsnames.orafileonthemachinerunningSQL*Plus,orforiSQL*Plus,themachinerunningtheiSQL*PlusApplicationServer.Anentryintnsnames.oraisnotrequiredifyouuseaconnectionidentifier.

Example4-1tnsnames.oraentryforthesalesdatabase

SALES1=

（DESCRIPTION=

（ADDRESS=（PROTOCOL=tcp）（HOST=sales-server）（PORT=1521））

（CONNECT_DATA=

（SERVICE_NAME=）））

Example4-2Startacommand-linesessiontothesalesdatabaseusingthenetservicename

SQLPLUShr/password@SALES1

SeetheOracleNetServicesReferenceGuideandtheOracleNetServicesAdministrator'sGuideformoreinformationaboutdatabaseconnectionsandnetservicenamedefinitions.

FullConnectionIdentifier

Dependingonyourconfiguration,usethefullconnectionidentifiersyntaxlike:

（DESCRIPTION=

（ADDRESS=（PROTOCOL=tcp）（HOST=host）（PORT=port））

（CONNECT_DATA=

（SERVICE_NAME=service_name）））

Youcanoptionallyusethe（INSTANCE_NAME=instance）phraseinplaceofthe（SERVICE_NAME=service_name）phrase.

WhenconnectingtoanOracle8idatabase,usethe（SID=name）phraseinplaceofthe（SERVICE_NAME=service_name）phrase.

Example4-3FullconnectionidentifierforSALES1

SQLPLUShr/password@（DESCRIPTION=

（ADDRESS=（PROTOCOL=tcp）（HOST=sales-server）（PORT=1521））

（CONNECT_DATA=

（SERVICE_NAME=）））

EasyConnectionIdentifier

Theeasyorabbreviatedconnectionidentifierhasthesyntax:

[//]host[:

port][/[service_name]]

Example4-4Startacommand-linesessiontothesalesdatabaseusingtheeasyconnectionidentifier

sqlplushr/password@sales-server:

1521/

Example4-5CONNECTtothesalesdatabaseusingtheeasyconnectionidentifier

connecthr/password@sales-server:

1521/

Theeasyconnectionidentifiercanbeusedwhereveryoucanuseafullconnectionidentifier,oranetservicename.Theeasysyntaxislesscomplex,andnotnsnames.oraentryisrequired.

ConnectionlessSessionwith/NOLOG

Inthecommand-lineinterface,itispossibletostartSQL*Pluswithoutconnectingtoadatabase.Thisisusefulforperformingsomedatabaseadministrationtasks,writingtransportablescripts,ortouseSQL*Pluseditingcommandstowriteoreditscripts.

Youusethe/NOLOGargumenttotheSQLPLUScommandtostartaconnectionlesscommand-linesession.AfterSQL*PlushasstartedyoucanconnecttoadatabasewiththeCONNECTcommand.

Example4-6StartaconnectionlessSQL*Plussessionwith/NOLOG

SQLPLUS/NOLOG

StartingSQL*Plus

IfyouareconnectingtoaremoteOracledatabase,makesureyourOracleNetsoftwareisinstalledandworkingproperly.Formoreinformation,seetheOracleNetServicesAdministrator'sGuide.

WhenyoustartaSQL*Pluscommand-lineorWindowsGUIsession,andafteraCONNECTcommandinthatsession,thesiteprofile,glogin.sql,andtheuserprofilefile,login.sql,areprocessed:

∙AfterSQL*Plusstartsandconnects,andpriortodisplayingthefirstprompt.

∙AfterSQL*Plusstartsandconnects,andpriortorunningascriptspecifiedonthecommand-line.

∙Priortothefirstpromptwhen/NOLOGisspecifiedonthecommand-lineandnoconnectionismade.

Thesiteprofilefile,glogin.sqlisprocessedfirst,thentheuserprofilefile,login.sql.

WhenyoustartaniSQL*Plussession,andafteraCONNECTcommandinthatsession,thesiteprofile,glogin.sql,isprocessed:

∙AfteriSQL*Plusstartsandconnects.

∙AfteriSQL*Plusstartsandconnects,andpriortorunningascriptspecifiedinadynamicURL.

BehaviorinSQL*Plus10.1maybeunexpecteddependingonthesettingofSETSQLPLUSCOMPATIBILITY.Forexample,processingglogin.sqlandlogin.sqlafteraCONNECTcommandonlyoccurswiththedefaultSQLPLUSCOMPATIBILITYsettingof10.1.Formoreinformation,seeSETSQLPLUSCOMPAT[IBILITY]{x.y[.z]}.

StartingCommand-lineSQL*Plus

TobeginusingSQL*Plus,youmustfirstunderstandhowtostartandstopSQL*Plus.

Example4-7StartingSQL*Plus

ThisexampleshowsyouhowtostartSQL*Plus:

1.MakesurethatSQL*Plushasbeeninstalledonyourcomputer.

2.Logontotheoperatingsystem（ifrequired）.

3.Enterthecommand,SQLPLUS,andpressReturn.

Note:

Someoperatingsystemsexpectyoutoentercommandsinlowercaseletters.Ifyoursystemexpectslowercase,entertheSQLPLUScommandinlowercase.

SQLPLUS

SQL*Plusdisplaysitsversionnumber,thecurrentdate,andcopyrightinformation,andpromptsyouforyourusername（thetextdisplayedonyoursystemmaydifferslightly）:

SQL*Plus:

Release10.1.0.2.0-ProductiononThuOct516:

29:

012003

（c）Copyright1982,2003OracleCorporation.Allrightsreserved.

Enteruser-name:

4.EnteryourusernameandpressReturn.SQL*Plusdisplaystheprompt"Enterpassword:

".

5.EnteryourpasswordandpressReturnagain.Foryourprotection,yourpassworddoe