Oracle-数据库对象

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