shrink space 的整个过程就是在做delete和insert

shrink space 的整个过程就是在做delete和insert
SQL > select segment_name , bytes / 1024 / 1024 from dba_segments where segment_name = 'TEST_SHRINK' ;


SEGMENT_NAME BYTES / 1024 / 1024
-------------------- ---------------
TEST_SHRINK 300.375



SQL > delete test_shrink where rownum < 100000 ;


99999 rows deleted .


SQL > commit ;


Commit complete .


SQL > delete test_shrink where rownum < 100000 ;


99999 rows deleted .


SQL > commit ;


Commit complete .




SQL > select segment_name , bytes / 1024 / 1024 from dba_segments where segment_name = 'TEST_SHRINK' ;


SEGMENT_NAME BYTES / 1024 / 1024
-------------------- ---------------
TEST_SHRINK 300.375



SQL > select * from v$sesstat where sid =( select distinct sid from v$mystat ) and statistic #=(select statistic# from v$statname where name='redo size');



SID STATISTIC # VALUE
---------- ---------- ----------

136 134 259857896



SQL > alter table test_shrink enable row movement ;


Table altered .


SQL > alter table test_shrink shrink space ;


Table altered .


SQL > select * from v$sesstat where sid =( select distinct sid from v$mystat ) and statistic #=(select statistic# from v$statname where name='redo size');



SID STATISTIC # VALUE
---------- ---------- ----------

136 134 541072808



SQL > select segment_name , bytes / 1024 / 1024 from dba_segments where segment_name = 'TEST_SHRINK' ;


SEGMENT_NAME BYTES / 1024 / 1024
-------------------- ---------------
TEST_SHRINK 175.875







SQL > select * from v$locked_object



XIDUSN XIDSLOT XIDSQN OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME PROCESS
---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------
LOCKED_MODE
-----------

9 4 145 10388 136 YXYUP oracle 11064

3









logminer日志



QL > select operation , sql_redo from v$logmnr_contents ;


OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
DELETE

delete from "YXYUP" . "TEST_SHRINK" where "OWNER" = 'SYS' and "OBJECT_NAME" = 'USE

R_QUEUE_PUBLISHERS' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = '4623' and "D

ATA_OBJECT_ID" IS NULL and "OBJECT_TYPE" = 'VIEW' and "CREATED" = TO_DATE ( '20-JA

N-07' , 'DD-MON-RR' ) and "LAST_DDL_TIME" = TO_DATE ( '20-JAN-07' , 'DD-MON-RR' ) and
"TIMESTAMP" = '2007-01-20:04:25:26' and "STATUS" = 'VALID' and "TEMPORARY" = 'N'

and "GENERATED" = 'N' and "SECONDARY" = 'N' and ROWID = 'AAACiUAAEAAANLOAAq' ;


INSERT



OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------
insert into "YXYUP" . "TEST_SHRINK" ( "OWNER" , "OBJECT_NAME" , "SUBOBJECT_NAME" , "OBJECT

_ID" , "DATA_OBJECT_ID" , "OBJECT_TYPE" , "CREATED" , "LAST_DDL_TIME" , "TIMESTAMP" , "STATU

S" , "TEMPORARY" , "GENERATED" , "SECONDARY" ) values ( 'SYS' , 'USER_QUEUE_PUBLISHERS' , NU

LL , '4623' , NULL , 'VIEW' , TO_DATE ( '20-JAN-07' , 'DD-MON-RR' ), TO_DATE ( '20-JAN-07' , 'DD

-MON-RR' ), '2007-01-20:04:25:26' , 'VALID' , 'N' , 'N' , 'N' );


DELETE

delete from "YXYUP" . "TEST_SHRINK" where "OWNER" = 'PUBLIC' and "OBJECT_NAME" = '

USER_QUEUE_PUBLISHERS' and "SUBOBJECT_NAME" IS NULL and "OBJECT_ID" = '4624' and


OPERATION
--------------------------------
SQL_REDO
--------------------------------------------------------------------------------

"DATA_OBJECT_ID" IS NULL and "OBJECT_TYPE" = 'SYNONYM' and "CREATED" = TO_DATE (
'20-JAN-07' , 'DD-MON-RR' ) and "LAST_DDL_TIME" = TO_DATE ( '20-JAN-07' , 'DD-MON-RR'
) and "TIMESTAMP" = '2007-01-20:04:25:26' and "STATUS" = 'VALID' and "TEMPORARY"

= 'N' and "GENERATED" = 'N' and "SECONDARY" = 'N' and ROWID = 'AAACiUAAEAAANLOA

Ar' ;


INSERT

insert into "YXYUP" . "TEST_SHRINK" ( "OWNER" , "OBJECT_NAME" , "SUBOBJECT_NAME" , "OBJECT

_ID" , "DATA_OBJECT_ID" , "OBJECT_TYPE" , "CREATED" , "LAST_DDL_TIME" , "TIMESTAMP" , "STATU



OPERATION

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

SQL_REDO

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

S" , "TEMPORARY" , "GENERATED" , "SECONDARY" ) values ( 'PUBLIC' , 'USER_QUEUE_PUBLISHERS'
, NULL , '4624' , NULL , 'SYNONYM' , TO_DATE ( '20-JAN-07' , 'DD-MON-RR' ), TO_DATE ( '20-JAN-07

' , 'DD-MON-RR' ), '2007-01-20:04:25:26' , 'VALID' , 'N' , 'N' , 'N' );



......................






可以看出shrink space 的整个过程都是在做delete和insert 操作 ,
感觉这个操作在生产DB不是逼不得已的时候 , 还是不用为好

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

转载于:http://blog.itpub.net/7364032/viewspace-17474/