雪月书韵茶香

Oracle 数据库对象##

1.视图

1.1 什么是视图

• 视图是一种数据库对象，它是由一个或多个数据表通过查询语句建立起来的“虚拟表”。
• 视图并不存放数据，它只存放表的定义。
• 视图创建在表的基础上，也可以在视图的基础上再创建视图。视图不能创建索引。
• 视图中可含有多表联接、集合运算符、DISTINCY 运算符、集合函数、GROUP BY、CONNECT BY 等字句时，视图通常是不能修改

1.2 视图的优点

• 安全，使用视图将用户与基表分开
• 方便，简化用户的 SQL 命令

1.3 创建视图语法

CREATE VIEW VIEW_NAME 
AS 
SELECT * FROM BASE_TABLE 
[WITH READ ONLY]
[WITH CHECK OPTION]

查看当前用户能够使用的所有视图

select * from user_views;

删除视图

drop view VIEW_NAME;

1.4 创建视图语句

创建一个员工视图，其中只显示员工姓名、性别和入职日期

create or replace view empview
as
select ename,sex,hire from emp;

查看视图结构

desc empview;

1.5 向视图添加数据

只能向基于单表的视图添加数据

insert into empview values(20,'coco','f',sysdate);

• emp 表中没有给出数据的字段的值将为 null，这些没有数据的字段必须定义为可为 null
• 基于多表的视图不能添加数据，除非使用替代型触发器
  创建如下视图

create or replace view empview
as
select * from emp where sar>5000;

向该视图添加数据

insert into empview values(21,'cole','m',sysdate,4000,2);

• 查看 emp 表时，可以看到添加的数据，但查询视图却看不到这条记录。这实际是因为创建视图时的 where 子句造成的错觉。所以在创建视图时，加入 with check option 选项

create or replace view empview
as
select * from emp where sar>5000
with check option; 

此时向视图添加数据时，必须满足 sar>5000

1.6 使用视图注意事项

• 基于单表创建的视图可以添加数据，数据将添加到基表中，但添加后，基表数据也要满足表的完整性要求
• 基于多表创建的视图不能添加、修改、删除数据，只能用于查询
• with check option 选项指定在向单表视图添加、修改、删除时，必须满足创建视图时的查询语句的 where 的条件
• read only 选项指定该视图不能添加、修改、删除数据，无论该视图是否基于单表创建

2.索引

2.1 什么是索引

索引是基于表建立的一种数据结构，通过表中的某些字段上建立索引，可以提高系统对表的查询速度
索引表中只保存索引关键字和纪录号，查询时根据索引关键字，可以从索引表中找到对应的纪录号，根据纪录号就可以快速的将纪录指针移到与关键字相对应的纪录上

2.2Oracle 支持的索引类型

1. B 树索引 用一个倒置的树状结构来加快查询表的速度
2. 位图索引 只存在与 oracle 的企业版本中，适合在数据表中的列值重复较多的情况下创建索引

2.2 创建索引语法

--创建不唯一索引
CREATE INDEX  index_name on TABLE_NAME(index_column) 
	[pctfree 0];

--创建唯一索引
CREATE UNIQUE INDEX idx_emp_ename on EMP(empno) 
	[pctfree 0];

--创建位图索引
CREATE bitmap INDEX idx2 on EMP(sex);
 --可能的值少，重复多

2.2.1pctfree 选项

• pctfree 表示在存放索引的每个数据块中，保留多少百分比的空间
• 一个索引对象最终占据着多个数据块，每个数据块如同存放目录的书页，当数据表中的数据在添加、修改或删除的时候，其“目录”也应该做出相应的调整。所以，存放索引的数据块一般会保留一定的空闲空间，用于“调整目录”时使用

2.3 索引的管理

• user_indexes:存放用户所创建的索引信息
• user_ind_columns:存放用户索引的字段信息
• 查询 emp 表的索引信息

select index_name,column_name,column_position from user_ind_columns where table_name=‘emp‘;

• 删除索引

drop index indexname;

3.同义词

3.1 什么是 同义词

同义词是为 oracle 数据库中的对象创建的别名，使该对象的非创建者也可以直接通过该别名来访问

3.2 同义词分类

• 公有同义词：*创建和删除都必须要有相应的权限，创建后可以由任何用户访问。但访问前也必须授权 *
• 私有同义词： *只能由当前用户创建和访问 *

3.2.1 公有同义词

scott 如果需要创建公共同义词，必须由管理员 sys 授予权限

conn sys as sysdba;
grant create public synonym to scott;

之后使用 scott/tiger 登录，创建公共同义词

conn scott/tiger;
create public synonym sc for emp;

授予其他用户访问公共同义词的权限

grant select on sc to tom 

**scott 如果需要删除公共同义词，也必须由管理员 sys 授予权限 **

conn sys as sysdba;
grant drop public synonym to scott;

之后使用 scott 登录，并删除公共同义词

conn scott/tiger;
drop public synonym sc;

3.2.2 私有同义词

私有同义词由当前用户创建，并只能由当前用户才能访问

create synonym sc for emp;

**删除私有同义词使用 **

drop synonym sc;

3.3 同义词管理

3.3.1 与同义词有关的数据字典

• DBA_SYNONYMS:是数据库中的所有同义词的描述
• ALL_SYNONYMS:是数据库中的所有同义词的描述
• User_SYNONYMS:是用户可存取的所有同义词

4.序列

4.1 什么是序列

• 序列就是一个连续的数字生成器，可设置为递增或递减
• 序列第一次被调用的时候将返回一个指定的值，然后根据规则增量增长
• 序列可以是循环的，也可以是连续增加的，直到一个限制值为止

4.2 创建序列

4.2.1 创建序列语法格式

create sequence seqname
increment by 1
start with 1 --不能小于minvalue的值
minvalue 1
maxvalue [nomaxvalue] 100
cycle[nocycle]
cache[nocache] 20

4.2.2 创建一个序列

create sequence seqname
	increment by 1
	start with 3
	minvalue 1
	maxvalue 10
	cycle
	nocache;

如果需要取出序列的值，可以使用以下两个关键字

• currval：表示序列的当前值。刚刚创建的序列没有当前值。必须使用 nextval 后才能获取当前值
• nextval：表示序列的下一个值

####4.3 使用序列
反复执行以下代码后，可以看到序列的值在增加到 10 之后，重新从 1 开始增加

select seqname.nextval from dual;

使用以下语句能查看序列的当前值

select seqname.currval from dual;

在向数据表添加数据时，指定记录的主键值由序列生成

create sequence seq;
create table tb(
	tid integer primary key,
	nam varchar2(20)
);
insert into tb values(seq.nextval,'tom');
insert into tb values(seq.nextval,'jack');
insert into tb values(seq.nextval,'kelly');

4.4 序列的管理

删除一个序列

drop sequence seqname;

4.4.1 序列相关的数据字典

• DBA_SEQUENCE：存放数据库中的所有序列的描述信息
• ALL_SEQUENCE：存放当前用户可存取的所有序列
• USER_SEQUENCES：用户序列的说明

4.5 序列使用注意事项

• 注意刚刚创建的序列不能使用 currval

5.表空间

5.1 什么是表空间

• 一个数据库通常有若干个表空间组成，所有的数据库对象都是存放于表空间中
• 一个应用系统如果使用 oracle 作为数据层，一般不去创建新数据库，因为那样做需要比较大的磁盘开销，而是创建表空间即可

使用以下语句能查看当前数据库有哪些表空间组成

select tablespace_name from user_tablespaces; 

select * from v$tablespace;

查看有哪些数据文件

select * from v$datafile;

5.2Oracle 自带的表空间

5.3 创建表空间

5.3.1 创建表空间语法格式

create [temporary|undo] tablespace
	 [logging|nologging]
	 datafile 'path/name.dbf' size xM reuse
	 [autoextend on next xxxk maxsize xxM|unlimited]
	 [extent management local|dictionary]

temporary|undo 表示创建表空间的类型，不指定时默认为数据表空间
logging|nologging 表示是否记录日志
datafile 指定表空间数据文件的存放路径
autoextend on 设定数据文件的空间增长方式
extent management 设定表空间的管理方式，推荐 local

5.3.2 创建 test 数据表空间

create tablespace test
	nologging
	datafile 'f:/test01.dbf' size 50M reuse
	autoextend on next 512k maxsize 100M
	extent management local;

5.3.3 创建 test 表空间的临时表空间

create temporary tablespace testtemp
	tempfile 'f:/testtemp01.dbf' size 10M
	extent management local;

5.3.4 创建日志表空间

create undo tablespace testundo
	datafile 'f:/testundo.log' size 10M;

5.4 使用表空间

5.4.1 添加数据文件

如果 test 数据表空间的数据文件指定了最大容量 maxsize 的值，且该值不是 unlimited 时，数据文件有可能在使用过程中出现容量不足的情况，此时可以为表空间添加数据文件

alter tablespace test
add datafile 'f:/test02.dbf' size 50M maxsize unlimited; 

5.4.2 修改/删除数据文件

或者修改原有数据文件的最大容量

alter database datafile 'f:/test01.dbf' resize 200M;

如果不再需要某个数据文件，可以删除

alter tablespace test drop datafile 'f:/test02.dbf';

5.5 表空间管理

设置表空间为只读，只读表空间不能写数据，可删除数据

alter tablespace test read only 

恢复表空间为可读写

alter tablespace test read write 

5.6 表空间状态

5.6.1 联机

• 用户可以正常访问此表空间的数据
• 可以将联机状态的表空间设置为只读或可读写

5.6.2 脱机

• 此状态的表空间或数据暂时不可用
• 用于部分的停止数据库、修复数据库文件、改物理文件名、移动物理文件
• 表空间脱机 alter tablespace test offline normal;
• 恢复表空间为联机状态 alter tablesapce test online;

5.6.3 修改表空间名称

`1.表空间脱机：

alter tablespace test offline normal

2.直接重命名数据文件或移动数据文件
3.修改表空间同数据文件的对应关系

alter tablespace test
		rename datafile '原数据文件路径和名称'
		to '新的数据文件路径和名称'

4.表空间联机

alter tablespace test online

5.7 删除表空间

当不再需要某个表空间时，可以删除

drop tablespace test--只删除逻辑名称
	including contents	--删除表空间中的对象(可选)
	and datafiles		--包括删除数据文件(可选)

1.如果没有 including contents and datafiles 选项，将只删除表空间的逻辑名称，并没有删除表空间中的数据对象以及数据文件；

2.including contents 表示删除表空间中的所有数据对象；

3.and datafiles 表示同时删除表空间对应的所有数据文件;