Moving an object to another schema might be a tiring activity especially if you need to move multiple objects at once and keep all settings the same.
DECLARE
v_curr_tbs VARCHAR2(50);
v_dest_tbs VARCHAR2(50);
v_sql VARCHAR2(1000);
-- separator definition
v_rpad_len INT;
v_rpad_sep CHAR;
-- debug mode setting
v_debug SMALLINT;
BEGIN
v_curr_tbs := 'YOUR_ACTUAL_TABLESPACE';
v_dest_tbs := 'YOUR_NEW_TABLESPACE';
v_rpad_len INT := 50;
v_rpad_sep CHAR(1) := '-';
v_debug SMALLINT := 1; -- 1 = print only; 0 = print and execute
-- get the unique list of users / schemas / owners
FOR list_owner IN (SELECT DISTINCT owner
FROM dba_segments
WHERE 1=1
AND owner IN ('OWNER1','OWNER2')
AND tablespace_name = v_curr_tbs)
LOOP
-- migrate
dbms_output.put_line('TABLES:');
dbms_output.put_line(rpad('',v_rpad_len, v_rep_sep);
FOR list_tables IN (SELECT table_name
,iot_name
FROM dba_tables
WHERE owner = list_owner.owner
AND tablespace_name = v_curr_tbs)
LOOP
IF list_tables.iot_name IS NOT NULL THEN
v_sql := 'ALTER TABLE "' || list_owner.owner || '"."' || list_tables.iot_name || '" MOVE TABLESPACE ' || v_dest_tbs;
dbms_output.put_line(v_sql);
IF debug = 0 THEN EXECUTE IMMEDIATE v_sql END;
ELSE
v_sql := 'ALTER TABLE "' || list_owner.owner || '"."' || list_tables.table_name || '" MOVE TABLESPACE ' || v_dest_tbs;
dbms_output.put_line(v_sql);
IF debug = 0 THEN EXECUTE IMMEDIATE v_sql END;
END IF;
END LOOP;
dbms_output.put_line(' ');
dbms_output.put_line('LOBS:');
dbms_output.put_line(rpad('',v_rpad_len, v_rep_sep);
FOR list_lobs IN (SELECT table_name
,column_name
FROM dba_lobs
WHERE owner = list_owner.owner
AND tablespace_name = v_curr_tbs)
LOOP
v_sql := 'ALTER TABLE ' || list_owner.owner || '.' || list_lobs.table_name || ' MOVE LOB(' || list_lobs.column_name || ') STORE AS (TABLESPACE ' || v_dest_tbs|| ')';
dbms_output.put_line(v_sql);
IF debug = 0 THEN EXECUTE IMMEDIATE v_sql END;
END LOOP;
dbms_output.put_line(' ');
dbms_output.put_line('INDEXES:');
dbms_output.put_line(rpad('',v_rpad_len, v_rep_sep);
FOR list_indexes IN (SELECT segment_name
,segment_type
FROM dba_segments
WHERE owner = list_owner.owner
AND tablespace_name = v_curr_tbs
AND segment_type = 'INDEX')
LOOP
v_sql := 'ALTER INDEX ' || list_owner.owner || '."' || list_indexes.segment_name || '" REBUILD TABLESPACE ' || v_dest_tbs;
dbms_output.put_line(v_sql);
IF debug = 0 THEN EXECUTE IMMEDIATE v_sql END;
END LOOP;
END LOOP;
END;
/