You can restore any record from the deleted schema that you may have deleted in error. This is done by using SQL insert statements that will copy over selected data from the deleted. In order to do this, you must know how to query the deleted schema. Please refer to the article in the Solution titled QUERY: How do I query the deleted schema?
Once you have found the record(s) in the deleted schema that you want to restore, here's how you would copy them back to the regular schema. Let's use CONSERVATION_SITE for our example. Somebody accidently deleted a site record and now we want to restore it.
Now you would think that you would restore the CONSERVATION_SITE row first, right? Wrong! You must remember that CONSERVATION_SITE is merely a child of the SHAPE table. You must restore the corresponding SHAPE first, before you can restore anything. Because of the relational nature of the database, anytime you do this, you must restore all parent records first, then any children. So, your first step should be to consult the data model and see where your starting point will be.
In SQL+, logged on as biotics_dlink, enter:
insert into shape (shape_id, feature_code, shape_type, has_shape_ind, digital_mapping_by, digital_mapping_date, map_qc_by, qc_com, deleted_ind,
rec_create_date,rec_create_user, rec_last_mod_date, rec_last_mod_user, shape_ou_uid, shape_seq_uid, map_qc_status)
select s.shape_id, s.feature_code, s.shape_type, s.has_shape_ind, s.digital_mapping_by, s.digital_mapping_date, s.map_qc_by, s.qc_com, s.deleted_ind,
s.rec_create_date,s.rec_create_user, s.rec_last_mod_date, s.rec_last_mod_user, s.shape_ou_uid, s.shape_seq_uid, s.map_qc_status
from bioticsdel.shape_del s, bioticsdel.conservation_site_del cs where cs.shape_id = s.shape_id
and s.shape_id = <id of site we want to restore, or any other criteria>;
The row should then be inserted.
Then enter commit;
Now you can restore the corresponding row in the CONSERVATION_SITE table. Your insert would look something like this:
insert into CONSERVATION_SITE ( yes, you have to type in all the columns that you want to restore here)
select cs.conservation_site_id, cs.shape_id, etc, etc, <these columns must match columns in insert clause above>
from bioticsdel.conservation_site_del cs
where cs.shape_id = <whatever you want to restore>;
The row should then be inserted.
Then enter commit;
Now you have the bare bones of a site record restored from the deleted schema. If the site had any child records, such as EO_SITE, SITE_COUNTY, SITE_TRS, SITE_REFERENCE, etc. you can now restore each of them using the above insert as an example. Don't forget to commit; after each insert.
Other types of records can be restored in a similar fashion. You simply need to find the parent record, restore that, then you can restore any child records.
One thing to keep in mind: this restores only tabular data. If you are restoring a record that has associated spatial data, you should read the documentation that accompanies the Solution Recovering a deleted EO.
One last thing: now that you've restored the record to the regular schema, the proper and tidy thing to do is to delete the restored record from the deleted schema. It could make future queries confusing to find identical records in both schemas, and it can lead to inappropriate record deletions in Central Biotics when NatureServe receives the deleted record during Data Exchange. To clean the deleted schema of restored records, follow the instructions in Solution Procedure to remove data from deleted schema which have been restored to the regular schema.
You can auto-generate the insert statements using the TOAD or SQL Developer functionality to export query results as insert statements. Here’s an SQL Developer screenshot showing where you switch from delimited to insert.
TOAD will automatically use the correct table name if it can, but in SQL Developer as far as I can tell it defaults to EXPORT_TABLE and you must manually change it to the correct table name.
Attached are 3 scripts for restoring various types of records - NOTE: These must be edited in multiple places to replace either the ID or date values and do not necessarily include all child tables. Make sure you understand and update the scripts accordingly before using them!