SHINK SPACE

SQL> create tablespace testing_lmt_assm datafile '/u01/app/oracle/oradata/testing_assm.dbf' size 1m reuse autoextend on next 1m extent management local
  uniform. size 64k segment space management auto;

Tablespace created.

SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> create table t tablespace testing_lmt_assm as select * from dba_objects;

Table created.

SQL> alter table t shrink space;
alter table t shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

SQL> alter table t enable row movement;

Table altered.

SQL> delete from t where rowid not in (select rowid from  t where rownum<1000);

49094 rows deleted.

SQL> commit;

Commit complete.

SQL> set autot trace;
SQL> select * from t;

999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3641 |   629K|   157   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    |  3641 |   629K|   157   (0)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        267  recursive calls
          0  db block gets
        878  consistent gets                                                                          878条一致性读。
          0  physical reads
          0  redo size
      48449  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

SQL> alter table t shrink space compact;

Table altered.

SQL> select * from t;

999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3641 |   629K|   157   (0)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| T    |  3641 |   629K|   157   (0)| 00:00:02 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        878  consistent gets                                                                      SHINK SPACE COMPAT没降低高水位
          0  physical reads
          0  redo size
      48449  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        999  rows processed

SQL> alter table t shrink space ;

Table altered.

SQL> select * from t;

999 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   999 |   172K|     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |   999 |   172K|     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        128  recursive calls
          0  db block gets
        107  consistent gets                                                               哇靠，这差距也太明显了点吧
          7  physical reads
          0  redo size
      48449  bytes sent via SQL*Net to client
       1218  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
        999  rows processed

 

SHINK还是不用在生产库上比较好，表大操作起来非常地慢。

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

MOVE    需要重建索引，因为改变了ROWID，用于离线表操作

SHRINK   用于在线表操作，不需要重建索引。这两个用于回收水位以下的块

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

DEALLOCATE UNUSERD 用户回收高水位以上的块

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668721/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-668721/