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

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

一、清理过早的数据

1.查询smp下面所有的表

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.第一步：构造数据：

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

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

create or replace procedure pro_delete_tb is
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%';
c_tb_name user_tables.table_name%type;
--c_tb_name cur_del_tb.table_name%type;
begin
  open cur_del_tb;
  loop
    fetch cur_del_tb into c_tb_name;
    exit when cur_del_tb%notfound;
    dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')||'要删除的表是：'||c_tb_name);
--    drop table c_tb_name; 存储过程不能直接执行ddl语句（create、delete、alter），只能执行dml语句。
    execute immediate 'drop table ' || c_tb_name;--table后面要跟个空格，否则就报错，这个问题我搞了2个小时才发现！！！
    dbms_output.put_line(to_char(sysdate,'yyyy-mm-dd HH24:mi:ss')||c_tb_name||' 删除成功！');
  end loop;
  close cur_del_tb;
end pro_delete_tb;

5.执行测试：

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system@GLOBALYK AS SYSDBA

SQL> set serveroutput on;
SQL> set time on;
22:26:43 SQL> set timing on;
23:21:30 SQL> select t.table_name from user_tables t where t.tablespace_name=upper('users') and t.table_name like '%TESTYK%';
TABLE_NAME
------------------------------
TESTYK11
TESTYK12
TESTYK13
Executed in 0.016 seconds

23:21:57 SQL> exec pro_delete_tb();
2015-04-30 23:22:13要删除的表是：TESTYK11
2015-04-30 23:22:13TESTYK11 删除成功！
2015-04-30 23:22:13要删除的表是：TESTYK12
2015-04-30 23:22:13TESTYK12 删除成功！
2015-04-30 23:22:13要删除的表是：TESTYK13
2015-04-30 23:22:13TESTYK13 删除成功！
PL/SQL procedure successfully completed
Executed in 0.031 seconds

23:22:13 SQL> select t.table_name from user_tables t where t.tablespace_name=upper('users') and t.table_name like '%TESTYK%';
TABLE_NAME
------------------------------
Executed in 0.016 seconds

23:22:15 SQL>

6.执行成功！

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

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

计算datafile可以resize收缩的空间.
--col name for a40
--col resizecmd for a80

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
       'alter database datafile '''||a.name||''' resize '||
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
     (select file_id,max(block_id+blocks-1) HWM
       from dba_extents
       group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5;

--里面包括这种语句，执行就好了alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\GLOBALYK\UNDOTBS01.DBF' resize 1963M;
-------------------------------------

如果只是想对某个表个间的datafile resize,可采用:
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
       'alter database datafile '''||a.name||''' resize '||
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
     (select file_id,max(block_id+blocks-1) HWM
       from dba_extents where file_id in
              (select b.file# From v$tablespace a ,v$datafile b
                where a.ts#=b.ts# and a.name='MP2000')
       group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5;
--修改那个MP2000为要调整的表空间就可以了
-------------------------------------

计划tempfile可以resize的空间.on apply that have only one tempfile

select 'alter database tempfile '''||a.name ||''' reize '||b.siz||'M;' from v$tempfile a,
     (select tmsize.maxblk*bk.value/1024/1024 siz from
         (select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
         (select value From v$parameter where name = 'db_block_size') bk) b

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

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
			
				----------------------------------------------------
select (5+6-3)nu from dual;
select (8192/1024) from dual;

select count(distinct(block_id)) from dba_extents;--等于5994
select count(block_id) from dba_extents;--等于9754
--------------------------------------------------------------
select * from v$tablespace;
select * from dba_tablespaces;
select * from user_tablespaces;
select * from all_tablespaces;---没有all_tablespaces这个对象
select a.NAME,b.tablespace_name from v$tablespace a,user_tablespaces b where a.name=b.tablespace_name(+);---他俩是一样的
select * from v$datafile;
select * from dba_data_files;---查看表空间与数据文件的隶属关系

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

----执行查询：
FILE#    NAME    CURRENTMB    RESIZETO    RELEASEMB    RESIZECMD
1    4    D:\APP\ADMINISTRATOR\ORADATA\GLOBALYK\USERS01.DBF    14352.5    518.9921875    13833.5078125    alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\GLOBALYK\USERS01.DBF' resize 519M;

---执行下条语句：
alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\GLOBALYK\USERS01.DBF' resize 14000M;
---再次执行查询：
1    4    D:\APP\ADMINISTRATOR\ORADATA\GLOBALYK\USERS01.DBF    14000    518.9921875    13481.0078125    alter database datafile 'D:\APP\ADMINISTRATOR\ORADATA\GLOBALYK\USERS01.DBF' resize 519M;

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

1. 查该数据文件中数据处在最大位置

　　　　select max(block_id) from dba_extents where file_id=15;

　　　　　　max(block_id)

　　　　　　　　383497

　　　　查询file_id,也可在toad工具tablespace直接看到。

　　　　select file#,name from v$datafile;

2. 查出最大块位置

　　　　select 383497*8/1024 from dual;

　　　　　　2996.0703125

　　　　这说明该文件中最大使用块位于2996M与3000M之间，

3. 修改表空间（也可用toad工具界面修改）

　　　　ALTER DATABASE TEMPFILE '/opt/oracle/oradata/gctwp101/temp02.dbf' RESIZE 3000M;

三、其他办法

还有一些其他的办法：
1.数据清洗之后，把剩下的数据导出来，然后把原来表空间及数据文件都删除。再重建表空间，把数据再倒入进来，比较麻烦点。
2.数据清洗之后，把剩下的数据对象，move到新建的表空间之后，再把原来的表空间及数据文件都删除。

SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' enable row movement; ' FROM dba_extents WHERE tablespace_name = 'USERS' AND segment_type = 'TABLE';

执行select之后部分信息如下：
1    alter table SCOTT,SALGRADE enable row movement;
2    alter table YK,SMMAIDY enable row movement;
3    alter table SYS,TESTYK2 enable row movement;
4    alter table OE,PURCHASEORDER enable row movement;
5    alter table YANGKAI,TESTA enable row movement;
6    alter table SCOTT,DEPT enable row movement;
7    alter table YK,TA enable row movement;
8    alter table SCOTT,EMP enable row movement;

参考这里
http://blog.csdn.net/aptweasel/article/details/8988427

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

今天就先这样子了，该睡觉了。2015-04-30 23:56