ITPub博客

首页 > 数据库 > Oracle > 12C 在线MOVE

12C 在线MOVE

原创 Oracle 作者:dmcatding 时间:2020-02-16 19:31:35 0 删除 编辑

准备:

--创建表空间

SQL> create tablespace test datafile '/u01/app/oradata/nopdb/test100.dbf' size 2g;

--创建用户

SQL> create user test identified by test default tablespace test;

--赋权

SQL> grant connect,resource to test;

SQL> grant unlimited tablespace to test;

--检查创建用户

SQL>  select username,account_status,DEFAULT_TABLESPACE,to_char(LAST_LOGIN,'yyyy-mm-dd hh24:mm:ss'),INHERITED from dba_users where INHERITED='NO';


USERNAME   ACCOUNT_STATUS                   DEFAULT_TABLESPACE             TO_CHAR(LAST_LOGIN, INH

---------- -------------------------------- ------------------------------ ------------------- ---

HR         EXPIRED & LOCKED                 SYSAUX                                             NO

OJVMSYS    EXPIRED & LOCKED                 SYSTEM                                             NO

TEST       OPEN                             TEST                                               NO


1、在线move数据文件或修改数据文件名称

--查看数据文件当前名称

   FILE_ID FILE_NAME                                                                       G      MAX_G AUTOEXTENSIBLE  ONLINE_ CREATION_TIME

---------- ---------------------------------------------------------------------- ---------- ---------- --------------- ------- -------------------

         5 /u01/app/oradata/nopdb/test100.dbf                                              2          0 NO              ONLINE  2020-02-17 00:26:59


SQL> alter database move datafile 5 to '/u01/app/oradata/nopdb/test01.dbf';

Database altered.   


2、MOVE 非分区表

--创建测试表

create table test.tab1 as select * from dba_objects;

create table test.tab2 as select * from dba_objects;

create table test.tab3 as select * from dba_objects;

--创建测试索引

create index idx_tab1_id on tab1(object_id);

create index idx_tab2_id on tab2(object_id);

create index idx_tab3_id on tab3(object_id);


--查看创建表、索引大小

SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';


OWNER      SEGMENT_NAME                   SEGMENT_TY TABLESPACE_NAME                                  MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST       TAB1                           TABLE      TEST                                             12

TEST       TAB2                           TABLE      TEST                                             12

TEST       TAB3                           TABLE      TEST                                             12

TEST       IDX_TAB1_ID                    INDEX      TEST                                              2

TEST       IDX_TAB2_ID                    INDEX      TEST                                              2

TEST       IDX_TAB3_ID                    INDEX      TEST                                              2


6 rows selected.


--模拟插入数据

SQL> insert into tab1 select * from tab2;

75722 rows created.


SQL> insert into tab1 select * from tab2;

75722 rows created.


SQL> commit;

Commit complete.


--再次查询

OWNER      SEGMENT_NAME                   SEGMENT_TY TABLESPACE_NAME                                  MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST       TAB1                           TABLE      TEST                                             34


--删除tab1表数据

SQL> delete tab1 ;

227165 rows deleted.


SQL> commit;


--再次插入数据

SQL> insert into tab1 select * from tab2 where object_id < 500;


SQL> commit;


--检查索引

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes   where owner='TEST';


TABLE_NAME      INDEX_NAME           STATUS       BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB1            IDX_TAB1_ID          VALID             1         168 NO

TAB2            IDX_TAB2_ID          VALID             1         168 NO

TAB3            IDX_TAB3_ID          VALID             1         168 NO


测试方法1:

--插入数据不提交后,MOVE表不加online

SQL> insert into tab1 select * from tab2 where object_id between 500 and 800;


300 rows created.


SQL> col OBJECT_NAME for a20

SQL> col MACHINE for a40

SQL> col PROGRAM for a40

SQL> col Deadlock for a60

SQL>  SELECT a.inst_id, a.sid,a.username,a.machine,a.program,c.object_name,b.LOCKED_MODE,c.REQUEST FROM gv$session a inner join  gv$locked_object b  on a.sid =b.SESSION_ID and a.inst_id=b.inst_id inner join dba_objects c on b.object_id=c.object_id inner join gv$lock c on a.sid=c.sid;


   INST_ID    SID USERNAME   MACHINE                                  PROGRAM                                  OBJECT_NAME          LOCKED_MODE    REQUEST

---------- ------ ---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ----------

         1     62 TEST       ORACLE-12C                               sqlplus@ORACLE-12C (TNS V1-V3)           TAB1                           3          0

         1     62 TEST       ORACLE-12C                               sqlplus@ORACLE-12C (TNS V1-V3)           TAB1                           3          0

         1     62 TEST       ORACLE-12C                               sqlplus@ORACLE-12C (TNS V1-V3)           TAB1                           3          0


--move表操作

SQL> alter table test.tab1 move;

alter table test.tab1 move

                 *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


结论:有锁报资源繁忙,MOVE表不支持DML操作


测试方法2:

--数据提交后,MOVE表不加online

SQL> insert into tab1 select * from tab2 where object_id between 500 and 800;


300 rows created.


SQL> commit;


Commit complete.


--此时检查没有锁,也没有DML操作

SQL>  SELECT a.inst_id, a.sid,a.username,a.machine,a.program,c.object_name,b.LOCKED_MODE,c.REQUEST FROM gv$session a inner join  gv$locked_object b  on a.sid =b.SESSION_ID and a.inst_id=b.inst_id inner join dba_objects c on b.object_id=c.object_id inner join gv$lock c on a.sid=c.sid;

no rows selected


--move表操作

SQL> alter table test.tab1 move;

Table altered.


--检查表大小及索引情况

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes   where owner='TEST';


TABLE_NAME      INDEX_NAME           STATUS       BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB1            IDX_TAB1_ID          UNUSABLE          1         168 NO



SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';


OWNER      SEGMENT_NAME                   SEGMENT_TY TABLESPACE_NAME                                  MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST       TAB1                           TABLE      TEST                                           .125

TEST       TAB2                           TABLE      TEST                                             12

TEST       TAB3                           TABLE      TEST                                             12

TEST       IDX_TAB2_ID                    INDEX      TEST                                              2

TEST       IDX_TAB3_ID                    INDEX      TEST                                              2



--重建索引

SQL> alter index test.IDX_TAB1_ID rebuild online;


--再次检查

SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes   where owner='TEST';


TABLE_NAME      INDEX_NAME           STATUS       BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB1            IDX_TAB1_ID          VALID             1           2 NO

TAB2            IDX_TAB2_ID          VALID             1         168 NO

TAB3            IDX_TAB3_ID          VALID             1         168 NO


SQL> select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';


OWNER      SEGMENT_NAME                   SEGMENT_TY TABLESPACE_NAME                                  MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST       TAB1                           TABLE      TEST                                           .125

TEST       TAB2                           TABLE      TEST                                             12

TEST       TAB3                           TABLE      TEST                                             12

TEST       IDX_TAB1_ID                    INDEX      TEST                                          .0625

TEST       IDX_TAB2_ID                    INDEX      TEST                                              2

TEST       IDX_TAB3_ID                    INDEX      TEST                                              2


6 rows selected.


结论:在没有DML操作时候,MOVE 表操作与11g一样正常,但索引失效,需要重建解决。



测试方法3:

--数据不提交,MOVE TAB2表加online (12c新特性)

SQL> insert into tab2 select * from tab3;

75723 rows created.


SQL> commit;

Commit complete.


SQL> delete tab2;

227168 rows deleted.


SQL> insert into tab2 select * from tab3 where object_id between 500 and 1800; 

1300 rows created.


--检查TAB2

SQL>  select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';  


OWNER      SEGMENT_NAME                   SEGMENT_TY TABLESPACE_NAME                                  MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST       TAB2                           TABLE      TEST                                             34

TEST       TAB3                           TABLE      TEST                                             12

TEST       IDX_TAB2_ID                    INDEX      TEST                                              6

TEST       IDX_TAB3_ID                    INDEX      TEST                                              2


SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes   where owner='TEST';


TABLE_NAME      INDEX_NAME           STATUS       BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB2            IDX_TAB2_ID          VALID             1         168 NO

TAB3            IDX_TAB3_ID          VALID             1         168 NO


--检查TAB2锁情况

SQL> SELECT a.inst_id, a.sid,a.username,a.machine,a.program,c.object_name,b.LOCKED_MODE,c.REQUEST FROM gv$session a inner join  gv$locked_object b  on a.sid =b.SESSION_ID and a.inst_id=b.inst_id inner join dba_objects c on b.object_id=c.object_id inner join gv$lock c on a.sid=c.sid;


   INST_ID    SID USERNAME   MACHINE                                  PROGRAM                                  OBJECT_NAME          LOCKED_MODE    REQUEST

---------- ------ ---------- ---------------------------------------- ---------------------------------------- -------------------- ----------- ----------

         1     62 TEST       ORACLE-12C                               sqlplus@ORACLE-12C (TNS V1-V3)           TAB2                           3          0

         1     62 TEST       ORACLE-12C                               sqlplus@ORACLE-12C (TNS V1-V3)           TAB2                           3          0

         1     62 TEST       ORACLE-12C                               sqlplus@ORACLE-12C (TNS V1-V3)           TAB2                           3          0

         

--move表操作加online

SQL> alter table test.tab2 move online;


注意此操作一直卡住,检查等待事件是因为DML没有提交导致,提交后MOVE表完成;

--检查索引和表情况

SQL>  select owner,segment_name,SEGMENT_TYPE,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';  


OWNER      SEGMENT_NAME                   SEGMENT_TY TABLESPACE_NAME                                  MB

---------- ------------------------------ ---------- ---------------------------------------- ----------

TEST       TAB2                           TABLE      TEST                                          .3125

TEST       TAB3                           TABLE      TEST                                             12

TEST       IDX_TAB2_ID                    INDEX      TEST                                           .125

TEST       IDX_TAB3_ID                    INDEX      TEST                                              2


SQL> select table_name,index_name,status,blevel,leaf_blocks,orphaned_entries from dba_Indexes   where owner='TEST';


TABLE_NAME      INDEX_NAME           STATUS       BLEVEL LEAF_BLOCKS ORP

--------------- -------------------- -------- ---------- ----------- ---

TAB2            IDX_TAB2_ID          VALID             1           5 NO

TAB3            IDX_TAB3_ID          VALID             1         168 NO



结论:加上online后 不影响索引有效性且不影响DML操作,但DML操作不提交会影响MOVE操作。



3、MOVE 分区表


--创建分区表

 CREATE TABLE t

(   tid            NUMBER,

      name   VARCHAR2(50),

     cdate  DATE)

     PARTITION BY RANGE (cdate)

     (PARTITION part_2014 VALUES LESS THAN (TO_DATE('01/01/2015', 'DD/MM/YYYY')) TABLESPACE test,

      PARTITION part_2015 VALUES LESS THAN (TO_DATE('01/01/2016', 'DD/MM/YYYY')) TABLESPACE test);

      

--插入数据,提交

SQL> INSERT INTO t SELECT level, 'name for ' || level, CASE WHEN MOD

      (level, 2) = 0 THEN TO_DATE

      ('01/07/2014', 'DD/MM/YYYY') ELSE TO_DATE

      ('01/07/2015', 'DD/MM/YYYY') END FROM dual CONNECT BY level <= 1000;


1000 rows created.


SQL> commit;

Commit complete.


--收集统计信息

SQL>  EXEC DBMS_STATS.gather_table_stats(USER, 'T');


--创建分区索引

SQL> create index idx_t_tid on T(tid) local;

Index created.


 

--检查分区大小和索引

SQL> select owner,segment_name,SEGMENT_TYPE,PARTITION_NAME,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST' and SEGMENT_NAME='T';


OWNER      SEGMENT_NAME                             SEGMENT_TYPE                             PARTITION_NAME                 TABLESPACE_NAME                                  MB

---------- ---------------------------------------- ---------------------------------------- ------------------------------ ---------------------------------------- ----------

TEST       T                                        TABLE PARTITION                          PART_2014                      TEST                                              8

TEST       T                                        TABLE PARTITION                          PART_2015                      TEST                                              8



SQL> col INDEX_OWNER for a20

SQL> col PARTITION_NAME for a30

SQL> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS from  DBA_IND_PARTITIONS where INDEX_OWNER='TEST';


INDEX_OWNER          INDEX_NAME           PARTITION_NAME                 STATUS       BLEVEL LEAF_BLOCKS

-------------------- -------------------- ------------------------------ -------- ---------- -----------

TEST                 IDX_T_TID            PART_2014                      USABLE            1           2

TEST                 IDX_T_TID            PART_2015                      USABLE            1           2


--再次插入不提交

SQL>  INSERT INTO t SELECT level, 'name for ' || level, CASE WHEN MOD

  2        (level, 2) = 0 THEN TO_DATE

  3        ('01/07/2014', 'DD/MM/YYYY') ELSE TO_DATE

  4        ('01/07/2015', 'DD/MM/YYYY') END FROM dual CONNECT BY level <= 90000;


90000 rows created.


--在线move 表,卡住

SQL> ALTER TABLE TEST.T MOVE PARTITION PART_2014 ONLINE;


--检查锁等待

BLOCK_INST USER_NAME       LO OBJECT          LOCK_MODE            OWNER         SID SQL_ID          LOGON_TIME          EVENT                          SECONDS_IN_WAIT BLOCKING_SESSION

---------- --------------- -- --------------- -------------------- ---------- ------ --------------- ------------------- ------------------------------ --------------- ----------------

         1 SYS             TM SYS_JOURNAL_763 Exclusive            TEST            1 11kvvhbbsgc03   2020-02-17 00:10:43 enq: TX - row lock contention               46               62

                              97


         1 SYS             TM SYS_RMTAB$$_H76 Exclusive            TEST            1 11kvvhbbsgc03   2020-02-17 00:10:43 enq: TX - row lock contention               46               62

                              397


         1 SYS             TM T               Row Exclusive        TEST            1 11kvvhbbsgc03   2020-02-17 00:10:43 enq: TX - row lock contention               46               62

         1 SYS             OD T               Row Exclusive        TEST            1 11kvvhbbsgc03   2020-02-17 00:10:43 enq: TX - row lock contention               46               62

         1 SYS             OD T               Exclusive            TEST            1 11kvvhbbsgc03   2020-02-17 00:10:43 enq: TX - row lock contention               46               62

         1 SYS             TM T               Row Exclusive        TEST            1 11kvvhbbsgc03   2020-02-17 00:10:43 enq: TX - row lock contention               46               62

select  inst_id,SID,SERIAL#,USER#,USERNAME,PROGRAM,event,SQL_ID,blocking_session from gv$session where sid=62


   INST_ID    SID    SERIAL#      USER# USERNAME   PROGRAM                                  EVENT                                    SQL_ID          BLOCKING_SESSION

---------- ------ ---------- ---------- ---------- ---------------------------------------- ---------------------------------------- --------------- ----------------

         1     62      16515        107 TEST       sqlplus@ORACLE-12C (TNS V1-V3)           SQL*Net message from client              8ma202nswg34m

         

--会话62提交后,MOVE表完成

SQL> commit; 

Commit complete.


SQL> ALTER TABLE TEST.T MOVE PARTITION PART_2014 ONLINE;

Table altered.


--检查表以及索引情况:均正常

SQL> select owner,segment_name,SEGMENT_TYPE,PARTITION_NAME,TABLESPACE_NAME,BYTES/1024/1024 mb from dba_segments where owner='TEST';


OWNER      SEGMENT_NAME                             SEGMENT_TYPE                             PARTITION_NAME                 TABLESPACE_NAME                                  MB

---------- ---------------------------------------- ---------------------------------------- ------------------------------ ---------------------------------------- ----------

TEST       TAB2                                     TABLE                                                                   TEST                                          .3125

TEST       TAB3                                     TABLE                                                                   TEST                                             12

TEST       T                                        TABLE PARTITION                          PART_2015                      TEST                                              8

TEST       T                                        TABLE PARTITION                          PART_2014                      TEST                                              8

TEST       IDX_TAB2_ID                              INDEX                                                                   TEST                                           .125

TEST       IDX_TAB3_ID                              INDEX                                                                   TEST                                              2

TEST       IDX_T_TID                                INDEX PARTITION                          PART_2015                      TEST                                         8.0625

TEST       IDX_T_TID                                INDEX PARTITION                          PART_2014                      TEST                                              8


SQL> select INDEX_OWNER,INDEX_NAME,PARTITION_NAME,STATUS,BLEVEL,LEAF_BLOCKS from  DBA_IND_PARTITIONS where INDEX_OWNER='TEST';


INDEX_OWNER          INDEX_NAME           PARTITION_NAME                 STATUS       BLEVEL LEAF_BLOCKS

-------------------- -------------------- ------------------------------ -------- ---------- -----------

TEST                 IDX_T_TID            PART_2014                      USABLE            1         181

TEST                 IDX_T_TID            PART_2015                      USABLE            1           2


结论:分区表MOVE操作与非分区表一样,只是语法不通而已。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30327022/viewspace-2675787/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
此BLOG仅作为个人笔记记录,如果有误请大家指正,谢谢。

注册时间:2015-07-04

  • 博文量
    61
  • 访问量
    88991