博客

一、sqlplus小知识

1、显示当前用户

show user;

2、修改oracle密码

passw 或者 passw + 用户名

（可以带用户名，也可以不带用户名，但是给别人带用户名，必须带用户名）

3、切换用户

conn + 用户名

4、查询所有的用户名

select username from dba_users;

select * from dba_users;

二、用户管理

1、创建用户

create user 用户名 identified by 密码

创建用户必须要创建用户的权限

刚刚创建的用户不能登录，因为没有登录的权限：

2、给新用户赋予登录权限

给用户赋值登录的权限：

grant create session to 用户名

3、系统管理员给用户修改密码

如果忘记了密码，需要找系统管理员重置秘密：

4、综合案例（创建用户和权限赋值）

①创建用户  gg

②给gg赋予登录的权限

③gg创建一张表

发现没有权限，所以需要分配角色

④分配角色

grant resource to gg;

⑤再次创建表

connect resource 介绍：

⑥回收权限

revoke 权限名 from 用户名

⑦删除用户

drop user gg;

在删除用户的时候，如果该用户已经创建过数据对象，需要加上一个cascade,表示和数据对象一并删除。

drop user gg cascade;

5、使用profile文件对口令进行管理

举例，如果一个用户连续输入密码错误3次，则锁定该用户3天，三天之后才可以登录？

–创建profile文件

create profile lock_account limit failed_login_attempts 3 password_lock_time 3；

–使用profile文件

alter user 用户名 profile lock_account；

当用户输入错误3次之后，再一次登录，不管密码是不是正确，都会显示：

ORA-28000: the account is locked

② 可以使用有权限的管理员给用户解锁

alter user 用户 account unlock；

alert user lwb account unlock;

③ 终止口令

为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作.

例子：给前面创建的用户lwb创建一个profile文件,要求该用户每隔10天要修改自家的登陆密码,宽限期为2天。

create profile myprofile limit password_life_time 10 password_grace_time 2

alter user tea profile myprofile

④ 口令历史

概述:如果希望用户在修改密码时,不能使用以前使用过的密码,可使用口令历史,这样oracle就会将口令修改的信息存放到数据字典中,这样当用户修改密码时,oracle就会对新旧密码进行比较,当发现新旧密码一样时,就提示用户重新输入密码

create profile password_history limit password_life_time 10 password_grace_time 2

password_reuse_time 10

password_reuse_time //指定口令可重用时间即10天后就需要修改

alter user tea profile myprofile

⑤ 删除profile

概述:当不需要某个profile文件时，可以删除该文件

三、关于用户的方案问题

如果有两个用户，如果 一个用户创建了一个表，但是另一个用户却不能访问这个表，这是为什么呢？

没错，oracle为每个用户创建了各自的方案，所以一个用户不能看见别的用户的表（数据对象）

那么在上图中，怎么让新建的lwb用户可以查询scott的emp表呢？ 操作如下：

① scott登录

conn scott

② 赋予一个对象权限给lwb

grant select或者update或者delete或者insert或者all on emp to lwb;

例如：grant select on emp to lwb;

登录到lwb查询的时候：select * from scott.emp; 这样就可以查询到数据了。

四、数据库的启动流程

① oracle启动流程-windows下

1、lsnrctl start (启动监听)

2、oradim –startup –sid  数据库实例名

② oracle启动流程-linux下

1、lsnrctl start (启动监听)

2、sqlplus  sys/change_on_install   as sysdba (以sysdba 身份登录,在oracle10g后可以这样写)

sqlplus  /nolog

conn  sys/change_on_install as sysdba

3、startup

五、oracle的登录认证

在我的windows系统中，安装了一个oracle数据库，当我用 as sysdba的方式登录的时候，发现如下一个问题：

即不论使用什么样的用户名和密码，只要是加上 as sysdba 那么都可以登录到sys用户，如上图。

这是什么原因呢？原来，当我们在安装oracle的时候，会在我们的系统上创建一个用户组，如下图：

该用户组是安装oracle的时候，自动创建的用户组，点开这个用户组可以发现：

在该用户组中，只要是包含的用户，那么在使用as sysdba登录的时候，默认不会去校验用户名和密码。而我的windows系统当前登录的用户正好是Administrator用户，所以在用as sysdba模式登录的时候，不会校验用户名和密码。当然，这是oracle的默认方式，该方式是可以修改的。

总结如下：

① 对于普通用户,oracle默认使用数据库验证。

② 对于特权用户(比如sys用户),oracle默认使用操作系统认证,

③如果验证不通过,再到数据库验证(密码文件验证)。通过配置sqlnet.ora文件，可以修改oracle登录认证方式

SQLNET.AUTHENTICATION_SERVICES= (NTS)是基于操作系统验证；

SQLNET.AUTHENTICATION_SERVICES= (NONE)是基于Oracle验证；

SQLNET.AUTHENTICATION_SERVICES= (NONE，NTS)是二者共存。

④ oracle登录认证方式-linux下

默认情况下linux下的oracle数据库sqlnet.ora文件没有SQLNET.AUTHENTICATION_SERVICES参数，

此时是基于操作系统认证和oracle密码验证共存的，加上SQLNET.AUTHENTICATION_SERVICES参数后，

不管SQLNET.AUTHENTICATION_SERVICES设置为NONE或者NTS，都是基于oracle密码验证的。

六、丢失管理员密码怎么

恢复办法：把原有密码文件删除,生成一个新的密码文件。

恢复步骤如下：

① 搜索名为 PWD数据库实例名.ora 文件 （比如我的：PWDorcl.ora）

② 删除该文件,为预防万一,建议大家备份

③ 生成新的密码文件,在dos控制台下输入命令:

orapwd file=原来密码文件的全路径\密码文件名.ora password=新密码 entries=10;//entries:允许几个特权用户。密码文件名 一定要和原来的密码文件名一样。

七、oracle数据类型

① char(size)  定长字符串，最多可以放2000个字符。

name char(32)   如果插入的数据少于32，那么就用空格来填充，如果多余32个，就报错。（一个中文字，占用两个字符空间），一个字符空间是使用一个字节来设计的。

② varchar2(size) 变长，最多可以存放4000个字符。

name(32) 如果插入数据少于32个，那么实际是多长就占用多长，剩余的空间就被回收了。如果超过32个字符，那么会自动增加存储空间。

如果以后要存储的数据是定长的，比如商品编号，身份证号，那么就使用char，不但约束了存储的长度，并且char的存储速度比varchar2快，如果存储的字符是变长的，那么就使用varchar来存储。

③ nchar(size) 定长，最长可以存储2000个字符

nchar采用的是unicode的编码方式。(一个中文字，占一个字符空间)，因为一个字符空间是使用两个字节来设计的。

④ nvarchar2(size) 变长，最长可以存储4000个字符

采用unicode编码方式。

⑤ clob 变长，字符型大对象，最多可存储8TB

⑥ blob 变长，二进制数据，可以存放图片和视屏等

⑦ number(p,s) ，变长

可以存放整数和小数。

p：代表有效位 1 <= p <= 38

s：代表小数位  -84 <= s <= 127

保存数据范围：-1.0e-130  <=  number  <  1.0e+126

⑧ date 日期类型

按照日-月-年的方式存储的

insert into users vaues(‘12-11月-2017’)；

八、基础sql

以下的表为演示sql语句的基础表：

小知识：

单引号作用： 表示字符串类型的数据

双引号作用:  主要用在别名、关键字作为名字

oracle中的不等于： <>

如果输出的文本中有单引号则使用两个单引号：select ‘he”s is a boy’ from dual;

①字符串的连接

1.使用concat运算符号

select concat(e.last_name,e.first_name) from employees e;

2.使用||运算符

select last_name||first_name from employees;

②like运算符查询特殊字符

首先在employees中插入两条数据：

Insert into departments values(300,’cd_lwb’,205,1700);

Insert into departments values(310,’cd%lwb’,205,1700);

由于在oracle中_代表一个字符，%代表任意字符，所以查询上面这样的特殊数据的时候

需要用到转移符关键字

escape

，如下：

select * from departments d where d.department_name like ‘cd\_lwb’ escape ‘\’;

select * from departments d where d.department_name like ‘cd\%lwb’ escape ‘\’;