一、单表空间自动拓展测试

1、创建测试表空间并查看大小

创建表空间

create tablespace tbs_test DATAFILE 'test01.dbf' size 200 AUTOEXTEND on;

创建表t001，t002，t003保存在该表空间

create table t001 (c1 varchar,c2 varchar,c3 varchar,c4 varchar) tablespace tbs_test;

create table t002 (c1 varchar,c2 varchar,c3 varchar,c4 varchar) tablespace tbs_test;

create table t003 (c1 varchar,c2 varchar,c3 varchar,c4 varchar) tablespace tbs_test;

查看该表空间大小sql如下：

SELECT 

    a.tablespace_name AS "表空间名称",

    a.file_name AS "数据文件路径",

    ROUND(a.bytes / 1024 / 1024, 2) AS "总大小(MB)",

    ROUND((a.bytes - NVL(b.free_space, 0)) / 1024 / 1024, 2) AS "已用空间(MB)",

    ROUND(NVL(b.free_space, 0) / 1024 / 1024, 2) AS "剩余空

间(MB)",

    ROUND((a.bytes - NVL(b.free_space, 0)) / a.bytes * 100, 2) AS "使用率(%)"

FROM 

    (SELECT tablespace_name, file_name, SUM(bytes) AS bytes

     FROM dba_data_files

     GROUP BY tablespace_name, file_name) a

LEFT JOIN 

    (SELECT tablespace_name, SUM(bytes) AS free_space

     FROM dba_free_space

     GROUP BY tablespace_name) b

ON a.tablespace_name = b.tablespace_name

where a.tablespace_name='TBS_TEST';

可以看到此时表空间大小为200M，已用4M

2、插入测试数据并查看表空间大小

向3张表（t001、t002、t003）各插入10万条测试数据。

BEGIN

  -- 一次性插入10万行到t001

  INSERT /*+ APPEND */ INTO t001

  SELECT DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000)

  FROM DUAL

  CONNECT BY LEVEL <= 100000;

  

  COMMIT;

  

  -- 插入t002

  INSERT /*+ APPEND */ INTO t002

  SELECT DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000)

  FROM DUAL

  CONNECT BY LEVEL <= 100000;

  

  COMMIT;

  

  -- 插入t003

  INSERT /*+ APPEND */ INTO t003

  SELECT DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000),

         DBMS_RANDOM.string('U', 1000)

  FROM DUAL

  CONNECT BY LEVEL <= 100000;

  

  COMMIT;

  

  DBMS_OUTPUT.PUT_LINE('批量插入完成：每表10万行，总计30万行');

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    DBMS_OUTPUT.PUT_LINE('插入失败：' || SQLERRM);

    RAISE;

END;

/

查看此时表空间大小

可以看到表空间已经拓展到了1288M，已用达到了1179

二、新建表空间数据文件拓展测试

1、新建表空间数据文件测试

alter tablespace tbs_test add DATAFILE 'test02.dbf' size 200 AUTOEXTEND on;

新建一个新的数据文件用作测试

但是看到此时显示表空间已用空间为负数，经查资料的值，新建数据文件时，系统会预分配部分空间用于元数据管理（如文件头、簇位图等），这部分空间被标记为“已使用”，但实际尚未存储用户数据，因此，新文件的 已用空间 = 预分配空间 > 0，而 剩余空间 = 总大小 - 预分配空间，则计算结果为负数；

像这三张表插入一万条数据

DECLARE

  v_commit_batch_size NUMBER := 1000;  -- 每批提交的行数

BEGIN

  FOR i IN 1..10000 LOOP

    -- 插入t001

    INSERT INTO t001 VALUES(

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000)

    );

    

    -- 插入t002

    INSERT INTO t002 VALUES(

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000)

    );

    

    -- 插入t003

    INSERT INTO t003 VALUES(

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000),

      DBMS_RANDOM.string('U', 1000)

    );

    

    -- 每1000行提交一次

    IF MOD(i, v_commit_batch_size) = 0 THEN

      COMMIT;

    END IF;

  END LOOP;

  

  -- 提交剩余行

  COMMIT;

  

  DBMS_OUTPUT.PUT_LINE('数据插入完成：每表1万行，总计3万行');

EXCEPTION

  WHEN OTHERS THEN

    ROLLBACK;

    DBMS_OUTPUT.PUT_LINE('插入失败：' || SQLERRM);

    RAISE;

END;

插入完成后查看数据文件使用情况

此时新建的数据文件开始拓展，而旧数据文件未拓展

2、测试新旧数据文件一起开始拓展的临界点

用以上sql向表插入十万条数据，随时观察数据文件拓展情况

由以上三张图片看出，当新的数据文件大小拓展到1288时，旧数据文件开始拓展，并在拓展完成后，新旧数据文件大小始终保持一致

3、总结

当表空间数据文件占用空间很大后，新建数据文件，新插入的数据将会保存在新的数据文件中，原来的数据文件不会再扩大，当新数据文件扩展到旧数据文件一致大的时候，新旧数据文件将会一起扩展，并保持数据文件大小平衡。

三、表空间大小回收测试

1、查询表空间占有率

删除表t003后，通过以下sql查询表空间占有率

SELECT 

    a.tablespace_name AS "表空间名称",

    a.file_name AS "数据文件路径",

    ROUND(a.bytes / 1024 / 1024, 2) AS "总大小(MB)",

    ROUND((a.bytes - NVL(b.free_space, 0)) / 1024 / 1024, 2) AS "已用空间(MB)",

    ROUND(NVL(b.free_space, 0) / 1024 / 1024, 2) AS "剩余空间(MB)",

    ROUND((a.bytes - NVL(b.free_space, 0)) / a.bytes * 100, 2) AS "使用率(%)"

FROM 

    (SELECT tablespace_name, file_name, SUM(bytes) AS bytes

     FROM dba_data_files

     GROUP BY tablespace_name, file_name) a

LEFT JOIN 

    (SELECT tablespace_name, SUM(bytes) AS free_space

     FROM dba_free_space

     GROUP BY tablespace_name) b

ON a.tablespace_name = b.tablespace_name

where a.tablespace_name='TBS_TEST';

看到此时数据文件已用空间很少，剩余空间很大，在生产环境中，新写入的数据会先使用剩余空间，等剩余空间不足时继续拓展数据文件，但是有时候服务器资源不足，用户想要删除数据后释放表空间大小，此时尝试缩减表空间；

2、收缩表空间

使用命令alter tablespace TBS_TEST resize datafile 'test01.dbf' to 1000

尝试收缩表空间大小到1000M

此时会报错数据文件大小无效，调大收缩值，直到1300时命令执行成功

看到此时数据文件大小已经被压缩到1300

3、总结

数据已使用空间仅不到400M，不能收缩全部free空间，这是因为数据库的数据文件空间由固定大小的簇组成即使删除数据，已分配的簇不会立即释放，而是标记为空闲簇，供后续插入重用，数据文件不能截断包含数据的簇区域，收缩后的尺寸必须≥已用数据空间+系统预留空间，所以表空间大小不能随意收缩；

四、表空间迁移

在正式环境如果需要进行表空间迁移，必须要验证原表空间和目标表空间的目录大小，路径等；

1、在线迁移

1）查看当前表空间数据文件路径

1. 2）修改表空间TBS_TEST为脱机状态

ALTER TABLESPACE “TBS_TEST” OFFLINE;

1. 3）4）修改表空间数据文件路径

alter tablespace "TBS_TEST" rename datafile 'D:\DM\data\DAMENG\test02.dbf' to 'D:\DM\data\test02.dbf';

alter tablespace "TBS_TEST" rename datafile 'D:\DM\data\DAMENG\test01.dbf' to 'D:\DM\data\test01.dbf';

1. 4）修改表空间TBS_TEST为联机状态

ALTER TABLESPACE "TBS_TEST" ONLINE;

1. 5）查看表空间路径，迁移成功

1. 2、停机迁移

1. 1）当前数据文件路径

1. 2）停止数据库，将数据库转换控制文件为文本文件

./dmctlcvt TYPE=1 SRC=/dmdata/data/TEST/dm.ctl DEST=/dmdata/data/TEST/dmctl.txt

1. 3）修改控制文件，手动修改数据文件位置

找到需要修改的表空间，修改数据文件路径

将数据文件拷贝到新目录下

1. 4）5）转换控制文件，启动数据库

./dmctlcvt TYPE=2 SRC=/dmdata/data/TEST/dmctl.txt DEST=/dmdata/data/TEST/dm.ctl

1. 6）验证数据和文件路径

1. 3、总结

在线迁移仅支持仅迁移普通表空间，不支持迁移SYSTEM、TEMP、ROLL等系统表空间，迁移过程中业务可正常访问数据库，（仅目标表空间短暂脱机）；

停机迁移可以迁移所有表空间（包括系统表空间），需手动修改控制文件，适合复杂路径变更需求；