达梦数据库表备份还原的使用介绍及批量备份还原多张表，跨模式备份还原表

达梦数据库表备份还原的使用介绍及批量备份还原多张表，跨模式备份还原表

达梦数据库支持逻辑备份和物理备份吗？        答案是肯定的。         逻辑备份是指利用dexp导出工具，将指定对象（库级、模式级、表级）的数据导出到文件的备份方式。逻辑备份针对的是数据内容，并不关心这些数据物理存储在什么位置。         物理备份则直接扫描数据库文件，找出那些已经分配、使用的数据页，拷贝并保存到备份集中。物理备份过程中，不关心数据页的具体内容是什么，也不关心数据页属于哪一张表，只是简单的根据数据库文件系统的描述，来挑选有效的数据页。          这两种备份方式，分别适应不同的应用场景，这里重点介绍物理备份中的表备份，关于逻辑备份更详细的说明

在物理备份中，按照备份内容不同，可以分为数据备份和归档日志备份。数据备份主要针对数据文件内容，包括库备份、表空间备份和表备份。

本文主要针对表备份还原的使用进行讲解。

目录

​​1、表备份​​

​​2、表还原​​

​​3、备份还原语法​​

​​3.1、备份语法：​​

​​3.2、还原语法：​​

​​4、示例​​

​​4.1、备份示例​​

​​4.2、还原示例​​

​​5、表还原高级主题​​

​​5.1、指定还原时不重建索引​​

​​5.2、指定还原时不重建约束 ​​

​​6、实战补充​​

​​6.1、批量生成表备份的SQL语句​​

​​6.2、批量进行表备份​​

​​6.3、批量生成表还原的语句​​

​​6.4、批量进行表还原​​

​​7、实战补充高级​​

​​7.1、跨用户还原​​

​​7.2、详细操作​​

​​7.2.1、原用户下批量生成备份表的SQL语句​​

​​7.2.2、源用户批量进行表备份​​

​​7.2.3、逻辑导出源用户表结构​​

​​7.2.4、逻辑导入目的用户表结构​​

​​7.2.5、目的用户批量生成还原表结构的SQL语句​​

​​7.2.6、目的用户批量进行表结构还原​​

​​7.2.7、目的用户批量生成还原表的SQL语句​​

​​7.2.8、目的用户批量进行表还原​​

​​7.2.9、目的用户创建索引​​

​​7.2.10、使用源用户根据列为非空的表查询拼出设置表列非空的语句​​

​​7.2.11、目的用户修改列为非空​​

​​8、注意事项​​

​​9、附录​​

​​9.1、总结​​

​​9.2、还原时使用不同条件的测试情况​​

所有测试基于版本：DM8.1.2.128-ENT

1、表备份

表备份是指拷贝指定表的所有数据页到备份集中，并会记录各个数据页之间的逻辑关系用以恢复。表备份只能在联机状态下执行，一次表备份操作只能备份一张用户表，并且不支持增量表备份。

表备份主要包括数据备份和元信息备份两部分。与库备份和表空间备份不同，表备份不是直接扫描数据文件，而是从BUFFER中加载数据页，拷贝到备份片文件中。表备份的元信息则包括建表语句、重建约束语句、重建索引语句，以及其他相关属性信息。表备份不需要配置归档就可以执行，并且不支持增量表备份。

2、表还原

表还原是从表备份集读取数据，重新恢复目标表数据，还会在目标表上重建索引、约束。

3、备份还原语法

3.1、备份语法：

BACKUP TABLE <表名> [TO <备份名>] [BACKUPSET '<备份集路径>'] [DEVICE TYPE <介质类型> [PARMS '<介质参数>']] [BACKUPINFO '<备份描述>'] [MAXPIECESIZE <备份片限制大小>] [LIMIT |] [IDENTIFIED BY <密码>|”<密码>” [WITH ENCRYPTION ][ENCRYPT WITH <加密算法>]] [COMPRESSED [LEVEL <压缩级别>]] [TRACE FILE ''] [TRACE LEVEL ]; ::= READ SPEED <读速度上限> [WRITE SPEED <写速度上限>] ::= WRITE SPEED <写速度上限> TABLE：指定备份的表，只能备份用户表。 TO：指定生成备份名称。若未指定，系统随机生成，默认备份名格式为：DB_备份类型_表名_备份时间。其中，备份时间为开始备份的系统时间。 BACKUPSET：指定当前备份集生成路径。若指定为相对路径，则在默认备份路径中生成备份集。若不指定具体备份集路径，则在默认备份路径下以约定规则生成默认的表备份集目录。表备份默认备份集目录名生成规则：TAB_表名_BTREE_时间，如TAB_T1_BTREE_20180518_143057_123456。表明该备份集为2018年5月18日14时30分57秒123456毫秒时生成的表名为T1的表备份集。若表名超长使备份集目录完整名称长度大于128个字节将直接报错路径过长。 DEVICE TYPE：指存储备份集的介质类型，表备份暂时只支持DISK。PARMS：只对介质类型为TAPE时有效。BACKUPINFO：备份的描述信息。最大不超过256个字节。 MAXPIECESIZE：最大备份片文件大小上限，以M为单位，最小32M，32位系统最大2G，64位系统最大128G。 LIMIT：指定备份时最大的读写文件速度，单位为M/S，默认为0，表示无速度限制。 IDENTIFIED BY：指定备份时的加密密码。密码可以用双引号括起来，这样可以避免一些特殊字符通不过语法检测。密码的设置规则遵行ini参数pwd_policy指定的口令策略。 WITH ENCRYPTION：指定加密类型，0表示不加密，不对备份文件进行加密处理；1表示简单加密，对备份文件设置口令，但文件内容仍以明文方式存储；2表示完全数据加密，对备份文件进行完全的加密，备份文件以密文方式存储。当不指定WITH ENCRYPTION子句时，采用简单加密。 ENCRYPT WITH：指定加密算法。当不指定ENCRYPT WITH子句时，使用AES256_CFB加密算法。 说明：加密算法包括： DES_ECB、DES_CBC、DES_CFB、DES_OFB、DESEDE_ECB、 DESEDE_CBC、DESEDE_CFB、DESEDE_OFB、AES128_ECB、 AES128_CBC、AES128_CFB 、AES128_OFB、AES192_ECB、 AES192_CBC、AES192_CFB 、AES192_OFB、AES256_ECB、 AES256_CBC、AES256_CFB 、AES256_OFB 、RC4COMPRESSED：是否对备份数据进行压缩处理。LEVEL表示压缩等级，取值范围0~9：0表示不压缩；1表示1级压缩；9表示9级压缩。压缩级别越高，压缩速度越慢，但压缩比越高。若指定COMPRESSED，但未指定LEVEL，则压缩等级默认1；若未指定COMPRESSED，则默认不进行压缩处理。 TRACE FILE：指定生成的TRACE文件。启用TRACE，但不指定TRACE FILE时，默认在DM数据库系统的log目录下生成DM_SBTTRACE_年月.LOG文件；若使用相对路径，则生成在执行码同级目录下；若用户指定TRACE FILE，则指定的文件不能为已经存在的文件，否则报错。TRACE FILE不可以为ASM文件。 TRACE LEVEL：是否启用TRACE。有效值1、2，默认为1表示不启用TRACE，此时若指定了TRACE FILE，会生成TRACE文件，但不写入TRACE信息；为2启用TRACE并在TRACE文件中写入TRACE相关内容。 READ SPEED：备份时读速度上限，取值范围0~2147483647，单位为M/S，0表示无限制。 WRITE SPEED：备份时写速度上限，取值范围0~2147483647，单位为M/S，0表示无限制。使用说明： 1. 仅支持对用户的非分区的行存储表和堆表进行备份，不支持对分区表的备份。在非分区表中，也不支持对临时表、物化视图表、物化视图附属表、日志表和特定模式（DBG_PKG/INFORMATION_SCHEMA/INFO_SCHEM/SYSREP/SYSGEO/SYSJOB/SYSCPT/SYS）下的表进行表备份。 2. 表的列类型为对象类型的表不支持表备份。 3. 表备份不备份表上的注释以及default表达式中的函数定义，因此还原时需用户自行确认。 4. 当备份数据超过限制大小时，会生成新的备份文件，新的备份文件名是初始文件名后加文件编号。 5. 表备份时，其所属表空间必须处于联机状态。 6. 目前表备份不支持备份到TAPE介质上。

3.2、还原语法：

RESTORE TABLE [<表名>] [STRUCT] [KEEP TRXID] FROM BACKUPSET'<备份集路径>' [DEVICE TYPE <介质类型> [PARMS '<介质参数>']] [IDENTIFIED BY <密码>|”<密码>” [ENCRYPT WITH <加密算法>]] [TRACE FILE ''] [TRACE LEVEL ]; 表名：指定需要还原的表名称。指定表名还原时数据库中必须存在该表，否则报错，不会从备份集判断是否存在目标表。 STRUCT：执行表结构还原，若未指定，则认为是表中数据还原；表数据还原要求还原目标表结构与备份集中完全一致，否则报错，所以表结构还原可以在表数据还原之前执行，减少报错。 KEEP TRXID：指定还原后数据页上记录的TRXID保持不变，若发现备份时系统最大的TRXID大于等于当前系统的最大TRXID，则将当前系统最大事务ID+1000。调整后副作用：rec_id >= next_trxid的记录，或者rec_id <= bak_max_trxid + 1000的记录，可能因为执行了表还原，导致查询结果不正确，原本不可见的数据，变得可见了。 BACKUPSET:表备份时指定的备份集路径。若指定为相对路径，会在默认备份目录下搜索备份集。 DEVICE TYPE：指存储备份集的介质类型，表还原暂时只支持DISK。PARMS：只对介质类型为TAPE时有效。IDENTIFIED BY：加密备份表时，用户设置的加密密码。密码可以用双引号括起来，这样可以避免一些特殊字符通不过语法检测。 ENCRYPT WITH：加密备份表时，用户设置的加密算法。当不指定ENCRYPT WITH子句时，默认为AES256_CFB加密算法。 TRACE FILE：指定生成的TRACE文件。启用TRACE，但不指定TRACE FILE时，默认在DM数据库系统的log目录下生成DM_SBTTRACE_年月.log文件；若使用相对路径，则生成在执行码同级目录下；若用户指定TRACE FILE，则指定的文件不能为已经存在的文件，否则报错。TRACE FILE不可以为ASM文件。 TRACE LEVEL：是否启用TRACE。有效值1、2，默认为1表示不启用TRACE，此时若指定了TRACE FILE，会生成TRACE文件，但不写入TRACE信息；为2启用TRACE并在TRACE文件中写入TRACE相关内容。使用说明: 1. 仅支持对普通用户表进行还原，包括堆表。其中，系统表、临时表、物化视图表、物化视图附属表、日志表以及特定模式（DBG_PKG/INFORMATION_SCHEMA/INFO_SCHEM/SYSREP/SYSGEO/SYSJOB/SYSCPT/SYS）下的表不支持还原。 2. 列类型为对象类型的表不支持表还原。 3. 若还原表中存在位图连接索引和位图连接虚索引则不支持还原。 4. 若为加密库，表还原时要求源库与目标库加密算法一致。 5. 备份集路径指备份集所在目录，其中应包含完整备份数据，包括元数据文件(.meta)和备份片文件(.bak)。仅支持从表备份集中还原表。 6. 表名设置为可选参数。若指定，则数据库中必须存在该表且表定义必须与备份表严格一致；若不指定，则使用备份集中记录的备份表作为还原目标表。 7. 目标表所在的表空间必须处于联机状态。 8. 数据守护环境下，主库允许表备份还原，备库不允许。 9. MOUNT和SUSPEND状态下不允许进行表还原。10. MPP环境不允许进行表还原。 11. 若在语句中指定STRUCT关键字，则执行表结构还原。表结构还原会根据备份集中备份表还原要求，对目标表定义进行校验，并删除目标表中已存在的二级索引和约束。 12. 若不指定STRUCT关键字，则执行表数据还原，表数据还原默认仅会将备份表中聚集索引上的数据进行还原。表数据还原默认仅会在目标表定义与备份表一致且不存在二级索引和约束的情况下执行。 13. 若在未指定STRUCT的情况下，执行还原出现存在二级索引或冗余约束的错误，或在不指定目标表的情况下，报目标不存在的错误，可先执行STRUCT还原后，再继续执行实际数据的还原。 14. 若用户指定TRACE FILE，则指定的文件不能为已经存在的文件，否则报错；也不可以为ASM文件。 15. 若表中存在大字段列，且表备份时INI参数BLOB_OUTROW_REC_STOR大于0，但建立还原目标表时INI参数BLOB_OUTROW_REC_STOR等于0，那么若大字段列存在行外数据，则在执行表还原时会报错，且表数据会丢失。 16. 表还原不检查目标表的缺省表达式（default值）。 17. 若表列进行了加密，则表还原时不能跨库或跨表还原，只能还原到自身。

4、示例

4.1、备份示例

完整的备份表步骤如下：  1) 保证数据库处于OPEN状态；  2) 创建待备份的表TAB_01:

SQL>CREATE TABLE TAB_01(C1 INT);

3) DIsql中输入备份表语句，简单的备份语句如下：

SQL>BACKUP TABLE TAB_01 BACKUPSET '/home/dm_bak/tab_bak_01';

4.2、还原示例

完整的表备份还原步骤如下：  1) 保证数据库为OPEN状态；  2) 创建待备份的表；

SQL>CREATE TABLE TAB_FOR_RES_01(C1 INT);

3) 备份表数据；

SQL>BACKUP TABLE TAB_FOR_RES_01 BACKUPSET '/home/dm_bak/tab_bak_for_res_01';

4) 校验备份，此步骤为可选；

SQL>SELECT SF_BAKSET_CHECK('DISK','/home/dm_bak/tab_bak_for_res_01');

5) 还原表数据。

SQL>RESTORE TABLE TAB_FOR_RES_01 FROM BACKUPSET '/home/dm_bak/tab_bak_for_res_01';

表还原实质是表内数据的还原，以及索引和约束等的重建。如果备份文件与目标表中都包含索引或约束该如何还原呢？

下面以表中包含索引为例说明如何还原表，具体步骤如下：  1) 保证数据库为OPEN状态；  2) 创建待备份的表；

SQL>CREATE TABLE TAB_FOR_RES_02(C1 INT);

3) 创建索引；

SQL>CREATE INDEX I_TAB_FOR_RES_02 ON TAB_FOR_RES_02(C1);

4) 备份表；

SQL>BACKUP TABLE TAB_FOR_RES_02 BACKUPSET '/home/dm_bak/tab_bak_for_res_02';

5) 校验备份，此步骤为可选；

SQL>SELECT SF_BAKSET_CHECK('DISK','/home/dm_bak/tab_bak_for_res_02');

6) 执行表结构还原，表备份和目标表中都包含索引，如果直接执行表数据还原会报错： 还原表中存在二级索引或冗余约束；

SQL>RESTORE TABLE TAB_FOR_RES_02 STRUCT FROM BACKUPSET '/home/dm_bak/tab_bak_for_res_02';

7) 执行表数据还原。

SQL>RESTORE TABLE TAB_FOR_RES_02 FROM BACKUPSET '/home/dm_bak/tab_bak_for_res_02';

5、表还原高级主题

5.1、指定还原时不重建索引

表备份时会默认备份表中的索引，还原时使用RESTORE TABLE...WITHOUT INDEX...语句可选择不还原索引。完整示例如下：  1) 保证数据库为OPEN状态；  2) 准备数据，创建待备份的表及索引；

SQL>CREATE TABLE TAB_FOR_IDX_01(C1 INT); SQL>CREATE INDEX I_TAB_FOR_IDX_01 ON TAB_FOR_IDX_01 (C1);

3) 备份表数据；

SQL>BACKUP TABLE TAB_FOR_IDX_01 BACKUPSET '/home/dm_bak/tab_bak_for_res_01';

4) 校验备份，此步骤为可选；

SQL>SELECT SF_BAKSET_CHECK('DISK','/home/dm_bak/tab_bak_for_res_01');

5) 还原表数据，但不重建索引。

SQL>RESTORE TABLE TAB_FOR_RES WITHOUT INDEX FROM BACKUPSET '/home/dm_bak/tab_bak_for_res_01';

5.2、指定还原时不重建约束

表备份时会默认备份表中的索引定义，还原时使用RESTORE TABLE...WITHOUT CONSTRAINT...语句可选择还原时不重建约束。完整示例如下：

1) 保证数据库为OPEN状态；  2) 准备数据，创建待备份的表及索引；

SQL>CREATE TABLE TAB_FOR_CONS_01(C1 INT); SQL>CREATE INDEX I_TAB_FOR_CONS_01 ON TAB_FOR_CONS_01 (C1);

3) 备份表数据；

SQL>BACKUP TABLE TAB_FOR_CONS_01 BACKUPSET '/home/dm_bak/tab_bak_for_res_01';

4) 校验备份，此步骤为可选；

SQL>SELECT SF_BAKSET_CHECK('DISK','/home/dm_bak/tab_bak_for_res_01');

5) 还原表数据，但不还原约束。

SQL>RESTORE TABLE TAB_FOR_RES WITHOUT CONSTRAINT FROM BACKUPSET '/home/dm_bak/tab_bak_for_res_01';

6、实战补充

实际工作中如果只涉及几张表，那么参考以上方式手工进行表的备份还原就可以了，个别情况下可能需要备份上百张表或更多表，那么使用以上方式去做的话效率是非常低的，因此可以使用以下方式批量操作

6.1、批量生成表备份的SQL语句

--使用要进行备份的用户登录数据库，执行以下SQLSELECT 'BACKUP TABLE "'||TABLE_NAME||'" BACKUPSET ''/home/dmdba/dmbak/TAB_BAK_'||TABLE_NAME ||''';' FROM USER_TABLES;

6.2、批量进行表备份

--示例备份SQL语句如下，以下SQL还可以拆分，分别到不同的session去执行BACKUP TABLE "REGION" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_REGION';BACKUP TABLE "CITY" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_CITY';BACKUP TABLE "LOCATION" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_LOCATION';BACKUP TABLE "JOB" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB';BACKUP TABLE "DEPARTMENT" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_DEPARTMENT';BACKUP TABLE "EMPLOYEE" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_EMPLOYEE';BACKUP TABLE "JOB_HISTORY" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB_HISTORY';BACKUP TABLE "T_OWNERS" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERS';BACKUP TABLE "T_OWNERTYPE" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERTYPE';BACKUP TABLE "T_PRICETABLE" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_PRICETABLE';BACKUP TABLE "T_AREA" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_AREA';BACKUP TABLE "T_OPERATOR" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OPERATOR';BACKUP TABLE "T_ADDRESS" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ADDRESS';BACKUP TABLE "T_ACCOUNT" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ACCOUNT';

6.3、批量生成表还原的语句

--使用要进行备份的用户登录数据库，执行以下SQLSELECT 'RESTORE TABLE "'||TABLE_NAME||'" FROM BACKUPSET ''/home/dmdba/dmbak/TAB_BAK_'||TABLE_NAME ||''';' FROM USER_TABLES;

6.4、批量进行表还原

RESTORE TABLE "REGION" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_REGION';RESTORE TABLE "CITY" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_CITY';RESTORE TABLE "LOCATION" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_LOCATION';RESTORE TABLE "JOB" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB';RESTORE TABLE "DEPARTMENT" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_DEPARTMENT';RESTORE TABLE "EMPLOYEE" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_EMPLOYEE';RESTORE TABLE "JOB_HISTORY" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB_HISTORY';RESTORE TABLE "T_OWNERS" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERS';RESTORE TABLE "T_OWNERTYPE" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERTYPE';RESTORE TABLE "T_PRICETABLE" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_PRICETABLE';RESTORE TABLE "T_AREA" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_AREA';RESTORE TABLE "T_OPERATOR" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OPERATOR';RESTORE TABLE "T_ADDRESS" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ADDRESS';RESTORE TABLE "T_ACCOUNT" FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ACCOUNT';

7、实战补充高级

通过以上示例，我们已经实现了批量对表进行备份还原，不过，其实表备份还原还有更有意思的玩法。

7.1、跨用户还原

与其说跨用户还原，不如说是跨模式还原，在达梦中创建一个用户A，就会自动生成一个与用户名同名的模式名，具体用户名和模式名的对应关系这里不再细说。通过使用表备份，我们可以将源用户A的表备份，并还原到目的用户B下，也就是说，使用表备份，也可以实现在达梦数据库中模式间的数据迁移。

7.2、详细操作

现在有一个需求，要求将A用户的数据迁移到B用户，逻辑导入导出效率较慢，寻求更高的效率完成，那么表的备份还原也是一个不错的方法。

源用户：A

目的用户：B

7.2.1、原用户下批量生成备份表的SQL语句

--A用户连接数据库执行以下SQL，批量生成表备份的SQL语句SELECT 'BACKUP TABLE "A"."'||TABLE_NAME||'" BACKUPSET ''/home/dmdba/dmbak/TAB_BAK_'||TABLE_NAME ||''';' FROM USER_TABLES;

7.2.2、源用户批量进行表备份

--A用户执行以下SQL进行表备份操作BACKUP TABLE "A"."REGION" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_REGION';BACKUP TABLE "A"."CITY" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_CITY';BACKUP TABLE "A"."LOCATION" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_LOCATION';BACKUP TABLE "A"."JOB" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB';BACKUP TABLE "A"."DEPARTMENT" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_DEPARTMENT';BACKUP TABLE "A"."EMPLOYEE" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_EMPLOYEE';BACKUP TABLE "A"."JOB_HISTORY" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB_HISTORY';BACKUP TABLE "A"."T_OWNERS" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERS';BACKUP TABLE "A"."T_OWNERTYPE" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERTYPE';BACKUP TABLE "A"."T_PRICETABLE" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_PRICETABLE';BACKUP TABLE "A"."T_AREA" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_AREA';BACKUP TABLE "A"."T_OPERATOR" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OPERATOR';BACKUP TABLE "A"."T_ADDRESS" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ADDRESS';BACKUP TABLE "A"."T_ACCOUNT" BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ACCOUNT';

7.2.3、逻辑导出源用户表结构

--使用逻辑导出的方式将A用户的对象导出，当然，也可以选择只导出表对象./dexp A/123456789@localhost:5236 FILE=A.dmp DIRECTORY=/home/dmdba/dmbak SCHEMAS=A PARALLEL=4 ROWS=N log=dexp_`date +%Y%m%d%H%M%S`.log

7.2.4、逻辑导入目的用户表结构

--将A的对象逻辑导入到B用户下，注意，约束不导入，后边使用表还原时约束会自动还原./dimp B/123456789@localhost:5236 FILE=A.dmp DIRECTORY=/home/dmdba/dmbak REMAP_SCHEMA=A:B TABLE_EXISTS_ACTION=REPLACE COMMIT_ROWS=10000 PARALLEL=4 log=dimp_`date +%Y%m%d%H%M%S`.log INDEXFILE=idx.sql CONSTRAINTS=N

7.2.5、目的用户批量生成还原表结构的SQL语句

--目的用户B执行以下SQL，生成还原表结构的语句SELECT 'RESTORE TABLE "B"."'||TABLE_NAME||'" STRUCT FROM BACKUPSET ''/home/dmdba/dmbak/TAB_BAK_'||TABLE_NAME ||''';' FROM USER_TABLES;

7.2.6、目的用户批量进行表结构还原

--B用户执行以下SQL进行表结构还原，这一步是为了清理表上的冗余约束和索引RESTORE TABLE "B"."REGION" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_REGION';RESTORE TABLE "B"."CITY" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_CITY';RESTORE TABLE "B"."LOCATION" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_LOCATION';RESTORE TABLE "B"."JOB" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB';RESTORE TABLE "B"."DEPARTMENT" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_DEPARTMENT';RESTORE TABLE "B"."EMPLOYEE" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_EMPLOYEE';RESTORE TABLE "B"."JOB_HISTORY" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB_HISTORY';RESTORE TABLE "B"."T_OWNERS" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERS';RESTORE TABLE "B"."T_OWNERTYPE" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERTYPE';RESTORE TABLE "B"."T_PRICETABLE" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_PRICETABLE';RESTORE TABLE "B"."T_AREA" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_AREA';RESTORE TABLE "B"."T_OPERATOR" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OPERATOR';RESTORE TABLE "B"."T_ADDRESS" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ADDRESS';RESTORE TABLE "B"."T_ACCOUNT" STRUCT FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ACCOUNT';

7.2.7、目的用户批量生成还原表的SQL语句

--目的用户B执行以下SQL，生成还原表的语句SELECT 'RESTORE TABLE "B"."'||TABLE_NAME||'"WITHOUT INDEX FROM BACKUPSET ''/home/dmdba/dmbak/TAB_BAK_'||TABLE_NAME ||''';' FROM USER_TABLES;

7.2.8、目的用户批量进行表还原

--B用户执行以下SQL进行表还原,注意，不重建索引，因为重建后的索引不属于B用户的默认索引表空间，而是位于A用户的默认索引表空间下RESTORE TABLE "B"."REGION" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_REGION';RESTORE TABLE "B"."CITY" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_CITY';RESTORE TABLE "B"."LOCATION" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_LOCATION';RESTORE TABLE "B"."JOB" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB';RESTORE TABLE "B"."DEPARTMENT" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_DEPARTMENT';RESTORE TABLE "B"."EMPLOYEE" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_EMPLOYEE';RESTORE TABLE "B"."JOB_HISTORY" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_JOB_HISTORY';RESTORE TABLE "B"."T_OWNERS" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERS';RESTORE TABLE "B"."T_OWNERTYPE" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OWNERTYPE';RESTORE TABLE "B"."T_PRICETABLE" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_PRICETABLE';RESTORE TABLE "B"."T_AREA" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_AREA';RESTORE TABLE "B"."T_OPERATOR" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_OPERATOR';RESTORE TABLE "B"."T_ADDRESS" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ADDRESS';RESTORE TABLE "B"."T_ACCOUNT" WITHOUT INDEX FROM BACKUPSET '/home/dmdba/dmbak/TAB_BAK_T_ACCOUNT';

7.2.9、目的用户创建索引

SQL> start /home/dmdba/dmbak/idx.sql

7.2.10、使用源用户根据列为非空的表查询拼出设置表列非空的语句

SELECT 'ALTER TABLE "B"."' ||TABLE_NAME ||'" ALTER COLUMN "' ||COL_NAME ||'" SET NOT NULL;'FROM ( SELECT DISTINCT B.NAME AS TABLE_NAME, D."NAME" AS COL_NAME FROM SYSOBJECTS B INNER JOIN SYSCOLUMNS D ON B.ID=D.ID WHERE B.TYPE$ ='SCHOBJ' AND B.SUBTYPE$ ='UTAB' AND D.NULLABLE$ ='N' AND B.SCHID = ( SELECT A.ID FROM SYSOBJECTS A WHERE A.NAME='SYSDBA' AND A.TYPE$='SCH' ) GROUP BY B.NAME, D."NAME" );

7.2.11、目的用户修改列为非空

--B用户执行以下SQL修改对应表的列为非空，由于表还原后，非主键列的not null丢失，因此需要单独执行设置非空ALTER TABLE "B"."QUERYFUNCDEFINE" ALTER COLUMN "MODULEID" SET NOT NULL;ALTER TABLE "B"."QUERYFUNCDEFINE" ALTER COLUMN "FUNCCODE" SET NOT NULL;ALTER TABLE "B"."T1" ALTER COLUMN "C1" SET NOT NULL;ALTER TABLE "B"."T2" ALTER COLUMN "C1" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "ACTIVITY_NAME" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "INTRACTIVE_TYPE" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "EMAIL" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "MOBILE" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "USERAGENT" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "EMAIL_TITLE" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "LABEL" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "CATEGORY1" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "TIME" SET NOT NULL;ALTER TABLE "B"."USERINFO" ALTER COLUMN "TIME1" SET NOT NULL;

8、注意事项

表列类型为对象类型的表不支持表备份，也不支持表还原。表备份不备份表上的注释，default表达式中函数定义，所以还原时需用户自行确认。表名中包含保留字的表不允备份还原。若还原表中存在位图连接索引和位图连接虚索引也不支持还原。表备份支持压缩，但不支持without语句。跨用户还原表时必须指定表名，否则将还原到原来的用户下。跨用户还原要求目的用户表已存在，否则即使指定了表名，仍然会报错“第1 行附近出现错误[-2106]:无效的表或视图名[T1].”而导致还原失败。非cluster主键的表必须先使用“RESTORE TABLE "模式名"."表名" STRUCT FROM BACKUPSET '备份集目录';”还原表结构，因为dimp导入时可不导入约束索引，但是表上的主键约束和系统定义的二级索引还是会创建，如果直接执行“restore table from backupset”会报错“[-8327]:还原表中存在二级索引或冗余约束.”cluster主键的表可直接指定表名方式还原表，无需“restore table struct from backupset”这一步骤表还原后，约束位于目的用户的默认表空间下，但还原后的索引仍然位于原用户的默认索引表空间下，因此索引需要表还原后重建，或使用本文中的方式在还原表时加without index选项不还原索引，而是使用目的用户执行dimp导入对象时的indexfile参数生成的SQL文件（索引创建语句），该文件中的SQL不带索引存储的表空间，因此可以直接到目的用户执行。在第2点中提到的注意事项可以通过逻辑导的方式（dexp/dimp）把结构迁移到目的用户，此后在进行表还原时，表的注释和默认值等不会丢失如果担心7.2.10中查到的设置非空语句有问题（7.2.10使用的语句拼出的SQL包含了主键列的非空设置语句，但在表还原时，主键正常还原，主键自动在对应的列上已经加过了not null），可以使用以下方式，只查询拼写出非主键的列非空语句

SELECT 'ALTER TABLE "' ||TABLE_NAME ||'" ALTER COLUMN "' ||COL_NAME ||'" SET NOT NULL;'FROM ( SELECT DISTINCT B.NAME AS TABLE_NAME, D."NAME" AS COL_NAME FROM SYSOBJECTS B INNER JOIN SYSCOLUMNS D ON B.ID=D.ID WHERE B.TYPE$ ='SCHOBJ' AND B.SUBTYPE$ ='UTAB' AND D.NULLABLE$ ='N' AND B.SCHID = ( SELECT A.ID FROM SYSOBJECTS A WHERE A.NAME='SYSDBA' AND A.TYPE$='SCH' ) EXCEPT ( SELECT A.TABLE_NAME, A.COLUMN_NAME FROM USER_CONS_COLUMNS A INNER JOIN USER_CONSTRAINTS B ON A.CONSTRAINT_NAME=B.CONSTRAINT_NAME WHERE B.CONSTRAINT_TYPE='P' AND B.OWNER ='SYSDBA' ) ) ;

9、附录

9.1、总结

使用表备份方式跨用户进行数据迁移（备份还原），大致流程如下：

（1）dexp导出源用户的对象     （2）dimp导入对象到目的用户（INDEXFILE=idx.sql CONSTRAINTS=N），只生成索引创建语句SQL文件     （3）表还原STRUCT + RESTORE WITHOUT INDEX，还原约束，不还原索引，主外键约束等可以正常还原     （4）执行索引创建的SQL脚本 start /home/dmdba/dmbak/idx.sql     （5）设置对应表的列非空

9.2、还原时使用不同条件的测试情况

--表结构还原STRUCT：主键，主键列的not null，唯一约束，系统定义的索引都被删除，但检查约束还在STRUCT WITHOUT INDEX：在STRUCT相同STRUCT WITHOUT CONSTRAINT：在STRUCT基础上，删除了检查约束lSTRUCT WITHOUT INDEX WITHOUT CONSTRAINT：与STRUCT WITHOUT CONSTRAINT相同--表还原STRUCT + RESTORE：其他列的not null丢失，索引所处的表空间错误STRUCT + RESTORE WITHOUT INDEX：其他列的not null丢失，索引没有重建，可手动重建（如果没有not null的列，推荐的方式）STRUCT + RESTORE WITHOUT CONSTRAINT：表还原报错[-8327]:还原表中存在二级索引或冗余约束.STRUCT + RESTORE WITHOUT INDEX WITHOUT CONSTRAINT：表还原报错[-8327]:还原表中存在二级索引或冗余约束.STRUCT WITHOUT INDEX + RESTORE四种情况与以上一样STRUCT WITHOUT CONSTRAINT + RESTORE：其他列的not null丢失，索引所处的表空间错误STRUCT WITHOUT CONSTRAINT + RESTORE WITHOUT INDEX：其他列的not null丢失，索引没有重建，可手动重建STRUCT WITHOUT CONSTRAINT + RESTORE WITHOUT CONSTRAINT：所有约束，所有列上的not null，系统定义索引全部删除STRUCT WITHOUT CONSTRAINT + RESTORE WITHOUT INDEX WITHOUT CONSTRAINT：所有约束，索引，所有列上的not null全部删除STRUCT WITHOUT INDEX WITHOUT CONSTRAINT + RESTORE四种情况与以上一样

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。