完整一点的话: ALTER INDEX index_name REBUILD TABLESPACE new_tablespace ONLINE NOLOGGING STORAGE (INITIAL 10K NEXT 10K)
问题是我现在所有的索引都是建立在USERS表空间中,那不是要执行n次上面的脚本吗?
那你可以写个过程,把所有的索引都取出来,一个循环,执行动态sql就ok了
如: type c_curtype is ref cursor; c_cur c_curtype; v_index_name; v_sql string(1000):=''; begin open c_cur for select ........; loop fetch c_cur into v_index_name; exit when c_cur%notfound; v_sql:='....'; execute immediate v_sql; end loop; end;
select 部分大概就是这样的: select index_name from USER_INDEXES where tablespace_name='USERS' AND table_owner='YOUR_USRE_NAME' 我想你已经知道如何作了吧!
SQL> select 'alter INDEX '||INDEX_NAME||' rebuild ONLINE TABLESPACE newspace;' FROM DBA_INDEXES WHERE tablespace_name='USERS';
ALTER INDEX index_name REBUILD
TABLESPACE new_tablespace
ONLINE
NOLOGGING
STORAGE (INITIAL 10K NEXT 10K)
type c_curtype is ref cursor;
c_cur c_curtype;
v_index_name;
v_sql string(1000):='';
begin
open c_cur for select ........;
loop
fetch c_cur into v_index_name;
exit when c_cur%notfound;
v_sql:='....';
execute immediate v_sql;
end loop;
end;
select index_name from USER_INDEXES
where tablespace_name='USERS' AND table_owner='YOUR_USRE_NAME'
我想你已经知道如何作了吧!