Oracle Spostamento Oggetti: differenze tra le versioni
Jump to navigation
Jump to search
Riga 9: | Riga 9: | ||
from dba_data_files |
from dba_data_files |
||
where tablespace_name = '&1' ) |
where tablespace_name = '&1' ) |
||
order by block_id,file_id desc |
order by block_id desc,file_id desc |
||
) where rownum <= 10; |
) where rownum <= 10; |
||
Versione delle 16:40, 28 dic 2018
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';
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 ;
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; /