Oracle表空间

表空间概述

在Oracle中，表空间中数据存储在磁盘的数据文件上。创建表空间时必须创建数据文件，增加数据文件时也必须指定对应的空间。表空间可以看作Oracle数据库的逻辑结构，而数据文件可以看作Oracle数据库的物理结构。

表空间由一个或多个段组成；一个段由一个或多个盘区组成；一组连续的数据块组成盘区。

表空间和数据文件的相关数据字典（很重要）：

dba_tablespaces

dba_data_files

查看表空间与对应的数据文件的相关信息

# 进入数据库实例
[oracle@oracle ~]$ sqlplus / as sysdba
# 查看表空间和对应的数据文件大小
SQL> select tablespace_name,file_name,
bytes/1024/1024 as MB,
maxbytes/1024/1024 as MAX_MB
from dba_data_files;

从查询结果可以看出，一个数据库默认就创建多个表空间，这些表空间都是数据库创建时自动创建的。一个表空间包含一个或多个数据文件。

Oracle的默认表空间

1、SYSTEM表空间

system表空间用于存储内部数据和数据字典。

2、SYSAUX表空间

SYSTEM表空间主要用于存储Oracle系统内部的数据字典，而SYSAUX表空间则充当SYSTEM的辅助表空间，主要用于存储除数据字典以外的其他数据对象，降低了system表空间的负荷。SYSAUX表空间一般不存储用户数据,由Oracle系统内部自动维护。

查询SYSAUX表空间所存储的用户及其所拥有的对象数量。

SQL> select owner,count(segment_name) from dba_segments
where tablespace_name='SYSAUX'
group by owner

以下是运行结果：

用户可以对SYSAUX表空间进行增加数据文件和监视等操作，但不能对其执行删除、重命名或设置只读(READ ONLY)等操作。

3、UNDO表空间

UNDO表空间用于存储UNDO信息，当执行DML操作时，Oracle会将这些操作的旧数据写入UNDO段中，而UNDO段驻留在UNDO表空间中。

4、临时表空间

临时表空间主要用于内存排序区不足而必须将数据写到磁盘的那个逻辑区域。临时表空间等提交完后自动释放空间

永久表空间管理

创建表空间语法：

CREATE [SMALLFILE/BIGFILE] TABLESPACE tablespace name
DATAFILE '/path/filename' SIZE number  REUSE
AUTOEXTEND [ON|OFF] NEXT number
MAXSIZE [UNLIMITED|number]
DEFAULT STORAGE storage
[ONLINE|OFFLINE]
[LOGGING|NOLOGGING]
EXTENT MANAGEMENT DICTIONARY|LOCAL 
[AUTOALLOCATE|UNIFORM SIZE number]

参数说明：

（1）SMALLFILE/BIGFILE：表示创建的是小文件表空间还是大文件表空间（默认是小文件表空间，一个数据文件最大能达到32G）

（2）AUTOEXTEND [ON|OFF] NEXT number：表示数据文件是自动扩展还是非自动扩展，如果是自动扩展则需要设置NEXT值。

（3）MAXSIZE UNLIMITED|number：当数据文件自动扩展时，允许扩展的最大字节，如果指定UNLIMITED，则表示不限制。

（4）ONLINE|OFFLINE：指定创建表空间时状态是在线还是离线

（5）LOGGING|NOLOGGING：指定该表空间内的表在加载数据时是否产生日志。默认是LOGGING模式。

（6）EXTENT MANAGEMENT DICTIONARY|LOCAL：指定表空间的扩展方式是使用数据字典管理还是本地化管理，默认为本地化管理。

（7）AUTOALLOCATE|UNIFORM SIZE number：如果采用本地化管理表空间，在表空间扩展时，指定每次盘区扩展的大小是由系统自动指定还是按照等同大小进行。

1、创建表空间

（1）创建表空间时指定大小

# 在创建之前查看一下表空间一般放置在什么位置。
SQL> select file_name from dba_data_files;
# 创建允许自动扩展大小为10M，最大可用为100M的表空间
SQL> create tablespace ts1
datafile '/u01/app/oracle/oradata/ORCL/ts1.dbf' size 10M
autoextend on next 10M
maxsize 100M;

（2）创建表空间不限制大小

SQL> create tablespace ts2 
    datafile '/u01/app/oracle/oradata/ORCL/ts2.dbf' size 10M
    autoextend on next 10M
    maxsize unlimited;

# 创建不限制大小的表空间最大可扩展为32G。

（3）创建表空间时指定多个数据文件

SQL> create tablespace ts3 
datafile '/u01/app/oracle/oradata/ORCL/ts31.dbf' size 10M,
'/u01/app/oracle/oradata/ORCL/ts33.dbf' size 10M
autoextend on next 10M
maxsize 100M;

（4）创建大文件表空间

说明：创建大文件表空间只能指定一个数据文件，最大可用达到128TB。

SQL> create bigfile tablespace ts4
datafile '/u01/app/oracle/oradata/ORCL/ts4.dbf' size 10M;

2、查看表空间

（1）查看表空间名称，是否是大文件表空间，是否在线。

SQL> select tablespace_name,bigfile,status from dba_tablespaces;

（2）查看数据文件相关信息

SQL> col file_name for a60
SQL> select tablespace_name,file_name,
bytes/1024/1024 as MB,
maxbytes/1024/1024 as MAX_MB 
from dba_data_files;

3、维护表空间

（1）调整数据文件大小对于已经使用了自动增长的表空间没必要调整大小。等表空间快使用完时，系统会根据next值自动调整，直至达到数据文件存储的最大值。

SQL> alter database         
datafile '/u01/app/oracle/oradata/ORCL/ts1.dbf' resize 20M;

（2）设置默认表空间

在创建用户时，如果不指定表空间，则默认的临时表空间是TEMP，默认的永久表空间是USERS。

生产环境建议为应用系统创建一个单独的永久表空间和一个单独的临时表空间。如果创建用户时没有指定独立永久表空间，可以通过以下语句进行修改

# 修改zj用户的默认表空间
SQL> alter user zj default tablespace ts1;

# 查看用户的默认表空间
SQL> select username,default_tablespace 
from dba_users where username='ZJ'

USERNAME      DEFAULT_TABLESPACE
-------------------- ---------------
ZJ       TS1

# 更改系统的默认临时表空间(全局生效）
SQL> alter database default temporary tablespace temp2

（3）更改表空间状态

# 将表空间设置为只读模式
SQL> alter tablespace ts2 read only;
# 将表空间设置为读写模式
SQL> alter tablespace ts2 read write;
# 将表空间设置为offline状态
SQL> alter tablespace ts3 offline;
# 将表空间设置为online状态
SQL> alter tablespace ts3 offline;

（4）重命名表空间

注意：数据库管理员只能对普通的表空间进行重命名，不能对SYSTEM和SYSAUX表空间进行命名，也不能对OFFLINE状态的表空间进行命名。在修改表空间名称之后，原表空间中所有数据库对象会被保存到新表空间名下。

# 将ts1表空间重命名为ts11
SQL> alter tablespace ts1 rename to ts11;

# 查看表空间
SQL> select tablespace_name from dba_tablespaces where tablespace_name='TS11'

TABLESPACE_NAME
------------------------------
TS11

（5）删除表空间

在默认情况下，Oracle系统不采用Oracle Managed Files方式管理文件，这样删除表空间实际上仅是从数据字典和控制文件中将该表空间的有关信息清除掉，但并没有真正删除该表空间包含的所有物理文件。因此，要想彻底删除表空间来释放磁盘空间，在执行删除表空间的命令之后，还需要手动删除该表空间中包含的所有物理文件。

当Oracle系统采用Oracle Managed Files方式管理文件时，删除某个表空间后，Oracle系统将自动删除该表空间包含的所有物理文件。

# 删除表空间，但数据文件还存在
SQL> drop tablespace ts11;
# 注意：如果表空间里面有内容，需要添加including contents才能删除。

# 连同数据文件一起删除
SQL> drop tablespace ts2 including contents and datafiles;

（6）给表空间添加数据文件当表空间达到最大值时，可以通过添加数据文件来解决表空间不足的问题。

# 给ts3表空间添加一个数据文件，并设置为自动扩展，扩展量为10M，并且扩展空间不受限制
SQL> alter tablespace ts3
add datafile '/u01/app/oracle/oradata/ORCL/ts34.dbf' size 10M
autoextend on next 10M
maxsize unlimited;
# 查看数据文件，可以看到有TS3表空间有三个数据文件
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name ='TS3'

TABLESPACE_NAME         FILE_NAME
------------------------------ ----------------------------
TS3          /u01/app/oracle/oradata/ORCL/ts34.dbf
TS3          /u01/app/oracle/oradata/ORCL/ts31.dbf
TS3          /u01/app/oracle/oradata/ORCL/ts33.dbf

四、undo表空间管理

undo表空间的操作和永久表空间操作基本一样，只是需要在tablespace前面添加undo。

1、创建UNDO表空间

SQL> create undo tablespace undotbs3 
datafile '/u01/app/oracle/oradata/ORCL/undotbs3.dbf'
size 100M;

由于UNOD表空间只能用于存储UNDO数据，所以不要在UNDO表空间内创建任何数据对象。

2、修改undo表空间

# 向undotbs3表空间中添加一个数据文件
SQL> alter tablespace undotbs3
add datafile '/u01/app/oracle/oradata/ORCL/undotbs32.dbf' size 2G;

# 查看表空间
SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'UNDOTBS3';

TABLESPACE_NAME         FILE_NAME
------------------------------ ------
UNDOTBS3         /u01/app/oracle/oradata/ORCL/undotbs3.dbf
UNDOTBS3         /u01/app/oracle/oradata/ORCL/undotbs32.dbf

3、切换UNDO表空间

启动实例并打开数据库后，同一时刻指定实例只能使用一个UNDO表空间。可以更换默认的UNDO表空间

# 修改默认的UNDO表空间为UNDOTBS3
SQL> alter system set undo_tablespace=undotbs3;

# 查看当前的UNDO表空间，
SQL> show parameter undo_tablespace;
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace        string  UNDOTBS3
# 从结果看已经将默认UNDO表空间切换为UNDOTBS3

4、删除UNDO表空间

# 正在使用的UNDO表空间不能删除
SQL> drop tablespace undotbs3;
drop tablespace undotbs3
*
第 1 行出现错误:
ORA-30013: 还原表空间 'UNDOTBS3' 当前正在使用中

# 需要切换后再删除
SQL> alter system set undo_tablespace = undotbs1;
# 删除UNDO表空间
SQL> drop tablespace undotbs3 including contents and datafiles;

表空间已删除。

五、临时表空间管理

1、创建临时表空间

临时表空间是用临时文件而不是数据文件创建的，临时表空间不需要备份。临时表空间中数据的修改也不会被记录到重做日志中。

SQL> create temporary tablespace temp3 
  2  tempfile '/u01/app/oracle/oradata/ORCL/temp3.dbf' size 1G;

2、修改默认临时表空间

SQL> alter database default temporary tablespace temp3;

3、查询临时表空间

SQL> select file_name,bytes/1024/1024,tablespace_name from dba_temp_files;

FILE_NAME           BYTES/1024/1024 TABLESPACE_NAME
------------------------------------------------------------ --------------- ------------------------------
/u01/app/oracle/oradata/ORCL/temp01.dbf      130 TEMP
/u01/app/oracle/oradata/ORCL/temp3.dbf     1024 TEMP3

4、删除临时表空间

# 不能删除默认临时表空间
SQL> drop tablespace temp3 including contents and datafiles;
drop tablespace temp3 including contents and datafiles
*
第 1 行出现错误:
ORA-12906: 不能删除默认的临时表空间

# 修改默认临时表空间
SQL> alter database default temporary tablespace temp3;

# 再次尝试删除成功
SQL> drop tablespace temp3 including contents and datafiles;

1. 创建表时指定表空间

在创建表时，可以使用TABLESPACE子句显式地指定该表要存储在哪个表空间中。语法示例

-- 创建一个名为employees的表，并将其存储在名为example_tablespace的表空间中
CREATE TABLE employees (
    employee_id NUMBER(10),
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
)
TABLESPACE example_tablespace;

解释在上述示例中，TABLESPACE example_tablespace指定了employees表的数据将存储在example_tablespace表空间中。如果不指定表空间，Oracle 会将表存储在默认的表空间中，默认表空间通常是在创建用户时指定的。

2. 修改表的存储表空间

如果需要将已存在的表从一个表空间移动到另一个表空间，可以使用ALTER TABLE语句。语法示例

-- 将employees表从当前表空间移动到new_example_tablespace表空间
ALTER TABLE employees MOVE TABLESPACE new_example_tablespace;

解释 ALTER TABLE语句的MOVE子句用于将表的数据从一个表空间移动到另一个表空间。执行此操作后，表的数据将存储在新的表空间中。

3. 查看表所在的表空间

可以通过查询数据字典视图DBA_TABLES、ALL_TABLES或USER_TABLES来查看表所在的表空间。语法示例

-- 查询当前用户下所有表及其所在的表空间
SELECT table_name, tablespace_name
FROM USER_TABLES;
-- 查询指定表（例如employees表）所在的表空间
SELECT table_name, tablespace_name
FROM USER_TABLES
WHERE table_name = 'EMPLOYEES';
-- 如果使用DBA用户，可以查询所有用户的表及其所在的表空间
SELECT owner, table_name, tablespace_name
FROM DBA_TABLES;

解释 USER_TABLES视图包含了当前用户拥有的所有表的信息。 DBA_TABLES视图包含了数据库中所有用户的表的信息，需要有足够的权限才能查询。 ALL_TABLES视图包含了当前用户可以访问的所有表的信息。

4. 表的索引与表空间的关联

表的索引也是存储在表空间中的，创建索引时也可以指定索引存储的表空间。语法示例

-- 在employees表的employee_id列上创建一个索引，并将该索引存储在index_tablespace表空间中
CREATE INDEX idx_employee_id ON employees(employee_id)
TABLESPACE index_tablespace;

解释上述示例中，TABLESPACE index_tablespace指定了idx_employee_id索引的数据将存储在index_tablespace表空间中。通过合理地将表和索引存储在不同的表空间中，可以提高数据库的性能。

5. 默认表空间的概念

每个 Oracle 用户都有一个默认表空间。当创建数据库对象（如表）时，如果没有明确指定表空间，该对象就会被创建在用户的默认表空间里。你可以通过以下 SQL 语句查看当前用户的默认表空间：

SELECT username, default_tablespace 
FROM dba_users 
WHERE username = USER;

6. 不指定表空间创建表的示例

以下是一个不指定表空间创建表的简单示例：

-- 创建一个名为employees的表
CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    hire_date DATE,
    job_id VARCHAR2(10)
);

在上述代码中，CREATE TABLE 语句用于创建一个名为 employees 的表，定义了几个列及其数据类型，但没有指定表空间。因此，employees 表会被创建在当前用户的默认表空间中。

7. 注意事项

表空间容量：默认表空间的可用空间可能有限。如果默认表空间已满，创建表操作可能会失败。你可以使用以下语句查看表空间的使用情况：

SELECT tablespace_name, 
       SUM(bytes) / 1024 / 1024 AS total_size_mb,
       SUM(bytes - NVL(maxbytes, 0)) / 1024 / 1024 AS used_size_mb,
       SUM(NVL(maxbytes, bytes)) / 1024 / 1024 AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name;

性能考虑：默认表空间可能不是为特定的业务需求（如高性能读写）而设计的。在实际生产环境中，建议根据表的用途（如数据仓库表、临时表等）明确指定合适的表空间，以优化性能和管理数据。权限问题：确保当前用户有在默认表空间创建表的权限。如果权限不足，创建表操作会失败。如果你想将表创建到指定的表空间，可以在 CREATE TABLE 语句中使用 TABLESPACE 子句，示例如下：

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20),
    last_name VARCHAR2(25),
    email VARCHAR2(25),
    hire_date DATE,
    job_id VARCHAR2(10)
) TABLESPACE your_tablespace_name;

上述代码中，your_tablespace_name 应替换为你实际要使用的表空间名称。

表空间与用户

在 Oracle 数据库中，表空间和用户并不是直接绑定的，但可以通过一系列操作让用户与特定的表空间关联起来，以控制用户的数据存储位置和资源使用。下面详细介绍如何将用户与表空间进行关联。

1. 创建表空间

在将用户与表空间关联之前，需要先创建相应的表空间。以下是创建永久表空间和临时表空间的示例：创建永久表空间

-- 创建一个名为 example_data 的永久表空间
CREATE TABLESPACE example_data
DATAFILE '/u01/app/oracle/oradata/your_database/example_data01.dbf'
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;

上述语句创建了一个名为 example_data 的永久表空间，数据文件存放在 /u01/app/oracle/oradata/your_database/example_data01.dbf，初始大小为 100M，自动扩展，每次扩展 10M，最大大小为 200M。创建临时表空间

-- 创建一个名为 example_temp 的临时表空间
CREATE TEMPORARY TABLESPACE example_temp
TEMPFILE '/u01/app/oracle/oradata/your_database/example_temp01.dbf'
SIZE 50M
AUTOEXTEND ON NEXT 5M MAXSIZE 100M;

上述语句创建了一个名为 example_temp 的临时表空间，临时文件存放在 /u01/app/oracle/oradata/your_database/example_temp01.dbf，初始大小为 50M，自动扩展，每次扩展 5M，最大大小为 100M。

2. 创建用户并指定默认表空间和临时表空间

创建用户时，可以指定该用户的默认表空间和临时表空间，从而将用户与表空间关联起来。

-- 创建一个名为 example_user 的用户，指定默认表空间为 example_data，临时表空间为 example_temp
CREATE USER example_user
IDENTIFIED BY password
DEFAULT TABLESPACE example_data
TEMPORARY TABLESPACE example_temp;

上述语句创建了一个名为 example_user 的用户，密码为 password，默认表空间为 example_data，临时表空间为 example_temp。

3. 授予用户权限

创建用户后，需要授予用户必要的权限，以便用户能够连接到数据库并在指定的表空间中创建对象。

-- 授予用户连接数据库的权限
GRANT CONNECT TO example_user;
-- 授予用户创建表的权限
GRANT CREATE TABLE TO example_user;
-- 授予用户在 example_data 表空间上的配额
ALTER USER example_user
QUOTA UNLIMITED ON example_data;

上述语句授予了 example_user 用户连接数据库和创建表的权限，并允许该用户在 example_data 表空间上使用无限的空间。

4. 修改用户的默认表空间和临时表空间

如果需要修改用户的默认表空间和临时表空间，可以使用以下语句：

-- 修改 example_user 用户的默认表空间为 new_example_data
ALTER USER example_user
DEFAULT TABLESPACE new_example_data;
-- 修改 example_user 用户的临时表空间为 new_example_temp
ALTER USER example_user
TEMPORARY TABLESPACE new_example_temp;

总结

通过以上步骤，你可以将用户与特定的表空间关联起来，包括创建表空间、创建用户并指定默认表空间和临时表空间、授予用户权限以及修改用户的表空间设置。这样可以更好地管理用户的数据存储和资源使用。

Z笔记

左老师的课堂笔记