使用日期范围导出/导入 Oracle 数据库，避免约束问题

引言

在导出/导入具有特定日期范围的大型 Oracle 数据库时，我们可能会遇到悬挂记录。这意味着子表缺少父键。在本文中，提供了确保安全导出/导入，而不会出现任何数据库约束问题的步骤。

例如

• 父表：pTable
• 子表：cTable（外键到 pTable）

pTable 包含 1 月 1 日至 1 月 31 日的所有数据。在进行一些对账后，子表在 2011 年 2 月 1 日获得了 1 月 29 日、1 月 30 日和 1 月 31 日的数据。

当您想从 2011 年 2 月 1 日到 2011 年 2 月 28 日导入所有数据时，cTable 表将包含所有父表数据，即从 1 月 29 日到 1 月 31 日输入并在 2011 年 2 月 1 日插入的数据。但是，父表 (pTable) 不会包含在 2011 年 2 月 1 日之前插入的数据。

如果数据库中的多个表中存在这种情况怎么办？

在导出时编写简单的日期范围查询，然后在导入后删除缺失的数据，可以解决这些类型的问题。

以下步骤可确保安全导出/导入，而不会出现任何数据库约束问题。我希望这对某人有用。

步骤

1. 使用参数 ROWS=N 和参数 CONSTRAINTS=Y 运行第一次导出。

找到所有具有父子关系的表。仅导出约束和模式的过滤表。不要包含行。

exp user/pwd@mydatabase parfile=FilteredRecordTables.par

在 par 文件中，您可以提及带有日期范围查询的表。这是一个示例 par 文件

buffer=2000000 
compress=YES
FILE=C:\Dumps\Data_Extraction\DB_FilteredRecordTables.dmp
LOG=C:\Dumps\Data_Extraction\DB_FilteredRecordTables.log
Statistics=NONE
triggers=N
tables=Table1
,Table2
,Table3
,...
,...
, Table 200
query= "WHERE DATETIME_STAMP >= (SYSDATE - 90)"

2. 使用 ROWS=Y 和 CONSTRAINTS=N 运行第二次导出。

导出所有过滤的表，无需约束，并且所有行都包含在过滤表的日期范围内。

3. 运行第一次创建的转储文件的导入，以使用约束构建表。
4. 使用 ALTER TABLE DISABLE CONSTRAINT 禁用所有外键约束。

--this will disable all foreign key constraints
BEGIN
for i in (select constraint_name, table_name 
          from user_constraints where constraint_type ='R'
and status = 'ENABLED') 
LOOP
execute immediate 'alter table '||i.table_name||' 
        disable constraint '||i.constraint_name||'';
END LOOP;
END;

5. 运行第二次创建的转储文件的导入，该导入导入数据而不构建约束。
6. 创建一个异常表“Exception”。

使用以下 SQL 语句创建异常表“Exception”，或从 @?/rdbms/admin/UTLEXCPT.SQL 获取它。

create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));

7. 启用所有外键约束。

使用 ALTER TABLE ENABLE CONSTRAINT 启用所有外键约束。将所有错误添加到异常表中。

--this will enable them again
BEGIN
for i in (select constraint_name, table_name from 
          user_constraints where constraint_type ='R'
and status = 'ENABLED') 
LOOP
execute immediate 'alter table '||i.table_name||' enable constraint 
    '||i.constraint_name|| 'EXCEPTIONS INTO EXCEPTIONS' '';
END LOOP;
END;

8. 测试异常表。

测试异常表 - Select row_id from Exceptions where owner=’USER_NAME’ and table_name='SOME_TABLE_NAME'。

9. 删除各自表中未找到父键的行。

--this will delete all rows from table with parent keys not found
BEGIN
for i in (select row_id , table_name from EXCEPTIONS) 
LOOP
execute immediate 'delete '||i.table_name||'where rowid='||i.row_id;
END LOOP;
END;

示例测试

delete 'TABLE1' where rowid in (select row_id 
   from exceptions where owner=user and table_name=''TABLE1');

10. 再次应用约束。

--this will enable them again
BEGIN
for i in (select constraint_name, table_name 
          from user_constraints where constraint_type ='R'
and status = 'ENABLED') 
LOOP
execute immediate 'alter table '||i.table_name||' enable constraint 
     '||i.constraint_name|| 'EXCEPTIONS INTO EXCEPTIONS' '';
END LOOP;
END;

导出所有过滤的表，无需约束，并且所有行都包含在过滤表的日期范围内。

结论

从 Oracle 10g 开始，用户可以选择使用旧的 imp/exp 实用程序，或新引入的数据泵实用程序，称为 expdp 和 impdp。这些新的实用程序引入了急需的性能改进、基于网络的导出和导入等。