Oracle Spostamento Oggetti

mappa oggetti per tablespace

set pagesize 200 linesize 200 trim on trims on tab off

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

select tablespace_name,segment_type,count(*),sum(bytes)/(1024*1024) from dba_segments where owner in (select username from dba_users where oracle_maintained='N') group by tablespace_name,segment_type order by 2,1;

select s.tablespace_name,s.segment_type,count(*),sum(s.bytes)/(1024*1024) from dba_segments s inner join dba_users u on (s.owner=u.username and u.oracle_maintained='N') group by s.tablespace_name,s.segment_type order by 2,1;

Per schema

col owner for a20
select s.owner,s.tablespace_name,s.segment_type,count(*),round(sum(s.bytes)/(1024*1024)) as mb from dba_segments s inner join dba_users u on (s.owner=u.username and u.oracle_maintained='N') group by s.tablespace_name,s.segment_type,s.owner order by 1,2,3,5;

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 &2 online nologging compute statistics;' from dba_indexes where index_type='NORMAL' and tablespace_name='&1';

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

Ordinati per grandezza

select '/* size ' || s.bytes/(1024*1024) || ' table ' || i.table_name || ' */ alter index ' || i.owner || '.' || i.index_name || ' rebuild tablespace &tbs_destinazione online nologging compute statistics;' 
 from dba_indexes i inner join dba_segments s on s.segment_name=i.index_name and s.owner=i.owner 
 where i.index_type='NORMAL' and s.tablespace_name='&tbs_sorgente' order by s.bytes desc;

rebuild indici UNUSABLE:

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

rimettere in logging

Rimette in logging

prompt 'ALTER SESSION SET ddl_lock_timeout=30;'
select 'alter index ' || owner || '.' || index_name || ' logging;' from dba_indexes where LOGGING='NO' and owner !='PERFSTAT' and owner in (select username from dba_users where oracle_maintained='N');

non va messo in logging un indice su una tabella in nologging:

select '/* ' || i.table_name || ' */ alter index ' || i.owner || '.' || i.index_name || ' logging;' from dba_indexes i
 where i.logging='NO' and i.owner !='PERFSTAT'
 and not exists (select 1 from dba_users u where u.username=i.owner and u.oracle_maintained='Y') 
 and not exists (select 1 from dba_tables t where t.table_name=i.table_name and t.owner=i.table_owner and t.logging='NO');

versione con inner join

select '/* ' || i.table_owner || '.' || i.table_name || ' ' || t.logging || ' */ alter index ' || i.owner || '.' || i.index_name || ' logging;'
 from dba_indexes i
 inner join dba_users u on (u.username=i.owner and u.oracle_maintained='N')
 inner join dba_tables t on (t.table_name=i.table_name and t.owner=i.table_owner and t.logging='YES')
 where i.logging='NO' and i.owner !='PERFSTAT';

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.table_name,l.column_name,
round(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;

Per tabelle partizionate

--
set pagesize 200 linesize 200 trim on trims on
col owner for a20
col partition_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,s.tablespace_name,l.table_name,s.partition_name,l.column_name,
round(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,3,5;

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