Oracle表空间

数据库、数据库实例、表空间、用户

完整的Oracle数据库通常由两部分组成：Oracle数据库和数据库实例。

1. 数据库是一系列物理文件的集合（数据文件，控制文件，联机日志，参数文件等）；
2. Oracle数据库实例则是一组Oracle后台进程/线程以及在服务器分配的共享内存区。

在启动Oracle数据库服务器时，实际上是在服务器的内存中创建一个Oracle实例（即在服务器内存中分配共享内存并创建相关的后台内存），然后由这个Oracle数据库实例来访问和控制磁盘中的数据文件。Oracle有一个很大的内存块，称为全局区（SGA）。

1. 数据库

数据库是数据的集合。Oracle是一种数据库管理系统（DBMS），是一种关系型的数据库管理系统（RDBMS）。
通常情况了我们称的“数据库”，并不仅指物理的数据集合，他包含物理数据、数据库管理系统。也即物理数据、内存、操作系统进程的组合体。

我们在安装Oracle数据库时，会让我们选择安装启动数据库（即默认的全局数据库）

oracle1.jpg

启动数据库：也叫全局数据库，是数据库系统的入口，它会内置一些高级权限的用户如SYS，SYSTEM等。我们用这些高级权限账号登陆就可以在数据库实例中创建表空间，用户，表了。

全局数据库名：就是一个数据库的标识，在安装时就要想好，以后一般不修改，修改起来也麻烦，因为数据库一旦安装，数据库名就写进了控制文件，数据库表，很多地方都会用到这个数据库名。

查询当前数据库名：

select name from v$database;

2. 数据库实例

实例是访问Oracle数据库所需的一部分计算机内存和辅助处理后台进程，是由进程和这些进程所使用的内存(SGA)所构成一个集合。

其实就是用来访问和使用数据库的一块进程，它只存在于内存中。就像Java中new出来的实例对象一样。

我们访问Oracle都是访问一个实例，但这个实例如果关联了数据库文件，就是可以访问的，如果没有，就会得到实例不可用的错误。

实例名指的是用于响应某个数据库操作的数据库管理系统的名称，也叫SID。实例名是由参数instance_name决定的。

查询当前数据库实例名：

select instance_name from v$instance;

数据库实例名(instance_name)用于对外部连接。在操作系统中要取得与数据库的联系，必须使用数据库实例名。比如我们作开发，要连接数据库，就得连接数据库实例名：

jdbc:oracle:thin:@localhost:1521:orcl（orcl就为数据库实例名）

一个数据库可以有多个实例，在作数据库服务集群的时候可以用到。

3. 表空间

Oracle数据库是通过表空间来存储物理表的，一个数据库实例可以有N个表空间，一个表空间下可以有N张表。

有了数据库，就可以创建表空间。

表空间(Tablespace)是数据库的逻辑划分，每个数据库至少有一个表空间（称作SYSTEM表空间）。为了便于管理和提高运行效率，可以使用一些附加表空间来划分用户和应用程序。例如：USER表空间供一般用户使用，RBS表空间供回滚段使用。一个表空间只能属于一个数据库。

创建表空间语法：

CREATE TABLESPACE 表空间名称  
DATAFILE          表空间数据文件路径  
SIZE              表空间初始大小  
AUTOEXTEND ON

如：

create tablespace db_test  
datafile 'D:\oracle\product\10.2.0\userdata\db_test.dbf'  
size 50m  
autoextend on;

查看已经创建好的表空间：

select default_tablespace, temporary_tablespace, d.username from  dba_users d

4. 用户

Oracle数据库建好后，要想在数据库里建表，必须为数据库建立用户，并为用户指定表空间。

上面我们建好了数据库和表空间，接下来建用户：

创建新用户：

CREATE USER          用户名  
IDENTIFIED BY        密码  
DEFAULT TABLESPACE   表空间(默认USERS)  
TEMPORARY TABLESPACE 临时表空间(默认TEMP) 

如：

CREATE USER utest  
IDENTIFIED BY utestpwd  
DEFAULT TABLESPACE db_test  
TEMPORARY TABLESPACE temp; 

有了用户，要想使用用户账号管理自己的表空间，还得给它分权限：

GRANT CONNECT TO utest;  
GRANT RESOURCE TO utest;  
GRANT dba TO utest; -- dba为最高级权限，可以创建数据库，表等。

查看数据库用户：

-- 连接SQLPlus
$ sqlplus / as sysdba

-- SQLPlus下切换到用户abc, 密码abcd
connect abd/abcd

-- 查询当前用户，命令可以不用加分号
show user

-- 查看所有用户
select username, user_id from dba_users;

Oracle数据库表空间

Oracle表空间涉及的相关概念关系：

主要有：数据库、表空间、段、区间、数据块等。其中最核心的概念就是Oracle特有的表空间。一个数据库实例可以有N个表空间，一个表空间下可以又N个表。

表空间2.png

表空间是一个逻辑概念，一个数据库对应一或多个表空间，每个表空间在物理上对应一或多个数据文件（.dbf）。

一般一个表空间上只创建一个用户名。用户名类似于MySQL的数据库名，Oracle的用户名.表名等价于MySQL的数据库名.表名，因此在写兼容MySQL和Oracle的语句时可以用到。

Oracle安装完数据库的一般流程：建表空间（分区）-->建用户（分配相应的表空间和用户权限）-->登陆用户，建用户表。

表空间1.jpg

数据库、表空间（Tablespace）、段（Segment）、区间（Extent）、Oracle数据块，它们之间是依次存在一对多的对应关系。

• 每个表空间逻辑上由若干段（Segment）组成，表空间物理上由若干数据库文件（.dbf）组成，数据文件是实实在在的OS文件。逻辑上表空间中一般有多个表。每个ORACLE数据库均有SYSTEM表空间。

• 每个段（Segment）逻辑上有若干区间（Extent）组成，段可以有表段、索引段、回滚段、临时段和高速缓存段等，一个表要占一个段，一个索引也要占一个段。通常可以用表来理解段，即一个表对应一个段。

• 一个区间（Extent）又有一组连续的数据块(data block)组成。这段连续的数据块逻辑上是连续的，有可能在物理磁盘上是分散的。

• 一个Oracle数据块（通常是8K）对应了若干的OS磁盘块（例如：1K）。

典型的数据库及表空间名称(注意表空间名都是大写)：

1. 系统表空间（SYSTEM）
   系统表空间(SYSTEM Tablespace )是每个Oracle数据库都必须具备的。这是数据库创建时自动创建的。SYSTEM表空间必须总要保持联机,因为其包含着整个数据库的[数据字典]。

不能脱机offline
不能置为只读read only
不能重命名
不能删除

2. 辅助表空间（SYSAUX）
   SYSAUX表空间是作为SYSTEM表空间的辅助表空间。使用独立表空间或系统表空间的数据库组件在SYSAUX表空间中创建，通过分离这些组件和功能，SYSTEM 表空间的负荷得以减轻，反复创建一些相关对象及组件引起SYSTEM 表空间的碎片问题得以避免。SYSAUX表空间不可用，数据库核心功能将保持有效；使用SYSAUX表空间的特点将会失败或功能受限。

不能删除
不能重命名
不能置为read only

3. 临时表空间（TEMP）

TEMP临时表空间主要用来做查询和存放一些缓冲区数据，临时表空间消耗的主要原因是需要对查询的中间结果进行排序。重启数据库可以释放临时表空间，如果不能重启实例，而一直保持sql语句的执行，TEMP表空间会一直增长。直到耗尽磁盘空间。
临时表空间的主要作用：

索引create或rebuild
Order by或group by
Distinct操作
Union或intersect或minus
Sort-merge joins
analyze

使用原理 ：在oracle数据库中进行排序，分组汇总，索引等时，会产生很多的临时数据，通常情况下，oracle数据库会将这些临时数据存放到内存的PGA内。在这个程序全局区中有一个叫做排序区的地方，专门用来存放这些因为排序操作而产生的临时数据。但这个分区的容量是有限的。当这个分区的大小不足以容纳排序后所产生的记录时，数据库系统就会将临时数据存放到临时表空间中。这就是临时表空间的来历。

4.撤销表空间

用于保存oracle数据库撤销信息，即保存用户回滚段( Rollback Segment )的表空间称之为回滚表空间（或简称为撤销表空间（UNDO Tablespace））。

表空间操作示例

以下操作涉及的数据字典表：

• dba_users：数据库用户信息表
• dba_tablespaces：系统管理员级别查看的数据库表空间信息表
• user_tablespaces：普通用户查看的数据库表空间信息表
• dba_data_files：表空间对应物理信息表
• dba_tables：数据库表元数据表

1. 创建表空间

语法：

CREATE TABLESPACE 表空间名
DATAFILE '数据文件路径' SIZE 大小
[AUTOEXTEND ON] [NEXT 大小]
[MAXSIZE 大小];

说明：

• [ ]里面的内容是可选项，数据文件路径中若包含目录需先创建
• SIZE为初始表空间大小，单位为K或者M
• AUTOEXTEND是否自动扩展，值为ON或OFF
• NEXT为文件满了后扩展大小
• MAXSIZE为文件最大大小，值为数值或UNLIMITED(表示不限大小)

示例：

CREATE TABLESPACE ts_test
DATAFILE 'D:\oracle_tablespace\ts_test.dbf' SIZE 20M
AUTOEXTEND ON;

2. 查询表空间及相关信息

示例：

-- 查看用户的默认表空间
select default_tablespace from dba_users where username='ABC';

-- 查看用户的临时表空间
select default_tablespace from dba_users where username='ABC';

-- 设置用户的默认或临时表空间, 普通用户没有设置表空间的权限
alter user username DEFAULT|TEMPORARY tablespace tablespace_name;

-- 查看目前已有的所有表空间
select tablespace_name from dba_data_files group by tablespace_name;
-- 或
select tablespace_name from dba_tablespaces group by tablespace_name;

-- 查询表空间是否自动扩充等信息：
select file_name,autoextensible,increment_by from dba_data_files WHERE tablespace_name = 'XXX';

-- 查看表空间的状态，表空间的状态分为ONLINE和OFFLINE，脱机时不可用，默认是联机的
select status from dba_tablespaces where tablespace_name='XXX';

-- 查看表空间对应的物理数据文件
select file_name from dba_data_files where tablespace_name='XXX';

-- 查看表空间大小（单位：MB）：
select tablespace_name, count(*), sum(blocks), sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

-- 查看指定表空间中的所有表（如果表空间中有表的话）
select tablespace_name, table_name from dba_tables where tablespace_name='XXX';

-- 查看某个表属于哪个表空间
select table_name, tablespace_name from all_tables where table_name='YYY';

3. 修改表空间

语法：

ALTER TABLESPACE 表空间名
ADD DATAFILE '文件路径' SIZE 大小
[AUTOEXTEND ON] [NEXT大小]
[MAXSIZE 大小];

示例：

ALTER TABLESPACE ts_test
ADD DATAFILE 'D:\oracle_tablespace\ts_test2.dbf 'SIZE 5M
AUTOEXTEND ON;

-- 修改表空间联机状态，表空间脱机时不可用，默认是联机的
alter tablespace tablespace_name online|offline;

-- 设置表空间只读或可读写的状态，只有在联机状态才可以更改,默认的联机状态就是读写状态
alter tablespace tablespace_name read only | read write;

-- 增加数据文件
alter tablespace tablespace_name add datafile ‘xx.dbf’ size xx;

-- 移除数据文件，不能删除表空间中的第一个数据文件，如果要删除就需要删除整个表空间
alter tablespace tablespace_name drop datafile ‘xx.dbf’;

4. 删除表空间

语法：

-- 仅删除表空间
DROP TABLESPACE 表空间名;

-- 删除表空间及数据文件
DROP TABLESPACE 表空间名 INCLUDING CONTENTS AND DATAFILES;

示例：

--仅删除表空间
DROP TABLESPACE ts_test;

--删除表空间及数据文件
DROP TABLESPACE ts_test INCLUDING CONTENTS AND DATAFILES;