关于删记录释放表空间的测试

今天做一个关于回收表空间的测试，下面看具体过程：

[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

Fixed Size     2257840 bytes

Variable Size   507513936 bytes

Database Buffers   322961408 bytes

Redo Buffers     2371584 bytes

Database mounted.

Database opened.

SQL> select ts#,name from v$tablespace;  

       TS# NAME

---------- ------------------------------

 0 SYSTEM

 1 SYSAUX

 2 UNDOTBS1

 4 USERS

 3 TEMP

 6 EXAMPLE

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';

TABLESPACE_NAME         TABLE_NAME

------------------------------ ------------------------------

TEST        T1

由于设置过默认表空间，因此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';

INDEX_NAME        TABLE_NAME       TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------

IDX_T1_OID        T1        TEST

索引默认表空间也是为test

--查看directory目录

SQL> set line 130 pages 130  

SQL> col owner for a5

SQL> col directory_name for a25

SQL> col directory_path for a80

SQL> select * from dba_directories; 

 

OWNER DIRECTORY_NAME DIRECTORY_PATH

----- ------------------------- --------------------------------------------------------------------------------

SYS   SUBDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry//2002/Sep

SYS   SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/order_entry/

SYS   LOG_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/

SYS   MEDIA_DIR  /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/

SYS   DATA_FILE_DIR /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/

SYS   XMLDIR /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

SYS   ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/dbhome_1/ccr/hosts/ora11g/state

SYS   DATA_PUMP_DIR /u01/app/oracle/admin/ora11g/dpdump/

SYS   ORACLE_OCM_CONFIG_DIR2 /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

Total estimation using BLOCKS method:  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

. . exported "ZLM"."T1"                                   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

SQL> select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZLM';

INDEX_NAME        TABLE_NAME       TABLESPACE_NAME      STATUS

------------------------------ ------------------------------ ------------------------------ --------

IDX_T1_OID        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

---------------

     57

继续删除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.

SQL> select index_name,table_name,tablespace_name,status from dba_indexes where owner='ZLM';

INDEX_NAME        TABLE_NAME       TABLESPACE_NAME      STATUS

------------------------------ ------------------------------ ------------------------------ --------

IDX_T1_OID        T1        TEST      UNUSABLE

SQL> select table_name,tablespace_name from dba_tables where owner='ZLM';

TABLE_NAME        TABLESPACE_NAME

------------------------------ ------------------------------

T1         TEST2

这次将表移动到test2的表空间上，但索引仍然在test表空间上面

SQL> set autot trace

SQL> select count(*) from t1 where object_id<10000;

Execution Plan

----------------------------------------------------------

Plan hash value: 3724264953

---------------------------------------------------------------------------

| Id  | Operation    | Name | Rows  | Bytes | Cost (%CPU)| Time   |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |   | 1 |    13 |  1170   (1)| 00:00:15 |

|   1 |  SORT AGGREGATE    |   | 1 |    13 |        |   |

|*  2 |    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

----------------------------------------------------------

 11  recursive calls

  0  db block gets

       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

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed

由于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

--------------------------------------------------------------------------------

| Id  | Operation   | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |     1 |    13 |    88  (0)| 00:00:02 |

|   1 |  SORT AGGREGATE   |        |     1 |    13 |     |        |

|*  2 |    INDEX RANGE SCAN| IDX_T1_OID | 35782 |   454K|    88  (0)| 00:00:02 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("OBJECT_ID"<10000)

Note

-----

   - dynamic sampling used for this statement (level=2)

Statistics

----------------------------------------------------------

 11  recursive calls

  0  db block gets

147  consistent gets

 63  physical reads

  0  redo size

528  bytes sent via SQL*Net to client

523  bytes received via SQL*Net from client

  2  SQL*Net roundtrips to/from client

  0  sorts (memory)

  0  sorts (disk)

  1  rows processed

重建索引后，就可以走索引范围扫描了

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';

INDEX_NAME        TABLE_NAME       TABLESPACE_NAME      STATUS

------------------------------ ------------------------------ ------------------------------ --------

IDX_T1_OID        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';

INDEX_NAME        TABLE_NAME       TABLESPACE_NAME      STATUS

------------------------------ ------------------------------ ------------------------------ --------

IDX_T1_OID        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';

INDEX_NAME        TABLE_NAME       TABLESPACE_NAME      STATUS

------------------------------ ------------------------------ ------------------------------ --------

IDX_T1_OID        T1       TEST       VALID

总结：

对于删除部分表的内容又想要对表进行空间回收，就不能简单的利用truncate来完成，只能通过delete删除部分行之后，再对表进行move操作（本表空间或另外的表空间）， 只要对表执行过move操作，都是需要重建索引的，无论是在原来的表空间还是另外的表空间，执行完操作后，必须对索引进行重建。另外需要注意的是，move表是会产生锁资源占用的，因此，不建议在业务繁忙期间进行该操作。