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

-- la rebuild di indici su colonne varchar bytes viene eseguita in varchar char se nls_semantics a char.
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 owner, index_name from dba_indexes where owner='PERFSTAT' and tablespace_name='USERS')
loop
  begin
   execute immediate 'alter index "' || c1.owner || '"."' || c1.index_name || '" rebuild tablespace "PERFSTAT" online nologging';
   exception
    when others then
     null;
  end;
end loop;
end;
/

Spostamento LOB

Spazio occupato dai LOB

--
set pagesize 200 linesize 200 trim on trims on
col owner for a20
col segment_name for a25
col column_name for a30
col table_name for a30
col tablespace_name for a15
col comp for a6
col dedup for a4
select l.owner,l.segment_name,s.tablespace_name,l.column_name,l.table_name,
s.bytes/(1024*1024) mb,l.compression comp,l.deduplication dedup,l.logging,l.securefile
 from dba_lobs l inner join dba_segments s on (l.owner=s.owner and l.segment_name=s.segment_name)
 and s.tablespace_name not in ('SYSTEM','SYSAUX')
 order by 6;

Comandi Spostamento LOB

con nls_length_semantics a char ci sono brutti effetti collaterali:

prompt alter session set nls_length_semantics=BYTE;

Sintassi

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

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

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

utilizzo funzione listagg per spostare tutti i lob di una tabella con un solo comando

select 'alter table ' || l.owner || '.' || l.table_name || ' move lob (' ||
 listagg(l.column_name,') store as securefile (nocache nologging) lob (')
    within group (order by l.column_name) ||
 ') store as securefile (nocache nologging) nologging;'
from dba_lobs l where l.securefile='NO'
and l.owner not in ('SYS','SYSTEM','XDB','OUTLN','WMSYS','AUDSYS') 
group by l.owner,l.table_name;

compressione e deduplica richiedono avanced compression. La clausola *online*, oltre a richiedere EE, utilizza un grosso quantitativo di spazio per segmenti temporanei sul tablespace sorgente:

select 'alter table ' || l.OWNER || '.' || l.TABLE_NAME || ' move lob (' || l.COLUMN_NAME || ') store as securefile (compress high deduplicate nocache nologging tablespace TBSLOB ) online;'
 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 ;

per riportare in logging:

select 'alter table ' || l.OWNER || '.' || l.TABLE_NAME || ' modify lob(' || l.COLUMN_NAME || ') (cache logging );'
 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 ;

--
set pagesize 200 linesize 200 trim on trims on
col owner for a10
col segment_name for a30
col column_name for a20
col table_name for a30
select l.owner,l.segment_name,s.tablespace_name,l.column_name,l.table_name,s.bytes/(1024*1024),compression
 from dba_lobs l inner join dba_segments s on (l.owner=s.owner and l.segment_name=s.segment_name)
 where s.owner='LOBUSER';

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