关于 alter table move tablespace 的一点理解

来源：互联网发布：alpha软件go下载编辑：程序博客网时间：2024/05/21 07:13

测试一：

SQL> alter table hlj move tablespace test;

alter table hlj move tablespace test
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST

当test 的空间不足以存放 hlj 表的数据时，oracle并不是那么智能，在初始化test表空间数据文件前就抛出报错，实际是oracle会先在test 表空间为hlj 生成临时段，等没有空间可扩展时，才会抛出ORA-1652: unable to extend temp segment by 128 in tablespace TEST ,然后再去清理掉test中的临时段，但是只是标记而已，不会立刻清理。

21:15:37 SQL> alter table hlj move tablespace test;
alter table hlj move tablespace test
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEST
Elapsed: 00:00:12.18
21:16:07 SQL>

ALert.log 记录报错信息:

Thu Nov 6 21:16:06 2014
ORA-1652: unable to extend temp segment by 128 in tablespace TEST

测试二：

关于 alter tablemove 是否会回收数据文件空间？答案是：不会释放数据文件已经格式化的磁盘空间

hlj 表原来在 example 表空间

Status Name Type Extent Management Total Size (M) Used (M) Free (M) Used %
------------------ -------------------- ------------------ -------------------- -------------------------- -------------------------- -------------------------- --------------
ONLINE TEMP TEMPORARY LOCAL .000 .000 .000 0.00
ONLINE UNDOTBS1 UNDO LOCAL 32767.984 70.250 384.750 0.21
ONLINE SYSAUX PERMANENT LOCAL 32767.984 263.688 6.313 0.80
ONLINE SYSTEM PERMANENT LOCAL 32767.984 498.625 1651.375 1.52
ONLINE EXAMPLE PERMANENT LOCAL 32767.984 771.250 4.375 2.35 ------------》775.625=771.250 + 4.375
ONLINE USERS PERMANENT LOCAL 32767.984 1193.438 1231.563 3.64
ONLINE TEST PERMANENT LOCAL 200.000 8.000 192.000 4.00

将hlj 表从 example 表空间move到 users

SQL> alter table hlj move tablespace users;

Table altered.

Status Name Type Extent Management Total Size (M) Used (M) Free (M) Used %
------------------ -------------------- ------------------ -------------------- -------------------------- -------------------------- -------------------------- --------------
ONLINE TEMP TEMPORARY LOCAL .000 .000 .000 0.00
ONLINE UNDOTBS1 UNDO LOCAL 32767.984 70.375 384.625 0.21
ONLINE EXAMPLE PERMANENT LOCAL 32767.984 68.250 707.375 0.21 -----------》68.250+707.375=775.625
ONLINE SYSAUX PERMANENT LOCAL 32767.984 263.688 6.313 0.80
ONLINE SYSTEM PERMANENT LOCAL 32767.984 498.625 1651.375 1.52
ONLINE TEST PERMANENT LOCAL 200.000 8.000 192.000 4.00
ONLINE USERS PERMANENT LOCAL 32767.984 1889.438 535.563 5.77

发现前后数据文件的大小没啥变化

下面的数据比对更清晰：

21:16:07 SQL> select tablespace_name,bytes from dba_data_files;
TABLESPACE_NAME BYTES
------------------------------------------------------------ ----------
USERS 2542796800
SYSAUX 283115520
UNDOTBS1 477102080
SYSTEM 2254438400
EXAMPLE 813301760
TEST 209715200

Elapsed: 00:00:00.00
21:32:02 SQL> alter table hlj move tablespace EXAMPLE;
Elapsed: 00:00:27.80

21:32:41 SQL> select tablespace_name,bytes from dba_data_files;
TABLESPACE_NAME BYTES
------------------------------------------------------------ ----------
USERS 2542796800
SYSAUX 283115520
UNDOTBS1 477102080
SYSTEM 2254438400
EXAMPLE 813301760
TEST 209715200

0 0