Oracle数据库（二）
表空间是数据库逻辑结构的一个重要组件，可以存放各种应用对象，如表、索引。每一个表空间由一个或多个数据文件组成。
表空间分类：
|
类别 |
说明 |
|
永久性表空间 |
一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的 |
|
临时性表空间 |
只用于保存系统中短期活动的数据，如排序数据等 |
|
撤销表空间 |
用来帮助回表未提交的事务数据，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能 |
表空间的目的：
（1）对不同用户分配不同的表空间，对不同的模式对象分配不同的表空间，方便对用户数据的操作，对模式对象管理
（2）可以将不同数据文件创建到不同的磁盘中，有利于管理磁盘空间，有利于提高I/O性能。
创建表空间：
create tablespace tablespace_name
datafile ‘filename’ [ size integer [K|M]]
[autoextend [off|on]];
--datafile指定组成表空间的一个或多个数据文件，当有多个数据文件时使用逗号分隔。
--filename是数据文件的路径和名称。
--size指定文件的大小，用K指定千字节大小，用M指定兆字节大小。
--autoextend子句用来启用或禁用数据文件的自动扩展，设置为on则空间使用完毕会自动扩展，设置为off则很容
易出现表空间剩余容量为0的情况，使数据不能存储到数据库中。
例: create tablespace worktbs--表空间名
datafile ‘d:\orcle\worktbs.dbf’
size 10M autoextend on;
删除表空间：drop tablespace tablespace_name;
修改表空间：
alter database --修改表空间文件大小
datafile ‘F:\oracle\product\10.1.0\oradata\orcl\hello.dbf’
resize 10M;
alter tablespace hello --为表空间hello添加一个数据文件
add datafile 'F:\oracle\product\10.1.0\oradata\orcl\helloAdd.dbf'
size 10M
自定义用户管理
当创建一个新数据库时，系统默认创建一些数据库用户，如Sys、System和Scott等。Sys和System都是Oracle的系统用户，Scott是Oracle数据库的一个示范账户，里面有一些测试样例表。
1.Sys，是Oracle中的一个超级用户。数据库中的所有数据字典和视图都存储在SYS模式中。数据存储了用来管理数据库对象的所有信息。主要用来维护系统信息和管理实例。Sys只能以SYSOPER或SYSDBA角色登录系统。
2.System,是Oracle中默认的系统管理员，它拥有DBA权限。该用户拥有Oracle管理工具使用的内部表和视图。管理Oracel数据库的用户、权限和存储等。不建议在System模式中创建用户表。System不能以SYSOPER或SYSDBA角色登录系统，只能以默认方式登录。
3.Scott，用户是一个示范用户包含4个示范表，其中一个是Emp表。使用USERS表空间存储模式对象。每个用户都有一个默认表空间和一个临时表空间。Oracle将USERS设为默认表空间，将TEMP设为临时表空间。
创建用户：
create user user_name
identified by password
[default tablespace tablespace_name]
[temporary tablespace tablespace_name]
--user_name是用户名，用户名必须是一个标识符。
--password是用户口令，必须是一个标识符，且不区分大小写。
--default或temporary tablespace 为用户确定默认表空间或临时表空间。如果没有确定default表空间，则系
统默认把users表空间当作默认表空间。
例: create user martin--用户名
identified by martinpwd--口令
default tablespace worktbs--默认表空间
temporary tablespace temp;--临时表空间
修改口令(密码)：
alter user martin --要修改的用户martin
identified by mpwd;--新口令
删除用户，drop user命令可以用于删除用户，但当用户拥有模式对象时则无法删除用户，而必须使用cascade选项删除用户和用户模式对象。
drop user martin;--没有模式对象的情况
drop user martin cascade;--用模式对象的情况
数据库权限管理
1.系统权限，是指被授权用户是否可以连接到数据库上及在数据库中可以进行哪些系统操作;系统权限是在数据库中执行某种系统级别的操作，或者针对某一类的对象执行某种操作的权利。
常用的系统权限：
create session :连接到数据库。
create table :创建表。
create view :创建视图。
create sequence:创建序列。
create public synonym:创建公有同义词
2.对象权限，是指用户对数据库中具体对象所拥有的权限。对象权限是针对某个特定的模式对象执行操作的权利。只能针对样式对象来设置和管理对象权限，如数据库中的表、视图、序列、存储对程、存储函数等。
Oracle数据库用户有两种途径获得权限：
1）管理员直接向用户授予权限。
2）管理员将权限授予角色，然后再将角色授予给一个用户。通常使用角色向用户授予权限。
Oracle中常用的系统预定义角色如下：
connect :需要连接上数据库的用户，特别是那些不需要创建表的用户，通常授予该角色。
resource :更为可靠和正式的库用户可以授予该角色，可以创建表、触发器、过程等。
dba :数据库管理员角色，拥有管理数据库的最高权限。一个具有DBA角色的用户可以给其他用户、角色授权，可以撤销任何别的用户甚至别的dba权限。所以不要轻易授予该权限。
（1）授予权限语法：grant 权限|角色 to 用户名;
（2）撤销权限语法：revoke 权限|角色 from 用户名;
例: grant connect,resource to martin;--授予connnect和resource两个角色
revoke connect,resource from martin;--撤销connect和resource两个角色
grant select on scott.emp to martin; --允许用户查看scott模式下emp表的记录
授予表的访问权限：
例：grant select on dept to public; --授予所有连接数据库的用户都可以访问这个表的权限
数据库用户安全设计原则：
1）数据库用户权限授权按照最小分配原则。
2）数据库用户要分为管理、应用、维护、备份四类用户。
3）不允许使用Sys和System用户建立数据库应用对象。
4）禁止grant dba to user;--授予dba角色
序列(sequence)是用来生成唯一、连续的速递的数据库对象。序列通常用来自动生成主键或唯一键的值，可以是升序，也可以是降序。
创建序列：
create sequence sequence_name
[start with integer]
[increment by integer]
[maxvalue integer|nomaxvalue]
[minvalue integer|nominvalue]
[cycle|nocycle]
[cache integer|nocache];
--start with :指定要生成的第一个序列号。对于升序，其默认值为序列最小值。对于降序，其默认值为序列最大值
--increment by :用于指定序列号之间的间隔，默认值为1。如果n为正值，则是升序序列。如果n为负值，则是降
序序列。
--maxvalue :指定序列可以生成的最大值。
--nomaxvalue :Oracle将升序序列的最大值设为10的27次幂，将降序序列的最大值设为-1。 这是默认选项。
--minvalue :指定序列的最小值。必须小于或等于start with的值，并且必须小于maxvalue。
--nominvalue :Oracle将升序序列的最小值设为1,将降序序列的最小值设为-10的26次幂。这是默认选项。
--cycle :指定序列在达到最大值或最小值后，将继续从头开始生成值。
--nocycle :指定序列在达到最大值或最小值后，将不能再继续生成值。这是默认值。
--cache :使用cache选项可以预先分配一组序列号，并将其保存在内存。当用完缓存中的所有序列号时，Oracle将
生成另一组数值，并将其保留在缓存中。
--nocache :使用nocache选项，则不会为加快访问速度而预先分配序列号。如果在创建序列时忽略了cache和
nocache选项，Oracle将默认缓存20个序列号。
例: create sequence myseq
start with 10 --从10开始
increment by 1 --间隔1
maxvalue 2000 --最大值2000
nocycle
cache 30; --缓存30个序列号
访问序列：
nextval:创建序列后第一次使用nextval时，将返回该序列的初始值。以后将使用incrememt by 子句来增加序
列值，并返回这个新值。
currval:返回序列的当前值，即最后一次用nextval时返回的值。当序列是新建的，没有用过。则查询会报错
例: insert into toys(toyid,toyname,toyprice)
values (myseq.nextval,’TWENTY’,25);
select myseq.currval from dual;
更改序列:
alter sequence 命令用于修改序列的定义。如果执行下列操作，则会修改序列：
设置或删除minvalue或maxvalue;
修改增量值;
修缓存中序列号的数目;
语法：
alter sequence [schema.]sequence_name
[increment by integer]
[maxvalue integer|nomaxvalue]
[minvalu integer|nominvalue]
[cycle|nocycle]
[cache integer|nocache];
-- 不能修改序列的start with参数。注意最小值应小于最大值。
删除序列：
drop sequence [schema.]sequence_name;
如：drop sequence myseq;
sys_guid函数生成32位的唯一编码，可以用来作为主键值。它源自不需要对数据库进行访问的时间戳和机器标识符。
select sys_guid() from dual;//每次都是不一样的
--在一个并行的环境里或者希望避免使用序列的情况下，才选择sys_guid来设置主键值。
同义词
用途：
（1）简化SQL语句。
（2）隐藏对象的名称和所有者。
（3）为分布式数据库的远程对象提供了位置透明性。
（4）提供对对象的公共访问。
分类：
1）私有同义词，只能被当前模式的用户访问。私有同义词名称不可与当前模式的对象名称相同，要在当前模式下创建私有同义词，用户必须拥有create synonym系统权限。如果在其他用户模式下创建私有同义词，必须拥有
create any synonym系统权限。
语法: create [or replace] synonym [schema.]synonym_name
for [schema.]object_name;
--or replace: 表示在同义词存在的情况下替换该同义词。
--object_name: 指定要为之创建同义词的对象名称。
例： create sysnonym sy_emp for a_hr.employee;--创建同义词
select * from sy_emp; --访问同义词
2)公有同义词，可被所有的数据库用户访问。公有同义词可以隐藏数据库对象的所有者和名称，并降低SQL语句的复杂性。创建它用户必须有create public synonym系统权限。
语法: create [or replace] public synonym synonym_name
for [schema.]object_name;
例: --在A_hr模式下创建公有同义词public_sy_emp作为A_hr用户employee表的别名
create public synonym public_sy_emp for employee;
--在A_oe模式下访问公有同义词
select * form public_sy_emp;
注：使用同义词前，要获得同义词对应对象的访问权限。
对与私有同义词不能同名; 对象和公有同义词同名时，数据库优先选择对象作为目标，私有同义词和公有同义词同
名时，数据库优先选择私有同义词作为目标。
删除同义词：要删除同义词，用户必须拥有相应的权限。
drop [public] synonym [schema.]synonym_name;
例：drop synonym A_oe.sy_emp;--删除私有同义词
drop public synonym A_hr.public_sy_emp;--删除公有同义词
此命令只会删除同义词，不会删除对应的对象。
索引
索引是与表关联的可选结构，是一种快速访问数据的途径，可提高数据库性能。当索引键作为查询条件时，该索引将直接
指向包含这些值的得的位置。
一个列只能创建一种索引。
索引分类表
|
物理分类 |
逻辑分类 |
|
分区或非分区索引 |
单列或组合索引 |
|
B树索引(标准索引) |
唯一或非唯一索引 |
|
政常或反向键索引 |
基于函数索引 |
|
位图索引 |
|
1.B树索引，通常也称为标准索引。索引的顶部为根，其中包含揭向索引其中包含指向索引中下一级的项。
语法： create [unique] index index_name on table_name(column_list)
[tablespace tablespace_name];
--unique: 用于指定唯一索引，默认情况下为非唯一索引。
--index_name: 索引名称
--table_name: 表示为之创建索引的表名。
--column_list: 在其上创建索引的列名的列表，可以基于多列创建索引，列之间用逗号分割。
--tablespace_naem: 为索引指定表空间。
2.唯一索引和非唯一索引
唯一索引：定义索引的列中任何两行者没有重复值。唯一索引中的索引关键字只能指向表中的一行。在创建主键统束和创建唯一约束时者会创建一个与之对应的唯一索引。
非唯一索引：单个关键字可以有多个与其关联的行。
例：create unique index index_unique_grade on salgrade(grade);
3.反向键索引，与常规B树索引相反，反向键索引在保存列顺序的同时反转索引列的字节。反向键索引通过反转索引键的数据值来实现。优点：对于连续增长的索引列，反转索引列，可以将索引数据分散在多个索引块间，减少I/O瓶颈的发生。
反向键索引通常建立在一此值连续增长的列上，如系统生成的员工编号，但不能执行范围搜索。
例：create index index_reverse_empno on employee(empno) reverse;
4.位图索引，优点在于它最适合低基数列（即该列的值是有限的，理论上不会是无穷大)。
优点：
1）对于大批即时查询，可以减少响应时间。
2）相比其他索引技术，点用空间明显减少。
3）即使在配置很低的终端硬件上，也能获得显著的性能。
例：create bitmap index index_bit_job on employee(job);
5.其他索引
组合索引：在表内多列上创建。索引中的列不必与表的中碰钉子顺序一致，也不必相互邻接，最多包含32列。
创建组合索引时，应将唯一性高的列放在第一位。
基于函数的索引：若使用的函数或表达式涉及正在建立索引的表中的一列或多列，则创建基于函数的索引。可以将基于函数的索引列创建为B树或位图索引。
例：--在员工employee表中，为员工名称ename列创建大写函数索引
create index index_ename on employee(upper(ename));
创建索引原则：
1）频繁搜索的列可以作为索引。
2）经常排序、分组的列。
3）经常用作连接的列（主键/外键）。
4）将索引放在一个单独的表空间中，不要放在有回退段、临时段和表的表空间中。
5）对大型索引而言，考虑使用nologging子句创建大型索引。
6）根据业务发生的频率，定期重新生成或重新组织索引，并进行碎片整理。
7）仅包含几个不同值的列不可以创建为B树索引，根据需要创建位图索引。
8）不要在仅包含几行的表中创建索引。
删除索引
1）使用drop index语句用于删除索引
drop index index_bit_job;--在Oracle中索引名在用户账户中是唯一的，删除时不需要指定表名。
2）何时应删除索引：
（1）应用程序不再需要索引。
（2）执行批量加载前删除索引。大量加载数据前删除索引，加载后再重建索引有以下好处：①提高加载性能;
②更有效地使用索引空间。
（3）索引国损坏。
重建索引
1.alter index....rebuild 语句用于重建索引
alter index index_reverse_empno rebuild noreverse;
2.何时应重建索引
（1）用户表被移动到新的表空间后，表上的索引不是自动转移，需将索引移到揭定表空间。
alter index index_name rebuild tablespace tablespace_name;
（2）索引中包含很多已删除的项。对表进行频繁删除，造成索引空间浪费，可以重建索引。
（3）需将现有正常索引转换成反向键索引。
表分区
Oracle允许用户把一个表中的所有行分为几个部分，并将这些部分存储在不同的位置。被分区的表称为分区表，分成的每一个部分称为一个分区。
优点：
1）改善表的查询性能，在对表进行分区后，用户执行SQL查询时可以只访问表中的特定分区而非整个表。
2）表更容易管理，因为分区表的数据存储在多个部分中，按分区加载和删除数据比在表中加载和删除更容易。
3）便于备份和恢复，可以独立地备份和恢复每个分区。
4）提高数据安全性，将不同的分区分布在不同的磁盘，可以减小民有分区的数据同时损坏的可能性。
分区条件：
1）数据量大于2GB。
2）已有的和新添加的数据有明显的界限划分。
3）要分区的表不能具有long和long raw数据类型的列。
分区表的分类：范围分区、列表分区、散列分区、复合分区、间隔分区和、虚拟列分区等。
1.范围分区(range)是应用范围比较广的表分区方式，它以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。
可以在分区后加上(tablespace tablespace_name)来指定该分区在哪个表空间中。这样可以提高查询性能和安全性。
一般创建范围分区时，都会将最后一个分区设置为maxvalue，使其他落入此分区。一旦需要某一数据时，可以利用拆分分区的技术将需要的从最后一个分区分离出去，单独形成一个分区，如果没有创建足够大的分区，插入
的数据超出范围就会报错。如果插入的数据就是分区键上的值，则该数据落入下一分区。在按时间分区时，如果某些记录暂时无法预测范围，则可以创建maxvalue分区，所有不在指定范内的记录都会被存储到maxvalue所在的分区中。
例:
create table sales1 ( sales_id number, product_id varchar2(5), sales_date date not null, .... ) partition by range (sales_date) ( partition p1 values less than (to_date(‘2013-04-1’,’yyyy-mm-dd’)), partition p2 values less than (to_date(‘2013-07-1’,’yyyy-mm-dd’)), partition p3 values less than (to_date(‘2013-10-1’,’yyyy-mm-dd’)), partition p4 values less than (to_date(‘2014-01-1’,’yyyy-mm-dd’)), partition p5 values less than (maxvalue) );
--查看第三季度的数据：select * from sales1 partition(p3);
--删除第三季度的数据：delete from sales1 partition(p3);
2.间隔分区(Interval)是Oracle 11g版本新引入的分区方法，是范围分区的一种增强功能，可以实现范围分区的自
动化。优点为，在不需要创建表时就将所有分区划分清楚。间隔分区随着数据的增加合划分更多的分区，并自动自
动创建新的分区。
例: --创建间隔分区表
create table sales2 ( sales_id number, product_id varchar2(5), sales_date date not null, .... ) partition by range(sales_date) interval(numtoyminterval(3,’MONTH’)) (partition p1 values less than (to_date(‘2013-05-1’,’yyyy/mm/dd’)));
--插入数据
insert into sales2 values (1,’a’,to_date(‘2013-08-1’),’1’);
--获得分区情况
select table_name,parttion_name
from user_tab_partitions
where table_name=uppper(‘sales2’);
--查询输出结果，系统自动根据输入数据情况创建新分区“SYS_P82”
TABLE_NAME PARTITION_NAME
------------------------------
SALES2 P1
SALES2 SYS_P82
--查询分区数据
select * from sales2 partition(sys_p82);
1）只需创建第一个开始分区。
2）interval(numtoyminterval(3,’MONTH’))语句中，interval代表“间隔”，即按照后面括号中的定义间隔添加分区。
3）numtoyminterval(3,’MONTH’)表示每3个月为一个分区。
numtoyminterval(n, ‘interval_unit’)函数用于将n转换成interval_unit所指定的值。
interval_unit的值可以为YEAR或MONTH。
与该类型相关的函数还有numtodsinterval(n, ‘interval_unit’)，用于将n转换成interval_unit所指
定的值。这里的interval_unit的值可以设为DAY，HOUR，MINUTE，SECOND。但不支持YEAR和MONTH
4）系统会根据自动创建分区。
经验：可以利用间隔分区将开始创建时没有分区的表创建为新的间隔分区表。代码如下：
create table sales3 partition by range(sales_date) interval(numtoyminterval(3,’MONTH’)) partition p1 values less than (to_date(‘2013-04-1’,’yyyy/mm/dd’))) as select * from sales; --sales表为已经创建的表
3.列表分区 列的值可以枚举的
如:
create table sales( sid number, sarea varchar2(10), total number ) partition by list (sarea)( partition part1 values('北京') tablespace space1, partition part2 values('上海') tablespace space2, partition part3 values('广东') tablespace space3 );
4.散列分区，服务器自动给每一行给一个hash值，然后自动分区。只需要指定有哪些分区就可以了
create table employee( empno number, ename varchar2(10) ) partition by hash(ename)( artition part1 tablespace space1, partition part2 tablespace space2, partition part3 tablespace space3 ); select * from employee partition (part1); select * from employee partition (part2); select * from employee partition (part3);
