Records being restored from the deleted schema are not automatically removed from the deleted schema after restoration. It is important to remove data from the deleted schema once it has been restored to the regular schema for data exchange purposes as well as future retrieval purposes. If there are multiple instances of the data within the deleted schema, the script run in Part 1 to recover the deleted data will fail as it will encounter multiple records when it is expecting one.

Solution:

1. Log into SQLPlus as biotics_user. 

2. Create a spool file before running the script, for qc purposes.

3. After running the script, at the prompt enter: set serverout on - this will enable you to see the output in later steps.
4. You can either clean one table at a time or clean ALL tables, which is recommended. If you do it for one table, it is necessary to repeat the procedure for ALL child tables (ie. EO_SPECIMEN, EO_SURVEY, etc). The deleted schema doesn't have the integrity constraints that the regular schema does, so it will happily exist with broken links, but it won't be good for data exchange.

To clean ALL tables (RECOMMENDED), enter: exec clean_all_del;

If you receive an error while running clean_all_del or prefer to clean one table in the deleted schema at a time, enter: exec clean_del('eo'); The name of the REGULAR SCHEMA table you want to clean up goes in the quotes, case doesn't matter. Remember, if you run the procedure for one table, it is necessary to repeat the procedure for ALL child tables (ie. EO_SPECIMEN, EO_SURVEY, etc). The deleted schema doesn't have the integrity constraints that the regular schema does, so it will happily exist with broken links, but it won't be good for data exchange.

set serverout on
exec clean_del('SOURCE_FEATURE');
exec clean_del('SOURCE_FEATURE_REF');
exec clean_del('SOURCE_FEATURE_PRE_POLY');
exec clean_del('SOURCE_FEATURE_PRE_PT');
exec clean_del('SOURCE_FEATURE_PRE_LINE');
exec clean_del('SOURCE_FEATURE_LINE');
exec clean_del('SOURCE_FEATURE_POLY');
exec clean_del('SOURCE_FEATURE_PT');
exec clean_del('VISIT');
exec clean_del('SOURCE_FEATURE_REF');
exec clean_del('EO');
exec clean_del('eo_source_feature');
exec clean_del('eo_OWNER');
exec clean_del('eo_SURVEYOR');
exec clean_del('eo_site');
exec clean_del('eo_managed_area');
exec clean_del('eo_contact');
exec clean_del('eo_reference');
exec clean_del('eo_survey_type');
exec clean_del('eo_keyword');
exec clean_del('eo_survey_site');
exec clean_del('eo_specimen');