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 và chuyển dữ liệu non-partition từ tablespace USERS sang 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='DATA2018'
order by "GB" desc;

--/u02/app/oracle/oradata/orcl/data2018_001.dbf
select * from dba_data_files where tablespace_name='DATA2018';

drop tablespace DATA including contents and datafiles;

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='DATA2018' and table_name not like '%$%';

-- BINHTV.TP1
select distinct table_owner ||'.' || table_name
from dba_tab_partitions where tablespace_name='DATA2018' and table_name not like '%$%';

-- Chuyen du lieu
declare
    cursor c1 is  select  table_owner, table_name, partition_name
        from dba_tab_partitions where tablespace_name='DATA2018' 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='DATA' and table_name not like '%$%');

--2.2.Rebuild index partition UNUSABLE
select * from dba_ind_partitions
where (status='UNUSABLE'
--or tablespace_name='DATA2018'
) 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='USERS' and owner='BINHTV' and table_name not like '%$%';

select 'alter table '  || owner || '.' || table_name || ' move tablespace DATA nologging parallel 8;' from dba_tables
where tablespace_name='USERS' and owner='BINHTV'  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='USERS' and table_name not like '%$%');

--3.2.Rebuild index non-partition
select * from dba_indexes where status='UNUSABLE'
-- or tablespace_name='USERS';

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

-- Nhung index con lai trong USERS
select * from dba_indexes where tablespace_name='USERS';

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace INDX nologging parallel 8 online;'
from dba_indexes where tablespace_name='USERS';

select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace INDX nologging noparallel;'
from dba_indexes where tablespace_name='USERS';

--4.Chuyển dữ liệu LOB
--4.1.Chuyen du lieu LOB partition
select * from dba_lob_partitions where tablespace_name='USERS';

--4.2.Chuyen du lieu LOB non-partition
select * from dba_lobs where tablespace_name='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='USERS';

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

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