批量移动表到指定表空间

miss_puff

set serveroutput on
declare
sql_text varchar2(1000);
begin
for tb_u in (select distinct owner,table_name from dba_tables where owner in ('SCOTT') AND TABLESPACE_NAME='USERS') loop
sql_text:= 'alter table '|| tb_u.owner||'.'|| tb_u.table_name||' move tablespace ts_mes;';
dbms_output.put_line(sql_text);


for tb_us_idx in (select owner,index_name from dba_indexes where table_owner=tb_u.owner and table_name=tb_u.table_name) loop
sql_text:='alter index '|| tb_us_idx.owner||'.'|| tb_us_idx.index_name||' rebuild TABLESPACE ts_mes nologging;';
dbms_output.put_line(sql_text);


sql_text:='alter index '||tb_us_idx.owner||'.'||tb_us_idx.index_name||' logging;';
dbms_output.put_line(sql_text);


end loop;
end loop;
END;