ORACLE 知识汇总

oracle知识汇总

一、数据库三大范式：

第一范式：每个列都不可再拆分；

第二范式：在第一范式的基础上，非主键字段完全依赖于主键字段，而不是依赖部分主键字段；

第三范式：在第二范式的基础上，非主键字段只依赖于主键字段，不依赖其他非主键字段。

二、数据库索引

1、索引：建立在表一列或多列的辅助对象，目的是加快访问表的数据。

2、索引的优点：

(1)、创建唯一性索引，可以确保数据的唯一性；

(2)、大大加快数据检索速度；

(3)、加速表与表之间的连接；

(4)、在查询过程中，使用优化隐藏器，提高系统性能。

3、索引的缺点：

(1)、创建和维护索引需要耗费时间，随数据量增加而增加；

(2)、索引占用物理空间；

(3)、对表的数据进行增删改时，索引需要动态维护，降低了数据的维护速度。

4、常用索引sql语句

建立索引:create index index_name on table_name（[column1 [asc|desc]） 重命名索引:alter index index_name rename to index_new; 合并索引:alter index index_name coalesce; 重建索引:alter index index_name rebuild; 删除索引:drop index index_name； 查看索引:select * from all_indexes where table_name='表名';

5、应该建立索引的列

(1)、经常搜索的列，可加快搜索的速度；

(2)、主键列，确保数据唯一；

(3)、经常用于连接的列，加快连接速度；

(4)、经常排序的列，索引已经排序，可节省排序时间；

(5)、经常使用在where子句的列，加速判断时间；

6、不应该建立索引的列

(1)、查询中很少使用的列；

(2)、单一值的列；

(3)、blob数据类型的列；

7、索引限制

(1)、索引列使用不等于操作符<>、！=；

(2)、使用 is null 或is not null；

(3)、where子句中使用函数；

(4)、比较不匹配的数据类型(不匹配的数据类型之间比较会让Oracle自动限制索引的使用)；

8、索引分类

B-树索引:默认索引类型，可以是单列索引，也可以是多列索引，索引列的值都储存在索引中，

特点： 

适合与大量的增、删、改（OLTP）

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；  

位图索引 ：适合于决策支持系统(Decision Support System，DSS)和数据仓库

特点：

适合与决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引；  

HASH索引：必须使用HASH集群,HASH索引可能是访问数据库中数据的最快方法

特点：HASH对于一些包含有序值的静态数据非常有效。

索引编排表 

反转键索引 

基于函数的索引

分区索引：本地分区索引和全局分区索引：

本地分区索引通常使用的索引

全局分区索引在一个索引分区中包含来自多个表分区的键

三、存储过程

oracle存储过程procedure：是一个为了完成特定功能的sql语句块，经编译后存放在数据库中，第一次编译后不需要再进行编译，用户可以通过指定名称和传递参数来执行。

优点：

1.高效，编译一次后保存到数据库，以后调用不需要编译，直接执行，减少编译时间；

2.降低网络流量，存放于数据库中，远程调用不会传输大量字符串的sql语句；

3.复用性高，存储过程是针对某一特定功能创建的，当再需要完成特定功能时调用该存储即可；

4.可维护性高，功能发生细微变化时，只需修改小部分代码即可；

5.安全性高，完成某一特定功能的存储过程是特定用户创建的，具有身份限制。

结构：过程声明，执行过程部分，存储异常

分类：1、无参数存储过程：

create or replace procedure 存过名 as/is 变量2 number； begin --处理的业务功能块 exception --异常 end

2、有参数存储过程(声明参数与student表id字段类型一致)

create or replace procedure 存过名 (pal student.id%type) as/is name student.name%type --声明变量与student表的name字段类型一致 age number：=20 --声明变量age为number类型，默认值为20 begin --处理的业务功能板块 end

3.有输入参数和输出参数的存储过程

CREATE OR REPLACE PROCEDURE 存储过程名称(        s_no in varchar,        s_name out varchar,        s_age number) AS total NUMBER := 0; BEGIN   SELECT COUNT(1) INTO total FROM student s WHERE s.age=s_age;   dbms_output.put_line('符合该年龄的学生有'||total||'人');   EXCEPTION     WHEN too_many_rows THEN      DBMS_OUTPUT.PUT_LINE('返回值多于1行');  END

in 代表输入参数，out代表返回值参数，只能在过程内部赋值，表示可以将参数传递给调用过程，

调用存储过程：

call 存储过程名 （参数）；

/

运算符：

:= 代表赋值，

.. 范围，

|| 拼接字符

select into 将多个字段值赋予一个或多个变量

嵌套语句：

1.选择语句

1、if ..end if

IF s_sex=1 THEN   dbms_output.put_line('这个学生是男生'); END IF

2、if ..else end if

IF s_sex=1 THEN   dbms_output.put_line('这个学生是男生'); ELSE   dbms_output.put_line('这个学生是女生'); END IF

2.循环语句

1、loop循环

LOOP   IF 表达式 THEN     EXIT;   END IF END LOOP;

2、while循环

WHILE 表达式 LOOP   dbms_output.put_line('haha'); END LOOP;

3、for循环

FOR a in 10 .. 20 LOOP   dbms_output.put_line('value of a: ' || a); END LOOP;

四、游标

用来存储多条查询语句的结果集

优点：加快对数据的操作；

缺点：占用内存；

分类：

1、静态游标：显式游标和隐式游标

隐式游标：在 PL/SQL 中使用 DML(insert、delete、update) 和 select into 时，会 '自动创建隐式游标'，隐式游标自动声明、打开和关闭

显式游标：由cursor声明，可带参数，也可不带参数

2、动态游标

语法

DECLARE v_sql_select VARCHAR(200); v_stu_info_rows scott.stu_info%ROWTYPE; TYPE cur_stu_type IS REF CURSOR RETURN scott.stu_info%ROWTYPE; -- 有 return cur_stu_info cur_stu_type; BEGIN -- 测试 /* v_id := 1; v_sql_select := 'SELECT t.id, t.name FROM stu_info t WHERE t.id <= :b1';*/ OPEN cur_stu_info FOR SELECT t.id, t.name, t.sex FROM stu_info t; FETCH cur_stu_info INTO v_stu_info_rows; dbms_output.put_line(v_stu_info_rows.id || ' ：' || v_stu_info_rows.name); CLOSE cur_stu_info; END;

游标的属性：

属性 返回值类型 作用 sql%isopen 布尔型 判断游标是否 '开启' sql%found 布尔型 判断游标是否 '获取' 到值 sql%notfound 布尔型 判断游标是否 '没有获取' 到值（常用于 "退出循环"） sql%rowcount 整型 '当前' 成功执行的数据行数（非 "总记录数")

存储过程和函数区别：

存储过程针对于特定功能，函数针对数据；

存储过程可以单独调用执行，函数只能作为表达式一部分使用；

存储过程声明不需要描述返回类型。函数要描述返回类型，至少包括一个有效的return语句

存储过程可以返回0或者多个值，函数返回一个值；

五、触发器与视图

触发器是一种特殊的存储过程，触发器一般由事件触发并且不能接受参数，由语句块去调用

分类：

1.DML(INSERT, UPDATE, DELETE)触发器： 创建在表上，由DML事件引发

2.instead of触发器： 创建在视图上并且只能在行级上触发，用于替代insert，delete等操作（由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法）

3.DDL(CREATE、ALTER、DROP)触发器： 触发事件时数据库对象的创建和修改

4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发

　　CREATE [OR REPLACE] TIGGER触发器名 触发时间 触发事件 　　ON表名/视图名 　　[FOR EACH ROW] //加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器 　　BEGIN 　　pl/sql语句 　　END

视图

一个虚拟表，

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name AS SELECT查询 [WITH READ ONLY CONSTRAINT]

优点：当表结构过于复杂时，请使用视图。

视图可以简化操作(可以将多表中的字段合并在一张视图中)

六、事务和锁

事务：是用户定义的一个操作序列

特点：

1.原子性，要么不发生，要么全发生；

2.一致性，数据库是从一个一致性状态变成另一个一致性状态；

3.隔离性，同时发生的多个事务互不干扰

4.持久性，事务一旦提交，对数据库造成的影响是永久的；

  savepoint aa;    delete from emp where empid = 9999;  //正常操作    savepoint bb;    delete from emp;  //异常操作    rollback to bb;  //回滚到异常操作之前

结论：事务支持多个保存点，可以在返回最近的保存点后另外再返回其他的保存点，但一旦

     保存点返回，保存立即回收。事务提交之后，保存点失效。

隔离级别：

各个事务的隔离程度。

隔离级别	脏读	不可重复读	幻读
读未提交(Read uncommited)	√	√	√
读已提交(Read commited)	×	√	√
可重复读(Repeatable read)	×	×	√
可串行化(Serializable)	×	×	×

oracle的事务隔离级别是：读已提交

mysql的事务隔离级别是：可重复读

脏读：一个事务读取到另外一个事务未提交的动作

不可重复读：在同一事务中多次查询，由于其他事务所做的修改和删除，导致每次

             返回不同的结果集，此时发生非重复读

幻读：在同一事务中多次查询，由于其他事务所做的插入，导致每次返回不同的结果集，

此时发生幻读

如何在控制台设置事务的隔离级别：

set transaction isolation level committed;(默认) set transaction isolation level serializable; set transaction read only;

锁：

oracle数据库的一种轻量性锁定机制。

分类：排它锁(X锁)，共享锁(S锁)

排他锁也叫写锁.这种模式的锁防止资源的共享,用做数据的修改.

共享锁也可以叫读锁.该模式锁下的数据只能被读取,不能被修改.

按照作用对象又可分为DML锁,DDL锁和内部闩锁.

DML锁:该类型的锁被称为数据锁,用于保护数据.主要保证了并发访问时数据的完整性.再细分,又可分为行级锁(TX,也可叫事务锁)和表级锁(TM).

DDL锁:可以保护模式中的对象的结构.

内部闩锁:保护数据库的内部结构,完全自动调用.

1.TX:当修改表中的某行记录时,需要对将要修改的记录加行级锁,防止两个事务同时修改相同记录,事务结束,该锁也会释放,是粒度最细的锁.该锁只能属于排他锁(X锁).

2.TM:主要用用是防止在修改表的数据时,表结构发生变化.例如,会话S在修改表A的数据时它会得到表A的TM锁,而此时将不允许其他会话对该表进行变更或删除操作.表级锁包含如下几种模式:

RS(行级共享锁,ROW SHARE):该模式下不允许其他的并行会话对同一张表使用排它锁,但允许其利用DML语名或Lock命令锁定同一张表中的其他记录.select ...from for update语名就是给记录加上了RS锁.

RX(行级排他锁,ROW EXCLUSIVE):该模式下允许并行会话对同一张表的其他数据进行修改,但不允许并行会话对同一张表使用排他锁.

S(共享锁,SHARE):该模式下,不允许会话更新表,但允许对表添加RS锁.

SRX(共享行级排他锁,SHARE ROW EXCLUSIVE):该模式下,不能对同一张表进行DML操作,也不能添加S锁.

X(排他锁,EXCLUSIVE):该模式下,其他的并行会话不能对表DML和DDL操作,该表只能读.

七、数据库优化

见oracle优化

八、数据库备份分类

主要分为逻辑备份和物理备份；

物理备份-又分为俩种：冷备份和热备份

1、逻辑备份：

指利用exp命令进行备份;

优点：简单操作，不会影响正常的数据库操作，经常用于日常备份；

(1)exp命令详解

exp可以添加多个参数，以实现不同导出策略，可以通过exp-？查看，参数包括owner、tables、inctype

exp 用户名/密码 file=d:/exp.dmp

d:/exp.dmp指导出后文件位置，用户名/密码指登录数据库时的用户名和密码。

owner参数：可以指定某一用户，只导出该用户下所有的对象

exp 用户名/密码 owner=( dm_user) file=d:/exp.dmp

tables参数：指定某一张表

exp 用户名/密码 tables=( emp) file=d:/exp.dmp

inctype参数：只针对oracle9i之前的版本，现有版本已废除参数，用于指定增量导出，

(2)imp命令详解

imp命令用于导入数据文件以恢复数据库内容，

imp 用户名/密码 file=d:/exp.dmp

也可以指定参数owner参数、tables参数，用于恢复某一用户或者某一表的数据。

2、物理备份：指直接恢复数据库的文件

(1)热备份：数据库在归档模式下的进行的数据文件、控制文件、归档日志文件的备份；热备份数据库时，首先确保数据库开启归档模式，然后备份表空间的数据文件，最后备份控制文件。

1、查看当前数据库是否开启归档模式：

archive log list

2、开启归档模式的命令：

alter database archivelog

3、查看表空间和文件路径：

select tablespace_name,file_name from dba_data_files order by tablespace_name;

查询结果会有sysaux、system、undotbs1、users、以及其他表空间文件(.dbf)

4、对表空间备份，修改表空间为备份状态

alter tablespace user begin backup; 
alter tablespace sysaux begin backup;
 alter tablespace system begin backup; 
alter tablespace undotbs1 begin backup;
 alter tablespace 其他表空间 begin backup;

5、将表空间文件复制到磁盘文件：

host copy 源文件名称 目标路径名称

6、结束表空间的备份状态

alter tablespace user end backup; 
alter tablespace sysaux end backup; 
alter tablespace system end backup; 
alter tablespace undotbs1 end backup; 
alter tablespace 其他表空间 end backup;

7、备份控制文件

通常应该在数据库物理结构做出修改之后，如添加、删除或重命名数据文件，添加、删除或修改表空间，添加或删除重做日志文件和重做日志文件组等，都需要重新备份控制文件。

将控制文件转换为二进制文件

alter database backup controlfile to ‘目标路径.bkp’

将控制文件转换为文本文件

alter database backup controlfile to trace;

8、备份其他物理文件

归档当前联机重做日志文件

alter system archive log current;

--切换日志

alter system switch logfile;

9、备份归档重做日志文件，将所有的归档重做日志文件复制到备份磁盘中。

10、备份初始化参数文件，将初始化参数文件复制到备份磁盘中。

(2)冷备份：数据库在关闭状态下的备份，备份所有的数据库文件。包括：所有数据文件、所有控制文件、所有联机redo_log文件和Init.ora文件(可选择)

ex：进入服务器，以管理员身份连接数据库：sqlplus/ as sysdba;

关闭数据库：shutdown normal；

冷备份数据库文件：host copy 源文件名称 目标路径名称

备份完成后重启数据库：startup

优点：

• 简单、快捷，只需拷贝文件。
• 只需将文件再拷贝回去，就可以恢复到某一时间点上。
• 与数据库归档的模式相结合可以使数据库很好地恢复。
• 维护量较少，但安全性确相对较高。

数据库面试题

1、新建用户后需要赋予什么权限才能连接数据库？

connect权限

2、oracle_base和oracle_home的区别

oracle_base是oracle的根目录，oracle_home是oracle产品的目录

3、数据库正常启动的几种状态

startup nomount-数据库实例启动

startup mount-数据库装载

startup open-数据库打开

4、数据库归档模式和非归档模式优缺点

归档模式可以执行热备，同时支持增量备份，可以执行时间点恢复；归档日志文件需要占用额外的磁盘空间

非归档模式：

非归档模式只能进行冷备，只能还原到备份点，之后的数据无法恢复；一般在开发或者测试环境使用；