熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何移動表達到減小數據文件大小的目的

2013-11-13 16:14:51  來源: Oracle 

  通過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) Oracle All 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
From:http://tw.wingwit.com/Article/program/Oracle/201311/18020.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.