今天做一个关于回收表空间的测试，下面看具体过程：
[oracle@ora11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun May 31 17:10:14 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Database mounted.
Database opened.
SQL> select ts#,name from v$tablespace;
TS# NAME
---------- ------------------------------
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
ora11g
--创建测试表空间test、用户zlm并设置test为默认表空间
SQL> create tablespace test datafile '/u01/app/oracle/oradata/ora11g/test01.dbf' size 500m autoextend on next 10m;
Tablespace created.
SQL> create user zlm identified by zlm;
User created.
SQL> alter user zlm default tablespace test;
User altered.
SQL> grant dba to zlm;
Grant succeeded.
SQL> conn zlm/zlm
Connected.
--创建测试表t1
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> select count(*) from t1;
COUNT(*)
----------
86965
--增加记录数量以便更好地观察效果
SQL> insert into t1 select * from t1;
86965 rows created.
SQL> /
173930 rows created.
SQL> /
347860 rows created.
SQL> select tablespace_name,table_name from dba_tables where owner='ZLM';
------------------------------ ------------------------------
由于设置过默认表空间，因此t1表创建在test表空间上
SQL> select bytes/1024/1024 from dba_segments where segment_name='T1';
BYTES/1024/1024
---------------
80目前60多万条记录共占用了表空间80M大小
SQL> select index_name,table_name,tablespace_name from dba_indexes where owner='ZLM';
no rows selected
通过CTAS方式创建的表，不会讲原表上的索引和约束带过来，需要自己创建
SQL> create index idx_t1_oid on t1(object_id);
Index created.
SQL> select index_name,table_name,tablespace_name from dba_indexes where owner='ZLM';
TABLE_NAME
------------------------------ ------------------------------ ------------------------------
T1
TEST索引默认表空间也是为test
--查看directory目录
SQL> col owner for a5
SQL> col directory_name for a25
SQL> col directory_path for a80
SQL> select * from dba_directories;
DIRECTORY_PATH
----- ------------------------- --------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/
/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/ora11g/state
SYS DATA_PUMP_DIR /u01/app/oracle/admin/ora11g/dpdump/
/u01/app/oracle/product/11.2.0/dbhome_1/ccr/state
--使用系统默认的directory将测试表t1导出备份
[oracle@ora11g ~]$ expdp zlm/zlm directory=data_pump_dir dumpfile=t1.dmp logfile=t1.log tables=t1 indexes=y
Export: Release 11.2.0.4.0 - Production on Sun May 31 18:36:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "indexes=TRUE" Location: Command Line, ignored.
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "ZLM"."SYS_EXPORT_TABLE_01": zlm/******** directory=data_pump_dir dumpfile=t1.dmp logfile=t1.log tables=t1 reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
80 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
67.38 MB 695720 rows
Master table "ZLM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ZLM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ora11g/dpdump/t1.dmp
Job "ZLM"."SYS_EXPORT_TABLE_01" successfully completed at Sun May 31 18:36:46 2015 elapsed 0 00:00:10
从导出结果看，占用的逻辑空间评估值为80M，与之前查询出占用的表空间大小相同，60多W行，生成的dump文件大小为67.38M
--开始删除记录
SQL> set timing on
SQL> delete from t1 where rownum<=200000;
200000 rows deleted.
Elapsed: 00:00:15.95
SQL> set autot off
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
495720
SQL> select bytes/1024/1024 from dba_segments where segment_name='T1';
BYTES/1024/1024
---------------
80
删除完20W条记录后，可以看到占用的空间并没有被释放，仍然是80M
SQL> alter table t1 move;
Table altered.
SQL> select bytes/1024/1024 from dba_segments where segment_name='T1';
BYTES/1024/1024
---------------
57
对表执行move操作后，占用的空间才降下来，变成57M
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ --------
T1 TEST UNUSABLE
注意，对表进行move之后，由于rowid的改变，会引起索引失效，状态为“UNUSABLE”
SQL> set timing on
SQL> delete from t1 where rownum<=200000;
200000 rows deleted.
Elapsed: 00:00:12.31
SQL> set timing off
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
295720
SQL> select bytes/1024/1024 from dba_segments where segment_name='T1';
BYTES/1024/1024
---------------
继续删除20W条记录后，仍然为占用57M大小的表空间
SQL> create tablespace test2 datafile '/u01/app/oracle/oradata/ora11g/test02.dbf' size 500m autoextend on next 10m;
Tablespace created.
SQL> alter table t1 move tablespace test2;
Table altered.
TABLE_NAME
TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ --------
T1
TEST
SQL> select table_name,tablespace_name from dba_tables where owner='ZLM';
------------------------------ ------------------------------
TEST2
这次将表移动到test2的表空间上，但索引仍然在test表空间上面
SQL> set autot trace
SQL> select count(*) from t1 where object_id<10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Name | Rows | Bytes | Cost (%CPU)| Time
---------------------------------------------------------------------------
|
1 | 13 | 1170 (1)| 00:00:15 |
|
1 | 13 |
|
TABLE ACCESS FULL| T1 | 35782 | 454K| 1170 (1)| 00:00:15 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_ID"<10000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
4307 consistent gets
4465 physical reads
116 redo size
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
由于move到了test2表空间上，同样的，索引也是失效状态，因为执行计划中走了全表扫描
--重建索引
SQL> alter index idx_t1_oid rebuild;
Index altered.
SQL> select count(*) from t1 where object_id<10000;
Execution Plan
----------------------------------------------------------
Plan hash value: 3794164521
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
| 1 | 13 | 88
| 1 | 13 |
|
INDEX RANGE SCAN| IDX_T1_OID | 35782 | 454K| 88
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<10000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
147 consistent gets
528 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
重建索引后，就可以走索引范围扫描了
SQL> set autot off
SQL> select bytes/1024/1024 from dba_segments where segment_name='T1';
BYTES/1024/1024
---------------
34由于第2次move，占用的空间又减少了，现在变为34M
SQL> select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZLM';
TABLE_NAME
TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ --------
T1
TEST
VALID此时索引状态变为valid，表示又可以正常使用了
--将表move回原来的表空间
SQL> alter table t1 move tablespace test;
Table altered.
SQL> select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZLM';
TABLE_NAME
TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ --------
T1
TEST
UNUSABLE
SQL> alter index idx_t1_oid rebuild;
Index altered.
SQL> select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZLM';
TABLE_NAME
TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ --------
T1
TEST
VALID
总结：
只要对表执行过move操作，都是需要重建索引的，无论是在原来的表空间还是另外的表空间，执行完操作后，必须对索引进行重建。另外需要注意的是，move表是会产生锁资源占用的，因此，不建议在业务繁忙期间进行该操作。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
