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/