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 

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