--create tablespace
CREATE BIGFILE TABLESPACE "TBS_ADTDATA1" DATAFILE '+DG_DATA/tbs_adtdata1_n.dbf' SIZE 2621440m
AUTOEXTEND ON NEXT 32212254720 MAXSIZE 134217727M
LOGGING ONLINE PERMANENT BLOCKSIZE 32768
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE "TBS_ADTDATA1" DATAFILE
'+DG_DATA/tbs_adtdata_n.dbf' SIZE 1677721m
AUTOEXTEND ON NEXT 32212254720 MAXSIZE 134217727M
LOGGING ONLINE PERMANENT BLOCKSIZE 32768
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
--1.enable row movement,data tablespace
define OLD_TS_NAME='TS_BLOCK'
set serveroutput on;
declare
cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&OLD_TS_NAME' and se.segment_type in('TABLE','TABLE PARTITION') order by se.owner;
begin
for v in c loop
execute immediate 'alter table '||v.owner||'.'||v.segment_name ||' enable row movement';
dbms_output.put_line('-->Enable row movement OK : '||v.owner||'.'||v.segment_name);
end loop;
end;
/
--2.move table
define OLD_TS_NAME='TS_BLOCK'
define NEW_TS_NAME='USERS'
set serveroutput on;
declare
cursor c is select se.owner,se.segment_name from dba_segments se where se.tablespace_name='&OLD_TS_NAME' and se.segment_type='TABLE';
sql_stmt varchar2(200);
sql_enable varchar2(200);
sql_disable varchar2(200);
begin
execute immediate 'alter session enable parallel ddl';
for v in c loop
sql_stmt:= 'alter table '||v.owner||'.'||v.segment_name ||' move tablespace '|| '&NEW_TS_NAME'||' parallel 16';
--excute sqls
execute immediate sql_stmt;
dbms_output.put_line('-->Move table OK : '|| v.segment_name);
end loop;
end;
/
--3.move table partitions
define OLD_TS_NAME='TS_BLOCK'
define NEW_TS_NAME='USERS'
set serveroutput on;
declare
cursor c is select se.owner,se.segment_name,se.partition_name from dba_segments se where se.tablespace_name='&OLD_TS_NAME' and se.segment_type='TABLE PARTITION' order by se.owner;
sql_stmt varchar2(200);
begin
execute immediate 'alter session enable parallel ddl';
for v in c loop
--move table partition
sql_stmt:= 'alter table '||v.owner||'.'||v.segment_name ||' move partition '||v.partition_name || ' tablespace &NEW_TS_NAME parallel 16';
--dbms_output.put_line(sql_stmt);
--excute sqls
execute immediate sql_stmt;
dbms_output.put_line('-->Move table partition OK: '|| v.owner||'.'||v.partition_name);
end loop;
end;
/
--4.disable row movment,data tablespace
define NEW_TS_NAME='USERS'
set serveroutput on;
declare
cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&NEW_TS_NAME' and se.segment_type in('TABLE','TABLE PARTITION') order by se.owner;
begin
for v in c loop
execute immediate 'alter table '||v.owner||'.'||v.segment_name ||' disable row movement';
dbms_output.put_line('-->Disable row movement OK : '||v.owner||'.'||v.segment_name);
end loop;
end;
/
--5.check all tables to new tablespace,not in old tablespace,on table/table partitoon is OK
define TS_BLOCK='TS_BLOCK'
select * from dba_segments se where se.tablespace_name='&OLD_TS_NAME';
--6..rebuild indexes
define OLD_TS_IDX_NAME='TS_BLOCK'
define NEW_TS_IDX_NAME='USERS'
set serveroutput on;
declare
cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&OLD_TS_IDX_NAME' and se.segment_type='INDEX' order by se.owner;
sql_stmt varchar2(200);
begin
execute immediate 'alter session enable parallel ddl';
for v in c loop
--move table partition
sql_stmt:= 'alter index '||v.owner||'.'||v.segment_name ||' rebuild tablespace &NEW_TS_IDX_NAME parallel 16 nologging';
--dbms_output.put_line(sql_stmt);
--excute sqls
execute immediate sql_stmt;
dbms_output.put_line('-->Rebuild index OK: '|| v.owner||'.'||v.segment_name);
end loop;
end;
/
--7.rebuild partiton indexes
define OLD_TS_IDX_NAME='TS_BLOCK'
define NEW_TS_IDX_NAME='USERS'
set serveroutput on;
declare
cursor c is select distinct se.owner,se.segment_name,se.partition_name from dba_segments se where se.tablespace_name='&OLD_TS_IDX_NAME' and se.segment_type='INDEX PARTITION' order by se.owner;
sql_stmt varchar2(200);
begin
execute immediate 'alter session enable parallel ddl';
for v in c loop
--move table partition
sql_stmt:= 'alter index '||v.owner||'.'||v.segment_name ||' rebuild partition '||v.partition_name||' tablespace &NEW_TS_IDX_NAME parallel 16 nologging';
--dbms_output.put_line(sql_stmt);
--excute sqls
execute immediate sql_stmt;
dbms_output.put_line('-->Rebuild index OK: '||v.owner||'.'|| v.segment_name);
end loop;
end;
/
--8.check all indexes to new tablespace ,not in old tablespace ,no index index partition is OK
define OLD_TS_IDX_NAME='TS_BLOCK'
select * from dba_segments se where se.tablespace_name='&OLD_TS_IDX_NAME';
--9.gather table/index stats for new tables for update in new tablespace
define NEW_TS_NAME='USERS'
set serveroutput on;
declare
cursor c is select distinct se.owner,se.segment_name from dba_segments se where se.tablespace_name='&NEW_TS_NAME' and se.segment_type in('TABLE','TABLE PARTITION');
begin
for v in c loop
if c%NOTFOUND then
dbms_output.put_line('No data found!');
else
--dbms_output.put_line(v.owner||v.segment_name);
dbms_stats.gather_table_stats(ownname => v.owner,tabname => v.segment_name,estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => 'FOR ALL COLUMNS SIZE 1',degree => dbms_stats.AUTO_DEGREE,cascade => true);
dbms_output.put_line('Gather stats OK : ' || v.owner||'.'||v.segment_name);
end if;
end loop;
end;
/
--10.check statst on all objects,no data os OK
define NEW_TS_NAME='TS_BLOCK';
define NEW_TS_IDX_NAME='TS_BLOCK'
select se.owner,se.TABLE_NAME,se.ROW_MOVEMENT,se.degree,se.LAST_ANALYZED from dba_tables se where se.TABLESPACE_NAME='NEW_TS_NAME' and se.LAST_ANALYZED is null;
select es.owner,es.index_name,es.degree,es.LAST_ANALYZED from dba_indexes es where es.TABLESPACE_NAME='NEW_TS_NAME' and es.LAST_ANALYZED is null;
--11.drop old tablespaces;
drop tablespace xx_idx including contents and datafiles;
drop tablespace xx_data including contents and datafile;