一.问题描述:
有时候,要通过的方法移动表调整表的高水位,但是又不想改变他的表空间,此方法适用。
二.实验目的:
验证move表操作移动到同一表空间是不是有效。
表数据
SQL> select count(*) from LL_T1;
COUNT(*)
----------
890400
表空间确认
SQL> select table_name, tablespace_name
2 from user_tables t2
3 where t2.TABLE_NAME = 'LL_T1';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LL_T1 USERDATA
查看空间占用情况
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 96
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 15
删除大约一半数据,查看表的空间占用情况,没有变化
SQL> delete from LL_T1 where rownum <= 500000;
500000 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 96
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 15
将表移动表空间(同一表空间)
SQL> alter table LL_T1 move tablespace USERDATA;
Table altered.
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 37
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 15
SQL> alter index IND_LL_T1_ID rebuild;
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 8
Index altered.
SQL> truncate table LL_T1;
Table truncated.
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 .0625
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID .0625
SQL>
实验结果:
1. 通过表的表空间移动,可以有效回收数据所占空间,但对index无效,index需要rebuild。
2. delete不能释放数据空间,truncate表可以同时释放数据空间和index空间。
有时候,要通过的方法移动表调整表的高水位,但是又不想改变他的表空间,此方法适用。
二.实验目的:
验证move表操作移动到同一表空间是不是有效。
表数据
SQL> select count(*) from LL_T1;
COUNT(*)
----------
890400
表空间确认
SQL> select table_name, tablespace_name
2 from user_tables t2
3 where t2.TABLE_NAME = 'LL_T1';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LL_T1 USERDATA
查看空间占用情况
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 96
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 15
删除大约一半数据,查看表的空间占用情况,没有变化
SQL> delete from LL_T1 where rownum <= 500000;
500000 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 96
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 15
将表移动表空间(同一表空间)
SQL> alter table LL_T1 move tablespace USERDATA;
Table altered.
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 37
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 15
SQL> alter index IND_LL_T1_ID rebuild;
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID 8
Index altered.
SQL> truncate table LL_T1;
Table truncated.
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'LL_T1';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
LL_T1 .0625
SQL> SELECT SEGMENT_NAME, BYTES/(1024 * 1024) m FROM USER_SEGMENTS WHERE SEGMENT_NAME = 'IND_LL_T1_ID';
SEGMENT_NAME M
--------------------------------------------------------------------------------- ----------
IND_LL_T1_ID .0625
SQL>
实验结果:
1. 通过表的表空间移动,可以有效回收数据所占空间,但对index无效,index需要rebuild。
2. delete不能释放数据空间,truncate表可以同时释放数据空间和index空间。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29289867/viewspace-1976968/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29289867/viewspace-1976968/