Oracle 表空间详解（tablespace）

文章目录

• 1 概述
• • 1.1 相关视图
  • 1.2 内存结构图示
• 2 常用操作
• • 2.1 创建
  • 2.2 修改
  • 2.3 删除
  • 2.4 查询
• 3 扩展
• • 3.1 ORA-01653：表无法在表空间中扩展
  • 3.2 ASSM 自动段空间管理

1 概述

1. 表空间
   (1) 表空间是一个逻辑的概念，真正存放数据的是数据文件（data files） 
   (2) 1 个数据库 = N 个表空间（N >=1）
       1 个表空间 = N 个数据文件（N >= 1）
       -- 1 个数据文件 只能属于 1 个表空间       
2. 建立表空间的作用
   (1) 控制数据库占用 '磁盘空间' 的大小
   (2) 不同类型的数据存储到不同的位置，有利于提高 'I/O' 性能，同时有利于备份和恢复等操作

 
 

1.1 相关视图

-- 数据文件
select * from dba_data_files;
select * from dba_temp_files;
-- 表空间
select * from dba_tablespaces;
select * from dba_free_space;
-- 权限
select distinct t.privilege
  from dba_sys_privs t
 where t.privilege like '%TABLESPACE%';

 
 

1.2 内存结构图示

扩展：Oracle 体系结构详解（存储结构 + 内存结构 + 进程结构）

2 常用操作

2.1 创建

• 语法：create tablespace

语法：可选子句有很多，以下仅列举常用的，具体的请参考官方文档

create [temporary | undo] tablespace "TBS"           -- '表空间类型及名称', 默认 '永久'
datafile 'D:\Oracle\TBS.dbf' size 10m                -- '数据文件的位置及大小'
[autoextend off] | [autoextend on next n maxsize m]  -- '是否自动扩展'，默认 'off'
[loggin | nologgin]                                  -- '是否产生日志'，默认 'loggin'
[segment space management auto]                      -- '段空间自动管理'，默认 'auto', 建议默认
[extent management local [uniform size n]]           -- '表空间管理方式'，dictionary | local(默认，推荐)

 
 

例1：创建一个固定表空间 “TBS01”，其大小为 10MB

create tablespace tbs01
datafile 'D:\Oracle\TBS01.dbf' size 10m;
-- 1.路径必须存在，否则报错！
-- 2.表空间名称默认大写，除非用引号注明，如 "tbs" 则为小写

 
 

例2：创建一个自增表空间 “TBS02”，其大小为 10MB，每次扩展 1MB，最大扩展到 20MB

create tablespace tbs02
datafile 'D:\Oracle\TBS02.dbf' size 10m
autoextend on next 1m maxsize 20m;

 
 

查询上述情况：1M = 1024KB，1KB = 1024 Byte

select t.tablespace_name, -- 表空间
       t.file_name, -- 数据文件名 
       t.autoextensible, -- 表空间是否自增
       t.bytes / 1024 / 1024 "SIZE(M)", -- 表空间初始值，单位 m
       t.increment_by * 8 / 1024 "NEXT(M)", -- 每次增加数据块的个数 1blok = 8KB
       t.maxbytes / 1024 / 1024 "MAXSIZE(M)" -- 表空间最大值，单位 m
  from dba_data_files t
 where t.tablespace_name IN ('TBS01','TBS02');

 
 

查询截图：

2.2 修改

例1：修改数据文件的大小为 20M

alter database datafile 'D:\Oracle\TBS01.dbf' resize 20m;

 
 

例2：修改数据文件为自动扩展，最大值为 1G

alter database datafile 'D:\Oracle\TBS01.dbf' autoextend on next 20m maxsize 1g;

 
 

例3：新增数据文件

alter tablespace tbs01 add datafile 'D:\Oracle\TBS01_1.dbf' size 200m;
alter tablespace tbs01 add datafile 'D:\Oracle\TBS01_2.dbf' autoextend on next 20m maxsize 1g;

 
 

2.3 删除

例1：仅删除 空 的表空间（数据文件还在）

drop tablespace tbs; -- 可手动删除 数据文件

 
 

例2：可以删除 非空 的表空间（数据文件还在）

drop tablespace tbs including contents; -- 可手动删除 数据文件

 
 

例3：完全删除（表空间 和 数据文件）

drop tablespace tbs including contents and datafiles;
-- 若存在外键约束，则追加下列子句即可
cascade constraints;

 
 

2.4 查询

例1：查询表空间清单

select ddf.tablespace_name 表空间名,
       ddf.file_name 数据文件名,
       ddf.file_id 数据文件id,
       ddf.autoextensible 是否自动扩展,
       ddf.bytes / 1024 / 1024 "数据文件大小（M）",
       ddf.increment_by * 8 / 1024 "自增步长（M）",
       round(ddf.maxbytes / 1021 / 1021) "数据文件最大值（M）",
       dt.contents 表空间类型,
       dt.logging 是否生成日志,
       dt.extent_management 管理模式,
       dt.allocation_type 分配类型,
       dt.segment_space_management 段管理模式
  from dba_data_files  ddf, -- tablespace_name
       dba_tablespaces dt -- tablespace_name
 where dt.tablespace_name = ddf.tablespace_name
 order by ddf.file_id;

 
 

例2：表空间使用情况

with temp_data_files as
 (select ddf.tablespace_name, sum(bytes) total
    from dba_data_files ddf
   group by ddf.tablespace_name),
temp_free_space as
 (select dfs.tablespace_name, sum(bytes) free
    from dba_free_space dfs
   group by dfs.tablespace_name)
select dt.tablespace_name 表空间名称,
       dt.contents 类型,
       (tdf.total / 1024 / 1024) "总大小(M)",
       (tfs.free / 1024 / 1024) "空闲(M)",
       round((tdf.total - tfs.free) / 1024 / 1024, 2) "已使用(M)",
       round((tdf.total - tfs.free) / tdf.total * 100, 2) "使用占比(%)"
  from dba_tablespaces dt, -- tablespace_name
       temp_data_files tdf, -- tablespace_name
       temp_free_space tfs -- tablespace_name
 where tdf.tablespace_name = dt.tablespace_name
   and tfs.tablespace_name = dt.tablespace_name;

 
 

3 扩展

3.1 ORA-01653：表无法在表空间中扩展

报错截图：

错误原因&解决办法：

错误原因：'表空间满了'，有以下两种可能性
   1. 未设置表空间自动扩展
   2. 虽然设置了表空间自动扩展，但是超过了 'maxsize'
解决办法：
   1. 设置自动扩展(或指定 unlimited: 无限大小)
      alter database datafile 'D:\Oracle\TBS.dbf' autoextend on maxsize 5m;      
   2. 新增 '数据文件' -- 推荐
      alter tablespace TEST1 add datafile 'D:\Oracle\TBS.dbf' size 3m;
   3. 扩大 maxsize -- 不推荐（若过大，影响 I/0）

 
 

验证情况1：表空间满了 且 未设置表空间自动扩展

-- 创建表空间
create tablespace TEST1 datafile 'D:\Oracle\TEST1.dbf' size 1m;
-- 创建用户
create user test_tbs identified by test_tbs default tablespace TEST1;
grant create session to test_tbs; -- 允许登陆
grant resource to test_tbs;

 
 

数据验证：

create table test_tbs.tablespace_test (
  tid   number(10),
  tname varchar2(50)
);
-- 插入数据验证
declare
   v_sql_insert varchar2(500);
begin
   -- 模拟插入语句
   v_sql_insert := 'INSERT INTO test_tbs.tablespace_test (tid, tname) VALUES (:b1, :b2)';
   -- 百万级数据量
   for i in 1 .. 1000000 loop
      execute immediate v_sql_insert
         using i, 'a' || i;
   end loop;
end;

 
 

验证情况2：虽然设置了表空间自动扩展，但是超过了 'maxsize’

alter database datafile 'D:\Oracle\TEST1.dbf' autoextend on next 1m maxsize 2m;
再执行上述 'declare 插入数据验证' 即可

 
 

3.2 ASSM 自动段空间管理

ASSM: Auto Segment Space Management (自动段空间管理)
-- 一般自动管理，无需手动操作
select t.segment_space_management -- AUTO(自动) | MANUAL(手动) 
  from dba_tablespaces t;