Oracle Spostamento Oggetti: differenze tra le versioni
Jump to navigation
Jump to search
Riga 53: | Riga 53: | ||
==Spostamento LOB== |
==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 <code>nls_length_semantics</code> a <code>char</code> ci sono brutti effetti collaterali: |
con <code>nls_length_semantics</code> a <code>char</code> ci sono brutti effetti collaterali: |
Versione delle 00:47, 5 mag 2021
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);
select 'alter table ' || owner || '.' || TABLE_NAME || ' move lob (' || COLUMN_NAME || ') store as (tablespace &2);' from dba_lobs where TABLESPACE_NAME='&1';
compressione e deduplica richiedono avanced compression. 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