更新时间:2023-02-03 16:11:13
你可以使用 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;
希望对您有所帮助.