oracle各种用户登录的方式
Oracle有3种用户: system、sys、scott
1、system和sys的差别在与是否能创建数据库
2、sys用户登录创建数据库，
3、scott是给刚開始学习的人学习的用户。学习者能够用Scott登录系统，注意scott用户登录后，就能够使用Oracle提供的数据库和数据表，这些都是oracle提供的。
sqlplus
//这种方式，直接提示输入用户名和密码
[oracle@shdb02 ~]$ sqlplus SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 20:28:02 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name:
sqlplus /nolog
//这种方式，不会提示用户名和密码，但是并没有登录任何oracle [oracle@shdb02 ~]$ echo $ORACLE_SID htstandby2 [oracle@shdb02 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 20:30:39 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. //这个时候，你输入sql，提示未连接 SQL> select value from v$parameter where name='instance_name'; SP2-0640: Not connected //要进行连接，登录 SQL> conn /as sysdba Connected. SQL> select value from v$parameter where name='instance_name'; VALUE -------------------------------------------------------------------------------- htstandby2
一、sqlplus /nolog登录方式
//为防止看到密码，直接用这种方式 [oracle@shdb02 expdir]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:01:17 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL>
演示1：sqlplus /nolog (conn ./as sysdba)
oracle@prd:/home/oracle$sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 20:39:42 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn ./as sysdba
Enter password: Connected. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options //我们history一下 1041 conn ./as sysdba 1042 sqlplus 1043 sqlplus /nolog 1044 sqlplus 1045 history //并没有看到密码
演示2：sqlplus /nolog (conn /as sysdba)
//conn /as sysdba操作系统认证登录
oracle@prd:/home/oracle$sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 20:39:42 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn /as sysdba Connected.
演示3: sqlplus /nolog(conn username/passwd)
//一般是启动sqlplus但不进行连接操作,nolog 选项会启动sqlplus, 但不连接数据库,但是也可以连接 [oracle@shdb02 expdir]$ echo $ORACLE_SID fpmai2 [oracle@shfpdb02 expdir]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:07:29 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. //进入sqlplus 输入用户名和密码 SQL> conn sh/Shdb12 Connected. SQL> select value from v$parameter where name='instance_name'; VALUE -------------------------------------------------------------------------------- fp2 [oracle@shdb02 ~]$ sqlplus / as sysdba
二、sqlplus登录方式
//直接启动sqlplus，进入sqlplus命令行,提示进入哪个用户 [oracle@shdb02 expdir]$ sqlplus SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:02:39 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: shfp Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL>
三、sqlplus / as sysdba 登录方式
//这种方式是直接登录了实例，当然也可以连接其他用户
演示1
[oracle@shdb02 ~]$ echo $ORACLE_SID fpmai2 [oracle@shfpdb02 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:17:46 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options //查看当前的实例 SQL> select value from v$parameter where name='instance_name'; VALUE -------------------------------------------------------------------------------- fp2 //查询当前的用户 SQL> select user from dual; USER -------------------------------------------------------------------------------- SYS //如果你想连其他用户,设置错误 SQL> conn shp/sr ERROR: ORA-00942: table or view does not exist Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM //下面是具体的操作过程，出现错误----- 1、实例和用户不匹配的 [oracle@shdb02 ~]$ echo $ORACLE_SID ht [oracle@shdb02 ~]$ sqlplus /nolog //(进入sqlplus环境，不登录) SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 21:24:54 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn shp/Shdbrrrr //shp用户为fp实例的用户，并不是ht的，所以出现错误 ERROR: ORA-01017: invalid username/password; logon denied SQL> exit
//进入fp实例 [oracle@shdb02 ~]$ export ORACLE_SID=fp [oracle@shdb02 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 21:25:23 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn shp/sr Connected.
//查看当前登录用户
SQL> select user from dual;
USER
------------------------------
SH
//查看该用户所属的表空间
SQL> select * from dba_tablespaces;
演示2：
//如果你想查表空间，需要登录某个用户,而不是SYS用户 [oracle@shdb02 ~]$ sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 21:33:18 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> select * from dba_tablespaces; SP2-0640: Not connected
手工建库之后登录
oracle@prd:/home/oracle$export ORACLE_SID=fp oracle@prd:/home/oracle$echo $ORACLE_SID fp oracle@prd:/home/oracle$sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 21:04:35 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> select value from v$parameter where name='instance_name'; SP2-0640: Not connected SQL> conn sh/sh12 ERROR: ORA-00942: table or view does not exist Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM Connected.
//出现上面的提示，这种提示会导致很多其他操作也会失败.下面有解决方式
//解决办法
需要使用system用户登陆并执行 pupbld.sql脚本,每个安装不同，所以这个脚本位置不同
oracle@prd:/home/oracle$locate pupbld.sql
/u01/oracle/product/11.2.0/sqlplus/admin/pupbld.sql
oracle@prd:/home/oracle$sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 21:17:12 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn /as sysdba //超级管理员身份登录
Connected.
//修改system的密码，这个当中,system密码已找不到,需要重新修改密码
SQL> alter user system identified by abci12;
User altered.
SQL> conn system/abc12
Connected.
//执行pupbld.sql
SQL> @/u01/oracle/product/11.2.0/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
DATE_VALUE FROM PRODUCT_USER_PROFILE
*
ERROR at line 3:
ORA-00980: synonym translation is no longer valid
DROP TABLE PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist
Table created.
DROP TABLE PRODUCT_PROFILE
*
ERROR at line 1:
ORA-00942: table or view does not exist
DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist
View created.
Grant succeeded.
Synonym dropped.
Synonym created.
DROP SYNONYM PRODUCT_USER_PROFILE
*
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist
Synonym created.
Synonym dropped.
Synonym created.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@prod:/home/oracle$sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 21:25:15 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
//连接成功
SQL> conn sh/sh12
Connected.
###################登录系统账户############################################################################################################
as sysdba 就是以sysdba登录，oracle登录身份有三种：
1、normal 普通身份； 2、sysdba 系统管理员身份； 启动和关闭操作 更改数据库状态为打开/装载/备份，更改字符集 创建数据库 创建服务器参数文件spfile 日志归档和恢复 包含了“会话权限”权限 3、sysoper 系统操作员身份。 启动和关闭操作 更改数据库状态为打开/装载/备份 创建服务器参数文件SPFILE 日志归档和恢复 包含了“会话权限”权限 数据库用sysdba登录的验证有两种方式， 一种是通过OS认证， 一种是通过密码文件验证；登录方式有两种， 一种是在数据库主机直接登录（用os认证的方式）。
演示 sys 登录方式
[oracle@shdb02 ~]$ sqlplus SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 19 21:53:42 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter user-name: sys as sysdba Enter password:********** /**** 还可以这样 sqlplus sys as sysdba Enter user-name：sys Enter password：password as sysdba --以sys用户登陆的话 必须要加上 as sysdba 子句 **/
演示 sqlplus + sys as sysdba 方式登录,直接输入密码
[oracle@shdb02 ~]$ sqlplus sys as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:43:30 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: ****** Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> SQL> select user from dual; //查看当前用户 USER ------------------------------ SYS
进入51.10的sys 用户下
演示1：
//当前实例fp oracle@prd:/home/oracle$echo $ORACLE_SID fpmai oracle@prd:/home/oracle$sqlplus sys as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:12:16 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select user from dual; USER -------------------------------------------------------------------------------- SYS SQL>set pagesize 0 SQL>set line 8888 SQL>select * from dba_tablespaces;
演示2：
//我们在换一个实例prd看下
oracle@prd:/home/oracle$sqlplus sys as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Mar 20 19:12:16 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select user from dual; USER -------------------------------------------------------------------------------- SYS //显示的是这个实例的内容 SQL> select * from dba_tablespaces; //查看当前用户 SQL> show user; USER is "SYS" //查看当前数据库实例 SQL> show parameter instance_name; instance_name string prd
四、演示默认学习用户scott,默认密码是tiger
oracle@prd:/home/oracle$sqlplus /nolog SQL> conn scott/tiger ERROR: ORA-28000: the account is locked SQL> exit oracle@prd:/home/oracle$sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 20:28:21 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn / as sysdba //用超级管理账户登录 Connected. SQL> alter user scott account unlock; //解锁 User altered. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options //重新登录sqlplus软件，/nolog表示没有任何用户登录 oracle@prd:/home/oracle$sqlplus /nolog SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 21 20:28:53 2022 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> conn scott/tiger //提示密码已过期，重新设置 ERROR: ORA-28001: the password has expired Changing password for scott New password: Retype new password: Password changed Connected.
SQL> show user;
USER is "SCOTT"
SQL>alter user scott identified by tiger;
User altered.
