如何移動表來達到減小數據文件大小的目的
2024-07-21 02:08:14
供稿：網友
author：kamus
mail：[email protected]
date：2004-4
通過move tablespace來完成resize datafile。
hwm的概念就不在此闡述了。
測試環境為oracle10g for linux，其它版本的一樣。
我們先創建兩個表空間，分別為t_tbs和t_tbs1，分別有一個數據文件，大小都是5m
再創建一個test_user用戶，給這個用戶上述兩個表空間的無限限額，并且設置默認表空間是t_tbs。
[[email protected] zhangleyi]$ sqlplus / as sysdba
sql*plus: release 10.1.0.2.0 - production on tue apr 13 21:01:25 2004
copyright (c) 1982, 2004, oracle. all rights reserved.
connected to:
oracle database 10g enterprise edition release 10.1.0.2.0 - production
with the partitioning, olap and data mining options
sys at orcl10>alter user test_user default tablespace t_tbs;
user altered.
sys at orcl10>alter user test_user quota unlimited on t_tbs;
user altered.
sys at orcl10>alter user test_user quota unlimited on t_tbs1;
user altered
用test_user登錄，創建表
test_user at orcl10>create table t_obj as select * from dba_objects where rownum<10000;
table created.
test_user at orcl10>insert into t_obj select * from t_obj;
9999 rows created.
test_user at orcl10>/
19998 rows created.
test_user at orcl10>/
insert into t_obj select * from t_obj
*
error at line 1:
ora-01653: unable to extend table test_user.t_obj by 128 in tablespace t_tbs
test_user at orcl10>commit;
commit complete.
test_user at orcl10>select sum(blocks) "total blocks",sum(bytes) "total size" from dba_extents where owner='test_user' and segment_name='t_obj';
total blocks total size
------------ ----------
512 4194304
好，上面我們創建了一個表，并且插入了很多數據，通過dba_extents視圖我們可以看到總共用的block數和總共的大小。
下面我們用delete刪除全部數據，并且插入新的9999條數據
test_user at orcl10>delete from t_obj;
39996 rows deleted.
test_user at orcl10>insert into t_obj select * from dba_objects where rownum<10000;
9999 rows created.
test_user at orcl10>commit;
commit complete.
test_user at orcl10>select sum(blocks) "total blocks",sum(bytes) "total size" from dba_extents
2 where owner='test_user' and segment_name='t_obj';
total blocks total size
------------ ----------
512 4194304
再次查看dba_extents視圖，發現占用的空間并沒有減少。
我們嘗試resize這個數據文件，file#為6的是t_tbs表空間下面的數據文件
sys at orcl10>alter database datafile 6 resize 4m;
alter database datafile 6 resize 4m
*
error at line 1:
ora-03297: file contains used data beyond requested resize value
sys at orcl10>alter database datafile 6 resize 4500000;
database altered.
我們發現想resize到4m不可以，但是resize到4500000就可以了，因為上面查看出來的total size是4194304，這個值大于4m而小于4500000。
然后我們move這張表到t_tbs1表空間，這個表空間下面的數據文件file#是8
est_user at orcl10>alter table t_obj move tablespace t_tbs1;
table altered.
test_user at orcl10>select sum(blocks) "total blocks",sum(bytes) "total size" from dba_extents
2 where owner='test_user' and segment_name='t_obj';
total blocks total size
------------ ----------
128 1048576
我們檢查dba_extents視圖，發現total size已經變化了，此時已經可以說明move表是會重新進行block的整理的，同時也重置了hwm。
下面我們resize這個數據文件。
sys at orcl10>alter database datafile 8 resize 3m;
database altered.
sys at orcl10>host
[[email protected] orcl10]$ cd /oracle/oradata/orcl10/datafile/
[[email protected] datafile]$ ls -l
總用量 1419076
-rw-r----- 1 zhangleyi dba 20979712 4月 13 21:17 cattbs01.dbf
-rw-r----- 1 zhangleyi dba 157294592 4月 13 21:17 o1_mf_example_02p0gpoj_.dbf
-rw-r----- 1 zhangleyi dba 419438592 4月 13 21:20 o1_mf_sysaux_02p09kny_.dbf
-rw-r----- 1 zhangleyi dba 555753472 4月 13 21:17 o1_mf_system_02p09kno_.dbf
-rw-r----- 1 zhangleyi dba 20979712 4月 13 21:02 o1_mf_temp_02p0fzsd_.tmp
-rw-r----- 1 zhangleyi dba 62922752 4月 13 21:20 o1_mf_undotbs1_02p09kog_.dbf
-rw-r----- 1 zhangleyi dba 209723392 4月 13 21:17 o1_mf_users_02p09kqv_.dbf
-rw-r----- 1 zhangleyi dba 3153920 4月 13 21:21 test01.dbf
-rw-r----- 1 zhangleyi dba 4513792 4月 13 21:20 test.dbf
可以看到我們的目的已經達到了。
在真實應用中，我們可以將一個表空間中的所有object，全部move到一個新的表空間中，然后drop掉原來的表空間，再從磁盤上刪除原來表空間中的數據文件。
至于如何得知hwm，我們可以通過analyze之后的數據字典得到，那么如果不進行analyze的話，我們也可以運行下面這個腳本。
這個腳本可以用于檢查一個object占有的總共block數和處于hwm之上的block數，這當然也就知道了hwm是在什么位置。
declare
v_total_blocks number;
v_total_bytes number;
v_unused_blocks number;
v_unused_bytes number;
v_last_used_extent_file_id number;
v_last_used_extent_block_id number;
v_last_used_block number;
begin
dbms_space.unused_space('scott','bigemp','table',v_total_blocks,v_total_bytes,v_unused_blocks,v_unused_bytes,v_last_used_extent_file_id,v_last_used_extent_block_id,v_last_used_block);
dbms_output.put_line('total blocks: '||to_char(v_total_blocks));
dbms_output.put_line('blocks above hwm: '||to_char(v_unused_blocks));
end;
/
total blocks: 256
blocks above hwm: 0
pl/sql procedure successfully completed
executed in 0.01 seconds
