转移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;