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不是逼不得已的时候 , 还是不用为好
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7364032/viewspace-17473/