目录

雪月书韵茶香

专心做可以提升自己的事情
学习并拥有更好的技能
成为一个值得交往的人


X

Oracle数据库对象

image.png

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; 

表空间数据作用
表段存放表数据
索引段存放索引数据
临时段排序
回滚段事务读一致性、回滚
sys 查看有哪些表空间
select * from v$tablespace;

查看有哪些数据文件

select * from v$datafile;

5.2Oracle 自带的表空间

Oracle 自带表空间名称作用
SYSTEM存放着数据库中所有的数据字典。是存放 Oracle 数据库必要数据的表空间。不建议在其中存放用户自己的数据对象
SYSAUX该表空间在 Oracle10g 中被引入,是 SYSTEM 表空间的辅助表空间。用来存放 oracle 提供的新功能的模式对象,如:空间数据选项、XMLDB 和中间件
UNDOTBS1用来记录用户数据发生改变的信息
TEMP临时表空间,用来存放排序和查询的临时数据。临时表空间特点是不做备份,没有 redo 日志
USERS存放用户数据对象的表空间
EXAMPLE一个自带的示例表空间

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 表示同时删除表空间对应的所有数据文件;


标题:Oracle数据库对象
作者:shuaibing90
版权声明:本站所有文章除特别声明外,均采用 CC BY-SA 4.0转载请于文章明显位置附上原文出处链接和本声明
地址:https://www.xysycx.cn/articles/2019/10/12/1570851104616.html
欢迎加入博主QQ群点击加入群聊:验证www.xysycx.cn