通过move tablespace来完成resize datafile
HWM的概念就不在此阐述了
测试环境为Oracleg for Linux其它版本的一样
我们先创建两个表空间分别为t_tbs和t_tbs分别有一个数据文件大小都是M
再创建一个test_user用户给这个用户上述两个表空间的无限限额并且设置默认表空间是t_tbs
[zhangleyi@as zhangleyi]$ sqlplus / as sysdba
SQL*Plus: Release Production on Tue Apr ::
Copyright (c) OracleAll rights reserved
Connected to:
Oracle Database g Enterprise Edition Release Production
With the Partitioning OLAP and Data Mining options
SYS at orcl>alter user test_user default tablespace t_tbs;
User altered
SYS at orcl>alter user test_user quota unlimited on t_tbs;
User altered
SYS at orcl>alter user test_user quota unlimited on t_tbs;
User altered
用test_user登录创建表
TEST_USER at orcl>create table t_obj as select * from dba_objects where rownum<;
Table created
TEST_USER at orcl>insert into t_obj select * from t_obj;
rows created
TEST_USER at orcl>/
rows created
TEST_USER at orcl>/
insert into t_obj select * from t_obj
*
ERROR at line :
ORA: unable to extend table TEST_USERT_OBJ by in tablespace T_TBS
TEST_USER at orcl>commit;
Commit complete
TEST_USER at orcl>select sum(blocks) Total Blockssum(bytes) Total Size from dba_extents where owner=TEST_USER and segment_name=T_OBJ;
Total Blocks Total Size
好上面我们创建了一个表并且插入了很多数据通过dba_extents视图我们可以看到总共用的block数和总共的大小
下面我们用delete删除全部数据并且插入新的条数据
TEST_USER at orcl>delete from t_obj;
rows deleted
TEST_USER at orcl>insert into t_obj select * from dba_objects where rownum<;
rows created
TEST_USER at orcl>commit;
Commit complete
TEST_USER at orcl>select sum(blocks) Total Blockssum(bytes) Total Size from dba_extents
where owner=TEST_USER and segment_name=T_OBJ;
Total Blocks Total Size
再次查看dba_extents视图发现占用的空间并没有减少
我们尝试resize这个数据文件file#为的是t_tbs表空间下面的数据文件
SYS at orcl>alter database datafile resize M;
alter database datafile resize M
*
ERROR at line :
ORA: file contains used data beyond requested RESIZE value
SYS at orcl>alter database datafile resize ;
Database altered
我们发现想resize到M不可以但是resize到就可以了因为上面查看出来的Total Size是这个值大于M而小于
然后我们move这张表到t_tbs表空间这个表空间下面的数据文件file#是
EST_USER at orcl>alter table t_obj move tablespace t_tbs;
Table altered
TEST_USER at orcl>select sum(blocks) Total Blockssum(bytes) Total Size from dba_extents
where owner=TEST_USER and segment_name=T_OBJ;
Total Blocks Total Size
我们检查dba_extents视图发现Total Size已经变化了此时已经可以说明move表是会重新进行block的整理的同时也重置了HWM
下面我们resize这个数据文件
SYS at orcl>alter database datafile resize M;
Database altered
SYS at orcl>host
[zhangleyi@as ORCL]$ cd /oracle/oradata/ORCL/datafile/
[zhangleyi@as datafile]$ ls l
总用量
rwr zhangleyi dba月 : cattbsdbf
rwr zhangleyi dba月 : o_mf_example_pgpoj_dbf
rwr zhangleyi dba月 : o_mf_sysaux_pkny_dbf
rwr zhangleyi dba月 : o_mf_system_pkno_dbf
rwr zhangleyi dba月 : o_mf_temp_pfzsd_tmp
rwr zhangleyi dba月 : o_mf_undotbs_pkog_dbf
rwr zhangleyi dba月 : o_mf_users_pkqv_dbf
rwr zhangleyi dba 月 : TESTDBF
rwr zhangleyi dba 月 : testdbf
可以看到我们的目的已经达到了
在真实应用中我们可以将一个表空间中的所有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_spaceunused_space(SCOTTBIGEMPTABLEv_total_blocksv_total_bytesv_unused_blocksv_unused_bytesv_last_used_extent_file_idv_last_used_extent_block_idv_last_used_block);
dbms_outputput_line(Total Blocks: ||TO_CHAR(v_total_blocks));
dbms_outputput_line(Blocks above HWM: ||TO_CHAR(v_unused_blocks));
END;
/
Total Blocks:
Blocks above HWM:
PL/SQL procedure successfully completed
Executed in seconds
