ASM_Chuyển dữ liệu từ tablespace INDX sang INDX_RECO bằng cách rebuild index

-- Mục đích: Chuyển dữ liệu từ tablespace INDX sang INDX_RECO do nhu cầu chuyển sang phân vùng SATA tốc độ thấp bằng cách rebuild lại index
-- 1. Size tablespace_name like '%INDX%'
select * from dba_data_files where tablespace_name like '%INDX%';

select round(sum(bytes)/1024/1024/1024,2) "GB" from dba_data_files  where tablespace_name like '%INDX%';

-- 2.Tao tablespace INDX_RECO
create tablespace INDX_RECO datafile '/u01/app/oracle/oradata/orcl/indx_reco_001.dbf' size 100M autoextend on next 100m;

-- 3.Rebuild index tu INDX sang INDX_RECO
--3.1.Rebuild index partition
-- Nologging index
select distinct a.indx from (select 'alter index ' || index_owner || '.' || index_name || ' nologging;' indx
from dba_ind_partitions  where tablespace_name='INDX'
order by index_owner, index_name, partition_name) a
order by 1;

select 'alter index ' || index_owner || '.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX_RECO nologging parallel 8 online;'
from dba_ind_partitions  where tablespace_name='INDX' order by index_owner, index_name, partition_name;

-- Monitoring
SELECT  a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",
    ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",
    ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",
    ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
    --ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",
    ROUND (maxbytes / 1048576)  "Max MB",
    round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",
    ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/  ROUND (maxbytes / 1048576) * 100) "%Used of Max"
    FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc,  SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
            FROM dba_data_files f
            GROUP BY tablespace_name) a,
        (SELECT f.tablespace_name, SUM (f.bytes) bytes_free  FROM dba_free_space f  GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+) and a.tablespace_name in ('INDX','INDX_RECO','TEMP')
 order by tablespace_name desc;

-- 3.2.rebuild index non-partition
select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace INDX_RECO nologging parallel 8 online;'  from dba_indexes  where tablespace_name='INDX' order by owner, index_name;

--4. Check sau khi chuyen doi
select * from dba_segments where tablespace_name='INDX';

select * from dba_ind_partitions  where tablespace_name='INDX' order by index_owner, index_name, partition_name;

select * from dba_indexes  where tablespace_name='INDX' order by owner, index_name;

--5.Drop tablespace INDX
drop tablespace INDX including contents and datafiles;