Solution
Use the following instructions to delete the records that were loaded incorrectly:
1 Create an SQL (extension .sql) file containing the following command for each table that contains erroneous records. In this command, Table_Name refers to the database table from which you want to remove records.
TRUNCATE TABLE Table_Name
DROP SEQUENCE Table_Name_SEQ
CREATE SEQUENCE Table_Name_SEQ START WITH 1 INCREMENT BY 1 CACHE 75;
2 Launch SQL*Plus and connect as the owner of the database tables.
3 Run the
.sql file you created in step
1.
If records were loaded into a large number of tables before you stopped the load process and you want to remove the records from all of the tables and sequences in the database, use the following instructions:
1 Create an SQL (extension .sql) file containing the following commands:
set heading off
set pagesize 0
set linesize 100
spool truncall.sql
select ‘TRUNCATE TABLE ’ || table_name || ‘;’
from dba_tables
where owner = user;
select ‘DROP SEQUENCE ‘ || sequence_name || ‘;’
from dba_sequences
where sequence_owner = user
and sequence_name like ‘%SEQ’;
select ‘CREATE SEQUENCE ‘ || sequence_name || ‘ START WITH 1 INCREMENT BY 1 CACHE 75;’
from dba_sequences
where sequence_owner = user
and sequence_name like ‘%SEQ’;
spool off
2 Launch SQL*Plus and connect as the owner of the database tables.
3 Run the
.sql file created in step
1.
4 Open the truncall.sql file created by this process in a text editor:
vi truncall.sql
5 Remove any line that does not begin with the TRUNCATE, DROP, or CREATE statements.
6 Launch SQL*Plus and connect as the owner of the database tables.
7 Run truncall.sql.
Once you have removed erroneous records from the database, dump your source version data, making sure to specify the ‑yy 1920 parameter. Then reload your source version data.