oracle在线重定义案例分析

最新推荐文章于 2024-11-03 22:34:49 发布

TrsenZhang

最新推荐文章于 2024-11-03 22:34:49 发布

阅读量992

点赞数

本文链接：https://blog.csdn.net/trsenzhang/article/details/18228215

版权

本文通过案例详细介绍了如何使用Oracle的在线重定义功能，包括将表从一个表空间迁移到另一个表空间以及删除表中列的操作。在迁移过程中，涉及了中间表的创建、start_redef_table、finish_redef_table等步骤，并提醒了在执行过程中需要注意的细节和可能出现的问题。

摘要由CSDN通过智能技术生成

--创建测试用户

select * from dba_data_files;
create tablespace t_redef datafile '+data_dg/orcl/datafile/t_redef.dbf' size 1048m autoextend on next 2048 maxsize unlimited;
create tablespace t_redef_b datafile '+data_dg/orcl/datafile/t_redef_b.dbf' size 1048m autoextend on next 2048 maxsize unlimited;
create user t_redef identified by t_redef account unlock default tablespace t_redef;
grant dba to t_redef;
select * from dba_users;
-- Create table
select * from t_redef.t_redef
select *  from t_redef.int_t_redef;
select * from t_redef.MLOG$_T_REDEF  
create table t_redef.t_redef
(
  NODE_ID             VARCHAR2(256) not null,
  NAME                VARCHAR2(256),
  STATUS              VARCHAR2(20),
  AREA_BUFFER         VARCHAR2(256),
  X                   NUMBER(12,8),
  Y                   NUMBER(12,8),
  ORG_ID              VARCHAR2(256),
  CREATE_USER         VARCHAR2(100),
  CREATE_TIME         DATE,
  MONDIFY_USER        VARCHAR2(100),
  MONDIFY_TIME        DATE,
  CREATE_STATION_CODE VARCHAR2(100)
)tablespace t_redef;
alter table t_redef.t_redef
add constraint PK_t_redef_NODE primary key (NODE_ID)
using index
tablespace t_redef;
create index INDX_t_redef_01 on t_redef.t_redef (ORG_ID)
tablespace t_redef;
user_ind_columns;

--######案例1：利用在线重定义更改表的表空间######
--查看表所在的表空间，索引约束等对象

SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
T_REDEF                        T_REDEF--更改至t_redef_b表空间下
SQL> col object_name for a30;
SQL> select object_name,status,object_type from user_objects;
OBJECT_NAME                    STATUS  OBJECT_TYPE
------------------------------ ------- -------------------
T_REDEF                        VALID   TABLE
PK_T_REDEF_NODE                VALID   INDEX
SQL> col index_name for a15;
SQL> col table_name for a15;
SQL> col column_name for a10;
SQL> select index_name,table_name,column_name from user_ind_columns;
INDEX_NAME      TABLE_NAME      COLUMN_NAM
--------------- --------------- ----------
INDX_T_REDEF_01 T_REDEF         ORG_ID
PK_T_REDEF_NODE T_REDEF         NODE_ID

--首先利用CAN_REDEF_TABLE来查看此表是否支持在线重定义
SQL> begin
  2    dbms_redefinition.can_redef_table(uname =>'t_redef', tname =>'t_redef');
  3  end;
  4  /
PL/SQL procedure successfully completed.
--创建中间表,在该处指定表空间还是默认表空间，则相当于给该表做了碎片整理
create table t_redef.int_t_redef
(
  NODE_ID             VARCHAR2(256) not null,
  NAME                VARCHAR2(256),
  STATUS              VARCHAR2(20),
  AREA_BUFFER         VARCHAR2(256),
  X                   NUMBER(12,8),
  Y                   NUMBER(12,8),
  ORG_ID              VARCHAR2(256),
  CREATE_USER         VARCHAR2(100),
  CREATE_TIME         DATE,
  MONDIFY_USER        VARCHAR2(100),
  MONDIFY_TIME        DATE,
  CREATE_STATION_CODE VARCHAR2(100)
)tablespace t_redef_b;
--执行start_redef_table开始在线重定义
SQL> begin
  2    dbms_redefinition.start_redef_table(uname      => 't_redef',
  3                                        orig_table => 't_redef',
  4                                        int_table  => 'int_t_redef');
  5  end;
  6  /
PL/SQL procedure successfully completed.
--此时，我去做个update操作,会发现原始表和中间的name字段的值是不一样的,而且多了两个表出来；
SQL> update t_redef set name='a';
4382 rows updated.
 SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T_REDEF                        TABLE
INT_T_REDEF                    TABLE
MLOG$_T_REDEF                  TABLE
RUPD$_T_REDEF                  TABLE
SQL> commit;   
Commit complete.
--查看信息，多了MLOG$_T_REDEF和RUPD$_T_REDEF 两个对象
SQL>  select table_name,tablespace_name from user_tables;
TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
T_REDEF         T_REDEF
INT_T_REDEF     T_REDEF_B
MLOG$_T_REDEF   T_REDEF
RUPD$_T_REDEF
SQL>  select index_name,table_name,column_name from user_ind_columns;
INDEX_NAME      TABLE_NAME      COLUMN_NAM
--------------- --------------- ----------
INDX_T_REDEF_01 T_REDEF         ORG_ID
PK_T_REDEF_NODE T_REDEF         NODE_ID
SQL> select object_name,status,object_type from user_objects;
OBJECT_NAME                    STATUS  OBJECT_TYPE
------------------------------ ------- -------------------
T_REDEF                        VALID   TABLE
PK_T_REDEF_NODE                VALID   INDEX
INT_T_REDEF                    VALID   TABLE
MLOG$_T_REDEF                  VALID   TABLE
RUPD$_T_REDEF                  VALID   TABLE
--尝试直接做finish_redef_table操作，会不会出现异常的情况，如果没有会有什么情况？
SQL> begin
  2    dbms_redefinition.finish_redef_table(uname      => 't_redef',
  3                                         orig_table => 't_redef',
  4                                         int_table  => 'int_t_redef');
  5  end;
  6  /
PL/SQL procedure successfully completed.
--那么可想而知，所有的索引都在中间表上了没有过来，因为没有在finish_redef_table之前手工做相关对象或者利用copy_table_dependents来做，导致
--在重定义过程中，没有将索引和约束等给复制过来，再查看数据，update的数据，是在finish_redef_table做了处理，
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME      TABLESPACE_NAME
--------------- ------------------------------
INT_T_REDEF     T_REDEF
T_REDEF         T_REDEF_B-->表所在的表空间位置更改了
SQL>  select index_name,table_name,column_name from user_ind_columns;
INDEX_NAME      TABLE_NAME      COLUMN_NAM
--------------- --------------- ----------
INDX_T_REDEF_01 INT_T_REDEF     ORG_ID--索引和约束都在中间表上了
PK_T_REDEF_NODE INT_T_REDEF     NODE_ID
SQL> select object_name,status,object_type from user_objects;
OBJECT_NAME                    STATUS  OBJECT_TYPE
------------------------------ ------- -------------------
PK_T_REDEF_NODE                VALID   INDEX
INT_T_REDEF                    VALID   TABLE
T_REDEF                        VALID   TABLE
--手动在建立索引了，哎，手工删除中间表int_t_redef;
drop table t_redef.int_t_redef purge;
alter table t_redef.t_redef
add constrain