详解：Oracle 数据库空间表、自定义用户、权限管理、序列、同义词、索引

Bertha 。 2024-02-19 21:00 63阅读 0赞

## 一：数据库空间表 ##

表空间是数据库逻辑结构的一个重要组件，可以存放各种应用对象，如表、索引。每一个表空间由一个或多个数据文件组成。

**表空间分类：**  
1.永久性表空间  
一般保存表、视图、过程和索引等的数据。SYSTEM、SYSAUX、USERS、EXAMPLE表空间是默认安装的

2.临时性表空间  
只用于保存系统中短期活动的数据，如排序数据等

3.撤销表空间  
用来帮助回表未提交的事务数据，已提交了的数据在这里是不可以恢复的。一般不需要建临时和撤销表空间，除非把它们转移到其他磁盘中以提高性能

**表空间的目的：**  
（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;--删除公有同义词
    
    此命令只会删除同义词，不会删除对应的对象。

## 六：索引 ##

索引是与表关联的可选结构，是一种快速访问数据的途径，可提高数据库性能。当索引键作为查询条件时，该索引将直接

指向包含这些值的得的位置。

一个列只能创建一种索引。

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）即使在配置很低的终端硬件上，也能获得显著的性能。