目 录CONTENT

文章目录

Oracle 事务和常用数据库对象

简中仙
2020-05-21 / 0 评论 / 0 点赞 / 52 阅读 / 0 字 / 正在检测是否收录...
温馨提示:
本文最后更新于2024-02-29,若内容或图片失效,请留言反馈。 本文如有错误或者侵权的地方,欢迎您批评指正!

一、事务

1、事务的含义

事务就是业务上的一个逻辑单元,它能够保证其中对数据所有的操作要么全部成功,要么全部失败。

事务开始于一条可执行的SQL语句,继续执行事务主体,然后结束于以下的任意一种情况。

  • 显式提交(commit):当事务遇到commit指令时,将结束事务并永久保存所有更改的数据。

  • 显式回滚(rollback):当事务遇到rollback指令时,也将结束事务的执行,但是此时它回滚所有更改的数据到事务开始时的原始值,即取消更改。

  • DDL语句;一旦用户执行了DDL(DataDefinitionLanguage,数据定义语言,如CREATE,DROP等)语句,则之前所有的DML(Data Manipulation Language,数据操作语言)操作将作为一个事务提交,这种提交称为隐式提交。

  • 正常结束程序:如果Oracle数据库应用程序正常结束,如使用SQL*Plus工具更改了数据,而正常退出该程序(输入“exit"),则Oracle自动提交事务。

  • 非正常地结束程序:当程序崩溃或意外中止时,所有数据更改都被回滚,这种回滚称为隐式回滚。

2、事务的特点

事务的四个特性,即原子性(Atomicity),一致性(Consistency)、隔离性(lsolation)和持久性(Durability),简写为ACID特性

1、原子性:以转账操作为例,转出账户余额减少和转入账户余额增加是两个DML语句,但是必须作为一个不可分割的完整操作,要么同时成功,要么同时失败,只转出而没有转入显然是不可接受的。

2、一致性:无论是在事务前、事务中,还是在事务后,数据库始终处于一致的状态。

3、隔离性:在某个时间段,肯定有很多人在转账,每个人的转账都是在自己的事务中,所以在一个数据库中,会有很多事务同时存在。虽然同时存在很多事务,但是事务之间不会相互影响。

4、持久性:如果事务提交成功,则数据修改永远生效;如果是回滚,则数据完全没有被修改就相当于没有这件事情发生。

3、事务控制

1、使用commit 和rollback实现事务控制

commit:提交事务,即把事务中对数据库的修改进行永久保存

rollback:回滚事务,即取消对数据库所做的任何修改

SQL> insert into sales values('zhangsan','nan','haidian',90);        # 在sales表中插入数据

已创建 1 行。

SQL> col NAME for a10
SQL> col SEX for a10
SQL> col ADDRESS for a30
SQL> select * from sales;        # 查询sales表

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90

已创建 1 行。

SQL> commit;        # 提交事务

提交完成。

SQL> select * from sales;

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90

SQL> insert into sales values('zhoulan','nv','shenyang',88);        # 在sales表中插入数据

已创建 1 行。

SQL> rollback;        # 回滚

回退已完成。

SQL> select * from sales;        # 查询表发现数据并没有

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90

2、使用autocommit实现事务的自动提交

Oracle提供了一种自动提交DML操作的方式,这样一旦用户执行了DML操作,如UPDATE、DELETE等,数据就会自动提交。

SQL> set autocommit on;        # 自动提交
SQL> insert into sales values('lisi','nan','changping',70);

已创建 1 行。

提交完成。
SQL> select * from sales;

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90
lisi	   nan	      changping 			     70

SQL> rollback;

回退已完成。

SQL> select * from sales;

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90
lisi	   nan	      changping 			     70

3、程序异常退出对事务的影响

在事务执行过程中,程序会发生异常 如实例崩毁 断开连接等 此时事务结束并回滚所有的数据更改。

4、程序正常退出对事务的影响

在程序正常退出时,将提交所有的数据更改信息。

二、索引

1、索引的含义

索引是Oracle的一个对象,是与表关联的可选结构,提供了一种快速访问数据的途径,提高了数据库的检索性能。索引使数据库程序无须对整个表进行扫描,就可以在其中找到所需要的数据。

索引的特点如下:

  • 适当地使用索引可以提高查询速度。
  • 可以对表的一列或多列建立索引。
  • 建立索引的数量没有限制。
  • 索引需要磁盘存储,可以指定表空间,由Oracle自动维护。
  • 索引对用户透明,检索时是否使用索引是由Oracle决定的。

Oracle的数据库管理系统在访问数据时使用以下两种访问方法。

  • 全表扫描。
  • 使用索引。

当没有索引或者选择不使用索引时就使用全表扫描的方式。

2、索引的分类

1、B树索引

索引的顶部为根,其中包含指向下一级索引的项。下一级为分支块,分支块又指向索引中下一级的块。最低一级的块称为叶节点,其中包含指向表数据行的索引项。叶节点为双向链接,有助于按关键字值的升序和降序扫描索引。

SQL> create index sales_name_index on sales(address);        # 对sales表的address列创建B树索引sales_address_index

索引已创建。

Oracle创建普通索引时,如果没有说明类型那么就是B树索引。

2、唯一索引和非唯一索引

唯—索引:保证定义索引的列中没有重复值。唯—索引中的索引关键字只能指向表中的—行 。

非唯—索引:定义索引的列中可以有重复值

SQL> create unique index sales_name_unique_index on sales(name);        # 对sales表的name列创建唯一索引sales_name_unique_index

索引已创建。

3、反向键索引

与常规B树索引相反,反向键索引在保持列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值,使索引的修改平均分布到整个索引树上,它主要应用于多个实例可同时访问同一个数据库的场景中(如RAC)。而在常规的B树索引情况下,由于两个索引在索引树中的位置相近且处于同一个索引块中,所以多个实例同时更新时会发生冲突,从而导致I/O访问上的瓶颈。

反向键索引通常建立在一些值连续增长的列上。注意,反向键索引不能用于执行范围搜索。

SQL> create index sales_reverse_index on sales(results) reverse;        # 重点是reverse

索引已创建。

4、位图索引

位图索引适用于低基数的列,即该列的值是有限的几个。

位图索引具有下列优点:

  • 相对B树索引而言,基于位图索引列的查询可以减少响应时间。
  • 相比其他索引技术,位图索引占用空间明显减少。

位图索引不应当在频繁发生INSERT、UPDATE、DELETE操作的表上使用,这是因为单个位图索引项指向表的很多数据行,当修改索引项时需要将其指向的数据行全部锁定,这会严重降低数据库的并发处理能力。位图索引适合用于数据仓库和决策支持系统中。

SQL> create bitmap index sales_bit_index on sales(sex);        # 重点是bitmap

索引已创建。

5、其他索引

Oracle还支持组合索引和基于函数的索引。

1、组合索引:在表内多列上创建。索引中的列不必与表中的列顺序一致,也不必相互邻接。组合索引最多包含32列。

2、基于函数的索引:若使用的函数或表达式涉及正在建立索引的表中的一列或多列,则可创建基于函数的索引。基于函数的索引可创建为B树索引或位图索引。

基于函数的索引

SQL> create index sales_address_index on sales(upper(address));        # 在sales中为address列创建大写函数索引

索引已创建。

SQL> select * from sales where UPPER(address)='HAIDIAN';

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90

3、创建索引的原则

  • 频繁搜索的列可以作为索引。
  • 经常排序分组的列可以作为索引。
  • 经常用作连接的列(主键/外键)可以作为索引。
  • 将索引放在—个单独的表空间中 不要放在有回退段、临时段和表的表空间中。
  • 对大型索引而言,考虑使用NOLOGGING子句创建。
  • 根据业务数据发生的频率定期重新生成或重新组织索引以进行碎片整理。

不要在下面情况创建索引

  • 仅包含几个不同值的列。
  • 表中仅包含几行。

4、查看索引

SQL> col table_name for a20
SQL> col index_name for a30
SQL> select table_name,index_name from user_indexes where table_name='SALES';        # 查找sales表的索引

TABLE_NAME	     INDEX_NAME
-------------------- ------------------------------
SALES		     SALES_NAME_INDEX        		# B树索引
SALES		     SALES_NAME_UNIQUE_INDEX        # 唯一索引
SALES		     SALES_REVERSE_INDEX        	# 反向键索引
SALES		     SALES_BIT_INDEX        		# 位图索引
SALES		     SALES_ADDRESS_INDEX        	# 基于函数的索引

4、维护索引

1、重建索引

SQL> alter index sales_address_index rebuild;

索引已更改。

2、合并索引碎片

SQL> alter index sales_address_index coalesce;

索引已更改。

5、删除索引

SQL> drop index sales_address_index;

三、视图

视图(View)是一个虚表,不占用物理空间,因为视图本身的定义语句存储在数据字典中。视图中的数据是从一个或多个实际表中获得的。那些用于产生视图的表叫作视图的基表。一个视图也可以在另一个视图中产生。

1、视图的作用

通过限制对表中预定的—组行和列的访间,视图提供了一种额外的安全性。

视图隐藏了数据的复杂性。

视图简化了用户的命令。

视图将应用程序与基表定义的修改隔离开来。

视图通过重命名列从另— 个角度(相对于基表)提供了数据而不影响基表,增强了数据库的灵活性 。

2、创建视图(普通)

创建视图的语法

CREATE [OR REPLACE] [FORCE I NOFORCE] VIEW view_name
[ (alias [, alias] ...)]
AS select_statement
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY);
  • OR REPLACE:如果视图已存在,此选项将重新创建该视图。
  • FORCE:如果使用此关键字,则无论基表是否存在,都将创建视图。
  • NOFORCE:这是默认值。如果使用此关键字,则仅当基表存在时才创建视图。
  • view_name:要创建的视图的名称。
  • alias:指定由视图的查询所选择的表达式或列的别名。别名的数目必须与视图所选择的表
  • 达式的数目相匹配。
  • select_statement:SELECT 语句 。
  • WITHCHECK OPTION:此选项指定只能插入或更新视图可以访问的行。
  • WITH READ ONLY:此选项保证不能在此视图上执行任何修改操作。
SQL> create or replace view salesnv as select * from sales where sex='nv' with check option;        # 创建性别为女的视图

视图已创建。

SQL> insert into salesnv values('tom','nv','tianjing',100);        # 注意:条件为女才可以加入数据并同步到原表

已创建 1 行。

提交完成。

SQL> select * from sales;

NAME	   SEX	      ADDRESS				RESULTS
---------- ---------- ------------------------------ ----------
zhangsan   nan	      haidian				     90
lisi	   nan	      changping 			     70
tom	   nv	      tianjing				    100

3、DML 语句和复杂视图

此处的DML语句是指用于修改数据的INSERT、DELETE和UPDATE语句。因为视图是一个虚拟的表,所以这些语句也可与视图一同使用。一般情况下不通过视图修改数据,而是直接修改基表,因为这样条理更清晰。相对于表,在视图上使用DML语句有如下限制:

  • DML语句只能修改视图中的一个基表。
  • 如果对记录的修改违反了基表的约束条件,则将无法更新视图。
  • 如果创建的视图包含连接运算符、DISTINCT运算符、集合运算符、聚合函数和GROUPBY子句,则将无法更新视图。
  • 如果创建的视图包含伪列或表达式,则将无法更新视图。

Oracle视图包含简单视图和复杂视图:

  • 简单视图基于单个基表,不包括函数和分组函数,因此可以在此视图中进行INSERT、UPDATE,DELETE操作。这些操作实际上是在基表中插入、更新和删除行。
  • 复杂视图从多个表提取数据,包括函数和分组函数,复杂视图不一定能进行DML操作。

4、查询视图

SQL> select view_name from user_views;

5、删除视图

SQL> drop view salesnv;

视图已删除。

6、物化视图

物化视图是和普通视图相对应的。在Oracle使用普通视图时,它会重复执行创建视图的所有SQL语句,如果这样的SQL语句含有多张表的连接或者ORDERBY子句,而且表数据量很大,则会非常耗时,效率非常低下。为了解决这个问题,Oracle提出了物化视图的概念。

1、物化视图的含义

简单地讲,物化视图就是具有物理存储的特殊视图,占据物理空间,就像表一样。物化视图是基于表创建的。它需要和源表进行同步,不断地刷新物化视图中的数据。

物化视图中有两个重要概念:查询重写和物化视图的同步。

1、查询重写:对SQL语句进行重写。当用户使用SQL语句对基表进行查询时,如果已经建立了基于这些基表的物化视图,Oracle将自动计算和使用物化视图来完成查询。在某些情况下可以节约查询时间,减少系统I/O。Oracle的这种查询优化技术称为查询重写。参数query_rewrite_enabled决定是否使用重写查询。在创建物化视图时需要使用enable query rewrite指令来启动查询重写功能,通过show指令可以查看该参数的值,具体操作如下:

SQL> col name for a15
SQL> col type for a10
SQL> col value for a35
SQL> show parameter query rewrite enabled;        # 查询重写

NAME				     TYPE	VALUE
------------------------------------ ---------- ------------------------------
inmemory_query			     string	ENABLE
query_rewrite_enabled		     string	TRUE
query_rewrite_integrity 	     string	enforced

2、物化视图的同步:物化视图是基于表创建的,所以当基表变化时,需要同步数据以更新物化视图中的数据,保持物化视图中的数据和基表数据的一致性。Oracle提供了两种物化视图的同步方式,决定何时进行同步,即ONCOMMIT方式和ONDEMAND方式。

  • ON COMMIT指物化视图在对基表的DML操作事务提交的同时进行刷新。
  • ON DEMAND指物化视图在用户需要的时候进行刷新,可以手工通过DBMS_MVEW.REFRESH等方法来进行刷新,也可以通过JOB指令定时进行刷新。

选择刷新方式后,还需要选择一种刷新类型,刷新类型指刷新时基表与物化视图实现数据同步的方式,Oracle提供了以下四种刷新类型。

  • COMPLETE:对整个物化视图进行完全的刷新。
  • FAST:采用增量刷新,只刷新自上次刷新以后进行的修改。
  • AFORCE:Oracle在刷新时会判断是否可以进行快速刷新,如果可以则采用FAST方式,否则采用COMPLETE方式。
  • NEVER:物化视图不进行任何刷新。

默认值是FORCE刷新类型。

2、创建物化视图

1、创建物化视图的前提条件

具备创建物化视图的权限、query rewrite的权限,以及对创建物化视图所涉及的表的访问权限和创建表的权限。

SQL> create  user c##scott identified by 123456 default tablespace users
  2  temporary tablespace temp quota unlimited on users;

用户已创建。

SQL> grant connect,resource to c##scott;        # 具备基本权限

授权成功。

SQL> grant create materialized view to c##scott;        # 具备创建物化视图的权限

授权成功。

SQL> grant query rewrite to c##scott;        # 具备物化视图写入的权限

授权成功。

SQL> grant create any table to c##scott;        # 具备创建表的权限

授权成功。

SQL> grant select any table to c##scott;        # 具备查询表的权限

授权成功。

2、创建物化视图日志

SQL> conn c##scott/123456;
已连接。
SQL> select table_name from user_all_tables;        # 查询表

TABLE_NAME
--------------------------------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE

SQL> create table test(id int,name varchar(10),sex varchar(5));        # 创建表

表已创建。

SQL> create materialized view log on test with rowid;        # 开启实体化视图日志功能

实体化视图日志已创建。

3、创建物化视图

SQL> create materialized view mt_test
  2  build immediate        					# 立即创建物化视图
  3  refresh fast        						# 刷新数据的类型选择fast类型
  4  on commit        							# 在基表有更新时提交并立即更新物化视图
  5  with rowid         
  6  as        									# 定义后面的查询语句
  7  select * from test where sex='nv';        	# 如果没有启用查询重写功能添加enable query rewrite
实体化视图已创建。

SQL> insert into test values ('1','zs','nan');        # 插入数据

已创建 1 行。

SQL> insert into test values ('2','ls','nv');        # 插入数据

已创建 1 行。

SQL> commit;        # 提交事务

提交完成。

SQL> select * from mt_test;

	ID NAME 			  SEX
---------- ------------------------------ ---------------
	 2 ls				  nv

3、删除物化视图

SQL> drop materialized view mt_test;

四、序列

序列是用来生成唯一的、连续的整数类型的数据库对象。序列通常用来自动生成主键或唯一键的值。序列可以按升序排列,也可以按降序排列。

1、创建和访问序列

1、创建序列

CREATE SEQUENCE sequence_name
	[START WITH integer]
	[INCREMENT BY integer]
	[MAXVALUE integer|NOMAXVALUEI
	[MINVALUE integer I NOMINVALUE]
	[CYCLE|NOCYCLE]
	[CACHE integer|NOCACHE];
  • START WITH:指定要生成的第一个序列号。对于升序序列,其默认值为序列的最小值;对于降序序列,其默认值为序列的最大值。
  • INCREMENT BY:用于指定序列号之间的间隔,其默认值为1。如果n为正值,则生成的序列将按升序排列:如果n为负值,则生成的序列将按降序排列。
  • MAXVALUE:指定序列可以生成的最大值。MAXVALUE必须大于或等于START WITH,且必须大于MINVALUE。
  • NOMAXVALUE:如果指定了NOMAXVALUE,Oracle则将升序序列的最大值设为10^27,或将降序序列的最大值设为-1。这是默认选项。
  • MINVALUE:指定序列的最小值。MINVALUE必须小于或等于STARTWITH的值,并且必须小于MAXVALUE。
  • NOMNVALUE:如果指定了NOMINVALUE,Oracle则将升序序列的最小值设为1,或将降序序列的最小值设为-10^26。这是默认选项。
  • CYCLE:指定序列在达到最大值或最小值后,将继续从头开始生成值,
  • NOCYCLE:指定序列在达到最大值或最小值后,将不能再继续生成值。这是默认选项。
  • CACHE:使用CACHE选项可以预先分配一组序列号,并将其保留在内存中,这样可以更快地访问序列号。当用完缓存中的所有序列号时,Oracle将生成另一组数值,并将其保留在缓存中。
  • NOCACHE:使用NOCACHE选项,则不会为加快访问速度而预先分配序列号。如果在创建序列时忽略了CACHE和NOCACHE选项,Oracle将默认缓存20个序列号。
SQL> create sequence sale_seq         
  2  start with 1        	#  指定要生成的第一个序列号
  3  increment by 1      	# 用千指定序列号之间的间隔,其默认值为1。如果n为正值,则生成的序列将按升序排列,如果n为负值,则生成的序列将按降序排列
  4  nomaxvalue        
  5  nocycle;        		# 不会为加快访问速度而预先分配序列号

序列已创建。

2、访问序列

创建了序列之后,可以通过NEXTVAL和CURRVAL伪列来访问该序列的值,还可以从伪列中选择值,但是不能操纵它们的值。下面分别说明NEXTVAL和CURRVAL的作用。

  • NEXTVAL:创建序列后第一次使用NEXTVAL时,将返回该序列的初始值。以后在引用NEXTVAL时,将使用INCREMENT BY子句的值来增加序列值,并返回这个新值。
  • CURRVAL:返回序列的当前值,即最后一次引用NEXTVAL时返回的值。
SQL> create table project(id int,name varchar2(10));        # 创建表project

表已创建。

SQL> insert into project values(sale_seq.nextval,'zhangsan');        # 向project表添加记录

已创建 1 行。

SQL> insert into project values(sale_seq.nextval,'lisi');
已创建 1 行。

SQL> select * from project;

	ID NAME
---------- ------------------------------
	 1 zhangsan
	 2 lisi

SQL> select sale_seq.currval from dual;        # 查看序列的CURRVAL值

   CURRVAL
----------
	 2

2、更改序列

ALTER SEQUENCE命令用于修改序列的定义。如果要进行下列操作,则会修改序列。

  • 设置或删除MINVALUE或MAXVALUE。
  • 修改增量值。
  • 修改缓存中的序列号的数目。

更改序列的语法如下:

ALTER SEQUENCE [schema.]sequence_name
	[INCREMENT BY integer]
	[MAXVALUE integer|NOMAXVALUE]
	[MINVALUE integer|NOMINVALUE]
	[CYCLE|NOCYCLE]
	[CACHE integer|NOCACHE];

例如:

SQL> alter sequence sale_seq
  2    maxvalue 5000        # 修改序列的最大值到5000
  3    cycle;

序列已更改。

3、查看序列

SQL> select sequence_name,increment_by,cache_size from user_sequences where sequence_name='SALE_SEQ';

SEQUENCE_NAME
--------------------------------------------------------------------------------
INCREMENT_BY CACHE_SIZE
------------ ----------
SALE_SEQ
	   1	     20

4、删除序列

SQL> drop sequence SALE_SEQ;

序列已删除。

五、同义词

同义词是对象的一个别名,不占用任何实际的存储空间,只在Oracle的数据字典中保存其定义描述。在使用同义词时,Oracle会将其翻译为对应对象的名称。

1、同义词的通途

1、简化SQL语句

如果用户创建的表的名字很长,可以为这个表创建一个Oracle同义词来简化SQL语句。

2、隐藏对象的名称和所有者

多用户协同开发中,可以屏蔽对象的名称及其持有者。如果没有同义词,当操作其他用户的表时,必须通过“用户名,表名”的形式操作,采用Oracle同义词之后就可以隐蔽用户名了。例如,用户c##tom要访问用户c##scott的emp表,必须使用c##scott,emp来引用,如果为用户创建一个名为emp的同义词代表c##scott.emp,那么c##tom就可以用该同义词如同访问自己的表一样引用c##scott.emp了.

3、为分布式数据库的远程对象提供位置透明性

要完成远程对象的访问,先要了解数据库链接的概念。数据库链接是一个命名的对象,说明一个数据库到另一个数据库的路径,通过其可以实现不同数据库之间的通信。同义词在数据库链接中的作用就是提供位置透明性。

创建数据库链接的语法如下:

CREATE DATABASE LINK 数据库链接名 CONNECT TO 用户名 IDENTIFIED BY 口令 USING 'Oracle链接串';

4、提供对数据库对象的公共访问

公有同义词只是为数据库对象定义了一个公共的别名,其他用户都可以通过这个别名访问,但能否访问成功,还要看是否具有数据库对象的访问权限。

2、同义词的分类

同义词可分为以下两类。

1、私有同义词

私有同义词只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同。要在自身的模式创建私有同义词,用户必须拥有CREATE SYNONYM系统权限。要在其他用户模式创建私有同义词,用户必须拥有CREATE ANY SYNONYM系统权限。

创建私有同义词的语法如下:

CREATE [OR REPLACE] SYNONYM [schema.]synonym_name FOR [schema.] object_name;
  • OR REPLACE:在同义词存在的情况下替换该同义词。
  • synonym_name:要创建的同义词的名称。
  • object_name:指定要为之创建同义词的对象的名称。

创建私有同义词(私有同义词名称不可以与当前模式的对象名相同)

SQL> create synonym sy_t for c##scott.emp@orcl;

同义词已创建。

要在自身的模式创建私有同义词,用户必须拥有create synonym系统权限。要在其他用户模式创建私有同义词,用户必须拥有create any synonym系统权限

SQL> set line 120;
SQL> select * from sy_t;            # 访问当前实例c##scott.emp表

     EMPNO ENAME	   JOB			  MGR HIREDATE		  SAL	    COMM     DEPTNO
---------- --------------- --------------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH           CLERK                 7902 17-12月-80          800                    20
      7499 ALLEN           SALESMAN              7698 20-2月 -81         1600        300         30
      7521 WARD            SALESMAN              7698 22-2月 -81         1250        500         30
      7566 JONES           MANAGER               7839 02-4月 -81         2975                    20
      7654 MARTIN          SALESMAN              7698 28-9月 -81         1250       1400         30
      7698 BLAKE           MANAGER               7839 01-5月 -81         2850                    30
      7782 CLARK           MANAGER               7839 09-6月 -81         2450                    10
      7788 SCOTT           ANALYST               7566 19-4月 -87         3000                    20
      7839 KING            PRESIDENT                  17-11月-81         5000                    10
      7844 TURNER          SALESMAN              7698 08-9月 -81         1500          0         30
      7876 ADAMS           CLERK                 7788 23-5月 -87         1100                    20

     EMPNO ENAME	   JOB			  MGR HIREDATE		  SAL	    COMM     DEPTNO
---------- --------------- --------------- ---------- ------------ ---------- ---------- ----------
      7900 JAMES           CLERK                 7698 03-12月-81          950                    30
      7902 FORD            ANALYST               7566 03-12月-81         3000                    20
      7934 MILLER          CLERK                 7782 23-1月 -82         1300                    10

已选择 14 行。

2、公有同义词

公有同义词可被所有的数据库用户访问。公有同义词可以隐藏基表的身份,降低SQL语句的复杂性。要创建公有同义词,用户必须拥有CREATE PUBLIC SYNONYM系统权限。

创建公有同义词的语法如下:

CREATE [OR REPLACE] PUBLIC SYNONYM synonym_name FOR [schema.] object_name;

创建公有同义词

SQL> create user  c##user1 identified by  123456;

用户已创建。

SQL> grant  connect  to   c##user1;

授权成功。

SQL> grant create public synonym to c##scott;        # 将创建公有同义词权限给scott

授权成功。

SQL> conn c##scott/123456;
已连接。

SQL> grant select on dept to c##user1;        # 将查询dept权限给c##user1

授权成功。

SQL> create public synonym public_sy_dept for dept;        # 创建公有同义词public_sy_dept作为c##scott用户dept表的别名

同义词已创建。

SQL> conn c##user1/123456
已连接。
SQL> select * from public_sy_dept;

    DEPTNO DNAME				      LOC
---------- ------------------------------------------ ---------------------------------------
	10 ACCOUNTING				      NEW YORK
	20 RESEARCH				      DALLAS
	30 SALES				      CHICAGO
	40 OPERATIONS				      BOSTON

3、删除同义词

SQL> drop synonym system.sy_t;            # 删除私有同义词

同义词已删除。

SQL> drop public synonym public_sy_dept;            # 删除公有同义词

同义词已删除。

此命令只删除同义词,不会删除对应的表。

六、分区表

1、分区表的含义

Oracle允许用户把一个表中的所有行分成几部分,并将他们存储在不同的表空间。分成的每部分称为一个分区,被分区的表称为分区表(Partition)。

对于包含大量数据的表来说,分区很有必要,分区表的优点如下:

  • 改善表的查询性能。在对表进行分区后,用户执行SQL查询时可以只访问表中的特定分区而非整个表。
  • 表更容易管理。因为分区表的数据存储在多个部分中,按分区加载和删除数据比在表中加载和删除更容易。
  • 便于备份和恢复。可以独立地备份和恢复每个分区。
  • 提高数据安全性。将不同的分区分布在不同的磁盘,可以减小所有分区的数据同时损坏的可能性。

符合以下条件的表可以建成分区表。

1、数据量大于2GB。

2、已有的数据和新添加的数据有明显的界限划分。

分区表对用户是透明的,即应用程序可以不知道表已被分区,在更新和查询分区表时仍当作普通表来操作,但Oracle优化程序知道表已被分区。

2、分区表的分类

Oracle提供的分区方法有范围分区,列表分区、散列分区、复合分区,间隔一引用分区、TRUNCATE和EXCHANGE分区等。

范围(Range)分区是应用范围比较广的分区表方式。它以列的值的范围作为分区的划分条件,并将记录存放在列值所在的范围分区中。

1、创建表并且分区,以age分区

SQL> conn c##scott/123456
已连接。
SQL> create table student
(
  id number,
  name varchar2(10),
  age number)
partition by range (age)
(
 partition p1 values less than (10),
 partition p2 values less than (20),
 partition p3 values less than (maxvalue)
);

表已创建。

SQL> insert  into student values(1,'t1',8);

已创建 1 行。

SQL> insert  into student values(2,'t2',9);
已创建 1 行。

SQL> insert  into student values(3,'t3',15);
已创建 1 行。

SQL> insert  into student values(4,'t4',18);
已创建 1 行。

SQL> insert  into student values(5,'t5',50);

已创建 1 行。

2、分别查询p1,p2,p3的数据

SQL> select * from student partition(p1);            # 如果表里无数据则会报错

	ID NAME 				 AGE
---------- ------------------------------ ----------
	 1 t1					   8
	 2 t2					   9

SQL> select * from student partition(p2);

	ID NAME 				 AGE
---------- ------------------------------ ----------
	 3 t3					  15
	 4 t4					  18

SQL> select * from student partition(p3);

	ID NAME 				 AGE
---------- ------------------------------ ----------
	 5 t5					  50

3、删除小于10岁的数据

SQL> delete from  student  partition(p1);

已删除 2 行。

3、查看分区情况

SQL> SELECT * FROM user_tab_partitions;
0

评论区