ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中移动表 -- 达到缩小数据文件大小的目的

Oracle中移动表 -- 达到缩小数据文件大小的目的

原创 Linux操作系统 作者:tolywang 时间:2007-07-11 00:00:00 0 删除 编辑
通过move tablespace来完成resize datafile。
  
  HWM的概念就不在此阐述了(参考:
http://afy.itpub.net/post/48/70865 )。
  
  测试环境为Oracle10g for Linux,其它版本的一样。
  
  我们先创建两个表空间,分别为t_tbs和t_tbs1,分别有一个数据文件,大小都是5M


  
  再创建一个test_user用户,给这个用户上述两个表空间的无限限额,并且设置默认表空间是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登录,创建表
  
  TEST_USER at orcl10>create table t_obj as select * from dba_objects where rownuminsert 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 rownumcommit;
  
  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
  [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    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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-84756/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13380034