Java基础——Oracle（六）
一、数据字典和动态性能视图
数据字典: oracle中的重要组成部分,提供了数据库的一些系统信息，记录了数据库的系统信息,它是只读表和视图的集合,数据字典的所有者为 sys 用户。用户只能在数据字典上执行查询操作(select语句),其维护和修改是由系统完成的。
其组成包：
1.字典基本表 //存储数据库的基本信息,普通用户不能直接访问字典的基表
2.数据字典视图 //基于数据字典基表所建立的视图,可以供普通用户查询
数据字典视图主要包括：
uesr_xxx,all_xxx,dba_xxx 三种类型
动态性能视图: 记载了例程启动后的相关信息。用于记录当前例程的活动信息,当启动oracle server 时,系统会自动建立动态视图,当停止时,会删除动态性能视图 oracle中所有的动态性能视图都是以 v_$ 开始的,并且 oracle 为每个动态性能视图都提供了相应的同义词,并且其同义词是以 V$ 开始的,例如 v_$datafile 同义词为V$datafile 动态性能视图所有者为sys, 一般情况下,由dba 或是特权用户来查询动态性能视图，实际中较少用。
---user_tables 用于显示当前用户所拥有的所有表,它只返回用户所对应方案的所有表
select table_name from user_tables; // 用 sys 实测,得到 701 行数据
如果用 scott 用户登录 得到 七八个表
--- all_tables
用于显示当前用户可以访问的所有表,不仅会返回当前用户方案的所有表,还会返回当前用户可以访问的
其他方案的表
select table_name from all_tables;
--- dba_tables
会显示所有方案拥有的数据库表,但查询这种视数据字典视图,用户必须是dba 或是有select _any_table 系统权限。
例如,当用system 用户查询数据字典视图 dba_tables时.会返回 system,sys,scott ...方案所对应的数据库表与用户名,权限,角色等相关的数据字典等。
在建立用户时,oracle 会把用户的信息存放到数据字典中,当给用户授权或分配角色时,oracle 会将权限和角色的信息存放到数据字典。
通过查询 dba_users 可以显示所有数据库用户的详细信息
数据字典视图 dba_sys_privs 可以显示用户所具有的系统权限
数据字典视图 dba_tab_privs 可以显示用户所具有的对象权限
数据字典 dba_col_privs 可以显示用户所具有的列权限
数据字典视图 dba_role_privs 可以显示用户所具有的角色
查询 scott 具有的角色
SQL> select * from dba_role_privs where grantee='SCOTT'; //结果为 connect 和 resource (在任意表空间中建表)
结果中名为 admin 的列,值为 NO,表示是否可以把这个角色继续往下派生
二、管理表空间和数据文件
1.表空间
数据库的逻辑组成部分,从物理上说,数据库中的数据都存放在文件上,从逻辑上说,数据库是存放在表空间中的,表空间有一个或多个数据文件组成。
Oracle 中逻辑结构包扩表空间,段,区,和块
说明:数据库由表空间组成,表空间由段组成,段由区组成,区由oracle块组成,这样的结构可以提升数据库的效率。oracle 中的表空间是没有限制的,整个的表空间构成了大的数据库。表空间是由数据文件组成的，表空间中包含多少个物理文件是可以变化的。
2.建立表空间
createtablespace 一般情况下,由特权用户或是dba执行,如果是其他用户 要有 create tablespaces 权限
建立数据库后,为了便于管理,最好建立自已的表空间
create tablespace data01 datafile 'c:\test\data01.dbf' size 5m uniform size 128k; //uniform 指的是区的大小 ,size 最大不能超过 500M
会建立名为 data01 的表空间 ,并为该表空间建立名为 data01.dbf 的数据文件,区的大小是128k; //注意,必须要有 c:/test 这个目录
3.使用表空间
create table t_user (id number(4),name varchar2(30) ) tablespace data01
以往没带参数创建table , 那么这个表建到哪个表空间里了? users
4.改变表空间的状态
当表空间建立的时候,它处于联机状态 (online ),这时这个表空间是可以访问的,而且是可以读写的,但在系统进行维护的时候，可能需要改变表空间的状态,一般由特权用户或是dba操作。
1)使表空间脱机
alter tablespace 表空间名 offline // 联机 online
2)使表空间联机
alter tablespace 表空间名 online
3)只读表空间
当建立表空间时,表空间可以读写, 但如果不希望往表空间上执行写(update,insert,delete) 操作时,可以使表空间只读。
alter tablespace 表空间名 read only //设定表空间只读,这时表空间中的内容只能查询,不可写
alter tablespace 表空间名 read write //改成可以写的
例子 (说明指读特性)
1) 如果知道表空间的名字,显示这个表空间中有哪些表
select * from all_tables where tablespace_name='表空间名' //注意,表空间名要大写 //例如: select * from all_tables where tablespace_name='DATA01';
2) 知道表名,显示该表属于哪个表空间
select tablespace_name,table_name from user_tables where table_name='STUDENT' ; //只显示当前登录用户的数据,结果是 USER_DATA
3) 把上例中的表 STUDENT 所在的表空间设成只读的
alter tablespace USER_DATA read only; //此时如对该表空间的表进行操作,则出现提示: 此时无法修改文件
4) 删除表空间
delete from student where id=5;
一般情况下,由特权用户或dba操作 如果是其他用户,要有 drop tablespace 权限
drop tablespace 表空间名 including contents and datafiles;
例如:
drop tablespace data01 including contents and datafiles //注意,data01 两端不要加单引号, including contents 表示删除表空间时,同时删除该表空间中的所有数据对象,datafiles 表示连数据文件也删除
5) 扩展表空间
表空间是由数据文件组成,表空间的大小实际上就是数据文件的大小,create tablespace data01 datafile 'c:\test\data01.dbf' size 2m uniform size 128k;
假定 emp表放在 data01 这个表空间上,表空间开始的大小 就是 2m,如果往空间里添加数据,如果数据超过2m，则会出现数据空间不足。
create table TestKKK (idAAA number,dateAAA varchar2(50),randomAAA number , nameAAA varchar2(50)) tablespace data01 ; insert into TestKKK (idAAA ,dateAAA ,randomAAA,nameAAA) select rownum , to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss'), trunc(dbms_random.value(0, 100)), dbms_random.string('x', 20) from dual connect by level <= 50000;
扩展表空间,主要有以下几种方式
1.增加数据文件
alter tablespace data01 add datafile 'c:\test\data02.dbf' size 50m;
2.增加数据文件的大小
alter tablespace data01 datafile 'c:\test\data01.dbf' resize 50m //注意,数据文件大小不能超过500M
3.设置文件的自增长
alter tablespace data01 'c:\test\data01.dbf' autoextend on next 10m maxsize=500M;
三、故障的处理
移动数据文件，有时,如果你的数据文件所在的磁盘损坏时,该 数据文件将不能再使用,为了能够重新使用,要将这些文件的副本移动其他磁盘,然后恢复
下面以移动数据文件sp01.dbf为例来说明
1 确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='C:\TEST\DATA01.DBF' 注意,一定要大写 得到 DATA001
2 使表空间脱机
确保数据文件的一致性,将表空间转变为offline 的 状态
alter tablespace data001 offline
3 使用命令移动数据文件到指定的目标位置
host move d:\test\sp01.dbf c:\test\sp01.dbf
4 执行alter tablespace 命令
在物理上移动了数据后,还必须执行alter tablesapce 命令对数据库文件进行逻辑修改
alter tablespace sp01 rename datafile 'd:\sp001.dbf' to 'c:\test\sp01.db';
5 使表的空关联机
移动了数据后,为了使用户可以访问表空间,必须将其转变为online状态
alter tablespace data01 online
其他表空间（除了常用的数据表空间外,还有其他类型的表空间）
1 索引表空间
2 undo 表空间
3 临时表空间
4 非标准块的表空间
四、使用JDBC连数据库
1) jar 包 //C:\oracle\product\10.2.0\db_1\jdbc\lib 下的 ojdbc14.jar
2) 连接串的写法,驱动类的写法
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:nm", "sa", "sa");
五、数据完整性
数据完整性:确保数据遵从一定的商业和逻辑规则,在oracle 中,数据完整性可以用约束,触发器,用户程序(存储过程,函数) 三种方工来实现,因为约束易维护,具有良好的性能,所以用的最多。
六、oracle 中的约束
not null //非空
unique //唯一(一个表中只能有一个主键,但是可能有多个 unique 约束)
primary key //主键约束 (不能为null ,不能重复)
foriegn key //外键约束
check //用于强制数据必须满足某种特定条件 比如,说年龄不能负数
例子 商店售货系统
goods (商品表)
-----------------
goodsid
goodsname
unitprice (单价)
cateogry (商品类别)
provider (供应商)
customer (客户表)
-------------------
custmerid
name
address
email
sex
idcard
purchase (购买表)
--------------
customerid
goodsid
nums (购买数量)
使用sql 语句创建上面的表,要有以下要求
1) 每个要有主键(应该有外键的表,要声明外键)
2) 客户的姓名不能为空
3) 商品的单价必须大于0
4) 购买的数量必须在 1-50之间
5) 电子邮件不能重复
6) 客户的性别必须是男/女,默认是男
//商品表 create table goods ( goodsId char(8) primary key, //加主键约束 goodsName varchar2(20), unitprice number(10,2) check(unitprice>0), //加check 约束 cateogry varchar2(20), provider varchar2(20) )
//客户表 create table customer ( custmerid char(8) primary key, //加主键约束 name varchar2(20) not null, //非空约束 address varchar2(200), email varchar2(50) unique, //唯一约束 sex char(2) default '男' check(sex in ('男','女')), idcard char(18) )
//购物表 cfreate table purchase( customerid char(8) references customer(custmerid), //外键约束 goodsid char(8) references goods(goodsId), //外键约束 nums number(5) check(nums between 1 and 50) )
如果建表的时候,没指定约束,则可以在表建好后使用 alter table 添加约束
注意 : 增加 not null 约束的时候,要使用 modify 选项,而增加其他四个约束的时候,要用add
== 商品名称也不能为空
alter table goods modify goodsName not null;
== 身份证号也不能重复
alter table customer add constraint AAAunique unique(idcard);
== 客户的地址 只能是中国,外国,外星
alter table customer add constraint BBBcheck check(address in ('中国','外国','外星'));
==约束的删除
alter table drop constraint 约束名称
注意:删除主键约束的时候,有可能出错
alter table 表名 drop primary key ; //此唯一/主键已被某些外键引用
如果两个表存在主从关系,要正确删除,必须加上 cascade 方弃从方关系
alter table 表名 drop primary key cascade;
==显示约束信息
1) 数据字典视图 user_constraints
select * from user_constraints where table_name='表名' //例如 : select * from user_constraints where table_name='customer'; //注意表名一定要大写
2) 显示约束列
select * from user_cons_columns where constraints_name='约束名'
约束定义的方式: 两种, 列级定义 :定义列的同时定义约束
create table dept( id number(3) constraint pk_dept primary key, ... )
表级定义：定义了所有的列后，再定义约束 注意 not null 只能在列级上定义
create emp( id number(4), name varchar(20), deptid number(4), constraint pk_emp primary key (id), constraint fk_dept foregin key (deptid) references dept(id)); )
七、索引
索引是用于加速数据存取的数据对象, 合理的使用索引,可以大幅度提高效率(降低i/o次数)
索引有很多种
1.管理索引/创建索引
2.单列索引
基于单个列所建的索引
create index 索引名 on 表名 (列名)
create index emp_BBB_index on scott.emp (empno)
附: 主键上建的索引叫 聚簇 索引 (速度非常快)
复合索引
基于两列或多列的索引,在同一张表上,可以有多个索引,但要求列的组合必须不同
create index 索引名 on 表名 (列名,列名 ....)
create index emp_AAA_index on scott.emp (ename,job)
create index emp_AAA_index on scott.emp (ename,deptno)
使用原则
1) 在大表上建索引才有意义
2) 要在where 子句或连接条件中经常用用的列上建立索引
3) 索引的层次不要超过4层
索引的缺点
先天不足
1) 建立索引,要占用大量的空间
2) 更新数据的时候,系统必须有额外的时间来对索引进行更新,以维护数据的一致性
不恰当的索引,反而会将低速度,在如下字段建立索引是不恰当的
1)很少使用或不从不引用的字段
2)逻辑数据,比性别,是或否
其他索引
按数据存诸方式,可以分为 B*树,反向索引,位图索引
按索引列的个数分,可以分为单列索引,复合索引
按索引的唯一 性,可分为唯一索引和非唯一索引
此外,还有函数索引,全局索引,分区索引....
显示索引信息/数据字典视图
dba_indexes 和 user_indexes 等 ,可以显示索引信息 select * from user_indexes //如果想看某个表上的索引 select * from user_indexes where TABLE_NAME ='GOODS' //注意,表名要大写
显示索引列/数据字典视图
user_ind_columns 可以显示索引对应的列的信息 select * from user_ind_columns select table_name,column_name from user_ind_columns where index_name='索引名'
八、视图
视图是基于一个或多个表或视图的逻辑表 ,本身不包含数据
视图是基于的表称为基表(oracle 中的数据对象分5种,表,视图,序列,索引,同意词)
视图是存储在数据字典里的一条select语句
视图的特点
1) 用户可以通过简单的查询或复杂的查询得到结果,把这个结果做为一个可用的数据集合(视图)
2) 维持数据的独立性,视图可以从多个表中取数据
3) 对于相同的数据,可能由不同的查询方式,产生不同的视图
4) 视图不包含数据,安全
创建一个视图
//给 scott 授权,能够创建视图 grant create any view to scott; create or replace view emp_dept_view (name,minsal,maxsal,avgsal) as select d.dname,min(e.sal),max(e.sal),avg(e.sal) from emp e , dept d where e.deptno =d.deptno group by d.dname;
八、触发器
指隐含执行的存诸过程,当定义触发器时,必须要指定触发的事件的触发的操作,常用的触发事件包扩
insert update,delete ,而触发操作实际上就是一个pl/sql块,可以使用create trigger 来建触发器
在oracle中，触发器是一种特殊的子程序，它在插入、更新、删除数据时自动执行。
使用触发器可以实现比较复杂的约束。(PK FK UQ CK DF都是单表约束数据完整性。)
触发器可以分为INSERT触发器、UPDATE触发器、DELETE触发器。INSERT 触发器在对表或视图执行INSERT语句时触发，UPDATE触发器在对表或视图执行UPDATE操作时执行，DELETE触发器在对表或视图执行DELETE操作时执行。在触发器中有两个变量非常重要，它们是NEW和OLD。NEW表示新添加的值，OLD表示原来的值。
触发器的语法如下：
Create [or replace] trigger 触发器名称
{before |after|inserted of} //1 表示触发器是在操作之前还是在之后触发，
{insert|update|delete[of 列1，列2，…]} //2表示触发器的类型
[or {insert |update |delete[of 列1，列2，….]}]
On 表或视图名
[refrencing [new as 别名] [old as 别名]] //3
[for each row] //4表示是行级触发器，
[when (条件)] //5
[declare 变量声明]
Begin
触发器主体
[exception 异常处理]
End [触发器名称]
需要解释的是：
1处表示触发器是在操作之前还是在之后触发，举例若是验证数据的合法性，可在前验证后插入。不过inserted of是针对视图来说的对于视图操作时，触发器的处理代码则替代对视图的操作。
2表示触发器的类型，表示哪一种触发器，并且是在操作那一列时触发的限定条件，操作该列可触发触发器 。
3refrencing：触发器对insert，update，delete操作时，存在新旧数据的问题，新数据采用new对象表示，旧数据采用old对象来表示，在insert中，new表示要插入的数据，此时old没有意义。在delete中，old表示已删除的数据，此时new没有意义。在update中，new表示已更新的新数据，old表示更新前的旧的数据。New和old是记录对象，refrencing子句可以对new和old指定一个别名。
4表示是行级触发器，每次对行操作时，都会触发触发器，处理代码都会执行。如果没有，则只触发一次，处理代码只执行一次。5限制触发器的条件，表示什么时候执行触发器。另外注意的是，触发器和触发语句同在一个屋檐（事务）下，一起提交执行，一起回滚。
create or replace trigger tr_TEST_AAA after insert on t1 for each row begin insert into t2 values (:new.id,:new.name,9000); end;
其他两个触发器update和delete触发器和insert触发器的使用方法方式基本相同，不在赘述。那么使用触发器的好处是什么呢？个人觉得触发器就像是机关或者说是地雷，在满足一些条件的时候，自动的做一些事情，是事情变得简单易处理。例如银行的数据库应该会有很多的触发器，每一步或每一次的数据库操作都会触发触发器，记录相应的操作过程或操作数据，会有大量的操作文档，保证数据的安全和完整性。总之，触发器就是满足一定的条件之后就去执行一些动作，跟if语句有点像，我们想做些什么，只要写好处理语句，再写好触发条件就ok了。
============================================================================
面试题
表空间用于从逻辑上组织数据库的数据,数据库逻辑上是由一个或是多个表空间组成的,通过表空间可以达到以下作用:
1 控制数据库占用的磁盘空间
2 dba 可以将不同的数据类型部署到不同的位置 这样有利于提高io性能,同时利于备分和恢复等管理操作
比如说将索引放到一个单独的表空间里
