标题: 批量移动表到指定表空间 [打印本页] 作者: miss_puff 时间: 2019-3-22 09:13 标题: 批量移动表到指定表空间 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);