February 2006

Replicating Data from VT to MSU

Tasks to be performed at Virginia Tech’s location:

  1. Install and configure Net8 if it has not already been done.
  2. Create an Oracle user with very limited privileges to allow access to data for replication:

CREATE USER snapuser IDENTIFIED BY guest

DEFAULT TABLESPACE gmusers_ts

TEMPORARY TABLESPACE gmtemp_ts;

GRANT CREATE SESSION TO snapuser; (Allows connection to database, cannot create objects.)

GRANT ‘SELECT’ privileges to SNAPUSER on each table to be copied.

  1. Create objects required by Oracle for snapshot management. Log in as SYS and run Oracle script file ‘dbmssnap.sql’ (located in $ORACLE_HOME/rdbms/admin). This was probably done when the Oracle software was installed or upgraded.

cd $ORACLE_HOME/rdbms/admin

sqlplus SYS

SQLPLUS> @dbmssnap

 

Tasks to be performed at Michigan State University:

  1. Create a private database link owned by GYPSY to connect to VT:

CREATE DATABASE LINK sts.world

CONNECT TO snapuser

IDENTIFIED BY guest

USING ‘trap.mothsbane.ento.vt.edu’;

  1. Create a tablespace to hold snapshots:

CREATE TABLESPACE gmsnap_ts

DATAFILE ‘/orandx/oracle/sts/gmsnap01.dbf’ SIZE 50M

AUTOEXTEND ON NEXT 2M MAXSIZE 300M

DEFAULT STORAGE (INITIAL 2M NEXT 2M MINEXTENTS 1

MAXEXTENTS UNLIMITED PCTINCREASE 0)

ONLINE;

  1. Create snapshots. Include storage parameters:

CREATE MATERIALIZED VIEW GYPSY.VA_ERRORS

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('07-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH ROWID

AS

SELECT "ERRORS"."ROW_ID" "ROW_ID","ERRORS"."OWNER" "OWNER","ERRORS"."TABLE_NAME" "TABLE_NAME","ERRORS"."CONSTRAINT" "CONSTRAINT","ERRORS"."MESSAGE" "MESSAGE" FROM "GYPSY"."ERRORS"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "ERRORS";

 

CREATE MATERIALIZED VIEW GYPSY.VA_ESUMM

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('07-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH ROWID

AS

SELECT "ERROR_SUMMARY"."ID" "ID","ERROR_SUMMARY"."CONSTRAINT" "CONSTRAINT","ERROR_SUMMARY"."DATE_IN" "DATE_IN","ERROR_SUMMARY"."DATE_OUT" "DATE_OUT" FROM "GYPSY"."ERROR_SUMMARY"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "ERROR_SUMMARY";

 

CREATE MATERIALIZED VIEW GYPSY.VA_GM_HISTORICAL

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('27-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 30) + 6/24

WITH PRIMARY KEY

AS

SELECT "GM_HISTORICAL"."ID" "ID","GM_HISTORICAL"."AIPM_QUAD" "AIPM_QUAD","GM_HISTORICAL"."SITE" "SITE","GM_HISTORICAL"."UTM_EAST" "UTM_EAST","GM_HISTORICAL"."UTM_NORTH" "UTM_NORTH","GM_HISTORICAL"."GRID" "GRID","GM_HISTORICAL"."PLACE_DATE" "PLACE_DATE","GM_HISTORICAL"."TOTAL_CATCH" "TOTAL_CATCH","GM_HISTORICAL"."COUNTY" "COUNTY","GM_HISTORICAL"."STATE" "STATE","GM_HISTORICAL"."AGENCY" "AGENCY","GM_HISTORICAL"."POL_JURISDICTION" "POL_JURISDICTION","GM_HISTORICAL"."TRAP_TYPE" "TRAP_TYPE","GM_HISTORICAL"."TRAPPER" "TRAPPER","GM_HISTORICAL"."HABITAT" "HABITAT","GM_HISTORICAL"."GRIDNODE_UTME" "GRIDNODE_UTME","GM_HISTORICAL"."GRIDNODE_UTMN" "GRIDNODE_UTMN","GM_HISTORICAL"."SENTINEL" "SENTINEL","GM_HISTORICAL"."PROJECT" "PROJECT","GM_HISTORICAL"."USGS_CODE" "USGS_CODE","GM_HISTORICAL"."GENERATED_CATCH" "GENERATED_CATCH","GM_HISTORICAL"."YEAR" "YEAR","GM_HISTORICAL"."GPS_DATA" "GPS_DATA","GM_HISTORICAL"."ENTRY_TYPE" "ENTRY_TYPE","GM_HISTORICAL"."OMIT_REASON" "OMIT_REASON","GM_HISTORICAL"."OUTSIDE_TARGET" "OUTSIDE_TARGET","GM_HISTORICAL"."DISTANCE_OUTSIDE" "DISTANCE_OUTSIDE","GM_HISTORICAL"."BLOCK_ID" "BLOCK_ID","GM_HISTORICAL"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."GM_HISTORICAL"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "GM_HISTORICAL";

 

CREATE UNIQUE INDEX GYPSY.PK_GMHIST_ID1 ON GYPSY.VA_GM_HISTORICAL

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_GM_HIST_INSP

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('27-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 30) +6/24

WITH PRIMARY KEY

AS

SELECT "GM_HIST_INSPECT"."QUAD" "QUAD","GM_HIST_INSPECT"."SITE" "SITE","GM_HIST_INSPECT"."DAY" "DAY","GM_HIST_INSPECT"."VISIT" "VISIT","GM_HIST_INSPECT"."CONDITION" "CONDITION","GM_HIST_INSPECT"."CATCH" "CATCH","GM_HIST_INSPECT"."TRAPPER" "TRAPPER","GM_HIST_INSPECT"."ID" "ID","GM_HIST_INSPECT"."DEPTH" "DEPTH","GM_HIST_INSPECT"."FIELD_CHECK" "FIELD_CHECK","GM_HIST_INSPECT"."GPS_DATA" "GPS_DATA","GM_HIST_INSPECT"."QC_FAIL" "QC_FAIL","GM_HIST_INSPECT"."UTM_EAST" "UTM_EAST","GM_HIST_INSPECT"."UTM_NORTH" "UTM_NORTH","GM_HIST_INSPECT"."ENTRY_TYPE" "ENTRY_TYPE","GM_HIST_INSPECT"."YEAR" "YEAR","GM_HIST_INSPECT"."STATE" "STATE","GM_HIST_INSPECT"."COUNTY" "COUNTY","GM_HIST_INSPECT"."BLOCK_ID" "BLOCK_ID","GM_HIST_INSPECT"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."GM_HIST_INSPECT"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "GM_HIST_INSPECT";

 

CREATE UNIQUE INDEX GYPSY.PK_GMHIST_INSP_ID1 ON GYPSY.VA_GM_HIST_INSP

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_INSPECT

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('07-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "INSPECTION"."QUAD" "QUAD","INSPECTION"."SITE" "SITE","INSPECTION"."DAY" "DAY","INSPECTION"."VISIT" "VISIT","INSPECTION"."CONDITION" "CONDITION","INSPECTION"."CATCH" "CATCH","INSPECTION"."TRAPPER" "TRAPPER","INSPECTION"."ADD_DATE" "ADD_DATE","INSPECTION"."SCAN_DATE" "SCAN_DATE","INSPECTION"."ID" "ID","INSPECTION"."DEPTH" "DEPTH","INSPECTION"."FIELD_CHECK" "FIELD_CHECK","INSPECTION"."GPS_DATA" "GPS_DATA","INSPECTION"."QC_FAIL" "QC_FAIL","INSPECTION"."UTM_EAST" "UTM_EAST","INSPECTION"."UTM_NORTH" "UTM_NORTH","INSPECTION"."ENTRY_TYPE" "ENTRY_TYPE","INSPECTION"."UTM_ZONE" "UTM_ZONE","INSPECTION"."STATE" "STATE" FROM "GYPSY"."INSPECTION"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "INSPECTION";

 

CREATE UNIQUE INDEX GYPSY.PK_INSPECT_ID1 ON GYPSY.VA_INSPECT

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_OMITS

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "OMITTED_SITES"."ID" "ID","OMITTED_SITES"."QUAD" "QUAD","OMITTED_SITES"."SITE" "SITE","OMITTED_SITES"."UTM_EAST" "UTM_EAST","OMITTED_SITES"."UTM_NORTH" "UTM_NORTH","OMITTED_SITES"."COUNTY" "COUNTY","OMITTED_SITES"."STATE" "STATE","OMITTED_SITES"."AGENCY" "AGENCY","OMITTED_SITES"."TRAPPER" "TRAPPER","OMITTED_SITES"."GRID" "GRID","OMITTED_SITES"."DAY" "DAY","OMITTED_SITES"."PROJECT" "PROJECT","OMITTED_SITES"."WHY" "WHY","OMITTED_SITES"."ADD_DATE" "ADD_DATE","OMITTED_SITES"."SCAN_DATE" "SCAN_DATE","OMITTED_SITES"."GPS_DATA" "GPS_DATA","OMITTED_SITES"."ENTRY_TYPE" "ENTRY_TYPE","OMITTED_SITES"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."OMITTED_SITES"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "OMITTED_SITES";

 

CREATE UNIQUE INDEX GYPSY.PK_OMITTED_QUADSITE1 ON GYPSY.VA_OMITS

(QUAD, SITE)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_PEOPLE

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "PEOPLE"."ID" "ID","PEOPLE"."INITIALS" "INITIALS","PEOPLE"."FIRSTNAME" "FIRSTNAME","PEOPLE"."MI" "MI","PEOPLE"."LASTNAME" "LASTNAME","PEOPLE"."ADDRESS1" "ADDRESS1","PEOPLE"."ADDRESS2" "ADDRESS2","PEOPLE"."CITY" "CITY","PEOPLE"."STATE" "STATE","PEOPLE"."ZIP" "ZIP","PEOPLE"."AGENCY" "AGENCY","PEOPLE"."PHONE" "PHONE","PEOPLE"."PHONE2" "PHONE2","PEOPLE"."TITLE" "TITLE","PEOPLE"."SUPERVISOR" "SUPERVISOR","PEOPLE"."TRAPPER" "TRAPPER","PEOPLE"."FAX" "FAX","PEOPLE"."E_MAIL" "E_MAIL","PEOPLE"."NETWORK_REP" "NETWORK_REP","PEOPLE"."MAIL_LABEL" "MAIL_LABEL" FROM "GYPSY"."PEOPLE"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "PEOPLE";

 

CREATE UNIQUE INDEX GYPSY.PK_PEOPLEID1 ON GYPSY.VA_PEOPLE

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_PLACE

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "PLACEMENT"."ID" "ID","PLACEMENT"."QUAD" "QUAD","PLACEMENT"."SITE" "SITE","PLACEMENT"."COUNTY" "COUNTY","PLACEMENT"."STATE" "STATE","PLACEMENT"."GRID" "GRID","PLACEMENT"."UTM_EAST" "UTM_EAST","PLACEMENT"."UTM_NORTH" "UTM_NORTH","PLACEMENT"."DAY" "DAY","PLACEMENT"."TRAP_TYPE" "TRAP_TYPE","PLACEMENT"."SENTINEL" "SENTINEL","PLACEMENT"."AGENCY" "AGENCY","PLACEMENT"."TRAPPER" "TRAPPER","PLACEMENT"."TOTAL_CATCH" "TOTAL_CATCH","PLACEMENT"."PROJECT" "PROJECT","PLACEMENT"."GRIDNODE_UTME" "GRIDNODE_UTME","PLACEMENT"."GRIDNODE_UTMN" "GRIDNODE_UTMN","PLACEMENT"."ADD_DATE" "ADD_DATE","PLACEMENT"."SCAN_DATE" "SCAN_DATE","PLACEMENT"."GPS_DATA" "GPS_DATA","PLACEMENT"."ENTRY_TYPE" "ENTRY_TYPE","PLACEMENT"."OUTSIDE_TARGET" "OUTSIDE_TARGET","PLACEMENT"."DISTANCE_OUTSIDE" "DISTANCE_OUTSIDE","PLACEMENT"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."PLACEMENT"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "PLACEMENT";

 

CREATE UNIQUE INDEX GYPSY.PK_PLACEMENT_QDSITEST1 ON GYPSY.VA_PLACE

(QUAD, SITE, STATE)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_SITES

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "SITE_LOCATIONS"."QUAD" "QUAD","SITE_LOCATIONS"."SITE" "SITE","SITE_LOCATIONS"."AGENCY" "AGENCY","SITE_LOCATIONS"."SUPERVISOR" "SUPERVISOR","SITE_LOCATIONS"."TRAPPER" "TRAPPER","SITE_LOCATIONS"."COUNTY" "COUNTY","SITE_LOCATIONS"."STATE" "STATE","SITE_LOCATIONS"."UTM_EAST" "UTM_EAST","SITE_LOCATIONS"."UTM_NORTH" "UTM_NORTH","SITE_LOCATIONS"."GRID" "GRID","SITE_LOCATIONS"."SENTINEL" "SENTINEL","SITE_LOCATIONS"."TRAP_TYPE" "TRAP_TYPE","SITE_LOCATIONS"."PLACED" "PLACED","SITE_LOCATIONS"."ID" "ID","SITE_LOCATIONS"."PROJECT" "PROJECT","SITE_LOCATIONS"."TREATMENT_TYPE" "TREATMENT_TYPE","SITE_LOCATIONS"."BID_UNIT" "BID_UNIT","SITE_LOCATIONS"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."SITE_LOCATIONS"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "SITE_LOCATIONS";

 

CREATE UNIQUE INDEX GYPSY.PK_SITELOC_QUADSITE1 ON GYPSY.VA_SITES

(QUAD, SITE, STATE)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_SITES_CTY

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "SITE_LOCATIONS_CTY"."COUNTY" "COUNTY","SITE_LOCATIONS_CTY"."BLOCK_ID" "BLOCK_ID","SITE_LOCATIONS_CTY"."SITE" "SITE","SITE_LOCATIONS_CTY"."QUAD" "QUAD","SITE_LOCATIONS_CTY"."AGENCY" "AGENCY","SITE_LOCATIONS_CTY"."SUPERVISOR" "SUPERVISOR","SITE_LOCATIONS_CTY"."TRAPPER" "TRAPPER","SITE_LOCATIONS_CTY"."STATE" "STATE","SITE_LOCATIONS_CTY"."UTM_EAST" "UTM_EAST","SITE_LOCATIONS_CTY"."UTM_NORTH" "UTM_NORTH","SITE_LOCATIONS_CTY"."GRID" "GRID","SITE_LOCATIONS_CTY"."SENTINEL" "SENTINEL","SITE_LOCATIONS_CTY"."TRAP_TYPE" "TRAP_TYPE","SITE_LOCATIONS_CTY"."PLACED" "PLACED","SITE_LOCATIONS_CTY"."ID" "ID","SITE_LOCATIONS_CTY"."PROJECT" "PROJECT","SITE_LOCATIONS_CTY"."TREATMENT_TYPE" "TREATMENT_TYPE","SITE_LOCATIONS_CTY"."BID_UNIT" "BID_UNIT","SITE_LOCATIONS_CTY"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."SITE_LOCATIONS_CTY"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "SITE_LOCATIONS_CTY";

 

CREATE UNIQUE INDEX GYPSY.PK_SITELOC_CTYSTBLKSITE1 ON GYPSY.VA_SITES_CTY

(STATE, COUNTY, BLOCK_ID, SITE)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_TINSPECT

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "TEST_INSPECTION"."ID" "ID","TEST_INSPECTION"."ABBREVIATION" "ABBREVIATION","TEST_INSPECTION"."SITE" "SITE","TEST_INSPECTION"."DAY" "DAY","TEST_INSPECTION"."VISIT" "VISIT","TEST_INSPECTION"."CONDITION" "CONDITION","TEST_INSPECTION"."CATCH" "CATCH","TEST_INSPECTION"."INITIALS" "INITIALS","TEST_INSPECTION"."DATE_IN" "DATE_IN","TEST_INSPECTION"."ROW_ID" "ROW_ID","TEST_INSPECTION"."DEPTH" "DEPTH","TEST_INSPECTION"."GPS_DATA" "GPS_DATA","TEST_INSPECTION"."QC_FAIL" "QC_FAIL","TEST_INSPECTION"."FIELD_CHECK" "FIELD_CHECK","TEST_INSPECTION"."WV_QUAD_CODE" "WV_QUAD_CODE","TEST_INSPECTION"."UTM_EAST" "UTM_EAST","TEST_INSPECTION"."UTM_NORTH" "UTM_NORTH","TEST_INSPECTION"."ENTRY_TYPE" "ENTRY_TYPE","TEST_INSPECTION"."BATCH_LOAD" "BATCH_LOAD","TEST_INSPECTION"."STATE" "STATE","TEST_INSPECTION"."UTM_ZONE" "UTM_ZONE","TEST_INSPECTION"."CSV_FILE" "CSV_FILE" FROM "GYPSY"."TEST_INSPECTION"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "TEST_INSPECTION";

 

CREATE UNIQUE INDEX GYPSY.PK_TESTINSPECT_ID1 ON GYPSY.VA_TINSPECT

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.VA_TPLACE

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('08-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')

NEXT TRUNC(SYSDATE + 1) + 6/24

WITH PRIMARY KEY

AS

SELECT "TEST_PLACEMENT"."ID" "ID","TEST_PLACEMENT"."ABBREVIATION" "ABBREVIATION","TEST_PLACEMENT"."SITE" "SITE","TEST_PLACEMENT"."COUNTY" "COUNTY","TEST_PLACEMENT"."STATE" "STATE","TEST_PLACEMENT"."GRID" "GRID","TEST_PLACEMENT"."UTM_EAST" "UTM_EAST","TEST_PLACEMENT"."UTM_NORTH" "UTM_NORTH","TEST_PLACEMENT"."DAY" "DAY","TEST_PLACEMENT"."TRAP_TYPE" "TRAP_TYPE","TEST_PLACEMENT"."SENTINEL" "SENTINEL","TEST_PLACEMENT"."AGENCY" "AGENCY","TEST_PLACEMENT"."INITIALS" "INITIALS","TEST_PLACEMENT"."DATE_IN" "DATE_IN","TEST_PLACEMENT"."ROW_ID" "ROW_ID","TEST_PLACEMENT"."EXTRA_VAR" "EXTRA_VAR","TEST_PLACEMENT"."GPS_DATA" "GPS_DATA","TEST_PLACEMENT"."ELEVATION" "ELEVATION","TEST_PLACEMENT"."WV_QUAD_CODE" "WV_QUAD_CODE","TEST_PLACEMENT"."ENTRY_TYPE" "ENTRY_TYPE","TEST_PLACEMENT"."BATCH_LOAD" "BATCH_LOAD","TEST_PLACEMENT"."OUTSIDE_TARGET" "OUTSIDE_TARGET","TEST_PLACEMENT"."DISTANCE_OUTSIDE" "DISTANCE_OUTSIDE","TEST_PLACEMENT"."UTM_ZONE" "UTM_ZONE","TEST_PLACEMENT"."CSV_FILE" "CSV_FILE" FROM "GYPSY"."TEST_PLACEMENT"@TRAP.MOTHSBANE.ENTO.VT.EDU@SNAP "TEST_PLACEMENT";

 

CREATE UNIQUE INDEX GYPSY.PK_TESTPLACE_ID1 ON GYPSY.VA_TPLACE

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

The built-in package DBMS_REFRESH is used to schedule automatic updates of the snapshots. The script that is used to create the DBMS_REFRESH package is in $ORACLE_HOME/rdbms/admin/dbmssnap.sql. You can read through the file to find information about the package.

  1. Create a refresh group to be used to schedule updates of the snapshots. The update interval specified in the refresh group will override the interval given with the snapshot creation statement. The following statement will create a refresh group that will be initially updated at 3:00 p.m. of the current day. The snapshots will be updated at 6:00 a.m. daily beginning the next day.

EXECUTE DBMS_REFRESH.MAKE(‘va_ref_group’, ‘va_errors, va_esumm, va_inspect, va_omits, va_people, va_place, va_sites, va_sites_cty, va_tinspect, va_tplace’, TRUNC(SYSDATE)+15/24, ‘TRUNC(SYSDATE+1)+6/24’,TRUE,TRUE);

EXECUTE DBMS_REFRESH.MAKE(‘va_ref_hist_group’,’va_gm_historical, va_gm_hist_insp’, TRUNC(SYSDATE)+15/24, ‘TRUNC(SYSDATE+30)+6/24’, TRUE, TRUE);

 

NOTE: Query the following views to get more information on snapshots and refresh groups:

DBA_SNAPSHOTS - information on snapshots

DBA_REFRESH - information on refresh groups

DBA_SNAPSHOT_REFRESH_TIMES – most recent refresh of snapshots