Oracle表空间管理 #

Oracle存储结构
├── 逻辑结构
│   ├── 表空间（Tablespace）
│   │   ├── 段（Segment）
│   │   │   ├── 区（Extent）
│   │   │   │   └── 块（Block）
│   │   │   └── ...
│   │   └── ...
│   └── ...
└── 物理结构
    └── 数据文件（Data File）

表空间类型
├── 系统表空间
│   ├── SYSTEM - 存储数据字典
│   └── SYSAUX - 辅助系统表空间
├── 用户表空间
│   ├── USERS - 默认用户表空间
│   ├── EXAMPLE - 示例表空间
│   └── 自定义用户表空间
├── 临时表空间
│   ├── TEMP - 默认临时表空间
│   └── 自定义临时表空间
├── 撤销表空间
│   ├── UNDOTBS1 - 默认撤销表空间
│   └── 自定义撤销表空间
└── 特殊表空间
    ├── UNDO - 撤销数据
    └── TEMP - 临时数据

-- 查看数据库中的表空间
SELECT 
    TABLESPACE_NAME,
    CONTENTS,
    STATUS,
    BIGFILE
FROM DBA_TABLESPACES;

-- 查看表空间使用情况
SELECT 
    TABLESPACE_NAME,
    ROUND(SUM(BYTES)/1024/1024, 2) AS SIZE_MB,
    ROUND(SUM(MAXBYTES)/1024/1024, 2) AS MAX_SIZE_MB,
    ROUND((SUM(BYTES) - SUM(FREE_BYTES))/1024/1024, 2) AS USED_MB
FROM (
    SELECT 
        TABLESPACE_NAME,
        BYTES,
        MAXBYTES,
        0 AS FREE_BYTES
    FROM DBA_DATA_FILES
    UNION ALL
    SELECT 
        TABLESPACE_NAME,
        0,
        0,
        BYTES
    FROM DBA_FREE_SPACE
)
GROUP BY TABLESPACE_NAME;

-- 创建表空间基本语法
CREATE TABLESPACE tablespace_name
DATAFILE 'path/to/datafile.dbf' SIZE size
[AUTOEXTEND ON|OFF] [NEXT size] [MAXSIZE size]
[EXTENT MANAGEMENT LOCAL|DICTIONARY]
[SEGMENT SPACE MANAGEMENT AUTO|MANUAL]
[LOGGING|NOLOGGING];

-- 创建简单表空间
CREATE TABLESPACE users_data
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
SIZE 100M;

-- 创建自动扩展表空间
CREATE TABLESPACE users_data
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
SIZE 100M
AUTOEXTEND ON
NEXT 10M
MAXSIZE 500M;

-- 创建多数据文件表空间
CREATE TABLESPACE users_data
DATAFILE 
    '/u01/app/oracle/oradata/orcl/users_data01.dbf' SIZE 100M,
    '/u01/app/oracle/oradata/orcl/users_data02.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

-- 创建大文件表空间（Bigfile Tablespace）
-- 大文件表空间只能有一个数据文件，最大可达32TB

CREATE BIGFILE TABLESPACE big_data
DATAFILE '/u01/app/oracle/oradata/orcl/big_data01.dbf' 
SIZE 1G
AUTOEXTEND ON NEXT 100M MAXSIZE 32T;

-- 大文件表空间优点
-- 1. 简化管理（只有一个数据文件）
-- 2. 支持更大的存储容量
-- 3. 减少数据文件数量

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE temp_data
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_data01.dbf' 
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 创建临时表空间组
CREATE TEMPORARY TABLESPACE temp_group1
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_group1.dbf' 
SIZE 50M
TABLESPACE GROUP temp_group;

CREATE TEMPORARY TABLESPACE temp_group2
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_group2.dbf' 
SIZE 50M
TABLESPACE GROUP temp_group;

-- 指定默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_data;

-- 创建撤销表空间
CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u01/app/oracle/oradata/orcl/undotbs2.dbf' 
SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

-- 切换撤销表空间
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

-- 查看撤销表空间信息
SELECT * FROM V$UNDOSTAT;

-- 本地管理表空间（推荐）
CREATE TABLESPACE local_tbs
DATAFILE '/u01/app/oracle/oradata/orcl/local_tbs01.dbf' SIZE 100M
EXTENT MANAGEMENT LOCAL
AUTOALLOCATE;  -- 或 UNIFORM SIZE 1M

-- 段空间自动管理（推荐）
CREATE TABLESPACE auto_seg_tbs
DATAFILE '/u01/app/oracle/oradata/orcl/auto_seg_tbs01.dbf' SIZE 100M
SEGMENT SPACE MANAGEMENT AUTO;

-- 日志模式
CREATE TABLESPACE nologging_tbs
DATAFILE '/u01/app/oracle/oradata/orcl/nologging_tbs01.dbf' SIZE 100M
NOLOGGING;

-- 块大小
CREATE TABLESPACE block_16k_tbs
DATAFILE '/u01/app/oracle/oradata/orcl/block_16k_tbs01.dbf' SIZE 100M
BLOCKSIZE 16K;

-- 修改表空间名称
ALTER TABLESPACE users_data RENAME TO users_data_new;

-- 修改表空间状态
ALTER TABLESPACE users_data OFFLINE;                    -- 离线
ALTER TABLESPACE users_data ONLINE;                     -- 在线
ALTER TABLESPACE users_data READ ONLY;                  -- 只读
ALTER TABLESPACE users_data READ WRITE;                 -- 读写

-- 修改日志模式
ALTER TABLESPACE users_data NOLOGGING;
ALTER TABLESPACE users_data LOGGING;

-- 添加数据文件
ALTER TABLESPACE users_data
ADD DATAFILE '/u01/app/oracle/oradata/orcl/users_data02.dbf' 
SIZE 100M;

-- 添加自动扩展数据文件
ALTER TABLESPACE users_data
ADD DATAFILE '/u01/app/oracle/oradata/orcl/users_data03.dbf' 
SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 修改数据文件大小
ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
RESIZE 200M;

-- 启用自动扩展
ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 关闭自动扩展
ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
AUTOEXTEND OFF;

-- 移动数据文件（需要表空间离线）
ALTER TABLESPACE users_data OFFLINE;
ALTER DATABASE 
RENAME FILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
TO '/u02/app/oracle/oradata/orcl/users_data01.dbf';
ALTER TABLESPACE users_data ONLINE;

-- 添加临时文件
ALTER TABLESPACE temp_data
ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp_data02.dbf' 
SIZE 100M;

-- 修改临时文件大小
ALTER DATABASE 
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_data01.dbf' 
RESIZE 200M;

-- 删除临时文件
ALTER DATABASE 
TEMPFILE '/u01/app/oracle/oradata/orcl/temp_data01.dbf' 
DROP INCLUDING DATAFILES;

-- 删除空表空间
DROP TABLESPACE users_data;

-- 删除表空间及其内容
DROP TABLESPACE users_data INCLUDING CONTENTS;

-- 删除表空间及其内容和数据文件
DROP TABLESPACE users_data INCLUDING CONTENTS AND DATAFILES;

-- 级联删除约束
DROP TABLESPACE users_data 
INCLUDING CONTENTS AND DATAFILES 
CASCADE CONSTRAINTS;

-- 不能删除系统表空间
DROP TABLESPACE SYSTEM;  -- ORA-01519

-- 删除前检查依赖
SELECT * FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'USERS_DATA';
SELECT * FROM DBA_TABLES WHERE TABLESPACE_NAME = 'USERS_DATA';
SELECT * FROM DBA_INDEXES WHERE TABLESPACE_NAME = 'USERS_DATA';

-- 检查是否为默认表空间
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS
WHERE DEFAULT_TABLESPACE = 'USERS_DATA' 
   OR TEMPORARY_TABLESPACE = 'USERS_DATA';

-- 查看表空间基本信息
SELECT 
    TABLESPACE_NAME,
    CONTENTS,
    STATUS,
    EXTENT_MANAGEMENT,
    SEGMENT_SPACE_MANAGEMENT,
    BIGFILE
FROM DBA_TABLESPACES;

-- 查看数据文件信息
SELECT 
    FILE_NAME,
    TABLESPACE_NAME,
    BYTES/1024/1024 AS SIZE_MB,
    MAXBYTES/1024/1024 AS MAX_SIZE_MB,
    AUTOEXTENSIBLE,
    STATUS
FROM DBA_DATA_FILES
ORDER BY TABLESPACE_NAME;

-- 查看临时文件信息
SELECT 
    FILE_NAME,
    TABLESPACE_NAME,
    BYTES/1024/1024 AS SIZE_MB,
    STATUS
FROM DBA_TEMP_FILES;

-- 表空间使用情况详细报告
SELECT 
    d.TABLESPACE_NAME,
    ROUND(d.TOTAL_SPACE_MB, 2) AS TOTAL_MB,
    ROUND(f.FREE_SPACE_MB, 2) AS FREE_MB,
    ROUND(d.TOTAL_SPACE_MB - f.FREE_SPACE_MB, 2) AS USED_MB,
    ROUND((d.TOTAL_SPACE_MB - f.FREE_SPACE_MB) / d.TOTAL_SPACE_MB * 100, 2) AS USED_PCT
FROM (
    SELECT 
        TABLESPACE_NAME,
        SUM(BYTES)/1024/1024 AS TOTAL_SPACE_MB
    FROM DBA_DATA_FILES
    GROUP BY TABLESPACE_NAME
) d
JOIN (
    SELECT 
        TABLESPACE_NAME,
        SUM(BYTES)/1024/1024 AS FREE_SPACE_MB
    FROM DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME
) f ON d.TABLESPACE_NAME = f.TABLESPACE_NAME
ORDER BY USED_PCT DESC;

-- 使用DBA_TABLESPACE_USAGE_METRICS视图（11g+）
SELECT 
    TABLESPACE_NAME,
    USED_SPACE,
    TABLESPACE_SIZE,
    USED_PERCENT
FROM DBA_TABLESPACE_USAGE_METRICS;

-- 查看表空间中的段
SELECT 
    SEGMENT_NAME,
    SEGMENT_TYPE,
    TABLESPACE_NAME,
    BYTES/1024/1024 AS SIZE_MB,
    EXTENTS
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'USERS'
ORDER BY BYTES DESC;

-- 查看段的区信息
SELECT 
    SEGMENT_NAME,
    EXTENT_ID,
    FILE_ID,
    BLOCK_ID,
    BYTES/1024 AS SIZE_KB
FROM DBA_EXTENTS
WHERE TABLESPACE_NAME = 'USERS'
AND SEGMENT_NAME = 'EMPLOYEES';

-- 手动扩展表空间
-- 方法1：添加数据文件
ALTER TABLESPACE users_data
ADD DATAFILE '/u01/app/oracle/oradata/orcl/users_data04.dbf' 
SIZE 100M;

-- 方法2：调整数据文件大小
ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
RESIZE 200M;

-- 方法3：启用自动扩展
ALTER DATABASE 
DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' 
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

-- 查看碎片程度
SELECT 
    TABLESPACE_NAME,
    COUNT(*) AS FREE_FRAGMENTS,
    SUM(BYTES)/1024/1024 AS TOTAL_FREE_MB,
    MAX(BYTES)/1024/1024 AS MAX_FREE_MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
HAVING COUNT(*) > 1;

-- 合并相邻空闲空间
ALTER TABLESPACE users_data COALESCE;

-- 使用SHRINK整理碎片（ASSM表空间）
ALTER TABLE employees SHRINK SPACE;
ALTER TABLE employees SHRINK SPACE COMPACT;

-- 检查数据文件状态
SELECT FILE_NAME, STATUS, ERROR FROM V$DATAFILE;

-- 恢复离线数据文件
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' ONLINE;

-- 使用RMAN恢复
RMAN> RECOVER DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf';
RMAN> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users_data01.dbf' ONLINE;

表空间规划建议
├── 系统表空间
│   ├── SYSTEM：数据字典，保持适当大小
│   └── SYSAUX：辅助数据，定期清理
├── 用户表空间
│   ├── 按业务模块分离
│   ├── 按数据类型分离（索引、LOB）
│   └── 按访问频率分离（热数据、冷数据）
├── 临时表空间
│   ├── 创建独立的临时表空间
│   └── 使用临时表空间组
└── 撤销表空间
    ├── 根据事务量设置大小
    └── 考虑闪回需求

-- 估算表空间需求
-- 考虑因素：
-- 1. 数据量
-- 2. 增长率
-- 3. 索引大小
-- 4. 安全余量（20-30%）

-- 计算表大小
SELECT 
    TABLE_NAME,
    NUM_ROWS,
    AVG_ROW_LEN,
    NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 AS EST_SIZE_MB
FROM USER_TABLES;

-- 计算索引大小
SELECT 
    INDEX_NAME,
    TABLE_NAME,
    LEAF_BLOCKS * 8 / 1024 AS SIZE_MB
FROM USER_INDEXES;

-- 创建表空间使用率监控脚本
SELECT 
    TABLESPACE_NAME,
    ROUND(USED_PERCENT, 2) AS USED_PCT,
    CASE 
        WHEN USED_PERCENT > 90 THEN 'CRITICAL'
        WHEN USED_PERCENT > 80 THEN 'WARNING'
        ELSE 'OK'
    END AS STATUS
FROM DBA_TABLESPACE_USAGE_METRICS
WHERE USED_PERCENT > 80;

-- 设置自动告警阈值
EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
    METRICS_ID => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
    WARNING_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GE,
    WARNING_VALUE => '80',
    CRITICAL_OPERATOR => DBMS_SERVER_ALERT.OPERATOR_GE,
    CRITICAL_VALUE => '90',
    OBSERVATION_PERIOD => 1,
    CONSECUTIVE_OCCURRENCES => 1,
    INSTANCE_NAME => NULL,
    OBJECT_TYPE => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
    OBJECT_NAME => 'USERS_DATA'
);

-- 查看表空间详细信息
SELECT 
    t.TABLESPACE_NAME,
    t.STATUS,
    t.CONTENTS,
    t.EXTENT_MANAGEMENT,
    t.SEGMENT_SPACE_MANAGEMENT,
    d.FILE_NAME,
    d.BYTES/1024/1024 AS SIZE_MB
FROM DBA_TABLESPACES t
JOIN DBA_DATA_FILES d ON t.TABLESPACE_NAME = d.TABLESPACE_NAME
ORDER BY t.TABLESPACE_NAME;

-- 查看默认表空间设置
SELECT 
    PROPERTY_NAME,
    PROPERTY_VALUE
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE '%TABLESPACE%';