oracle分区表在线重定义

「使用在线重定义的一些限制条件」：
必须有足够的表空间来容纳表的两倍数据量。
主键列不能被修改。
表必须有主键。
必须在同一个用户下进行在线重定义。
SYS和SYSTEM用户下的表无法进行在线重定义。
在线重定义无法采用nologging。
如果中间表有新增列，则不能有NOT NULL约束

模拟oracle分区表在线重定义

sqlplus par/par
SQL> create table par1 (id number(8) primary key,name varchar2(10) not null,par_date date) tablespace par;

Table created.

SQL> comment on table par1 is 'fenqubiao';

Comment created.

SQL>
SQL> comment on column par1.name is 'mingzi';

Comment created.

SQL> comment on column par1.par_date is 'fenquriqi';

Comment created.

SQL> create index index_name on par1(name) tablespace par;

Index created.

sqlplus par/par
SQL> begin
  2  for i in 1..30
  3  loop
  4  insert into par1 values (i,'yang'||i,add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i));
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.


select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'PAR1';

sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'PAR1'); 


sqlplus par/par
create table par2 
( 
  id       NUMBER(8), 
  name     VARCHAR2(20), 
  par_date DATE 
) 
partition BY RANGE(par_date)( 
partition par_P202101 values less than (TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202102 values less than (TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202103 values less than (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202301 values less than (TO_DATE(' 2023-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202302 values less than (TO_DATE(' 2023-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202303 values less than (TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202304 values less than (TO_DATE(' 2023-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202305 values less than (TO_DATE(' 2023-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202306 values less than (TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_P202307 values less than (TO_DATE(' 2023-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par, 
partition par_MAX values less than (maxvalue) tablespace par) 
             enable row movement 
             tablespace par; 
			 
sqlplus / as sysdba			 
select row_movement from dba_tables where table_name='PAR1' and owner='PAR'; 
select row_movement from dba_tables where table_name='PAR2' and owner='PAR'; 

sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'PAR1',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'PAR2',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;

sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','PAR1','PAR2'); 

sqlplus par/par
create index index_name_par on par2(name) tablespace par local parallel 8;

sqlplus / as sysdba
BEGIN 
dbms_redefinition.sync_interim_table( 
uname => 'PAR', 
orig_table => 'PAR1', 
int_table => 'PAR2'); 
END; 
/ 

sqlplus / as sysdba
BEGIN 
dbms_redefinition.finish_redef_table( 
uname => 'PAR', 
orig_table => 'PAR1', 
int_table => 'PAR2'); 
END; 
/ 


sqlplus par/par
SQL> select table_name,partitioned from user_tables where table_name in ('PAR1','PAR2');

TABLE_NAME                     PAR
------------------------------ ---
PAR1                           YES
PAR2                           NO

SQL> select INDEX_NAME,TABLE_NAME from dba_indexes where TABLE_NAME in ('PAR1','PAR2');

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME_PAR                 PAR1
INDEX_NAME                     PAR2
SYS_C0011135                   PAR2

SQL> alter index index_name rename to index_name_bak;

Index altered.

SQL> alter index index_name_par rename to index_name;

Index altered.

SQL> select INDEX_NAME,TABLE_NAME from dba_indexes where TABLE_NAME in ('PAR1','PAR2');

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
INDEX_NAME                     PAR1
INDEX_NAME_BAK                 PAR2
SYS_C0011135                   PAR2