在 oracle 中创建现有表的完整副本 [英] create full copy of existing table in oracle

问题描述

我知道我可以使用 create table t1 select * from table2; 轻松创建表格的副本，但我需要复制:

I know that I can easily create a copy of a table with create table t1 select * from table2; But I need to copy:

• 列
• 评论
• 表空间
• 索引
• 赠款
• 触发器
• 键
• 默认值
• ...
• 任何其他依赖项

有没有办法通过pl/sql 程序来做到这一点?我的 Oracle 版本是 11R2.

Is there way to do it by pl/sql procedure? My Oracle version is 11R2.

表的SQL:

-- Create table

    create table SCHEMA.MY_TABLE
    (
      id      number(1),
      name    varchar2(30),
      dat     date
    )
    tablespace MY_TS
      pctfree 10
      initrans 1
      maxtrans 255
      storage
      (
        initial 10M
        next 1M
        minextents 1
        maxextents unlimited
      );
    -- Add comments to the table 
    comment on table SCHEMA.MY_TABLE
      is 'MY TABLE';
    comment on column SCHEMA.MY_TABLE.ID
      is 'id';
    comment on column SCHEMA.MY_TABLE.NAME
      is 'name of operation';
    comment on column SCHEMA.MY_TABLE.DAT
      is 'date of operation';

    grant select on SCHEMA.MY_TABLE to PUBLIC;

推荐答案

你可以使用 DBMS_REDEFINITION 包，那个东西可以做整个表的副本，包括注释等.它还会碎片整理"新副本占用的空间比原始表少.

You can use DBMS_REDEFINITION package, that thing can do a copy of whole table including comments etc. It will also "defragment" the new copy to occupy less space than the original table.

更多信息请访问 https://docs.oracle.com/database/121/ARPLS/d_redefi.htm#ARPLS042

请求示例:

让我们创建一个测试模式、一个表并用一些数据填充它:

So let's create a test schema, a table and fill it with some data:

CREATE USER "TEST_SCHEMA" IDENTIFIED BY "TEST";
GRANT UNLIMITED TABLESPACE TO "TEST_SCHEMA";

CREATE TABLE "TEST_SCHEMA"."NAMES" ("ID" NUMBER, "NAME" VARCHAR2(25), PRIMARY KEY("ID"));

INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (1, 'joe');
INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (2, 'pete');
INSERT INTO "TEST_SCHEMA"."NAMES" VALUES (3, 'mark');

现在我们可以查看测试表的内容了:

Now we can check the content of the test table:

SELECT * FROM "TEST_SCHEMA"."NAMES";

让我们通过创建与源表完全一样的空表来开始复制过程(使用 WHERE 1=0，它不会接受任何数据).

Let's begin the copy process by creating empty table exactly as the source table looks like (using WHERE 1=0, which won't take any data).

CREATE TABLE "TEST_SCHEMA"."NAMES_COPY" AS SELECT * FROM "TEST_SCHEMA"."NAMES" "A1" WHERE 1=0;

在这里您可以设置并行化等，准备数据等.一切完成后，让我们开始重新定义过程:

Here you can setup paralellization etc, prepare data etc.. After everything is done, let's start the REDEFINITION process:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('TEST_SCHEMA','NAMES','NAMES_COPY');
END;
/

复制内容时，我们需要复制每个依赖于表的对象(如触发器、索引等......)

When the contents is copied, we need to copy every object that is dependent on the table (like triggers, indexes etc....)

DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('TEST_SCHEMA',  'NAMES',    'NAMES_COPY',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/

我们可以使用这个查询检查在复制依赖项期间是否发生任何错误:

We can check if any errors occured during copying of dependencies with this query:

SELECT "OBJECT_NAME", "BASE_TABLE_NAME", "DDL_TXT" FROM DBA_REDEFINITION_ERRORS;

如果一切正常，我们就可以完成这个过程:

If everything is alright, we can finish the process:

BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('TEST_SCHEMA', 'NAMES',    'NAMES_COPY');
END;
/

瞧，新桌子就像克隆羊多莉，里面装着所有东西:

And voila, the new table is like the cloned sheep Dolly with everything in it:

SELECT * FROM "TEST_SCHEMA"."NAMES_COPY";

现在要清理测试模式，运行这些:

Now to cleaup the test schema, run these:

DROP TABLE "TEST_SCHEMA"."NAMES";
DROP TABLE "TEST_SCHEMA"."NAMES_COPY";
DROP USER "TEST_SCHEMA" CASCADE;

希望对您有所帮助.

这篇关于在 oracle 中创建现有表的完整副本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！