关于Oracle及日常注意事项

岁月如梦 Lv4

2020-03-06 18:10:12 2024-11-14 13:04:17

技术

13.4k 字 49 分钟

MySQL数据库与Oracle 数据库有什么区别？

1、应用方面

MySQL 是中小型应用的数据库。一般用于个人和中小型企业。Oracle 属于大型数据库，一般用于具有相当规模的企业应用。

2、自动增长的数据类型方面

MySQL有自动增长的数据类型。Oracle 没有自动增长的数据类型。需要建立一个自增序列。

3、 group by 用法

MySQL 中group by 在SELECT 语句中可以随意使用，但在ORACLE 中如果查询语句中有组函数，那么其他列必须是组函数处理过的或者是group by子句中的列，否则会报错。

4、引导方面

MySQL中可以用单引号、双引号包起字符串，Oracle 中只可以用单引号包起字符串

什么是数据库？

本质上是使用数据结构存储数据的仓库。

什么是关系型数据库？

使用关系模型存储数据的数据库。
关系模型指表与表之间的关系，包含一对一、一对多、多读多。

什么是非关系型数据库？

使用非关系模型保存数据的数据库。例如列存储、键值对、文件形式。

说一说数据库三大范式？

第一范式，表中字段是原子的，不可再分；
第二范式，表中字段必须依赖主键的全部字段，不能只依赖一部分；
第三范式，表中字段必须直接依赖主键，不能存在传递依赖。

其实真正的数据库设计并没有遵循三大范式，特别是OLAP型数据库

事务的四大特性

原子性：事务要么全做，要么全不做
一致性：事务完成签后状态一致，不能产生错误结果
隔离性：各个事务见状态不可见，且互补影响
永久性：事务一旦完成，结果永远不会改变，即使机器故障，也能通过日志恢复

ORACLE数据库启动与关闭的步骤

启动：启动实例—-加载数据库数据—–打开数据库
关闭：关闭数据库—-卸载数据库数据—-关闭实例

怎样将一个旧数据库数据移到一个新的数据库

在旧数据库中，进入emap库查询默认表空间和临时表空间。
进入新库，创建表空间。
在新库创建用户和数据库
从旧库里备份数据
将备份的dump文件导入新库

用命令创建表空间、用户，并为用户授权、收回权限。

Create tablespace tabllespacename
Datafile ‘f:\orcl\user001.dbf size 20m
Default storage(
Initial 512k
Next 512k
minextents 2
pctincrease 50%
maxExitnts 2048)
minimum extent 512k
logging
online
permanent
extent management dictionary;

回收权限： revoke privilege from user；

create tablespace tablespace_name
datafile ‘’
default storage(
initial xxxkb
next yykb
minextents 2
pctincrease nnnn
maxextents mmm)
logging
online
extent management dictionary/local[autoallocate/uniform size xxxmb];

create user user_name
identified by passwore/
identified externally/
identified globally as ‘CN=user’
default tablespace tablespace_name
temporary tablespace tablespace_name
[account lock /unlock]

grant connect to user_name;
grant create table to suer_name;
grant update on table_name to user_name;
revoke create table from user_name;
revoke update on table_name from user_name;

表空间如何扩展?并用语句写出?

两种扩展方式：

增加数据文件

1	alter tablespace tablespace_name add datafile ‘’ xxMB

扩展数据文件大小

1	alter database datafile ‘’ resize newMB

用命令创建用户，并为用户授权。

Create user user_name
identified by password /
identified externally/
identified blobally as ‘CN=user’
default tablespace tablespace_name
temporary tablespace tablespace_name;

grant role/privilege to user_name;

谈谈你对角色的理解，常用的角色有哪些?

角色就是一组权限的数据库实体，它不属于任何模式或用户但是可以被授予任何用户。

常用的角色有：

CONNECT，
DBA，
RESOURCE，
SELECT_CATALOG_ROLE（查询所有表视图权），
DELETE_CATALOG_ROLE（删除权限）等。

角色的创建和授权：
和创建用户为用户授权差不多。

1 2	Create role role_name identified … grant 权限to role_name。

在Oracle中查看当前用户，通常有哪些方式?

1 2	show user; select username from user_users;

简述oracle中 dml、ddl、dcl的使用

DML包括：

SELECT：用于检索数据；
INSERT：用于增加数据到数据库；
UPDATE：用于从数据库中修改现存的数据
DELETE：用于从数据库中删除数据。

DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。

DDL包括：

CREATE 创建
ALTER 修改
DROP 删除

DDL（Data Definition Language，数据定义语言）：用于定义数据的结构，比如创建、修改或者删除数据库对象。

DCL包括：

GRANT 授权
REVOKE 回收权限
deny 拒绝授予主体权限。防止主体通过其组或角色成员身份继承权限

DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。

oracle创建表的几种方式；应该注意些什么？

建表方式：

完全新建一张表

1	create table emp(id integer)tablespace CICI;

在原有表的基础上新建一张表

1	CREATE TABLE EMP_TEMP TABLESPACE CICI AS( SELECT * FROM EMP) ;

注意事项：

表名和字段名的命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#
大小写不区分
不用SQL里的保留字, 一定要用时可用双引号把字符串括起来．
用和实体或属性相关的英文符号长度有一定的限制
建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
建表时如果有唯一关键字或者唯一的约束条件，建表时自动建了索引
一个表的最多字段个数也是有限制的，254个.

delete 与Truncate区别？

1 2	Truncate 是DDL 语句，DELETE 是DML语句。 Truncate 的速度远快于DELETE；

原因是：当执行DELETE操作时所有表数据先被COPY到回滚表空间，数据量不同花费时间长短不一。而TRUNCATE 是直接删除数据不进回滚表空间。

1、 delete 数据可以运行Rollback 进行数据回滚。而Truncate 则是永久删除不能回滚。
2、 Truncate 操作不会触发表上的delete触发器，而delete 会正常触发。
3、 Truncate 语句不能带where 条件意味着只能全部数据删除，而DELETE可带where 条件进行删除数据。
4、 Truncate 操作会重置表的高水位线（High Water Mark）,而delete 不会。

哪个后台进程刷新 materialized views?

1	The Job Queue Processes.

怎样创建一个触发器, 触发器的定义, 触发器的游标怎样定义

CREATE [OR REPLACE] TIGGER触发器名 触发时间 触发事件
　ON表名
　[FOR EACH ROW]
　BEGIN
　　pl/sql语句
CURSOR 游标名 is SELECT * FROM 表名 （定义游标）
　　END

其中：
　触发器名：触发器对象的名称。
　由于触发器是数据库自动执行的，因此该名称只是一个名称，没有实质的用途。
触发时间：指明触发器何时执行，该值可取：
before 表示在数据库动作之前触发器执行;
after 表示在数据库动作之后出发器执行。
触发事件：指明哪些数据库动作会触发此触发器：
　　 insert：数据库插入会触发此触发器;

oracle中存储过程，游标和函数的区别

1、游标类似指针，游标可以执行多个不相关的操作.如果希望当产生了结果集后,对结果集中的数据进行多种不相关的数据操作。
2、函数可以理解函数是存储过程的一种；函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值；两者都可以通过out参数返回值, 如果需要返回多个。
3、参数则建议使用存储过程；在sql数据操纵语句中只能调用函数而不能调用存储过程。

如何增加buffer cache的命中率？

在数据库较繁忙时

适用buffer cache advisory 工具，
查询v$db_cache_advice .

如果有必要更改，可以使用命令

1 2	alter system set db_cache_size

Oracle中function和procedure的区别？

1、可以理解函数是存储过程的一种
2、函数可以没有参数,但是一定需要一个返回值，存储过程可以没有参数,不需要返回值
3、函数return返回值没有返回参数模式，存储过程通过out参数返回值, 如果需要返回多个参数则建议使用存储过程
4、在sql数据操纵语句中只能调用函数而不能调用存储过程

怎样创建一个存储过程, 游标在存储过程怎么使用, 有什么好处?

创建存储过程

CREATE PROCEDURE user ()

BEGIN -- 定义变量

DECLARE i INT DEFAULT 100000;
DECLARE p_telphone VARCHAR(20);
DECLARE p_username VARCHAR(20);
DECLARE p_authcode VARCHAR(20);
DECLARE p_card VARCHAR(20);
DECLARE p_password VARCHAR(20);
WHILE i < 150000 DO

-- 对参数赋值
SET p_telphone = CONCAT('15410',i);
SET p_username = CONCAT('张',i);
SET p_authcode = CONCAT('',i);
SET p_card = CONCAT('SN000',i);
SET p_password = CONCAT('hnjb',i);

-- 创建数据
INSERT INTO user ( telphone, username, authcode, card, password ) VALUES ( p_telphone, p_username, p_authcode, p_card, p_password );
SET i=i+1;
END WHILE;
END

使用存储过程访问数据库比直接用SQL语句访问有何优点？

1、存储过程是预编译过的，执行时不须编译，执行速度更快。
2、存储过程封装了多条SQL，便于维护数据的完整性与一致性。
3、实现代码复用。

如何使用Oracle的游标？

1、 oracle中的游标分为显示游标和隐式游标

2、显示游标是用cursor…is命令定义的游标，它可以对查询语句(select)返回的多条记录进行处理；隐式游标是在执行插入 (insert)、删除(delete)、修改(update)和返回单条记录的查询(select)语句时由PL/SQL自动定义的。

3、显式游标的操作：打开游标、操作游标、关闭游标；PL/SQL隐式地打开SQL游标，并在它内部处理SQL语句，然后关闭它

使用游标好处

使用游标可以执行多个不相关的操作.如果希望当产生了结果集后，对结果集中的数据进行多种不相关的数据操作 。
使用游标可以提供脚本的可读性 。
使用游标可以建立命令字符串，使用游标可以传送表名，或者把变量传送到参数中，以便建立可以执行的命令字符串。
存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
可保证数据的安全性和完整性。
通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。
通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这种已经编译好的过程可极大地改善SQL语句的性能。 由于执行SQL语句的大部分工作已经完成，所以存储过程能以极快的速度执行。
可以降低网络的通信量, 不需要通过网络来传送很多sql语句到数据库服务器了 ，使体现企业规则的运算程序放入数据库服务器中，以便集中控制

如何判断游标已经到最后一行?

1	Cursor_name%notfound

集合操作符

1、 Union ：不包含重复值，默认按第一个查询的第一列升序排列。
2、 Union All : 完全并集包含重复值。不排序。
3、 Minus 不包含重复值，不排序。

怎样创建一个索引,索引使用的原则,有什么优点和缺点

创建索引的方法：

创建标准索引： 
CREATE  INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 
创建唯一索引: 
CREATE unique INDEX 索引名 ON 表名 (列名)  TABLESPACE 表空间名; 
创建组合索引: 
CREATE INDEX 索引名 ON 表名 (列名1,列名2)  TABLESPACE 表空间名; 
创建反向键索引: 
CREATE INDEX 索引名 ON 表名 (列名) reverse TABLESPACE 表空间名;

索引使用的原则：

1.搜索的索引列

要建索引的列不一定是所要结果的列，简单的说就是where条件中出现的列需要索引，或者链接子句用到的列需要索引，而只在select中出现的列不需要索引。

2.使用唯一索引

考虑某列中值的分布，索引列的基数越大越好，例如存放出生日期的列具有不同的值，很容易区分各行;而存放性别的列，只有两个值，所以对这种列加索引也没有什么价值，不管搜哪个值，都是大约一半的数据量。

3.使用短索引

如果对字符串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，有一个char（200）的列，如果前10～20个字符内，多数值是唯一的，那么就不要对整个列进行索引（时刻考虑对资源的占用问题）

4.利用最左前缀

在创建一个n列的索引时，实际是创建了mysql可利用的n个索引。多列索引可以起几个索引的作用，因为可以利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。

5.不要过度索引

什么列都建索引是错误的，比如从来都不查询的列，建上索引后一次也不用没什么价值，反而占用了额外的资源。

索引的优点：

通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

索引的缺点：

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

如何重构索引？

1	ALTER INDEX <index_name> REBUILD;

Oracle中经常使用到的函数

length 长度
lower 小写
upper 大写
to_date 转化日期
to_char 转化字符
to_number 转化数字
Ltrim 去左边空格
rtrim 去右边空格
substr 截取字符串
add_month 增加或减掉月份

1.数字函数

NVL( string1, replace_with) ：如果string1为NULL，则NVL函数返回replace_with的值，否则返回string1的值，如果两个参数都为NULL ，则返回NULL。
TRUNC(d,[fmt]) ：该函数用于截取日期时间数据.如果fmt指定年度，则结果为本年度的1月1日；如果fmt指定月，则结果为本月1日。
MOTNS_BETWEEN(d1,d2)：该函数用于返回d1和d2之间相差的月数.如果d1小于d2，则返回负数；如果d1和d2的天数相同，或都是月底，则返回整数；否则Oracle以每月31天为准来计算结果的小数部分。
NEXT_DAY(d,char)：该函数用于返回指定日期后的第一个工作日（由char指定）所对应的日期。

转换函数

TO_CHAR(data[,fmt[,nls_param]]) ：该函数用于将日期值转变为字符串，其中fmt用于指定日期格式，nls_param用于指定nls参数。
TO_CHAR(n[,fmt[,nls_param]])：用于将数字值转换为VARCHAR2数据类型。
TO_DATE(char[,fmt[,nls_param]])：该函数用于将符合指定日期的函数转变为DATE类型的数值

解码函数

DECODE函数为Oracle专属。

1	DECODE(value, if1, then1, if2,then2, if3,then3, . . . else )

数据库正常启动所经历的几种状态 ?

1
2
3

STARTUP NOMOUNT 数据库实例启动
STARTUP MOUNT 数据库装载
STARTUP OPEN 数据库打开

回滚段的作用是什么

事务回滚：当事务修改表中数据的时候，该数据修改前的值(即前影像)会存放在回滚段中，当用户回滚事务(ROLLBACK)时，ORACLE将会利用回滚段中的数据前影像来将修改的数据恢复到原来的值。

事务恢复：当事务正在处理的时候，例程失败，回滚段的信息保存在undo表空间中，ORACLE将在下次打开数据库时利用回滚来恢复未提交的数据。

1、读一致性：当一个会话正在修改数据时，其他的会话将看不到该会话未提交的修改。
2、当一个语句正在执行时，该语句将看不到从该语句开始执行后的未提交的修改(语句级读一致性)
3、当ORACLE执行Select语句时，ORACLE依照当前的系统改变号(SYSTEM CHANGE NUMBER-SCN)。
4、来保证任何前于当前SCN的未提交的改变不被该语句处理。可以想象：当一个长时间的查询正在执行时。
5、若其他会话改变了该查询要查询的某个数据块，ORACLE将利用回滚段的数据前影像来构造一个读一致性视图。

你必须利用备份恢复数据库，但是你没有控制文件，该如何解决问题呢？

重建控制文件，用带 backup control file 子句的recover 命令恢复数据库。

如何变动数据文件的大小？

1	ALTER DATABASE DATAFILE <datafile_name> RESIZE <new_size>;

怎样创建一个视图,视图的好处, 视图可以控制权限吗?

1
2
3

create view 视图名 as
select 列名 [别名]  …  from 表 
[unio [all] select … ] ]

好处：

视图可以简化用户的操作；
视图使用户能够多角度看待同一数据；
视图对重构数据库提供了一定程度的逻辑独立性；
视图能够对机密数据提供安全保护
适当利用视图可以更清晰地表达查询
可以简单的将视图理解为sql查询语句，视图最大的好处是不占系统空间
一些安全性很高的系统，不会公布系统的表结构，可能会使用视图将一些敏感信息过虑或者重命名后公布结构

视图可以控制权限吗：

可以控制权限的，在使用的时候需要将视图的使用权限grant给用户

哪个VIEW用来判断tablespace的剩余空间

1	DBA_FREE_SPACE

当用户进程出错，哪个后台进程负责清理它

PMON

ORA-01555的应对方法?

具体的出错信息是 snapshot too old within rollback seg , 通常可以通过增大 rollback seg来解决问题。当然也需要察看一下具体造成错误的SQL文本

data block , extent 和 segment的区别（这里建议用英文术语）

data block是数据库中最小的逻辑存储单元。当数据库的对象需要更多的物理存储空间时，连续的data block就组成了extent . 一个数据库对象拥有的所有extents被称为该对象的segment.

冷备份和热备份的不同点以及各自的优点

1.热备份针对归档模式的数据库，在数据库仍旧处于工作状态时进行备份。而冷备份指在数据库关闭后，进行备份，适用于所有模式的数据库。热备份的优点在于当备份时，数据库仍旧可以被使用并且可以将数据库恢复到任意一个时间点。

2.冷备份的优点在于它的备份和恢复操作相当简单，并且由于冷备份的数据库可以工作在非归档模式下,数据库性能会比归档模式稍好。（因为不必将archive log写入硬盘）

冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法
热备份是在数据库运行的情况下，采用归档方式备份数据的方法

冷备的优缺点：

是非常快速的备份方法（只需拷贝文件）
容易归档（简单拷贝即可）
容易恢复到某个时间点上（只需将文件再拷贝回去）
能与归档方法相结合，作数据库“最新状态”的恢复。
低度维护，高度安全。

冷备份不足：

单独使用时，只能提供到“某一时间点上”的恢复。
在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
不能按表或按用户恢复。

热备的优缺点

可在表空间或数据文件级备份，备份时间短。
备份时数据库仍可使用。
可达到秒级恢复（恢复到某一时间点上）。
可对几乎所有数据库实体作恢复。
恢复是快速的，在大多数情况下在数据库仍工作时恢复。

热备份的不足是：

不能出错，否则后果严重。
若热备份不成功，所得结果不可用于时间点的恢复。
因难于维护，所以要特别仔细小心，不允许“以失败而告终”。

解释什么是死锁，如何解决Oracle中的死锁？

简言之就是存在加了锁而没有解锁，可能是使用锁没有提交或者回滚事务，如果是表级锁则不能操作表，客户端处于等在状态，如果是行级锁则不能操作锁定行

解决办法：

查找出被锁的表

select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id;
select b.username,b.sid,b.serial#,logon_time
from vl o c k e d o b j e c t a , v locked_object a,vlocked 
objecta,vsession b
where a.session_id = b.sid order by b.logon_time;

杀进程中的会话

1	alter system kill session “sid,serial#”;

两个检查表结构的方法

1、 DESCRIBE命令
2、 DBMS_METADATA.GET_DDL 包

内连接，左连接，右连接的区别

1.内连接：
指主表，从表中符合连接条件的记录全部显示

2.左连接：
外连接方式，主要是显示主表，从表中符合连接条件的记录，并且主表中所有不符合连接条件的记录也要显示。

3.右连接：
外连接方式，主要是显示主表，从表中所有符合连接条件的记录，并且从表中不符合的记录也要显示。

Oracle_Home和Oracle_Base的区别？

ORACLE_BASE是oracle的根目录，ORACLE_HOME是oracle产品的目录。

Oracle的导入导出有几种方式，有何区别？

使用Oracle工具

使用Oracle工具exp/imp

dmp文件方式

dmp文件是二进制的，可以跨平台，并且包含权限，支持大字段数据，是用的最广泛的一种。

sql文件方式

SQL文件可用文本编辑器查看，有利于可读性，但效率不如dmp文件，适合小数据量导入导出。尤其注意的是表中不能有大字段（blob,clob,long）

pde文件

pde格式是PL/SQL 自带的文件格式，适用于PL/SQL工具，编辑器无法查看。

怎样优化Oracle数据库，有几种方式？

数据库性能最关键的因素在于I/O，优化数据库最关键的问题在于减少磁盘的I/O，优化Oracle可以分为物理和逻辑两个方面。

物理优化：

Oracle的运行环境（网络，硬件等）
使用合适的优化器
合理配置oracle实例参数
建立合适的索引，减少IO
将索引数据和表数据分开在不同的表空间上（降低IO冲突）
建立表分区，将数据分别存储在不同的分区上（以空间换取时间，减少IO）

逻辑优化

可以对表进行逻辑分割
如中国移动用户表，可以根据手机尾数分成10个表，这样对性能会有一定的作用
对sql语句进行优化:
Sql语句使用占位符语句，并且开发时候必须按照规定编写sql语句（如全部大写，全部小写等）oracle解析语句后会放置到共享池中
如： select * from Emp where name=? 这个语句只会在共享池中有一条，而如果是字符串的话，那就根据不同名字存在不同的语句，所以占位符效率较好
数据库不仅仅是一个存储数据的地方，同样是一个编程的地方，一些耗时的操作，可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能
尽量不使用*号，如select * from Emp，因为要转化为具体的列名是要查数据字典，比较耗时
选择有效的表名
对于多表连接查询，可能oracle的优化器并不会优化到这个程度， oracle 中多表查询是根据FROM字句从右到左的数据进行的，那么最好右边的表（也就是基础表）选择数据较少的表，这样排序更快速，如果有link表（多对多中间表），那么将link表放最右边作为基础表，在默认情况下oracle会自动优化，但是如果配置了优化器的情况下，可能不会自动优化，所以平时最好能按照这个方式编写sql
Where字句 规则
Oracle 中Where字句时从右往左处理的，表之间的连接写在其他条件之前，能过滤掉非常多的数据的条件，放在where的末尾， 另外!=符号比较的列将不使用索引，列经过了计算（如变大写等）不会使用索引（需要建立起函数）， is null、is not null等优化器不会使用索引
使用Exits Not Exits 替代 In Not in
合理使用事务，合理设置事务隔离性

数据库的数据操作比较消耗数据库资源的，尽量使用批量处理，以降低事务操作次数

优化的策略一般包括:

内存优化
操作系统优化
数据存储的优化
网络优化等方法

具体到不同的数据库涉及到要调整不同的数据库配置文件、不同的操作系统参数、网络参数等等, 不同的数据库不同.

Oracle中字符串用什么符号链接

使用符号“||”

使用CONCAT（）函数进行拼接，Oracle中的concat函数仅支持两个字符串进行拼接，如果要再多，可以嵌套使用。

Oracle分区是怎样优化数据库的?

Oracle的分区可以分为：列表分区、范围分区、散列分区、复合分区。

增强可用性：如果表的一个分区由于系统故障而不能使用，表的其余好的分区仍可以使用；
减少关闭时间：如果系统故障只影响表的一部份分区，那么只有这部份分区需要修复，可能比整个大表修复花的时间更少；
维护轻松：如果需要得建表，独产管理每个公区比管理单个大表要轻松得多；
均衡I/O：可以把表的不同分区分配到不同的磁盘来平衡I/O改善性能；
改善性能：对大表的查询、增加、修改等操作可以分解到表的不同分区来并行执行，可使运行速度更快
分区对用户透明，最终用户感觉不到分区的存在

Oracle是怎样分页的？

Oracle中使用rownum来进行分页, 这个是效率最好的分页方法，hibernate也是使用rownum来进行oralce分页的

1
2
3

select * from 
  ( select rownum r,a from tabName where rownum <= 20 ) 
where r > 10

Oralce怎样存储文件，能够存储哪些文件？

Oracle 能存储 clob、nclob、 blob、 bfile
Clob 可变长度的字符型数据，也就是其他数据库中提到的文本型数据类型
Nclob 可变字符类型的数据，不过其存储的是Unicode字符集的字符数据
Blob 可变长度的二进制数据
Bfile 数据库外面存储的可变二进制数据

你在项目现场，用户要求你向正在运行的表中添加一个字段，你该怎么做?

第一种方法：关闭数据库，然后使用受限模式打开，由sys/sysdba来进行
第二种方法：不关闭数据库，将数据库置于静默状态在SYS/SYSDBA模式下用ALTER SYSTEM QUISCE RESTRICTED，这种状态下只有SYS/SYSDBA才可以对数据库进行操作，修改完毕之后再退出静默状态ALTER SYSTEM UNQUISCE
在这里复习到了数据库的两种特殊状态：静默状态(QUISCE)和挂起状态
静默状态就是只有特殊权限的SYS/SYSDBA才可以对数据库进行操作，使用ALTER SYSTEM QUISCE RESTRICTED以后系统将等候活动着的会话主动结束，同时阻止建立新的会话，系统挂起所有的SQL语句，等恢复以后再重新激活会话执行挂起的SQL。
挂起状态就是系统将数据库所有对物理文件（数据文件，控制文件，日志文件）的I/O操作都暂停，但是并不禁止非DBA用户对数据库进行操作。这种状态主要用于进行数据库备份。

Oracle中回滚的概念?回滚段有什么作用。

回滚就是在事务提交之前将数据库数据恢复到事务修改之前数据库数据状态。
回滚段就是为回滚提供依据，记录的是事务操作数据库之前的数据或者对应于以前操作的操作，这个内容要根据以前的操作而定。

比如说以前事务操作如果是UPDATE那么回滚段则存储UPDATE以前的数据，如果事务是DELETE操作那么存储的则是与之相对应的INSERT操作语句，相反如果事务操作是INSERT那么记录相应的则是DELETE操作了。

执行COMMIT命令时，数据库将会发生什么改变，ROLLBACK呢?

commit 提交 时首先是与事务对应的重做日志信息将被写入到数据库物理文件中的重做日志文件中，至于是否会真正将事务操作的内容反映到数据文件还好看DBWR是否启动了。在完成数据库的插入，删除和修改操作时，只有当事务提交到数据库才算完成，有提交前只有操作数据库的本人才能看到，别人只有在最后提交完成才可以看到。
ROLLBACK 回滚 当前尚未提交的事务，使数据库恢复到事务操作前的状态。

oracle中执行语句错误时去哪里查找错误信息?

1	Select * from USER_ERRORS

oracle中有哪些类型的文件?

数据文件，控制文件，日志文件和数据字典

介绍一下oracle的体系结构?

逻辑体系结构：块，区，段，表空间
物理体系结构：表空间，三大文件
软件体系结构：SGA,后台进程

简述ORACLE中SGA的组成部分。

系统全局区包括：共享池、重做日志缓存区、数据高速缓存区，大池，JAVA池。

数据库缓冲区缓存（Database buffer cache）、
重做日志缓冲区（redo buffer）、
共享池（share pool）、
大池（large pool）、
Java池（Java pool）、
流池（stream pool）

共享池包含：库缓存（library cache）、数据字典缓存
数据库缓冲区缓存：存储从数据文件中读取到的数据块
重做日志缓冲区：保存重做日志
库缓存：存储sql语句解析以及执行计划
数据字典缓存：存储解析sql语句以及管理数据库的信息
大池：并行操作时、数据库恢复时使用

在Oracle表空间的分类和作用，如排序时数据将放在什么表空间?

作用是为了突破存储容量的限制，是一个逻辑概念。排序数据放在临时表空间。

关于RAC

RAC集群分为高可用集群和负载均衡集群，
下面我将不区分这两个概念，以防迷糊，我下面说的集群直接是指负载均衡集群。

所谓集群就是通过配置搭建一个这样的环境，环境中有两个或两个以上的服务器（节点），这些节点上分别运行着同一个服务，对于服务请求，这些节点将均衡请求负载，
而当一个节点down掉之后，原本在这个节点处理的请求会自动的转移到其他可用的节点上面。而对于用户而言，整个环境内部是透明的，就如同一台服务器一样。这就是集群的概念。

Grid

在oracle 10g中，grid方案的实现所需的软件包含在不同的地方，主要有clusterware集群软件，database中的RAC实现，以及database中dbca建库中的ASM的实现等。
到了oracle 11g后，oracle公司讲实现grid的这些个组件整合到了一起，构成了Grid Infrastructure（GI），这样就像clusterware对于集群一样，GI就是用来实现grid的软件。
这时候要注意，GI不等于grid。

GI主要包括了两个主要部分，即clusterware和ASM，当然还有一些其他组件，比如grid control、data pump等。
从11gR2开始，如果用户想使用clusterware或者ASM的话，则必须下载安装这个软件。

谈谈对oracle的row_ id是否理解?请简述?

ORACLE的row_id是一个伪列，其个是为18个字节可将这18个字节用6363来划分，分别表示段编号，数据文件编号，数据块编号和记录编号。
Row_id表示的是一个记录的物理存储地址。

data guard

DATA GUARD的最主要的功能是冗灾。当然根据配置的不同,DATA GUARD还可以具备以下特点：高可用、性能提升、数据保护以及故障恢复等。

Data Guard运行要求:

1.主机必需运行在归档模式下。
2.主备数据库的版本必须一样，操作系统必须一样，版本可不同,主备机可使用不同的目录结构。
3.主备机必须都要运行在32位或64位下。
4.主库避免nologing的方式，这样会导致备机无法与主机同步。

oracle中如何删除用户?

Oracle中使用DROP USER来删除用户
如果使用DROP USER CASCADE那么用户的对象也同时被删除掉。
为了达到删除用户的效果而又不影响对用户下的对象的使用可以使用alter user username account lock将用户锁定。

客户端对服务器端的oralce操作的流程是什么?

专用模式下：用户通过应用程序进程试图去得到一个与ORACLE数据库服务器的连接客户端通过网络传递连接请求，ORACLE服务器则使用监听进程监听用户请求，并且来验证用户身份，通过验证则为用户分配一专用服务进程用户提交SQL语句专用服务进程则首先在SGA区的共享池中检查是否有与该SQL语句相似的已经被解析执行并且缓存的SQL语句，如果有则采用它的解析结果和执行计划执行SQL语句，如果没有则对SQL语句进行语法解析生成执行计划通过解析则执行操作获取数据将执行结果返回给客户。

共享模式下：与专用模式不同在于当监听程序验证用户的合法性以后并不为它分配一个专用的服务进程，而是将该请求与响应的调度进程相联系起来，并将起放入到一个请求队列中，最终由响应的Dnnn来从调度队列中获取一个请求并为之分配一个空闲的服务进程，接下来有服务进程对该请求进行服务操作和专用方式下相同，处理完成之后由服务进程先将结果放入一个返回队列最后再由调度进程（Dnnn）将返回队列中的结果返回给对应的用户。

写一个存储过程

使employee(name,age,emp_no,salary)表中的salary值在0-1000之间的员工的工资上涨20%，（提示：要求用到游标）

Cteate or replace procedure emp_sal
V_name employee.name%type;
V_emp_no employee.emp_no%type;
V_salary employee.salary%type;
Cursor cursor_sal is
Select name,emp_no,salary from employee where salary between 0 and 1000;
Begin
Open cursor_sal;
Loop
Fetch cursor_sal into v_name,v_emp_no,v_salary;
Exit when cursor_sal%notfound;
Update employee set salary=salary*1.2 where name=v_name and emp_no=v_emp_no;
End loop;
Close cursor_sal;
Commit;
End;

##简述Oracle的归档与不归档工作模式，分别说明。

Oracle归档模式是指在创建数据库时指定了ARCHIVELOG参数，这种模式下，当重做日志文件写满的时候会将该重做日志文件的内容保存到指定的位置（由初始化文件中的参数ARCHIVE_LOG_DEST_n来决定）。
并不是数据库在归档模式下工作的时候就可以自动完成归档操作，在归档模式下可以有两种归档方式：自动归档（在初始化文件中的参数ARCHIVE_LOG_START被设置为TRUE）和手动归档。
如果归档模式下没有启动自动归档的话，而且又没有实行手动归档那么当LGWR进程将重做日志信息写入已经写满的重做日志文件时数据库将会被挂起直到进行了归档。
可见归档是对重做日志文件信息的一种保护措施。
Oracle非归档模式下当重做日志文件写满以后若是有LGWR进行重做日志信息的写入操作时，以前保存在重做日志文件中的重做日志信息就会被覆盖掉。

Oracle索引分为哪几类，说出唯一索引和位图索引的概念。

Oracle索引有B树索引，位图索引，函数索引，簇索引等。

唯一索引也是B树索引的一种，它要求被索引的字段值不可以重复。在创建的时候使用B树算法创建。
位图索引并不是采用象唯一索引那样存储（索引字段值，记录ROWID）来创建索引段的，而是为每一个唯一的字段值创建一个位图，位图中使用位元来对应一个记录的ROWID。位元到ROWID是通过映射的到的。

SQL中，执行四舍五入的是哪个函数?

Round（value，保留的小数位数）与只对应的还有一个特别相似的函数trunc（value，保留的小数位数）它的作用是根据要保留的小数位数来截取原数。

oracle数据库表存放到磁盘什么地方?数据文件

ORACLE数据库的表存放在物理文件中的数据文件中。

当执行insert语句并提交后，这些提交的数据存储到什么地方??

存储到了数据库的数据文件中。

Exits和in在ORALCE数据库中那个执行效率更高?

EXITS执行效率要比使用IN要快。

Decode函数的用法?

DECODE的语法：

DECODE(value,if1,then1,if2,then2,if3,then3,…,else)，表示如果value等于if1时，DECODE函数的结果返回then1,…,如果不等于任何一个if值，则返回else。

初看一下，DECODE 只能做等于测试，但刚才也看到了，我们通过一些函数或计算替代value，是可以使DECODE函数具备大于、小于或等于功能。

如何用decode进行大于小于的比较?

利用sign()函数和DECODE和在一起用

1	select decode(sign(变量1-变量2),-1,变量1,变量2) from dual;

decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)该函数的含义如下：

IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
……
ELSIF 条件=值n THEN
RETURN(翻译值n)

ELSE
RETURN(缺省值)
END IF

使用方法：

1、比较大小

1 2	select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; –取较小值 sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：

1 2	变量1=10，变量2=20 则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

2、表、视图结构转化

现有一个商品销售表sale，
表结构为：
month char(6) –月份
sell number(10,2) –月销售金额

现有数据为：
200001 1000
200002 1100
200003 1200
200004 1300
200005 1400
200006 1500
200007 1600
200101 1100
200202 1200
200301 1300

想要转化为以下结构的数据：

month1 number(10,2) –1月销售金额
month2 number(10,2) –2月销售金额
month3 number(10,2) –3月销售金额
month4 number(10,2) –4月销售金额
month5 number(10,2) –5月销售金额
month6 number(10,2) –6月销售金额
month7 number(10,2) –7月销售金额
month8 number(10,2) –8月销售金额
month9 number(10,2) –9月销售金额
month10 number(10,2) –10月销售金额
month11 number(10,2) –11月销售金额
month12 number(10,2) –12月销售金额

结构转化的SQL语句为：

create or replace view
v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12)
as
select
substrb(month,1,4),
sum(decode(substrb(month,5,2),’01′,sell,0)),
sum(decode(substrb(month,5,2),’02′,sell,0)),
sum(decode(substrb(month,5,2),’03′,sell,0)),
sum(decode(substrb(month,5,2),’04′,sell,0)),
sum(decode(substrb(month,5,2),’05′,sell,0)),
sum(decode(substrb(month,5,2),’06′,sell,0)),
sum(decode(substrb(month,5,2),’07′,sell,0)),
sum(decode(substrb(month,5,2),’08′,sell,0)),
sum(decode(substrb(month,5,2),’09′,sell,0)),
sum(decode(substrb(month,5,2),’10′,sell,0)),
sum(decode(substrb(month,5,2),’11′,sell,0)),
sum(decode(substrb(month,5,2),’12′,sell,0))
from sale
group by substrb(month,1,4);

CASE语句的用法?

Oracle用法很简单：

SELECT last_name, job_id, salary
CASE job_id
WHEN ‘IT_PROG’ THEN 1.10salary
WHEN ‘ST_CLERK’ THEN 1.15salary
WHEN ‘SA_REP’ THEN 1.20*salary
ELSE salary END “REVISED_SALARY”
FROM employees

排他锁(EXCLUSIVE)

使用方法：

SELECT * FROM order_master WHERE vencode=“V002”
FOR UPDATE WAIT 5;
LOCK TABLE order_master IN SHARE MODE;
LOCK TABLE itemfile IN EXCLUSIVE MODE NOWAIT;

ORACLE锁具体分为以下几类：

1.按用户与系统划分，可以分为自动锁与显示锁

自动锁：当进行一项数据库操作时，缺省情况下，系统自动为此数据库操作获得所有有必要的锁。
显示锁：某些情况下，需要用户显示的锁定数据库操作要用到的数据，才能使数据库操作执行得更好，显示锁是用户为数据库对象设定的。

2 . 按锁级别划分，可分为共享锁与排它锁

共享锁：共享锁使一个事务对特定数据库资源进行共享访问——另一事务也可对此资源进行访问或获得相同共享锁。共享锁为事务提供高并发性，但如拙劣的事务设计+共享锁容易造成死锁或数据更新丢失。
排它锁：事务设置排它锁后，该事务单独获得此资源，另一事务不能在此事务提交之前获得相同对象的共享锁或排它锁。

3.按操作划分，可分为DML锁、DDL锁

DML锁又可以分为，行锁、表锁、死锁

行锁：当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。
表级锁：当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行DDL语句影响记录行的更新。事务也可以在进行过程中获得共享锁或排它锁，只有当事务显示使用LOCK TABLE语句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用LOCK TABLE显示的定义一个表级的共享锁(LOCK TABLE具体用法请参考相关文档)。
死锁：当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁。

如事务1在表A行记录#3中有一排它锁，
并等待事务2在表A中记录#4中排它锁的释放，
而事务2在表A记录行#4中有一排它锁，并等待事务; 
1在表A中记录#3中排它锁的释放，
事务1与事务2彼此等待，因此就造成了死锁。
死锁一般是因拙劣的事务设计而产生。
死锁只能使用SQL下:
alter system kill session “sid,serial#”；
或者使用相关操作系统kill进程的命令，
如UNIX下kill -9 sid,
或者使用其它工具杀掉死锁进程。

DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁

排它DDL锁：创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。如使用alter table语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它DDL锁。
共享DDL锁：需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁。
如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时，该事务就获得了引用表的共享DDL锁。
分析锁：ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使运行相同语句的应用速度更快。一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型，ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。当一个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语句时，ORACLE重新分析编译此语句。

内部闩锁

内部闩锁：这是ORACLE中的一种特殊锁，用于顺序访问内部系统结构。当事务需向缓冲区写入信息时，为了使用此块内存区域，ORACLE首先必须取得这块内存区域的闩锁，才能向此块内存写入信息。

使用oracle 伪列删除表中重复记录

1	Delete table t where t.rowid!=(select max(t1.rowid) from table1 t1 where t1.name=t.name)

Oracle 分区在什么情况下使用

1.当一张表的数据量到达上亿行的时候，表的性能会严重降低，这个时候就需要用到分区了，通过划分成多个小表，并在每个小表上建立本地索引可以大大缩小索引数据文件的大小，从而更快的定位到目标数据来提升访问性能。
2.分区除了可以用来提升访问性能外，还因为可以指定分区所使用的表空间，因此也用来做数据的生命周期管理。当前需要频繁使用的活跃数据可以放到访问速度更快但价格也更贵的存储设备上，而2、3年前的历史数据，或者叫冷数据可以放到更廉价、速度更低的设备上。从而降低存储费用。

分区技术有如下优点：

1、分区技术使数据库的可管理性变得更加容易，如：用户可以往一个单独的分区中装载数据，而对其他分区没有任何影响；用户可以在单独的分区上创建索引等。

2、分区可以提高表的查询性能，SQL语句的where子句会过滤掉不需要的分区，oracle不会再扫描那些不需要的分区。

3、分区技术减少数据的不可用时间，用户可以单独维护一个分区中的数据，而不影响其他分区中数据的使用。

4、分区技术在数据库级完成，几乎不需要对应用程序做任何修改。

Oracle提供的分区方法

范围分区：根据表中列值的范围将整个表分成不同的部分，如按照时间进行范围分区。
列表分区：使用列表值将表划分成几部分。
散列分区：使用哈希函数把表分成几部分。
复合分区：同时使用两种分区方法对表进行分区。

1、范围分区

就是根据数据库表中某一字段的值的范围来划分分区。
数据中有空值，Oracle机制会自动将其规划到maxvalue的分区中。

2、散列分区

根据字段的hash值进行均匀分布，尽可能地实现各分区所散列的数据相等。
散列分区即为哈希分区，Oracle采用哈希码技术分区，具体分区如何由Oracle说的算，也可能我下一次搜索就不是这个数据了。

3、列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

4、复合分区

根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区同再使用散列分区的一种分区方法

Oracle创建分区

既然，Oracle分区有如此好处，我们在这里通过一个例子来讲解如何创建分区。在我们的学生信息系统案例当中，学生成绩表（SCORE）会随着学生的增多和课程的增多，表中的数据量会越来越大，所以可以考虑创建分区表来解决这个问题。

Oracle分区也是通过create table命令组成，但是对表进行分区时，得考虑一个字段作为分区建，通常按值的范围来划分分区，所以这里考虑使用成绩的录入时间进行分区。具体代码如下：

-- Create table
create table STUDENT.SCORE
(
  scoreid  VARCHAR2(18) not null,
  stuid    VARCHAR2(11),
  courseid VARCHAR2(9),
  score    NUMBER,
  scdate   DATE
)
partition by range(scdate)(
partition p_score_2018 values less than (TO_DATE('2019-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
 TABLESPACE TS_2018,
partition p_score_2019 values less than (TO_DATE('2020-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss'))
 TABLESPACE TS_2019,
partition p_score_2020 values less than (MAXVALUE)
 TABLESPACE TS_2020
);
-- Add comments to the table 
comment on table STUDENT.SCORE
  is '学生成绩表';
-- Add comments to the columns 
comment on column STUDENT.SCORE.scoreid
  is '学生成绩id';
comment on column STUDENT.SCORE.stuid
  is '学生学号';
comment on column STUDENT.SCORE.courseid
  is '课程id(年度+上下学期+课程序列)';
comment on column STUDENT.SCORE.score
  is '成绩';
comment on column STUDENT.SCORE.scdate
  is '成绩录入时间';
-- Create/Recreate primary, unique and foreign key constraints 
alter table STUDENT.SCORE
  add constraint PK_SCORE primary key (SCOREID)
  using index 
  tablespace USERS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

这里使用命令partition by range对成绩的录入日期（scdate）进行分区，如录入日期小于2019年的会被放入分区p_score_2018当中，2019年数据会被放入p_score_2019这个分区当中，大于2019年数据都会被放入到p_score_2020这个分区当中。

这里不必为最后一个分区指定最大值，maxvalue关键字会告诉Oracle使用这个分区来存储前面几个分区当中不能存储的数据。

上面实例展示的是Oracle按照值的范围进行分区，Oracle还支出散列分区，通过某一个字段，把表中的数据散列在各个分区中。可以通过关键字partition by hash，可以把分区散列到不同的表空间当中。

Oracle还支持列表分区（partition by list），它是通过按照指定分区建的值归并到各个分区，其实这里学生成绩表也可以考虑按照课程进行列表分区。

总结：Oracle分区对大型表（数据量大）有重大的性能提升，所以在表结构设计时，需要提前按照相关业务需求进行相应的改进。