:@(黑线) :@(黑线) :@(黑线) :@(黑线) :@(黑线) :@(黑线) :@(黑线) :@(黑线)

1. Oracle逻辑结构——段、区间、块间的关系，存储分配、管理空间的两个基本单位，块大小的定义位置和参数。

• 段由区间组成，区间由块组成
• 存储分配的基本单位：区间
• 管理空间的基本单位：块
• 标准的数据块大小在pfile文件
• 由初始参数DB_BLOCK_SIZE指定

2. Oracle段有哪些

3. SGA、PGA是什么，SGA的构成

• SGA：系统全局区(System Global Area, SGA) 是一组共享内存结构，包含Oracle数据库例程数据及控制信息。
  同时连接到同一例程的多个用户共享此例程的SGA数据

• SGA的数据结构：

  • 数据库缓冲区：SGA用来保存从数据文件中读取的数据块
  • 重做日志缓冲区：SGA中的循环缓冲区，用于记录数据库发生改变的信息
  • 共享池：用于保存用户程序
  • Java池：为Java命令提供语法分析
  • 大型池：DBA配置的可选内存区域，用于分配大量的内存
• PGA：程序全局区

4. 数据字典的含义，Oracle数据字典的隶属用户，存放的表空间。

• 数据字典：是Oracle存放关于 数据库内部信息 的地方，其用途是用来 描述数据库内部的运行和管理情况 。
• 隶属用户： sys 用户
• 存放的表空间： system 表

5. 索引、簇的作用

• 索引(与表相关联的可选结构)：

  • 可以提高读取数据的效率
  • 提供对表数据的访问路径，从而能够快速定位指定的信息。

• 簇：

  • 有些表共享公共的列，并经常被同时访问， 为了提高数据存取的效率 ，把这些表在物理上存储在一起，得到的表的组合就是簇
  • 与索引相似，簇并不影响应用程序的设计
  • 用户和应用程序并不关心表是否是簇的一部分，因为无论表在不在簇中，访问表的SQL语句都是一样的。

6. 归档模式和非归档模式的区别

• 非归档模式：

  • 只能进行脱机备份，数据库必须 完全关闭后 备份，在备份过程中 数据库不可用
  • 必须备份整个数据库，不能只备份部分数据库
  • 不能增量备份
  • 只能部分恢复，如果数据文件丢失需要恢复， DBA 只能恢复最后一次的完全备份 ，而之后的所有数据库改变全部丢失

• 归档模式：

  • 可以进行完全、不完全恢复：由于对数据库所做的全部改动都记录在日志文件中，如果发生硬盘故障等导致数据文件丢失的话，则可以利用物理备份和归档日志完全恢复数据库，不会丢失任何数据
  • 可以在数据库运行状态下，对数据库进行备份
  • 可以增量备份：只需做一次完全备份，以后只备份发生改变的数据，可以提高备份速度

7. 重做日志文件组的个数和作用

• 重做日志文件：至少两个
• 重做日志文件组：在oracle数据库中， 至少需要两个 可用的重做日志文件组，重做日志文件组的最大数量由控制文件中的最大参数MAXLOGFILES指定。每个重做日志文件组中，至少需要一个可用的重做日志文件成员。
• 重做日志文件：主要用于在Oracle发生故障的时候和数据库备份文件配合恢复数据库，其中保存了数据库的变更操作信息

8. 控制文件中定义的内容

• 数据库名称
• 相关数据文件和重做日志文件的名称和位置
• 数据库创建的时间戳
• 当前的日志序列号
• 检查点信息

9. 3种构成Oracle数据库的文件是什么

• 控制文件、数据文件、日志文件

10. 增加表空间大小的方法

• 给表空间增加数据文件

ALTER TABLESPACE 表空间名 ADD DATAFILE 'D:\XXX.DBF' SIZE 50M;

• 新增数据文件，并且允许数据文件自动增长

ALTER TABLESPACE 表空间名 ADD DATAFILE 'D:\XXX.DBF' SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

• 允许已存在的数据文件自动增长

ALTER DATABASE DATAFILE 'D:\XXX.DBF' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

• 手工改变已存在数据文件的大小

ALTER DATABASE DATAFILE 'D:\XXX.DBF' RESIZE 100M;

11. Oracle后台进程——LGWR、DBWn、CKPT、SMON、PMON的作用

• LGWR：日志写入进程，把重做日志缓冲区中的内容写入重做日志中
• DBWn：数据库写入进程，把缓冲区中的内容写入到数据文件中
• CKPT：检查点进程，当发生检查点事件时唤醒DBWn进程，更新数据库所有的数据文件和控制文件，并标记最新的检查点，以便下一次更新从最新的检查点开始
• SMON：系统监控进程，当失败的数据库实例重新启动时，SMON进程完成实例的恢复工作
• PMON：进程监控进程，当用户进程失败时，用于完成进程的恢复

12. Oracle实例是什么，Oracle实例支持的4种状态及各状态下可完成的操作。

• 实例是能够支撑数据库运行的数据库程序(SGA+Oracle进程)

• 四种状态：

  • 打开：启动例程，装载并打开数据库
  • 关闭：允许用户访问数据库的状态转变为休止状态
  • 已装载：启动例程并装载数据库，没有打开数据库。用于更改数据库的归档模式、执行恢复操作、数据文件恢复
  • 已启动：启动实例，不装载。对控制文件进行恢复或重新创建数据库

13. 概要文件是什么，对用户的作用

• 概要文件(资源文件)：是Oracle为了合理地分配和使用系统资源而提出的概念
• 作用：概要文件可以限制允许用户使用的资源

14. 参数文件是什么，有什么作用。

• Oracle参数文件：是一个包含一系列 参数以及参数对应值 的 操作系统文件
• 作用：决定了数据库的物理结构、内存、数据库的限制以及系统大量的默认值、数据库的各种物理属性、指定数据库控制文件名和路径等信息，是进行数据库设计和性能调优的重要文件

15. 序列是什么，如何使用序列

• 序列：是Oracle提供的用于生成一系列唯一数字的数据库对象。序列会自动生成顺序递增的序列号，以实现自动提供唯一的主键值

CREATE SEQUENCE sequence --创建序列名称
[INCREMENT BY n] --递增的序列值是 n 如果 n 是正数就递增,如果是负数就递减 默认是 1
[START WITH n] --开始的值,递增默认是 minvalue 递减是 maxvalue
[{MAXVALUE n | NOMAXVALUE}] --最大值  
[{MINVALUE n | NOMINVALUE}] --最小值
[{CYCLE | NOCYCLE}] --循环/不循环
[{CACHE n | NOCACHE}];--分配并存入到内存中

• 序列调用 产生一个新的序列

select seq_test.nextval from dual

• 查看当前序列的值

select seq_test.currval from dual

• 示例：

CREATE SEQUENCE seq_test;
-- Orcale中生成的源代码
create sequence SEQ_TEST
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

16. 角色是什么，角色和用户的关系，如何定义用户和角色，如何将角色、权限授权给用户、如何删除用户。

• 角色是一组相关权限的集合，为了简化权限管理
• 角色赋给用户，用户就有了该角色的权利
• 定义用户：create user 用户名;
• 定义角色：create role 角色名;
• 授权角色给用户：grant 角色名 to 用户名;
• 授权权限给用户：grant 权限名 on 表名 to 用户名;
• 删除用户：drop user 用户名;

17. Create/drop/alter的作用

• Create可以用来创建表、触发器、过程和函数
• Drop可以用来删除表，触发器、过程和函数
• Alter可以用来修改表的结构

18. rowid的作用

• 一个标识行中唯一特性的 行标识符 ，长度为18 位字符，在Oracle内部通过ROWID来定位所需记录的，包含了该行数据在Oracle数据库中的物理地址，可以 帮助 oracle数据库 快速寻找某一行数据的位置

19. Select/update/delete命令，子查询、连接、模糊匹配、涉空查询、集函数

• 子查询：

--where型子查询：查出每个栏目最新的商品(以good_id为最大为最新商品)
--goods货物表，good_id表的主键,cat_id栏目的编号
select cat_id,good_id,good_name 
  from goods 
  where good_id 
  in(selct max(good_id) from goods group by cat_id);

--from型子查询：查出每个栏目最新的商品(以good_id为最大为最新商品)
select * 
  from (select cat_id,good_id,good_name from goods order by cat_id asc, good_id desc) 
  as tep 
  group by cat_id;

--in子查询：查询年龄为20岁的员工部门
select * 
  from department 
  where did 
  in(SELECT did from employee where age=20);

--exists子查询:查询是否存在年龄大于21岁的员工
select * 
  from department 
  where EXISTS (SELECT did from employee where age>21);

• 连接：JOIN/INNER JOIN
• 模糊查询：LIKE
• 涉空查询：IS NULL
• 函数：

20. PL/SQL注释符，循环、分支语句语法，变量的声明和赋值

21. PL/SQL块

注释

• 注释符：单行注释符：-- 多行注释符：/**/

常量变量

• 声明常量格式：<常量名>constant<数据类型>:=<值>;

  • eg:num constant number(1):=4; str constant char:='abc';

• 声明变量格式：<变量名><数据类型>[(宽度):=<初始值>];

  • eg:count varchar(10):='1'

PL/SQL程序块

[DECLARE]
--声明部分
BEGIN
--执行部分
[EXCEPTION]
--异常处理部分
END

--示例：
SET SERVEROUTPUT ON;
DECLARE
  a number:=1;
BEGIN
  a:=a + 5;
  DBMS_OUTPUT.PUT_LINE('和为：'||TO_CHAR(a));
END;
--结果：和为6

循环分支语句

• 分支语句：

--1：
IF 条件 THEN
    执行语句....
END IF;
--2：
IF 条件 THEN
    执行语句....
ELSE
    执行语句.....
END IF;
--3：
IF 条件 THEN
    执行语句....
ELSIF 条件 THEN
    执行语句....
ELSE
    执行语句.....
END IF;

• 例题：

set serveroutput on;
declare
    day integer := 3;
    result varchar(200);
begin
    resule := case day
        when 1 then '星期一';
        when 2 then '星期二';
        ........
        when 7 then '星期天';
        ELSE '数据越界'
        END
    dbms_output.put_line(result);
End

• 循环语句格式：
  第一种：

LOOP
  循环体
  IF 条件表达式 THEN
    EXIT;
  END IF;
END LOOP;

eg:求10的阶乘

DECLARE
  n number:=1;
  count1 number:=2;
BEGIN
  LOOP
    n:=n*count1;
    count1:=count1+1;
    IF count1>10 THEN
      EXIT;
    END IF
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(n))
END;

第二种：

LOOP
  循环体
  EXIT WHEN 条件表达式 
END LOOP;

eg:

DECLARE
  n number:=1;
  count1 number:=2;
BEGIN
  LOOP
    n:=n*count1;
    count1:=count1+1;
    EXIT WHEN count1=11 
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(TO_CHAR(n));
END;

• while和for循环：

--FOR
FOR 循环变量 IN 初始值..终止值 --FOR count1 IN 2..10
  LOOP
    程序块
  END LOOP;
--WHILE
WHILE 条件表达式 --WHILE count1 <=10
  LOOP
    程序块1
  END LOOP;

22. 存储过程/函数/触发器的定义语法，调用存储过程和函数的方法

存储过程

• 存储过程：存放在数据字典中，可以在不同的用户和应用程序之间共享，并可实现程序的优化和重用

CREATE [OR REPLACE] PROCEDURE <过程名>
        [<参数名1>,<参数类型><数据类型>[DEFAULT<默认值>][,…n]]
{IS|AS}
[<变量声明>]
BEGIN
    <过程体>
END[<过程名>][;]

• 说明：

  1. 过程名：OR和REPLACE 表示在创建存储过程时，如果已经存在同名的过程，则重新创建

  2. 参数：IN/OUT/IN OUT

     IN：表示参数是输入给过程的
     OUT：表示参数在过程中将被赋值，可以传给过程体的外部
     IN OUT：表示该类型的参数既可以向过程体传值，也可以在过程体中赋值

  3. DEFAULT：制定过程中IN参数的默认值，默认值必须是常量
• eg：创建存储过程，计算指定学生的总学分

CREATE OR REPLACE PROCEDURE totalcredit(xh IN varchar2)
AS xf number;
BEGIN
    SELECT 总学分
    INTO xf
    FROM XSB
    WHERE 学号=xh AND ROWNUM=1；
    DBMS_OUTPUT.PUT_LINE(xf);
END;

• 调用存储过程：
  EXEC totalcredit(xh=>’151242’);

函数

CREATE [OR REPLACE] FUNCTION 函数名
(
  参数名1,参数类型 数据类型,
  参数名2,参数类型 数据类型,
  参数名3,参数类型 数据类型,
  ...
)
  RETURN 返回值类型
  IS | AS
  声明变量
  BEGIN
    函数体
    RETURN 返回表达式
  END 函数名;

• 参数类型和存储过程一样
  示例：计算某门可乘全体学生的平均成绩

CREATE OR REPLACE FUNCTION average(cnum IN char)
  RETURN number
AS
  avger number;
BEGIN
  SELECT AVG(成绩) INTO avger
    FROM CJB
    WHERE 课程号=cnum
    GROUP BY 课程号;
  RETURN(avger);
END;

• 调用函数

变量名:=函数名(实参1,实参2,...)

触发器

• 触发器是被指定关联到一个表的数据对象，他不需要调用，党对一个表的特别事件出现时，它就会被激活
• DML触发器：当数据库中发生数据操纵语言事件时将调用DML触发器
• 在触发器的执行部分只能用DML语句(SELECT、INSERT、UPDATE、DELETE)，不能使用DDL语句(CREATE、ALTER、DROP)

CREATE [OR REPLACE] TRIGGER [<用户方案名>.]<触发器名>
  {BEFORE|AFTER|INSTEAD OF}
  {DELETE|INSERT|UPDATE[OF <列名>[,..n]]}
    [OR {DELETE|INSERT|UPDATE[OF <列名>[,..n]]}]
  ON {<表名>|<视图名>}
  [FOR EACH ROW[WHEN(<条件表达式>)]]
  <PL/SQL语句块>

例1: 建立一个触发器, 当职工表 emp 表被删除一条记录时，把被删除记录写到职工表删除日志表中去

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2;
CREATE OR REPLACE TRIGGER tr_del_emp
   BEFORE DELETE --指定触发时机为删除操作前触发
   ON scott.emp
   FOR EACH ROW   --说明创建的是行级触发器
BEGIN
   --将修改前数据插入到日志记录表 del_emp ,以供监督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;
DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

例2：限制对Departments表修改（包括INSERT,DELETE,UPDATE）的时间范围，即不允许在非工作时间修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time
BEFORE INSERT OR DELETE OR UPDATE
ON departments
BEGIN
 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
     RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');
 END IF;
END;

23. 游标的定义和使用

• 游标：用来存储多条查询数据的一种数据结构（结果集），它有一个指针，从上往下移动，从而能够遍历每条记录
• 声明游标

DECLARE CURSOR 游标名
  IS
  SELECT语句

• 打开游标：OPEN 游标名
• 读取数据：FETCH 游标名 INTO 变量名,...n
  其中INTO 子句将读取的数据存放到指定的变量中
• 关闭游标：CLOSE 游标名

24. SQL PLUS是什么，常用的SQL PLUS命令及其功能

• SQL/Plus是用户和服务器之间的一种接口，用户可以通过它使用SQL语句交互式的访问数据库

25. 区分系统权限和对象权限

• 系统权限：系统规定用户使用数据库的权限。如创建表，创建索引，连接实例
• 对象权限：某种权限用户对其他用户的表或视图的存取权限。如：读取视图，可更新某些列