Thứ Năm, 6 tháng 9, 2018

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;



ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master