move&shrink--空间释放
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST3 11 88 .0625
TEST4 11 88 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> select count(*) from test3;
COUNT(*)
----------
50000
SQL> delete from test3 where rownum<=20000;
20000 rows deleted.
SQL> analyze table test3 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST3 11 88 .0625
TEST4 11 88 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test3;
USED_BLOCKS
-----------
46
SQL> alter table test3 move;
Table altered.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 80 8
TEST4 80 8
SQL> analyze table test3 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 80 8
SQL> delete from test4 where rownum<=20000;
20000 rows deleted.
SQL> analyze table test4 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 11 88 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 80 8
SQL> alter table test4 enable row movement;
Table altered.
SQL> alter table test4 shrink space;
Table altered.
SQL> analyze table test4 compute statistics;
Table analyzed.
SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGMENT_NAME in (TEST3,TEST4);
SEGMENT_NAME EXTENTS BLOCKS INIT
-------------------- ---------- ---------- ----------
TEST4 7 56 .0625
TEST3 7 56 .0625
SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name in (TEST3,TEST4);
TABLE_NAME BLOCKS EMPTY_BLOCKS
-------------------- ---------- ------------
TEST3 52 4
TEST4 46 10
move&shrink--索引
SQL> select rowid,id from test5;
ROWID ID
------------------ ----------
AAAVytAAEAAAC5jAAA 1
AAAVytAAEAAAC5jAAB 2
AAAVytAAEAAAC5jAAC 3
AAAVytAAEAAAC5jAAD 4
AAAVytAAEAAAC5lAAA 1
AAAVytAAEAAAC5lAAB 2
AAAVytAAEAAAC5lAAC 3
AAAVytAAEAAAC5lAAD 4
AAAVytAAEAAAC5lAAE 5
AAAVytAAEAAAC5lAAF 6
AAAVytAAEAAAC5lAAG 7
AAAVytAAEAAAC5lAAH 8
AAAVytAAEAAAC5lAAI 9
AAAVytAAEAAAC5lAAJ 10
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVytAAEAAAC5jAAA 11875
AAAVytAAEAAAC5jAAB 11875
AAAVytAAEAAAC5jAAC 11875
AAAVytAAEAAAC5jAAD 11875
AAAVytAAEAAAC5lAAA 11877
AAAVytAAEAAAC5lAAB 11877
AAAVytAAEAAAC5lAAC 11877
AAAVytAAEAAAC5lAAD 11877
AAAVytAAEAAAC5lAAE 11877
AAAVytAAEAAAC5lAAF 11877
AAAVytAAEAAAC5lAAG 11877
AAAVytAAEAAAC5lAAH 11877
AAAVytAAEAAAC5lAAI 11877
AAAVytAAEAAAC5lAAJ 11877
SQL> delete from test5 where id >2;
10 rows deleted.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVytAAEAAAC5jAAA 11875
AAAVytAAEAAAC5jAAB 11875
AAAVytAAEAAAC5lAAA 11877
AAAVytAAEAAAC5lAAB 11877
SQL> alter table test5 move;
Table altered.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test5;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVywAAEAAAC5zAAA 11891
AAAVywAAEAAAC5zAAB 11891
AAAVywAAEAAAC5zAAC 11891
AAAVywAAEAAAC5zAAD 11891
SQL> select index_name,status from user_indexes where index_name=T5;
INDEX_NAME STATUS
------------------------------ --------
T5 UNUSABLE
SQL> alter index t5 rebuild;
Index altered.
SQL> select index_name,status from user_indexes where index_name=T5;
INDEX_NAME STATUS
------------------------------ --------
T5 VALID
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAC 11867
AAAVysAAEAAAC5bAAD 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867
AAAVysAAEAAAC5bAAG 11867
AAAVysAAEAAAC5bAAH 11867
AAAVysAAEAAAC5bAAI 11867
AAAVysAAEAAAC5bAAJ 11867
AAAVysAAEAAAC5bAAK 11867
AAAVysAAEAAAC5bAAL 11867
AAAVysAAEAAAC5bAAM 11867
AAAVysAAEAAAC5bAAN 11867
SQL> delete from test6 where id >2;
10 rows deleted.
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867
SQL> alter table test6 shrink space;
Table altered.
SQL> select index_name,status from user_indexes where index_name=T6;
INDEX_NAME STATUS
------------------------------ --------
T6 VALID
SQL> select rowid,Dbms_Rowid.rowid_block_number(rowid) from test6;
ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------ ------------------------------------
AAAVysAAEAAAC5bAAA 11867
AAAVysAAEAAAC5bAAB 11867
AAAVysAAEAAAC5bAAE 11867
AAAVysAAEAAAC5bAAF 11867
move&shrink--表空间不足
SQL> create tablespace move datafile /oracle/files/move.dbf size 50M autoextend off;
Tablespace created.
SQL> create table test_move (id number) tablespace move;
Table created.
SQL> create tablespace shrink datafile /oracle/files/shrink.dbf size 50M autoextend off;
Tablespace created.
SQL> create table test_move (id number) tablespace move;
Table created
SQL> create table test_shrink (id number) tablespace shrink;
Table created.
SQL> declare
i number:=1;
begin
for i in 1..500000 loop
insert into scott.test_move (id) values(i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name=TEST_MOVE;
SUM(BYTES)/1024/1024
--------------------
31
SQL> delete from test_move where id < 2000;
9995 rows deleted.
SQL> alter table test_move move;
alter table test_move move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace MOVE
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name=TEST_SHRINK;
SUM(BYTES)/1024/1024
--------------------
31
SQL> delete from test_shrink where id < 2000;
9995 rows deleted.
SQL> alter table test_shrink enable row movement;
Table altered.
SQL> alter table test_shrink shrink space;
Table altered.
SQL> create tablespace move1 datafile /oracle/files/move1.dbf size 60M autoextend off;
Tablespace created.
SQL> alter table test_move move tablespace move1;
Table altered.
文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。
转载请注明本文地址:https://www.ucloud.cn/yun/129697.html
❤️ 一条独家专栏 ⭐️ 搞技术,进大厂,聊人生 ?《大厂面试突击》——面试10多家中大厂的万字总结 ?《技术专家修炼》——高薪必备,企业真实场景 ?《leetcode 300题》——每天一道算法题,进大厂必备 ?《糊涂算法》——数据结构+算法全面讲解 ?《从实战学python》——python的各种应用 ?《程序人生》——听一条聊职场,聊人生 ?更多资料点这里 天下难事,必作于易;天下大事,...
阅读 1346·2023-01-11 13:20
阅读 1684·2023-01-11 13:20
阅读 1132·2023-01-11 13:20
阅读 1858·2023-01-11 13:20
阅读 4099·2023-01-11 13:20
阅读 2704·2023-01-11 13:20
阅读 1385·2023-01-11 13:20
阅读 3594·2023-01-11 13:20