Oracle Spostamento Oggetti

Spostamento Indici

set pagesize 200
set linesize 200
set trim on
set trims on

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace GW_IDX online nologging compute statistics;' from dba_indexes where tablespace_name='GW_DAT';

prompt 'ALTER SESSION SET ddl_lock_timeout=30;'
select 'alter index ' || owner || '.' || index_name || ' logging;' from dba_indexes where LOGGING='NO' and owner !='PERFSTAT';

Metodo PL/SQL:

begin
for c1 in (select index_name from dba_indexes where owner='PERFSTAT' and tablespace_name='USERS')
loop
  begin
   execute immediate 'alter index perfstat.' || c1.index_name || ' rebuild tablespace "PERFSTAT" nologging';
   exception
    when others then
     null;
  end;
end loop;
end;
/

Spostamento LOB

ALTER TABLE table_name MOVE LOB (lob_column) STORE AS (TABLESPACE tablespace_name);

select 'alter table ' || owner || '.' || TABLE_NAME || ' move lob (' || COLUMN_NAME || ') store as (tablespace &2);' from dba_lobs where TABLESPACE_NAME='&1'

Spostamento Tabelle

begin
for c1 in (select table_name from dba_tables where owner='PERFSTAT' and tablespace_name='USERS')
loop
  begin
   execute immediate 'alter table perfstat.' || c1.table_name || ' move tablespace "PERFSTAT" nologging';
   exception
    when others then
     null;
  end;
end loop;
end;
/