Page History

...

set timing on
-- GEODESC. Remove last expedition's GEODESC project.
drop user geodp### cascade constraints;

-- LIMS. Remove last expedition's LIMS content.
-- Review TRANSFER.LOG entries to review these activities.
call transfer.util_cleanout_transfer_new_tables(); -- (1)
call transfer.box_asman_catalog_delete('EXP346'); -- (2) Last expedition
call transfer.box_lime_audit_delete(###);    -- (4)
call transfer.box_lims_expedition_delete('346');  -- (3) Last expedition
call transfer.box_lims_cleanup();
call transfer.box_lims_defrag(); -- (5)

-- OPS. Remove last expedition's IRIS data.
-- The timestamps below will surely not be correct. Review when the database export was conducted. Review the data based on a time range.
delete from ops.ris_data where observed_time <= to_timestamp('2023-08-11 13:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff');
delete from ops.ris_configuration where last_modified <= to_timestamp('2023-08-11 13:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff');
delete from ops.ris_event where event_timestamp <= to_timestamp('2023-08-11 13:00:00.0', 'yyyy-mm-dd hh24:mi:ssxff');

...

1. VARIANCE: If you need to keep some data from the previous expedition for the next expedition, skip this step. Even if you forget, you can still copy what you need from TRANSFER.NEW_tables without having to resort to the data pump import tool.
2. VARIANCE: If you need to keep some data from the previous expedition for reference on the next expedition, then custom SQL is required to preserve what you need. And you must communicate with the MCS to ensure what you need is preserved on the ASMAN file system--their routine is to expunge the previous expedition.

3. VARIANCE: If multiple expeditions need to be cleaned, repeat the command for each expedition.
   Run the command again if you see errors like this
   

   Code Block
   Error report - ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes ORA-06512: at "SYS.DBMS_OUTPUT", line 32 ...

   Alternatively "set serveroutput off". Then use SQL like that below to monitor progress of transfer.box_lims_expedition_delete() from another terminal window or SQL Developer worksheet. Example
   

   Code Block
   select x_expedition, count(*) from lims.sample group by x_expedition order by x_expedition;

   
   

4. Look up and specify ### (lims.x_lime_audit_main.audit_key). The script as written deletes all audit_key <= ### from lims.x_lime_audit_main. The deletion cascades to linked lims.x_lime_audit_detail.
5. Defragmentation of the space used by SAMPLE, TEST, RESULT is recommended, but not required if you are pressed for time. Defragmentation on 398 took an hour to run with current ODAs and configs. Older ODAs and configs experienced 9-14 hours.

2/13/2024: Removed constraints  from the end of the drop user geodp###  command. Was causing the script to fail.

Remove SCALES not relevant to current shipboard work

...