工作日志备查——表空间收缩

action: 使用此方法，有些表空间不能shrink  , db_block_size=8192  举例：tsp_equipment表空间   ,

1.用它可以显示出数据库中所有表空间的状态，如表空间的大小、已使用空间、使用的百分比、空闲空间数及现在表空间的最大块是多大。

conn internal/oracle
set pagesize 200
col "表空间名" for a20
SELECT upper(f.tablespace_name) "表空间名",
d.Tot_grootte_Mb "表空间大小(M)",
d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",
to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2),'990.99') "使用比",
f.total_bytes "空闲空间(M)",
f.max_bytes "最大块(M)"
FROM
(SELECT tablespace_name,
round(SUM(bytes)/(1024*1024),2) total_bytes,
round(MAX(bytes)/(1024*1024),2) max_bytes
FROM sys.dba_free_space
GROUP BY tablespace_name) f,
(SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb
FROM
sys.dba_data_files dd
GROUP BY dd.tablespace_name) d
WHERE d.tablespace_name = f.tablespace_name
ORDER BY 4 DESC;

2.  首先查询出max(block_id)，这个是hwm的临近点

conn internal/oracle
col segment_name for a30
 select   segment_name,   max(block_id)    
  from   dba_extents    
   where   tablespace_name=upper('tsp_equipment')   
   group   by   segment_name   order   by   2  ;

3.使用以下命令resize 表空间大小
alter database datafile 'D:\Oracle\oradata\ora816\APequipment .DBF'      resize 473m;  [resize后的数字为max(block_id)*8192/1024/1024要大点]

注：有些表空间中存在long，lob的大字段，造成shrink操作步骤复杂，对于短时间操作困难
以上操作要在实际中观察，本地测试通过。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

select   'alter   table   '||object_name||'   move   tablespace   data;'  

from   dba_objects 
where   owner=upper('scott')   and   object_type='TABLE';


select   'alter   index   '||index_name||'   rebuild;'   from   dba_indexes 
where   owner=upper('scott') ;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
 message: 

you have extents of a segment that are beyond the value you want to   resizeto.     
eg.    
  i have  a tablespace with 1 datafile, 10mb large.    
  my blocksize is  8192  bytes,  so i  have  1280  blocks    
   
  select   blocks,   bytes/1024/1024    
  from   dba_data_files    
  where   tablespace_name=tools    

  sql>   /    
   
  blocks   bytes/1024/1024    
  ----------   ---------------    
  1280   10    
   
  now,   what   is   the   largest   minimum   size   i   can   shrink   my   datafile   to?    
  i   have   to   query   dba_extents   for   that:    
   
  select   segment_name,   max(block_id)    
  from   dba_extents    
  where   tablespace_name=tools    
  group   by   segment_name   order   by   2    
   
  ...    
  rcver_version_unique   769    
   
  this   means   i   have   data   in   block   769   of   1280   blocks,   so   i   can   shrink   back   to  

769   *   8196   (blocksize)   =   6,3   mb    
  you   can   of   course   use   file_id   instead   of   tablespace_name...    
   
  sql>   alter   database   datafile   c:\oracle\oradata\ora920\tools01.dbf   resize   6m;    
  alter   database   datafile   c:\oracle\oradata\ora920\tools01.dbf   resize   6m    
  ora-03297   file   contains   used   data   beyond   requested   resize   value    
   
  sql>   alter   database   datafile   c:\oracle\oradata\ora920\tools01.dbf   resize   7m;    
  -->   succeeded    
   
  if   this   segment   is   an   index   segment   you   can   try   to   rebuild   the   index:    
  alter   index      rebuild.    
   
  if   this   segment   is   a   table   you   can   move   it   around:    
  alter   table      move   tablespace   ;    
  and   back    
  alter   table      move   tablespace   ;    
   
  do   not   forget   to   rebuild   the   indexes   after   this,   they   will   be   in   an   unusable  

state.    
   
  you   can   also   move   the   table   by   exporting/importing   it.    
 
resize只能减小到   高位   以上的大小