Oracle Advanced Notes-BASIC preparation-sqlplus User Login

1 Sqlplus Login

Log on locally (log on to the database server)

Oracle Login

Sqlplus account name/password as role name

1.1 SYS Login

Example: Sqlplus sys/oracle

ERROR:

Ora-28009:connection as SYS should be as SYSDBA or sysoper

SYS administrator must have role sysdba

650) this.width=650; "title=" clip_image002 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image002 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305178rkcz.jpg "" 244 "height="/>

[Email protected] ~]$ Sqlplus sys/oracle as Sysdba

650) this.width=650; "title=" clip_image004 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image004 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305178ooc4.jpg "" 244 "height="/>

Execute show user;

Sql> Show user; Show currently logged on user

USER is "SYS"

650) this.width=650; "title=" clip_image005 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image005 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305178cipn.png "" 244 "height="/>

Exit exit

1.2 System Login

Sqlplus system/oracle

650) this.width=650; "title=" clip_image006 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image006 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305179acke.png "" 217 "height="/>

1.3 Scott Login (on behalf of ordinary users)

Default normal user is locked not allowed to login

Can be unlocked with the administrator, the administrator can reset the password

1.3.1 Administrator Login

Sqlplus Sys/oracle as Sysdba

1.3.2 Unlock Reset Password

Alter user Scott account unlock; Unlock

650) this.width=650; "title=" clip_image007 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image007 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305179kqe4.png "" 244 "height="/>

Alter user Scott identified by Oracle; Set Scott's password to be Oracle

650) this.width=650; "title=" clip_image008 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image008 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305179c1z8.png "244" height= "Max"/>

1.3.3 Scott Login

Sqlpous scott/oracle

650) this.width=650; "title=" clip_image009 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image009 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305179o7oz.png "" 207 "height="/>

Question 1: Is it possible that a server has multiple databases

Sqlplus Sys/oracle as SYSDBA which one is logged in??????

When you log in, you will see the environment variable ORACLE_SID indicates which instance you are logged into

How to view a value with an environment variable that is currently in effect

[Email protected] ~]$ env |grep ORACLE_SID

650) this.width=650; "title=" clip_image010 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image010 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305179dpct.png "" 244 "height="/>

If you do not specify a default login should be an ORCL instance

Summary: Later locally logged-on templates

First define the instance you want to log into---> Export ORACLE_SID=ORCL

Then sign in---> Sqlplus sys/oracle as Sysdba

650) this.width=650; "title=" clip_image012 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image012 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305180pews.jpg "" 244 "height="/>

Question 2: When logged in is not entered the password, who verifies the password

SYS is operating system validation and database is not related

The database can be logged on either starting or not starting the administrator

What is operating system validation? Log in to the operating system first and then as long as you have permission to login, not verify password

Oracle Login---Oracle belongs to DBA Group----SYSDBA Permissions

Sqlplus/as sysdba; no password Authentication required

Sql> Show user;

USER is "SYS"

Data dictionary validation is used by ordinary users

A data dictionary is a table in a database that stores account and password information

If the database does not boot to the open state, the database is not allowed to read tables in the database

If a normal user is logged in, you must first ensure that the database is open

How do I check the status of a database?

1 Admin Login

Export ORACLE_SID=ORCL

Sqlplus/as SYSDBA

Sql> select status from V$instance;

650) this.width=650; "title=" clip_image013 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image013 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305180onzn.png "" 244 "height="/>

2 Regular users can log in

Export ORACLE_SID=ORCL

Sqlplus scott/oracle

Sql> Show user;

USER is "SCOTT"

Close the database

Export ORACLE_SID=ORCL

Sqlplus/as SYSDBA

Shutdown abort;

650) this.width=650; "title=" clip_image014 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image014 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305180ab1p.png "" 244 "height="/>

Administrator can log in

[Email protected] ~]$ Sqlplus/as SYSDBA

Sql*plus:release 11.2.0.1.0 Production on Sun Mar 16 04:18:01 2014

Copyright (c) 1982, Oracle. All rights reserved.

Connected to an idle instance.

650) this.width=650; "title=" clip_image015 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image015 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 14063051803fjc.png "" Height= "/>"

As long as you can log in to the database server, the administrator must log in

Normal user can not log in

Export ORACLE_SID=ORCL

[Email protected] ~]$ Sqlplus scott/oracle

650) this.width=650; "title=" clip_image016 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image016 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 14063051811uyp.png "" 244 "height=" 107 "/>

Administrator starts the database

Export ORACLE_SID=ORCL

Sqlplus/as SYSDBA

Startup

650) this.width=650; "title=" clip_image017 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image017 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305181w3sx.png "" 244 "height="/>

Sql> select status from V$instance;

650) this.width=650; "title=" clip_image018 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image018 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305181yd3u.png "" 244 "height="/>

Ordinary users must be able to log in

Sqlplus scott/oracle

650) this.width=650; "title=" clip_image019 "style=" border-top:0px; border-right:0px; border-bottom:0px; border-left:0px; Display:inline "border=" 0 "alt=" clip_image019 "src=" http://img1.51cto.com/attachment/201407/25/9202005_ 1406305181wbfq.png "" 198 "height="/>