Alright, let's tackle this duplicate data issue head-on. You had an import cut short with KILL -9, then re-imported without setting TABLE_EXISTS_ACTION=TRUNCATE, leading to duplicates across 500+ tables. Here's a practical, streamlined plan to fix this:

Step 1: Automate Truncate Statements for All Target Tables #

Manually writing TRUNCATE commands for 500 tables is a waste of time—let's generate them with a quick query. Run this in your database to get the full list of truncate commands:

SELECT CONCAT('TRUNCATE TABLE ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'your_target_schema'; -- Replace with your actual schema/database name

Copy the output into a SQL script file (e.g., truncate_all_tables.sql) for easy execution.

Step 2: Execute Truncations (Handle Foreign Keys) #

If your tables have foreign key constraints, direct truncation will fail. Work around this by temporarily disabling foreign key checks:

1. Log into your database and run:

   SET FOREIGN_KEY_CHECKS = 0; -- Turn off foreign key constraints temporarily
   

2. Run the truncate_all_tables.sql script you generated.
3. Re-enable foreign key checks once done:

   SET FOREIGN_KEY_CHECKS = 1;
   

Quick Backup Tip: Before truncating, take a lightweight backup of your current table data just in case something goes wrong. For Oracle, use:

expdp your_username/your_password@your_db schemas=your_target_schema CONTENT=TABLE_DATA dumpfile=pre_truncate_backup.dmp

For MySQL, use mysqldump --no-create-info to back up only table data.

Step 3: Re-Import Only Table Data with Safeguards #

Now re-run your import, but this time focus solely on table data and prevent duplicates. Using Oracle Data Pump (since you referenced TABLE_EXISTS_ACTION), use this command:

impdp your_username/your_password@your_db schemas=your_target_schema \
  TABLE_EXISTS_ACTION=TRUNCATE \
  CONTENT=TABLE_DATA

Let's break down the critical parameters:

• TABLE_EXISTS_ACTION=TRUNCATE: Ensures the import truncates each table before loading new data, wiping out any remaining duplicates.
• CONTENT=TABLE_DATA: Skips importing metadata (like procedures, functions, or table structures) since those were likely already created during your initial aborted import. This speeds up the process and avoids errors from re-creating existing objects.

Quick Lessons to Avoid This Next Time #

• Never use KILL -9 on database import processes unless it's an absolute emergency—it leaves the database in an inconsistent state. Use proper termination methods like CTRL+C for Data Pump, or the database's native process stop commands.
• Always specify a TABLE_EXISTS_ACTION (Oracle) or equivalent flags (like --truncate/--replace for MySQL) when re-importing to avoid accidental duplicate data.

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