使用expdp导出tour表及关联表数据时遇ORA-02291约束违反，咨询自动导出关联表的方法

Let's break down your problem first: the ORA-02291 error happens because you only exported the TOUR table, but it references a parent table (like HOSTTOUR, from the constraint FK_TOUR_HOSTTOUR) that wasn't included in the dump. When importing, the database can't find the parent records required by the foreign key constraint, hence the error.

Unfortunately, Oracle Data Pump (EXPDP) doesn't have a built-in parameter to automatically export a table plus all its related tables (both parent tables it references and child tables that reference it). But there's a reliable workaround using the database's data dictionary to identify all dependent tables, then including them in your export command.

Step 1: Identify All Related Tables #

You can query the data dictionary to get a list of all tables linked to TOUR via foreign keys. Run these SQL statements as your WHWEB10 user:

Get Parent Tables (Tables referenced by TOUR) #

SELECT DISTINCT c2.table_name
FROM user_constraints c1
JOIN user_cons_columns cc1 ON c1.constraint_name = cc1.constraint_name
JOIN user_constraints c2 ON c1.r_constraint_name = c2.constraint_name
WHERE c1.table_name = 'TOUR' AND c1.constraint_type = 'R';

Get Child Tables (Tables that reference TOUR) #

SELECT DISTINCT c1.table_name
FROM user_constraints c1
JOIN user_cons_columns cc1 ON c1.constraint_name = cc1.constraint_name
JOIN user_constraints c2 ON c1.r_constraint_name = c2.constraint_name
WHERE c2.table_name = 'TOUR' AND c1.constraint_type = 'R';

Step 2: Automate the Table List (Optional) #

If you have a lot of related tables, you can use a PL/SQL script to generate the full list automatically:

SET SERVEROUTPUT ON;
DECLARE
  v_table_list VARCHAR2(4000) := 'TOUR';
BEGIN
  -- Add parent tables
  FOR rec IN (
    SELECT DISTINCT c2.table_name
    FROM user_constraints c1
    JOIN user_cons_columns cc1 ON c1.constraint_name = cc1.constraint_name
    JOIN user_constraints c2 ON c1.r_constraint_name = c2.constraint_name
    WHERE c1.table_name = 'TOUR' AND c1.constraint_type = 'R'
  ) LOOP
    v_table_list := v_table_list || ', ' || rec.table_name;
  END LOOP;
  
  -- Add child tables
  FOR rec IN (
    SELECT DISTINCT c1.table_name
    FROM user_constraints c1
    JOIN user_cons_columns cc1 ON c1.constraint_name = cc1.constraint_name
    JOIN user_constraints c2 ON c1.r_constraint_name = c2.constraint_name
    WHERE c2.table_name = 'TOUR' AND c1.constraint_type = 'R'
  ) LOOP
    v_table_list := v_table_list || ', ' || rec.table_name;
  END LOOP;
  
  DBMS_OUTPUT.PUT_LINE('Full table list for export: ' || v_table_list);
END;
/

Step 3: Update Your EXPDP Command #

Take the table list from the steps above and include all of them in the tables parameter. For example, if your related tables are HOSTTOUR and TOUR_DETAILS, your command becomes:

expdp whweb10/whweb10 directory=dump_dir dumpfile=remoteexport.dmp logfile=remoteexport.log network_link=wamaslink tables=TOUR,HOSTTOUR,TOUR_DETAILS VERSION=10.1

Pro Tip for Data Consistency #

To ensure all exported data is consistent (so you don't run into partial records during import), add the FLASHBACK_SCN or FLASHBACK_TIME parameter. For example:

expdp whweb10/whweb10 directory=dump_dir dumpfile=remoteexport.dmp logfile=remoteexport.log network_link=wamaslink tables=TOUR,HOSTTOUR,TOUR_DETAILS VERSION=10.1 FLASHBACK_TIME="TO_TIMESTAMP('2024-05-20 10:00:00', 'YYYY-MM-DD HH24:MI:SS')"

Alternative: Disable Constraints During Import (Last Resort) #

If you can't export all related tables for some reason, you can temporarily disable foreign key constraints before importing, then re-enable them afterward. Note that this risks importing inconsistent data, so only use this if necessary:

1. Before import:

   ALTER TABLE tour DISABLE CONSTRAINT FK_TOUR_HOSTTOUR;
   -- Repeat for other constraints if needed
   

2. Run your import command
3. After import:

   ALTER TABLE tour ENABLE CONSTRAINT FK_TOUR_HOSTTOUR;
   -- Repeat for other constraints
   

内容的提问来源于stack exchange，提问作者Claudio Ferraro