Oracle 从主表填充备份表

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/305797/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 17:30:26  来源:igfitidea点击:

Oracle Populate backup table from primary table

oraclecopy

提问by EvilTeach

The program that I am currently assigned to has a requirement that I copy the contents of a table to a backup table, prior to the real processing.

我当前分配到的程序要求我在实际处理之前将表的内容复制到备份表。

During code review, a coworker pointed out that

在代码期间,一位同事指出

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

is unduly risky, as it is possible for the tables to have different columns, and different column orders.

风险太大,因为表可能有不同的列和不同的列顺序。

I am also under the constraint to not create/delete/rename tables. ~Sigh~

我也受到不创建/删除/重命名表的约束。~叹息~

The columns in the table are expected to change, so simply hard-coding the column names is not really the solution I am looking for.

表中的列预计会发生变化,因此简单地对列名进行硬编码并不是我正在寻找的真正解决方案。

I am looking for ideas on a reasonable non-risky way to get this job done.

我正在寻找有关完成这项工作的合理无风险方式的想法。

回答by Jim Hudson

Does the backup table stay around? Does it keep the data permanently, or is it just a copy of the current values?

备份表会留下来吗?它是永久保留数据,还是只是当前值的副本?

Too bad about not being able to create/delete/rename/copy. Otherwise, if it's short term, just used in case something goes wrong, then you could drop it at the start of processing and do something like

太糟糕了无法创建/删除/重命名/复制。否则,如果它是短期的,只是在出现问题时使用,那么您可以在处理开始时将其删除并执行类似的操作

create table backup_table as select * from primary_table;

Your best option may be to make the select explicit, as

您最好的选择可能是使选择明确,如

insert into backup_table (<list of columns>) select <list of columns> from primary_table;

You could generate that by building a SQL string from the data dictionary, then doing execute immediate. But you'll still be at risk if the backup_table doesn't contain all the important columns from the primary_table.

您可以通过从数据字典构建 SQL 字符串来生成它,然后立即执行。但是,如果backup_table 不包含primary_table 中的所有重要列,您仍然会面临风险。

Might just want to make it explicit, and raise a major error if backup_table doesn't exist, or any of the columns in primary_table aren't in backup_table.

可能只是想让它明确,如果backup_table 不存在,或者primary_table 中的任何列不在backup_table 中,则会引发重大错误。

回答by m0j0

How often do you change the structure of your tables? Your method should work just fine provided the structure doesn't change. Personally I think your DBAs should give you a mechanism for dropping the backup table and recreating it, such as a stored procedure. We had something similar at my last job for truncating certain tables, since truncating is frequently much faster than DELETE FROM TABLE;.

您多久更改一次表的结构?如果结构没有改变,您的方法应该可以正常工作。我个人认为您的 DBA 应该为您提供一种删除备份表并重新创建它的机制,例如存储过程。在我上一份工作中,我们有类似的事情来截断某些表,因为截断通常比DELETE FROM TABLE;.

回答by Michael Dillon

If I had this situation, I would retrieve the column definitions for the two tables right at the beginning of the problem. Then, if they were identical, I would proceed with the simple:

如果遇到这种情况,我会在问题开始时检索两个表的列定义。然后,如果它们相同,我将继续进行简单的操作:

INSERT INTO BACKUP_TABLE
SELECT *
FROM PRIMARY_TABLE

If they were different, I would only proceed if there were no critical columns missing from the backup table. In this case I would use this form for the backup copy:

如果它们不同,我只会在备份表中没有缺少关键列的情况下继续。在这种情况下,我将使用此表单作为备份副本:

INSERT INTO BACKUP_TABLE (<list of columns>) 
SELECT <list of columns> 
FROM PRIMARY_TABLE

But I'd also worry about what would happen if I simply stopped the program with an error, so I might even have a backup plan where I would use the second form for the columns that are in both tables, and also dump a text file with the PK and any columns that are missing from the backup. Also log an error even though it appears that the program completed normally. That way, you could recover the data if the worst happened.

但我也担心如果我只是因为错误而停止程序会发生什么,所以我什至可能有一个备份计划,我会在两个表中的列中使用第二种形式,并转储一个文本文件使用 PK 和备份中缺少的任何列。即使程序看起来正常完成,也要记录错误。这样,如果最坏的情况发生,您就可以恢复数据。

Really, this is a symptom of bad processes somewhere which should be addressed, but defensive programming can help to make it someone else's problem, not yours. If they don't notice the log error message which tells them about the text dump with the missing columns, then its not your fault.

确实,这是应该解决某个地方的不良流程的症状,但是防御性编程可以帮助使其成为其他人的问题,而不是您的问题。如果他们没有注意到日志错误消息告诉他们有关缺少列的文本转储,那么这不是您的错。

But, if you don't code defensively, and the worst happens, it will be partly your fault.

但是,如果您不进行防御性编码,而最坏的情况发生了,那么部分原因将是您的错。

回答by Justin Cave

Is there a reason that you can't just list out the columns in the tables? So

是否有理由不能只列出表格中的列?所以

INSERT INTO backup_table( col1, col2, col3, ... colN )
  SELECT col1, col2, col3, ..., colN
    FROM primary_table

Of course, this requires that you revisit the code when you change the definition of one of the tables to determine if you need to make code changes, but that's generally a small price to pay for insulating yourself from differences in column order, differences in column names, and irrelevent differences in table definitions.

当然,这需要您在更改其中一个表的定义时重新访问代码以确定是否需要进行代码更改,但这通常是为了使自己免受列顺序差异和列差异的影响而付出的很小的代价名称,以及表定义中不相关的差异。

回答by gx.

You could try something like:

你可以尝试这样的事情:

CREATE TABLE secondary_table AS SELECT * FROM primary_table;

Not sure if that automatically copies data. If not:

不确定是否会自动复制数据。如果不:

CREATE TABLE secondary_table AS SELECT * FROM primary_table LIMIT 1;
INSERT INTO secondary_table SELECT * FROM primary_table;

Edit:

编辑:

Sorry, didn't read your post completely: especially the constraints part. I'm afraid I don't know how. My guess would be using a procedure that first describes both tables and compares them, before creating a lengthy insert / select query.

抱歉,没有完全阅读您的帖子:尤其是约束部分。恐怕我不知道如何。我的猜测是在创建冗长的插入/选择查询之前使用首先描述两个表并比较它们的过程。

Still, if you're using a backup-table, I think it's pretty important it matches the original one exactly.

尽管如此,如果您使用的是备份表,我认为它与原始表完全匹配非常重要。