如何收縮數(shù)據(jù)文件的大小？
下面這個(gè)實(shí)驗(yàn)用于描述如何通過move tablespace來(lái)完成resize datafile。
HWM的概念就不在此闡述了。
測(cè)試環(huán)境為Oracle10g for Linux，其它版本的一樣。
我們先創(chuàng)建兩個(gè)表空間，分別為t_tbs和t_tbs1，分別有一個(gè)數(shù)據(jù)文件，大小都是5M
再創(chuàng)建一個(gè)test_user用戶，給這個(gè)用戶上述兩個(gè)表空間的無(wú)限限額，并且設(shè)置默認(rèn)表空間是t_tbs。
[zhangleyi@as 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登錄，創(chuàng)建表
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
好，上面我們創(chuàng)建了一個(gè)表，并且插入了很多數(shù)據(jù)，通過dba_extents視圖我們可以看到總共用的block熟和總共的大小。
下面我們用delete刪除全部數(shù)據(jù)，并且插入新的9999條數(shù)據(jù)
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視圖，發(fā)現(xiàn)占用的空間并沒有減少。
我們嘗試resize這個(gè)數(shù)據(jù)文件，file#為6的是t_tbs表空間下面的數(shù)據(jù)文件
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.
我們發(fā)現(xiàn)想resize到4M不可以，但是resize到4500000就可以了，因?yàn)樯厦娌榭闯鰜?lái)的Total Size是4194304，這個(gè)值大于4M而小于4500000。
然后我們move這張表到t_tbs1表空間，這個(gè)表空間下面的數(shù)據(jù)文件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視圖，發(fā)現(xiàn)Total Size已經(jīng)變化了，此時(shí)已經(jīng)可以說明move表是會(huì)重新進(jìn)行block的整理的，同時(shí)也重置了HWM。
下面我們r(jià)esize這個(gè)數(shù)據(jù)文件。
SYS at orcl10>alter database datafile 8 resize 2M;
Database altered.
SYS at orcl10>host
[zhangleyi@as ORCL10]$ cd /oracle/oradata/ORCL10/datafile/
[zhangleyi@as 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 2105344 4月 13 21:21 TEST01.DBF（這是file#8）
-rw-r----- 1 zhangleyi dba 4513792 4月 13 21:20 test.dbf（這是file#6）
可以看到我們的目的已經(jīng)達(dá)到了。
在真實(shí)應(yīng)用中，我們可以將一個(gè)表空間中的所有object，全部move到一個(gè)新的表空間中，然后drop掉原來(lái)的表空間，再?gòu)拇疟P上刪除原來(lái)表空間中的數(shù)據(jù)文件。
至于如何得知HWM，我們可以通過analyze之后的數(shù)據(jù)字典得到，那么如果不進(jìn)行analyze的話，我們也可以運(yùn)行下面這個(gè)腳本。
這個(gè)腳本可以用于檢查一個(gè)object占有的總共block數(shù)和處于HWM之上的block數(shù)，這當(dāng)然也就知道了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