转移LOB字段的表空间

--部署时若要导入的表空间名称和开发机上的不一致，而你的表中有LOB字段，那头就大了。--能治好这种头大症的代码如下：

create or replace procedure volumeChangeTableAndIndex
(
tablespacename   in varchar2
)
as
  cursor tablecur is select table_name from user_all_tables;
  cursor indexcur is select index_name,tablespace_name from user_indexes order by index_name;
  cursor lobtables is select table_name,column_name from user_tab_cols t where t.data_type like '%LOB%';
begin
  --修改当前用户所有表的表空
  for tablesname in tablecur loop
      execute immediate 'alter table '||tablesname.table_name ||' move tablespace '||tablespacename;
  end loop;

  --修改当前用户所有Lob字段的表空间
  for lobtable in lobtables loop
       execute immediate 'ALTER TABLE  '||lobtable.table_name||'  MOVE LOB('||lobtable.column_name||') STORE AS(TABLESPACE '||tablespacename||')';
  end loop;

  --重建索引
  for indexsname in indexcur loop
     if indexsname.tablespace_name <> Upper(tablespacename) then
        dbms_output.put_line(indexsname.index_name);
        execute immediate 'alter index '||indexsname.index_name ||' rebuild tablespace '||tablespacename;
      end if;
  end loop;
end;