oracle同义词和分区表

同义词：

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

1. 同义词的用途

1. 简化sql语句

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

1. 隐藏对象的名称和所有者

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

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

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

1. 提供对数据库对象的公共访问

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

同义词的分类

1. 同义词分为以下两类：私有同义词和公有同义词
2. 私有同义词只能在其模式内访问，且不能与当前模式的对象同名
3. 公有同义词可被所有的数据库用户访问

2-1：私有同义词

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

2）创建私有同义词的语法如下：

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

[OR REPLACE]:在同义词存在的情况下替换该同义词

synonym_name：要创建同义词的名称

object_name:指定要为之创建同义词的对象的名称。

 

例1：在SYSTEM模式下创建私有同义词访问SCOTT模式下EMP表。

1. 以SYSTEM用户身份登录数据库，并访问SCOTT下的EMP表。

由上图可以看出，使用模式名确实实现了查询，但却暴露了emp表的模式信息，使用私有同义词可以避免这个问题。

 

1. 以SYSTEM身份登录数据库,创建同义词。

访问同义词sy_emp，实际访问的是SCOTT的emp表（隐藏了真实的表名，提高了安全性）

例2：访问网络服务名为orclsv的远程数据库中的表scott.emp

（我这里只有一台服务器，所以我就把自己当做远程服务器，效果是一样的）

1）以SYSTEM用户身份登录数据库，创建数据库连接dblink_sw_orcl来连接远程数据库，其中远程数据库用户名为system，密码为pwd123，本地网络服务器名为orcl，最后查询远程数据库中的表emp。

创建私有同义词sy_t作为远程数据库表emp的别名

访问同义词sy_t，对应的是远程数据库中的表。

2-2：公有同义词

公有同义词被所有的数据库访问。公有同义词可以隐藏基表的身份，并降低sql语句的复杂性。要创建公有公有同义词，用户必须拥有create public SYNOYM的系统权限。

例3：在scott模式下对部门表dept创建公有同义词public_sy_dept,目的是使其他用户可以直接访问public_sy_dept。

注意：如果不创建公有同义词，那么其他用户访问scott模式下创建的同义词，一定要加scott前缀，即SCOTT.xxx。如果创建了公有同义词，同义词有了公有属性，那么其他用户都可以使用了。

1. 以system用户身份登录数据库，将创建公有同义词权限给SCOTT用户。

以SCOTT用户身份登录数据库

将查询tmp权限给ydw

创建公有同义词PUBLIC_SY_DEPT作为SCOTT用户dept表的别名

以ydw身份登录数据库

1. 删除同义词

要删除同义词，用户必须拥有相应的权限。

例：删除同义词sy_emp和公有同义词public_sy_dept，可以执行如下语句。

• 分区表

1. 分区表的含义

Oracle允许把一个表重的所有行分成几个部分，并将它们存储在不通的表空间，分成的每一部分成为一个分区，被分区的表成为分区表。

对于包含大量数据的表来说，分区很有用，优点有以下几点：

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

 

复合一下条件的表可以建成分区表：

1. 数据量大于2GB。
2. 已有的数据和新添加的数据有明显的界限划分。

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

 

注意：要分区的表不能具有LONG和LONG RAW数据类型的列。

 

1. 分区表的分类

Oracle提供的分区方法有范围分区、列表分区、散列分区、复合分区、间隔分区和虚拟列分区等。其中间隔分区和虚拟列分区是oracle11g的新特性

 

范围分区案例：

是一种常用的表分区方法，它是oracle引进的第一个分区类型。范围分区用于可以根据某些条件按范围分开的数据。如果数据均匀的分布在所建立的不同的范围内，那么使用范围分区将得到最好的分区效果。范围可以基于顺序数或部分数，范围分区技术通常基于时间（例如月或季度）

1. 创建表并且分区，以age分区。

向表中插入数据

查询P100区的数据

查询p200区的数据

如果向表中插入以下记录，会提示插入的分区关键字未映射到任何分区

按范围分区是，如果某些记录暂时无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在的分区中。

再次插入以下数据

查询

查看所有分区的命令

一般创建范围分区时都会将最后一个分区设置为maxvalue，将其他数据落入此分区，一旦需要时可以利用拆分分区的技术将需要的数据从最后一个分区分离出入，单独形成一个分区，如果没有创建最大的分区，插入的数据查出范围就会报错。如果插入的数据是分区键上的值，则该数据落入下一个分区，例如：插入数据为10就会落入p200分区。

 

1、创建表的同时创建主键约束

（1）无命名

create table student (
 studentid int primary key not null,
 studentname varchar(8),
 age int);

（2）有命名

create table students (
   studentid int ,
   studentname varchar(8),
   age int,
   constraint yy primary key(studentid));

2、删除表中已有的主键约束

（1）有命名

 alter table students drop constraint yy;

（2）无命名

可用 SELECT   *   from   user_cons_columns;

查找表中主键名称得student表中的主键名为SYS_C002715

alter table student drop constraint SYS_C002715;

 

3、向表中添加主键约束

 alter table student add constraint pk_student primary key(studentid); 

 

select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 要查询的表

---------------------