File System_Chuyển dữ liệu sang tablespace DATA, INDX bằng cách move partition (Oracle Database)

-- Mục đích: Quy hoạch lại các dữ liệu ví dụ như dữ liệu từ tablespace DATA2018, INDX2018, USERS sang tablespace DATA, INDX bằng phương pháp move partition table/table partition, index, sau đó drop các tablespace DATA2018, INDX2018, USERS đi để giải phóng dung lượng do phân mảnh (DATA2018 cấp 5TB dùng 1TB, INDX2018 cấp 3TB dùng 1TB, USERS cấp 10TB dùng 2TB --> Cấp 18TB dùng 4TB, thu hồi được 14TB)

-- Tính mở rộng: Thủ tục này có thể áp dụng từ tablespace này sang tablespace khác

--1.Check thông tin
select  round(sum(bytes)/1024/1024/1024,2) "GB" from dba_segments where tablespace_name in ('DATA2018','INDX2018','USERS')
order by "GB" desc;

--/u02/app/oracle/oradata/orcl/data2018_001.dbf
select * from dba_data_files where tablespace_name in ('DATA2018','INDX2018','USERS');

CREATE TABLESPACE DATA DATAFILE
  '/u01/app/oracle/oradata/orcl/data_001.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE TABLESPACE INDX DATAFILE
  '/u01/app/oracle/oradata/orcl/indx_001.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

--2.Chuyển dữ liệu  partition
--2.1.Bang partition
select * from dba_tab_partitions where tablespace_name in ('DATA2018','INDX2018','USERS') and table_name not like '%$%';

-- BINHTV.TP1
 select distinct table_owner ||'.' || table_name
from dba_tab_partitions where tablespace_name in ('DATA2018','INDX2018','USERS')and table_name not like '%$%';

-- Cach 1:

select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' tablespace DATA nologging parallel 8;', partition_name, tablespace_name 

from dba_tab_partitions

where tablespace_name in ('DATA2018','INDX2018','USERS') and table_name not like '%$%' 

and table_name not like '%XX%' and table_name not in ('TAB1')

and

( 

(length(partition_name)=12 and to_date(substr(partition_name,5,8),'yyyymmdd')>=to_date('20220901','yyyymmdd'))

or

(length(partition_name)=10 and to_date(substr(partition_name,5,6),'yyyymm')>=to_date('202209','yyyymm'))

)

order by table_owner, table_name, partition_name

;

hoặc cách 1 với tối thiểu hóa thời gian downtime từng partition theo câu lệnh:

select 'alter table '||table_owner||'.'||table_name||' move partition '||partition_name|| ' tablespace ' || substr(partition_name,1,8) ||'_RW nologging parallel 4;', partition_name, tablespace_name 

from dba_tab_partitions

where table_owner='BINH_OWNER' and table_name = 'TAB1' and partition_name like 'DATA2018%'

union

select 'alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name

|| ' tablespace INDX' || substr(partition_name,5,4) || '_RW nologging parallel 4 online;', partition_name, tablespace_name 

from dba_ind_partitions

where index_name in

    (select index_name from dba_indexes where

    table_owner='BINH_OWNER' and table_name = 'TAB1')

    and partition_name like 'DATA2014%'

union

select 'alter index '||owner||'.'||index_name||' noparallel;', null, null from dba_indexes where

table_owner='BINH_OWNER' and table_name = 'TAB1'

order by 2, 1 desc, 3;

-- Cach 2: Chuyen du lieu
declare
    cursor c1 is  select  table_owner, table_name, partition_name
        from dba_tab_partitions where tablespace_name in ('DATA2018','INDX2018','USERS') and table_name not like '%$%' order by 1,2,3;

begin
    for r1 in c1 loop
        if r1.table_owner='BINHTV' then
            dbms_output.put_line('alter table ' || r1.table_owner ||'.' || r1.table_name || ' move partition ' || r1.partition_name ||' tablespace USERS nologging parallel 8;');
        else
            dbms_output.put_line('alter table ' || r1.table_owner ||'.' || r1.table_name || ' move partition ' || r1.partition_name ||' tablespace DATA nologging parallel 8;');
        end if;
    end loop;
end;

-- Set noparallel
--alter table BINHTV.TP1 noparallel;
select 'alter table ' || table_owner || '.' || table_name || ' noparallel;' from (select distinct table_owner, table_name
from dba_tab_partitions where tablespace_name in ('DATA2018','INDX2018','USERS') and table_name not like '%$%');

--2.2.Rebuild index partition UNUSABLE
select * from dba_ind_partitions
where (status='UNUSABLE'
--or tablespace_name in ('DATA2018','INDX2018','USERS')
) and index_name not like '%$%';

--alter index BINHTV.TP1_I1 rebuild partition DATA20180819 tablespace INDX nologging parallel 8 online;
select 'alter index ' || index_owner ||'.' || index_name || ' rebuild partition ' || partition_name || ' tablespace INDX nologging parallel 8 online;'
from dba_ind_partitions
where (status='UNUSABLE'
) and index_name not like '%$%'
order by 1;

-- Set nologging
--alter index BINHTV.TP1_I1 nologging noparallel;
--alter index BINHTV.TP1_I2 nologging noparallel;
select 'alter index ' || index_owner ||'.' || index_name || ' nologging noparallel;' from (select distinct index_owner,index_name
from dba_ind_partitions
where (status='UNUSABLE') and index_name not like '%$%');

--3.Chuyển dữ liệu non-partition
--3.1.Chuyen du lieu bang non-partition
select * from dba_tables where tablespace_name in ('DATA2018','INDX2018','USERS')' and table_name not like '%$%';

select 'alter table '  || owner || '.' || table_name || ' move tablespace DATA nologging parallel 8;' from dba_tables
where tablespace_name  in ('DATA2018','INDX2018','USERS') and table_name not like '%$%'
union
select 'alter table ' || owner || '.' || table_name ||' nologging noparallel;' from
(select distinct owner, table_name from dba_tables where tablespace_name  in ('DATA2018','INDX2018','USERS') and table_name not like '%$%');

--3.2.Rebuild index non-partition
select * from dba_indexes where status='UNUSABLE'
-- or tablespace_name in ('DATA2018','INDX2018','USERS');

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace INDX nologging parallel 8 online;'
from dba_indexes where status='UNUSABLE'
--or tablespace_name in ('DATA2018','INDX2018','USERS')
union
select 'alter index ' || owner || '.' || index_name || ' nologging noparallel;'
from (select distinct owner, index_name
from dba_indexes where status='UNUSABLE'
--or tablespace_name in ('DATA2018','INDX2018','USERS')
);

-- Nhung index con lai trong USERS
select * from dba_indexes where tablespace_name in ('DATA2018','INDX2018','USERS') ;

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace INDX nologging parallel 8 online;'
from dba_indexes where tablespace_name in ('DATA2018','INDX2018','USERS');

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace INDX nologging noparallel;'
from dba_indexes where tablespace_name in ('DATA2018','INDX2018','USERS');

--4.Chuyển dữ liệu LOB
--4.1.Chuyen du lieu LOB partition
select * from dba_lob_partitions where tablespace_name in ('DATA2018','INDX2018','USERS');

--4.2.Chuyen du lieu LOB non-partition
select * from dba_lobs where tablespace_name in ('DATA2018','INDX2018','USERS');

--alter table t move lob(y) store as ( tablespace users  )
select 'alter table ' || owner ||'.' || table_name || ' move lob(' || column_name || ') store as (tablespace DATA);'
from dba_lobs where tablespace_name in ('DATA2018','INDX2018','USERS');

alter table CDC_NODBA.TS_DDLAUD move lob(STATEMENT) store as (tablespace DATA);

-- Trong lúc chuyển cần monitor tải và tablespace:

--Hiển thị dung lượng trống của tablespace

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 ('DATA','INDX'))

 order by "%Used of Max" desc;

--5.Check lại đảm bảo không có row nào
select * from dba_segments where tablespace_name in ('DATA2018','INDX2018','USERS')
and segment_name not like '%$%';

select * from dba_tab_partitions where tablespace_name in ('DATA2018','INDX2018','USERS');

select * from dba_tables where tablespace_name in ('DATA2018','INDX2018','USERS');

select * from dba_indexes where tablespace_name in ('DATA2018','INDX2018','USERS');

select * from dba_lob_partitions where tablespace_name in ('DATA2018','INDX2018','USERS');

select * from dba_lobs where tablespace_name in ('DATA2018','INDX2018','USERS');

--6.Resize DF ve 128KB
select * from dba_data_files where tablespace_name in ('DATA2018','INDX2018','USERS');

select 'alter database datafile ' || file_id || ' resize to 128k;' from dba_data_files where tablespace_name in ('DATA2018','INDX2018','USERS');

--7.Xóa TBS DATA2018, INDX2018, USERS
drop tablespace DATA2018 including contents and datafiles;
drop tablespace INDX2018 including contents and datafiles;
drop tablespace USERS including contents and datafiles;