缩小oracle表空间（表空间2TB）

在生产环境上，由于每月/天都会有很多数据被解析插入数据库，导致数据量增长非常快，其中有一个表空间smp都达到了2.5TB，服务器的存储一共才4TB，而且当时被韵味的同时告知表空间扩展是不可逆，不能随便无限制的增长，而且太大的话，肯定会影响oracle的性能。所以需要清理过期的数据，不可能好几年前的数据也放到生产环境上，就算是银行，你差交易流水，网银也只是只能查询一年的而已，如果要查更多，只能去柜台办理。所以现在面临的问题就是：

一、清理过早的数据

1.查询smp下面所有的表

1. select t.table_name from user_tables t where t.tablespace_name=upper('smp');---我这里测试用的users，并针对包括testyk的表

2.筛选2014年的数据并删除

如果表名带有时间戳如：cms_content_video201401，直接删除这个表drop table cms_content_video201401。如果表比较多，如几百几千个，就可以写个存储过程执行一下。（如果表名里面没有这样的时间戳，那一般这种数据表的属性，它都有会有个时间字段的。）为保证过程、程序真实有效，模拟测试。

3.第一步：构造数据：

1. create table testyk(pid number,pname varchar2(32));
   
2. insert into testyk values(1,'yangkai1');
   
3. insert into testyk values(2,'yangkai2');
   
4. insert into testyk values(3,'yangkai3');
   
5. select * from testyk;
   
6. create table testyk11 as select * from test2;
7. create table testyk12 as select * from test2;
8. create table testyk13 as select * from test2;
9. select * from testyk2;
   
10. alter table testyk2 move tablespace users;---修改某个表所属的表空间

4.第二步：创建存储过程：

1. create or replace procedure pro_delete_tb is
   
2. cursor cur_del_tb is select t.table_name from user_tables t where t.tablespace_name=upper('users') and t.table_name like '%TESTYK%';
   
3. c_tb_name user_tables.table_name%type;
   
4. --c_tb_name cur_del_tb.table_name%type;
   
5. begin
   
6.   open cur_del_tb;
   
7.   loop
   
8.     fetch cur_del_tb into c_tb_name;
   
9.     exit when cur_del_tb%notfound;
   
10.     dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')||'要删除的表是：'||c_tb_name);
    
11. -- drop table c_tb_name; 存储过程不能直接执行ddl语句（create、delete、alter），只能执行dml语句。
    
12.     execute immediate 'drop table ' || c_tb_name;--table后面要跟个空格，否则就报错，这个问题我搞了2个小时才发现！！！
    
13.     dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')||c_tb_name||' 删除成功！');
    
14.   end loop;
    
15.   close cur_del_tb;
    
16. end pro_delete_tb;

5.执行测试：

1. Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
   
2. Connected as system@GLOBALYK AS SYSDBA
   
3. 
   
4. SQL> set serveroutput on;
   
5. SQL> set time on;
   
6. 22:26:43 SQL> set timing on;
   
7. 23:21:30 SQL> select t.table_name from user_tables t where t.tablespace_name=upper('users') and t.table_name like '%TESTYK%';
   
8. TABLE_NAME
   
9. ------------------------------
   
10. TESTYK11
    
11. TESTYK12
    
12. TESTYK13
    
13. Executed in 0.016 seconds
    
14. 
    
15. 23:21:57 SQL> exec pro_delete_tb();
    
16. 2015-04-30 23:22:13要删除的表是：TESTYK11
    
17. 2015-04-30 23:22:13TESTYK11 删除成功！
    
18. 2015-04-30 23:22:13要删除的表是：TESTYK12
    
19. 2015-04-30 23:22:13TESTYK12 删除成功！
    
20. 2015-04-30 23:22:13要删除的表是：TESTYK13
    
21. 2015-04-30 23:22:13TESTYK13 删除成功！
    
22. PL/SQL procedure successfully completed
    
23. Executed in 0.031 seconds
    
24. 
    
25. 23:22:13 SQL> select t.table_name from user_tables t where t.tablespace_name=upper('users') and t.table_name like '%TESTYK%';
    
26. TABLE_NAME
    
27. ------------------------------
    
28. Executed in 0.016 seconds
    
29. 
    
30. 23:22:15 SQL>

6.执行成功！

二、缩小表空间的大小，降低存储空间的使用

对于这一步，开始我真的以为是不能缩小，只能增大、增加数据文件呢，后来上网查了查不是的，就搜集来网上的方法。它主要是根据数据块、段的位置，进行了调整，就算数据删光了，也不能无限的压缩，主要依赖于max(block_id+blocks-1) HWM。好像这个位置还可以移动，具体操作比较复杂，暂时还没研究。

1. 计算datafile可以resize收缩的空间.
   
2. --col name for a40
   
3. --col resizecmd for a80
   
4. 
   
5. select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
   
6.        ceil(HWM * a.block_size)/1024/1024 ResizeTo,
   
7.        (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
   
8.        'alter database datafile '''||a.name||''' resize '||
   
9.        ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
   
10. from v$datafile a,
    
11.      (select file_id,max(block_id+blocks-1) HWM
    
12.        from dba_extents
    
13.        group by file_id) b
    
14. where a.file# = b.file_id(+)
    
15. and (a.bytes - HWM *block_size)>0
    
16. order by 5;
    
17. 
    
18. --里面包括这种语句，执行就好了alter database datafile 'D:APPADMINISTRATORORADATAGLOBALYKUNDOTBS01.DBF' resize 1963M;
    
19. -------------------------------------
    
20. 
    
21. 如果只是想对某个表个间的datafile resize,可采用:
    
22. select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
    
23.        ceil(HWM * a.block_size)/1024/1024 ResizeTo,
    
24.        (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
    
25.        'alter database datafile '''||a.name||''' resize '||
    
26.        ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
    
27. from v$datafile a,
    
28.      (select file_id,max(block_id+blocks-1) HWM
    
29.        from dba_extents where file_id in
    
30.               (select b.file# From v$tablespace a ,v$datafile b
    
31.                 where a.ts#=b.ts# and a.name='MP2000')
    
32.        group by file_id) b
    
33. where a.file# = b.file_id(+)
    
34. and (a.bytes - HWM *block_size)>0
    
35. order by 5;
    
36. --修改那个MP2000为要调整的表空间就可以了
    
37. -------------------------------------
    
38. 
    
39. 计划tempfile可以resize的空间.on apply that have only one tempfile
    
40. 
    
41. select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
    
42.      (select tmsize.maxblk*bk.value/1024/1024 siz from
    
43.          (select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
    
44.          (select value From v$parameter where name = 'db_block_size') bk) b

一开始这个脚本看不明白，可以试着差下里面用的视图、表的结构和含义，部分如下：

1. ceil和floor函数在一些业务数据的时候，有时还是很有用的。
   ceil(n) 取大于等于数值n的最小整数；
   floor(n)取小于等于数值n的最大整数
   如下例子
   SQL> select ceil(9.5) from dual;
    
    CEIL(9.5)
   ----------
           10
    
   SQL> select floor(9.5) from dual;
    
   FLOOR(9.5)
   ----------
            9
   ----------------------------------------------------
   
2. select (5+6-3)nu from dual;
   
3. select (8192/1024) from dual;
   
4. 
   
5. select count(distinct(block_id)) from dba_extents;--等于5994
   
6. select count(block_id) from dba_extents;--等于9754
   
7. --------------------------------------------------------------
   
8. select * from v$tablespace;
   
9. select * from dba_tablespaces;
   
10. select * from user_tablespaces;
    
11. select * from all_tablespaces;---没有all_tablespaces这个对象
    
12. select a.NAME,b.tablespace_name from v$tablespace a,user_tablespaces b where a.name=b.tablespace_name(+);---他俩是一样的
    
13. select * from v$datafile;
    
14. select * from dba_data_files;---查看表空间与数据文件的隶属关系
    
15. 
    

我针对system表空间，进行了一个数据文件的修改，可以成功，过程如下：

1. ----执行查询：
2. FILE#    NAME    CURRENTMB    RESIZETO    RELEASEMB    RESIZECMD
   
3. 1    4    D:APPADMINISTRATORORADATAGLOBALYKUSERS01.DBF    14352.5    518.9921875    13833.5078125    alter database datafile 'D:APPADMINISTRATORORADATAGLOBALYKUSERS01.DBF' resize 519M;
   
4. 
   
5. ---执行下条语句：
   
6. alter database datafile 'D:APPADMINISTRATORORADATAGLOBALYKUSERS01.DBF' resize 14000M;
   
7. ---再次执行查询：
   
8. 1    4    D:APPADMINISTRATORORADATAGLOBALYKUSERS01.DBF    14000    518.9921875    13481.0078125    alter database datafile 'D:APPADMINISTRATORORADATAGLOBALYKUSERS01.DBF' resize 519M;

另外一个简单易懂的方法步骤：

1. 1. 查该数据文件中数据处在最大位置
   
2. 
   
3. 　　　　select max(block_id) from dba_extents where file_id=15;
   
4. 
   
5. 　　　　　　max(block_id)
   
6. 
   
7. 　　　　　　　　383497
   
8. 
   
9. 　　　　查询file_id,也可在toad工具tablespace直接看到。
   
10. 
    
11. 　　　　select file#,name from v$datafile;
12. 
    
13. 2. 查出最大块位置
    
14. 
    
15. 　　　　select 383497*8/1024 from dual;
16. 
    
17. 　　　　　　2996.0703125
18. 
    
19. 　　　　这说明该文件中最大使用块位于2996M与3000M之间，
    
20. 
    
21. 3. 修改表空间（也可用toad工具界面修改）
22. 
    
23. 　　　　ALTER DATABASE TEMPFILE '/opt/oracle/oradata/gctwp101/temp02.dbf' RESIZE 3000M;

三、其他办法

还有一些其他的办法：

1.数据清洗之后，把剩下的数据导出来，然后把原来表空间及数据文件都删除。再重建表空间，把数据再倒入进来，比较麻烦点。

2.数据清洗之后，把剩下的数据对象，move到新建的表空间之后，再把原来的表空间及数据文件都删除。

1. SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' enable row movement; ' FROM dba_extents WHERE tablespace_name = 'USERS' AND segment_type = 'TABLE';
   
2. 
   
3. 执行select之后部分信息如下：
   
4. 1    alter table SCOTT,SALGRADE enable row movement;
   
5. 2    alter table YK,SMMAIDY enable row movement;
   
6. 3    alter table SYS,TESTYK2 enable row movement;
   
7. 4    alter table OE,PURCHASEORDER enable row movement;
   
8. 5    alter table YANGKAI,TESTA enable row movement;
   
9. 6    alter table SCOTT,DEPT enable row movement;
   
10. 7    alter table YK,TA enable row movement;
    
11. 8    alter table SCOTT,EMP enable row movement;

1. 1.允许表移动
   
2. SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' enable row movement; ' FROM dba_extents WHERE tablespace_name = 'gcomm_bk' AND segment_type = 'TABLE';
   
3. 2.释放存贮--针对delte
   
4. SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' shrink space cascade; ' FROM dba_extents WHERE tablespace_name = 'gcomm_bk' AND segment_type = 'TABLE';
   
5. 3.移动表到新的表空间
   
6. SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' move tablespace GCOMM2; ' FROM dba_extents WHERE tablespace_name = 'gcomm_bk' AND segment_type = 'TABLE';
   
7. 4.重建索引到新的表空间
   
8. SELECT DISTINCT 'alter INDEX '||owner||',' || segment_name ||' REBUILD TABLESPACE GCOMM2; ' FROM dba_extents WHERE tablespace_name = '"gcomm_bk"' AND segment_type = 'INDEX';
   
9. 5.移动大对象到新的表空间
   
10. select 'alter table '||owner||','||table_name||' move lob('||colmn_name||') store as (tablespace GCOMM2)' from dba_lobs
    
11. where segment_name in(select segment_name
    
12.                          from dba_extents
    
13.                          where tablespace_name ='gcomm_bk' and segment_type like 'LOB%')