将表/分区移动到其他表空间是否会中断访问该表/分区的查询? [英] Will moving a table/partition to a different tablespace interrupt queries accessing said table/partition?

问题描述

正如标题所述:在访问表/分区时，对其进行移动(不重命名)会对访问该表/分区的任何查询产生负面影响吗?

As the title says: will moving (without renaming) a table/partition while it's being accessed have negative consequences on any queries accessing it?

例如，假设有一个长期运行的SELECT COUNT(*) FROM some_table.
如果我要去ALTER TABLE some_table MOVE TABLESPACE some_other_tablespace，SELECT会失败吗?
它会完成，但结果不正确吗?也许还有其他事情吗?

For example, say there's a long-running SELECT COUNT(*) FROM some_table.
If I were to ALTER TABLE some_table MOVE TABLESPACE some_other_tablespace, would the SELECT fail?
Would it complete, but have incorrect results? Maybe something else entirely?

我能找到的唯一信息是，在某些情况下，将表移动到另一个表空间将需要重建索引，但是没有提到任何活动查询会发生什么情况.

The only info I could find was that moving the table to a different tablespace would require rebuilding the indices under certain circumstances, but none made mention of what happens to any active queries.

推荐答案

使用ORA-08103: object no longer exists可能会失败.

在Oracle中，读取器和写入器不会互相阻塞.这意味着DML和查询不会互相干扰，除了少数奇怪的情况，例如用完UNDO空间.但是，移动表空间或任何类型的ALTER或其他DDL语句不是正常的写操作.当您运行DDL时，至少对于涉及的对象，多版本并发控制模型会崩溃，并且开始发生怪异的事情.

In Oracle, readers and writers do not block each other. Which means DML and queries will not interfere with each other, excluding a few weird cases like running out of UNDO space. But moving a tablespace, or any type of ALTER or other DDL statement, is not a normal write. The multiversion concurrency control model breaks down when you run DDL, at least for the involved objects, and weird things start to happen.

测试较大的动作很困难，但是您可以通过循环许多小的修改和查询来重现这些错误.如果您认为这只是一个理论问题，我已经在生产数据库中看到了这些错误在现实生活中发生的情况.

Testing a large move is difficult, but you can reproduce these errors by looping through a lot of small alters and queries. In case you think this is only a theoretical issue, I have seen these errors occur in real-life, on a production database.

警告:由于无法预测重现此错误将花费多长时间，因此下面存在无限循环.但这通常只需要我数十秒钟.

Warning: infinite loops below since I can't predict how long it will take to reproduce this error. But it usually only takes me tens of seconds.

--Create sample table.
drop table test1 purge;
create table test1(a number, b number)
partition by list(a) (partition p1 values(1), partition p2 values(2))
nologging tablespace users;

--Session 1
begin
  loop
    execute immediate '
      insert /*+ append */ into test1 select mod(level,2)+1, level
      from dual connect by level <= 100000';
    commit;
    execute immediate 'alter table test1 move partition p1 tablespace users';
  end loop;
end;
/       

--Session 2: Read from moved partition
declare
  v_count number;
begin
  loop
    select count(*) into v_count from test1 where a = 1;
  end loop;
end;
/

--Session 3: Read from unmoved partition
declare
  v_count number;
begin
  loop
    select count(*) into v_count from test1 where a = 2;
  end loop;
end;
/

会议2最终死于:

ORA-08103: object no longer exists
ORA-06512: at line 6

会话3将不会失败，它不会查询更改的分区.每个分区都有自己的段，并且是一个单独的对象，可能会不再存在".

Session 3 will not fail, it is not querying an altered partition. Each partition has its own segment, and is a separate object that can potentially "no longer exist".

这篇关于将表/分区移动到其他表空间是否会中断访问该表/分区的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！