Oracle的表空间管理基础操作

艺帆风顺 发布于 2025-04-07 26 次阅读

一、表空间概述

在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 
  2  datafile '/u01/app/oracle/oradata/ORCL/ts2.dbf' size 10M
  3  autoextend on next 10M
  4  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。

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

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

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

USERNAME      DEFAULT_TABLESPACE
-------------------- ---------------
LIYB       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;

本篇关于Oracle表空间管理的分享完结！感谢你的阅读，如果觉得还OK，欢迎点赞 ;关注 ; 收藏 ; 私信；一起成长！！！