Oracle Spostamento Oggetti

controlla

select tablespace_name,segment_type,count(*),sum(bytes)/(1024*1024) from dba_segments group by tablespace_name,segment_type order by 2,1;

cerca ultimo oggetto nel tablespace/datafile

set pagesize 200
set linesize 200
select * from (
       select owner, segment_name, segment_type, block_id, file_id
         from dba_extents
        where file_id in ( select file_id
                             from dba_data_files
                            where tablespace_name = '&1' )
     order by block_id desc,file_id desc
) where rownum <= 10;

Spostamento Indici

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

prompt alter session set nls_length_semantics=BYTE;

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

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace &2 online nologging compute statistics;' from dba_indexes where tablespace_name='&1' or status='UNUSABLE';

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

select 'alter index ' || i.owner || '.' || i.index_name || ' rebuild tablespace ' || i.tablespace_name || ' nologging compute statistics;'
from dba_indexes i where i.status='UNUSABLE';

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

prompt alter session set nls_length_semantics=BYTE;

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';

select 'alter table ' || l.OWNER || '.' || l.TABLE_NAME || ' move lob(' || l.COLUMN_NAME || ') store as ( tablespace TBSLOB );'
 from dba_lobs l
 where l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') and l.column_name !='USER_PROP' and tablespace_name !='TBSLOB'
 order by l.OWNER ;

plsql

begin
for c1 in (select owner,table_name,column_name from dba_lobs where tablespace_name='&1')
loop
  begin
   execute immediate 'alter table ' || c1.owner || '.' || c1.table_name || ' move lob (' || c1.column_name || ') store as securefile (tablespace "&2" ) nologging';
   exception
    when others then
     null;
  end;
end loop;
end;
/

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;
/

segment Advisor

--
set pagesize 200
set linesize 200
set autotrace off
-- select tablespace_name, segment_name, segment_type, partition_name,
-- recommendations, c1 from
-- table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));

spool segadv.txt
select '/*' || recommendations || ' */ ' || c1 from
 table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE')) where c1 is not null order by segment_type,reclaimable_space;
spool off