Oracle 表空间详解（tablespace）

2024-06-26 19:26| 来源: 网络整理| 查看: 265

文章目录 1 概述2 语法2.1 创建2.2 修改2.3 删除2.4 查询2.4.1 表空间属性2.4.2 表空间使用情况 3 扩展3.1 ORA-01653：表无法在表空间中扩展3.2 Oracle 体系结构详解（存储结构 + 内存结构 + 进程结构）

1 概述表空间逻辑概念，真正存放数据的是数据文件（dba_data_files） 1 个数据库 = N 个表空间，1 个表空间 = N 个数据文件分类管理。不同的项目使用不同的表空间 2 语法 2.1 创建

Oracle 官方文档：create tablespace

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

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

例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)", -- 初始值 t.increment_by * 8 / 1024 "NEXT(M)", -- 步长 1blok = 8KB t.maxbytes / 1024 / 1024 "MAXSIZE(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; 2.3 删除 -- 1 脱机（表空间为空） drop tablespace "TBS"; -- 2 脱机（表空间里有数据） drop tablespace "TBS" including contents; -- 3 完全删除（表空间 + 数据文件） drop tablespace "TBS" including contents and datafiles; -- 若存在约束，则追加下列子句即可 cascade constraints; 2.4 查询 -- 数据文件 select * from dba_data_files; -- 表空间 select * from dba_tablespaces; select * from dba_free_space; -- 权限 select distinct t.privilege from dba_sys_privs t where t.privilege like '%TABLESPACE%'; 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.4.2 表空间使用情况 SELECT dt.tablespace_name, -- 表空间名 dt.status, -- 状态 dt.contents, -- 内容 round(total / 1024 / 1024 / 1024, 4) AS "total(GB)", -- 当前空间大小 round(total_max / 1024 / 1024 / 1024, 4) AS "total_max(GB)", -- 最大空间 round(free / 1024 / 1024 / 1024, 4) AS "free(GB)", -- 空闲空间 round((total - free) / 1024 / 1024 / 1024, 4) AS "used(GB)", -- 已使用空间 round((total - free) / total_max, 4) * 100 AS "used%" -- 已使用百分比 FROM (SELECT tablespace_name, SUM(bytes) total, SUM(maxbytes) total_max FROM dba_data_files GROUP BY tablespace_name) ddf, (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) dfs, dba_tablespaces dt WHERE ddf.tablespace_name = dfs.tablespace_name AND ddf.tablespace_name = dt.tablespace_name ORDER BY 8 DESC; 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 Oracle 体系结构详解（存储结构 + 内存结构 + 进程结构） Oracle 体系结构详解（存储结构 + 内存结构 + 进程结构）

【本文地址】

公司简介

联系我们