-- 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');
--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;
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:
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;
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;