shrink space 的整個過程就是在做delete和insert

yxyup發表於2007-08-26
delete
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-17473/，如需轉載，請註明出處，否則將追究法律責任。

相關文章