Oracle 菜鸟学习 – 安装数据库之后解锁scott用户

Oracle 学习 – 安装数据库之后解锁scott用户

为什么要解锁scott用户呢？因为scott用户默认存在几个不同数据表，这些数据方便我们对Oracle数据库进行查询、修改等操作。

登陆数据库

安装了数据库之后，需要登陆数据库，我这里的环境是Redhat 6.5(Linux)，所以我用的是sqlplus进行链接

首先我们要查看Oracle数据库的监听是否已经开启

• 切换到oracle用户

[root@localhost ~]# su - oracle

• 查看oracle数据库监听器的状态

  [oracle@localhost ~]$ lsnrctl status
  LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-SEP-2018 17:38:01
  Copyright (c) 1991, 2009, Oracle.  All rights reserved.
  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
  TNS-12541: TNS:no listener
   TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
     Linux Error: 111: Connection refused
  [oracle@localhost ~]$ 

• 可以看出监听器并没有正常
• 启动监听器

  [oracle@localhost ~]$ lsnrctl start
  LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-SEP-2018 17:39:52
  Copyright (c) 1991, 2009, Oracle.  All rights reserved.
  Starting /u01/app/oracle/product/11.2.4/db_1/bin/tnslsnr: please wait...
  TNSLSNR for Linux: Version 11.2.0.1.0 - Production
  Log messages written to /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
  STATUS of the LISTENER
  ————————————————
  Alias                     LISTENER
  Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
  Start Date                29-SEP-2018 17:39:52
  Uptime                    0 days 0 hr. 0 min. 0 sec
  Trace Level               off
  Security                  ON: Local OS Authentication
  SNMP                      OFF
  Listener Log File         /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
  Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
  The listener supports no services
  The command completed successfully

登陆数据库，查询用户列表

• 使用sysdba用户登陆数据库，sysdba相当于windows的administrator

  [oracle@localhost ~]$ sqlplus / as sysdba
  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 17:35:30 2018
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  Connected to an idle instance.
  SQL> 

• 使用sql语句的时候可能会出现报错，

 SQL> select * from all_users;
  select * from all_users
  *
  ERROR at line 1:
  ORA-01034: ORACLE not available
  Process ID: 0
  Session ID: 0 Serial number: 0

• 使用startup启动数据库即可解决上面报错

  SQL> startup;
  ORACLE instance started.
  Total System Global Area 1653518336 bytes
  Fixed Size                  2213896 bytes
  Variable Size             989857784 bytes
  Database Buffers          654311424 bytes
  Redo Buffers                7135232 bytes
  Database mounted.
  Database opened.

• 查看用户列表，有没有存在scott用户

  SQL> select username from all_users;
  USERNAME
  ————————————————————
  SCOTT
  OWBSYS_AUDIT
  OWBSYS
  APEX_030200
  APEX_PUBLIC_USER
  FLOWS_FILES
  MGMT_VIEW
  SYSMAN
  SPATIAL_CSW_ADMIN_USR
  SPATIAL_WFS_ADMIN_USR
  MDDATA

可以看到scott用户是存在的
解锁用户

• 使用scott用户登陆
  scott用户的默认密码为tiger

  SQL> conn scott/tiger
  ERROR:
  ORA-28000: the account is locked
  SQL> 

• 提示被锁定
  再次执行sql语句是可能会提示以下错误，因为登陆失败，所以显示没有连接，这时候需要用其他用户登陆即可

  SQL> select username from all_users;
  SP2-0640: Not connected
  SQL> conn / as sysdba
  Connected.

• 查看用户状态

SQL> select username,account_status from dba_users;
  USERNAME                                                                                   ACCOUNT_STATUS
  —————————————————————————————————————
  SYS                                                                                        OPEN
  SYSTEM                                                                                     OPEN
  SCOTT                                                                                      LOCKED
  OUTLN                                                                                      EXPIRED & LOCKED

可以看到scott用户的状态是locked，即锁定的

解锁并使用scott用户连接数据库

• 解锁scott用户

  SQL> alter user scott account unlock;
  User altered.
  SQL>

• 连接scott用户，在此过程当中可能会提示修改密码，修改即可。

  SQL> conn scott/tiger
  Connected.
  SQL> 

• 或者在oracle用户下直接使用scott用户连接数据库

  SQL> conn scott/tiger
  Connected.
  SQL> exit
  Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  [oracle@localhost ~]$ sqlplus scott/weide123
  SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 17:59:43 2018
  Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  Connected to:
  Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
  With the Partitioning, OLAP, Data Mining and Real Application Testing options
  SQL> 

• 查看当前用户

  SQL> select USERNAME  from user_users;
  USERNAME
  ————————————————————————————————
  SCOTT
  1 row selected.
  SQL> conn / as sysdba
  Connected.
  SQL> select USERNAME  from user_users;
  USERNAME
  ————————————————————————————————
  SYS
  1 row selected.
  SQL>

• 使用scott用户查看当前数据表
  有以下表名即可

  SQL> select TABLE_NAME from user_tables;
  TABLE_NAME
  ————————————————————————————————
  DEPT
  EMP
  BONUS
  SALGRADE
  4 rows selected.
  SQL>