Task Description: STS Data Replication via Snapshots

Task:  Replicate data from the database node located at Michigan State University to the main database at Virginia Tech.

Rationale:     To provide the main database with a copy of the data that has been processed at MSU.

Time line:      Continuous

Brief Process Description:

  • Enable communication between the database nodes.
  • Create database users and grant limited privileges.
  • Create snapshots and snapshot groups.

The STS Database has been designed as a distributed database using Oracle’s snapshot utility. Data are processed at MSU for the following states: Indiana, Illinois, Michigan, Minnesota, Wisconsin. The following tables are copied to the main database via snapshots:

ERRORS
ERROR_SUMMARY
ERROR_SUMMARY_CTY
INSPECTION
INSPECTION_CTY
OMITTED_SITES
OMITTED_SITES_CTY
PEOPLE
PLACEMENT
PLACEMENT_CTY
SITE_LOCATIONS
SITE_LOCATIONS_CTY
TEST_INSPECTION
TEST_INSPECTION_CTY
TEST_PLACEMENT
TEST_PLACEMENT_CTY

Processes:

Tasks to be performed at Michigan’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@trap
SQLPLUS> @dbmssnap

Tasks to be performed at Virginia Tech:

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

CREATE DATABASE LINK sts.world
CONNECT TO snapuser
IDENTIFIED BY guest
USING ‘STS.scrub.ent.msu.edu’;

  1. Create a tablespace to hold snapshots:

CREATE TABLESPACE gmsnap_ts
DATAFILE ‘D:\oracle\oradata\trap\gmsnap01.dbf’ SIZE 50M
REUSE AUTOEXTEND
ON NEXT  8K MAXSIZE  32767M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO
ONLINE;

  1. Create snapshots:

CREATE MATERIALIZED VIEW GYPSY.MI_ERRORS
TABLESPACE GMSNAP_TS
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE GMUSER_TS
REFRESH COMPLETE
START WITH TO_DATE('03-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"@STS.WORLD "ERRORS";

 

CREATE MATERIALIZED VIEW GYPSY.MI_ESUMM
TABLESPACE GMSNAP_TS
NOCACHE
LOGGING
NOPARALLEL

BUILD IMMEDIATE
USING INDEX
TABLESPACE GMUSER_TS
REFRESH COMPLETE
START WITH TO_DATE('03-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"@STS.WORLD "ERROR_SUMMARY";

CREATE MATERIALIZED VIEW GYPSY.MI_ESUMM_CTY
TABLESPACE GMSNAP_TS
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
USING INDEX
TABLESPACE GMUSER_TS
REFRESH COMPLETE
START WITH TO_DATE('03-Feb-2006 06:00:00','dd-mon-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE+1)+6/24
WITH ROWID
AS
SELECT "ERROR_SUMMARY_CTY"."ID" "ID","ERROR_SUMMARY_CTY"."CONSTRAINT" "CONSTRAINT","ERROR_SUMMARY_CTY"."DATE_IN" "DATE_IN","ERROR_SUMMARY_CTY"."DATE_OUT" "DATE_OUT" FROM "GYPSY"."ERROR_SUMMARY_CTY"@STS.WORLD "ERROR_SUMMARY_CTY";

 

CREATE MATERIALIZED VIEW GYPSY.MI_INSPECT

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('03-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" FROM "GYPSY"."INSPECTION"@STS.WORLD "INSPECTION";

 

CREATE UNIQUE INDEX GYPSY.PK_INSPECT_ID1 ON GYPSY.MI_INSPECT

(ID)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_INSPECT_CTY

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

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

NEXT TRUNC(SYSDATE+1)+6/24

WITH PRIMARY KEY

AS

SELECT "INSPECTION_CTY"."COUNTY" "COUNTY","INSPECTION_CTY"."BLOCK_ID" "BLOCK_ID","INSPECTION_CTY"."SITE" "SITE","INSPECTION_CTY"."STATE" "STATE","INSPECTION_CTY"."QUAD" "QUAD","INSPECTION_CTY"."DAY" "DAY","INSPECTION_CTY"."VISIT" "VISIT","INSPECTION_CTY"."CONDITION" "CONDITION","INSPECTION_CTY"."CATCH" "CATCH","INSPECTION_CTY"."TRAPPER" "TRAPPER","INSPECTION_CTY"."ADD_DATE" "ADD_DATE","INSPECTION_CTY"."SCAN_DATE" "SCAN_DATE","INSPECTION_CTY"."ID" "ID","INSPECTION_CTY"."DEPTH" "DEPTH","INSPECTION_CTY"."FIELD_CHECK" "FIELD_CHECK","INSPECTION_CTY"."GPS_DATA" "GPS_DATA","INSPECTION_CTY"."QC_FAIL" "QC_FAIL","INSPECTION_CTY"."UTM_EAST" "UTM_EAST","INSPECTION_CTY"."UTM_NORTH" "UTM_NORTH","INSPECTION_CTY"."ENTRY_TYPE" "ENTRY_TYPE","INSPECTION_CTY"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."INSPECTION_CTY"@STS.WORLD "INSPECTION_CTY";

 

CREATE UNIQUE INDEX GYPSY.PK_INSP_CTY_ID ON GYPSY.MI_INSPECT_CTY

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_OMITS

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('04-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"@STS.WORLD "OMITTED_SITES";

 

CREATE UNIQUE INDEX GYPSY.PK_OMITTED_QUADSITE1 ON GYPSY.MI_OMITS

(QUAD, SITE)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_OMITS_CTY

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 "OMITTED_SITES_CTY"."ID" "ID","OMITTED_SITES_CTY"."COUNTY" "COUNTY","OMITTED_SITES_CTY"."BLOCK_ID" "BLOCK_ID","OMITTED_SITES_CTY"."SITE" "SITE","OMITTED_SITES_CTY"."QUAD" "QUAD","OMITTED_SITES_CTY"."UTM_EAST" "UTM_EAST","OMITTED_SITES_CTY"."UTM_NORTH" "UTM_NORTH","OMITTED_SITES_CTY"."STATE" "STATE","OMITTED_SITES_CTY"."AGENCY" "AGENCY","OMITTED_SITES_CTY"."TRAPPER" "TRAPPER","OMITTED_SITES_CTY"."GRID" "GRID","OMITTED_SITES_CTY"."DAY" "DAY","OMITTED_SITES_CTY"."PROJECT" "PROJECT","OMITTED_SITES_CTY"."WHY" "WHY","OMITTED_SITES_CTY"."ADD_DATE" "ADD_DATE","OMITTED_SITES_CTY"."SCAN_DATE" "SCAN_DATE","OMITTED_SITES_CTY"."GPS_DATA" "GPS_DATA","OMITTED_SITES_CTY"."ENTRY_TYPE" "ENTRY_TYPE","OMITTED_SITES_CTY"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."OMITTED_SITES_CTY"@STS.WORLD "OMITTED_SITES_CTY";

 

CREATE UNIQUE INDEX GYPSY.PK_OMIT_CTYBLOCKSITE ON GYPSY.MI_OMITS_CTY

(STATE, COUNTY, BLOCK_ID, SITE)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.MI_PEOPLE

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 "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"@STS.WORLD "PEOPLE";

 

CREATE UNIQUE INDEX GYPSY.PK_PEOPLEID1 ON GYPSY.MI_PEOPLE

(ID)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_PLACE

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 "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"@STS.WORLD "PLACEMENT";

 

CREATE UNIQUE INDEX GYPSY.PK_PLACEMENT_QUADSITE1 ON GYPSY.MI_PLACE

(QUAD, SITE)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_PLACE_CTY

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 "PLACEMENT_CTY"."ID" "ID","PLACEMENT_CTY"."COUNTY" "COUNTY","PLACEMENT_CTY"."BLOCK_ID" "BLOCK_ID","PLACEMENT_CTY"."SITE" "SITE","PLACEMENT_CTY"."QUAD" "QUAD","PLACEMENT_CTY"."STATE" "STATE","PLACEMENT_CTY"."GRID" "GRID","PLACEMENT_CTY"."UTM_EAST" "UTM_EAST","PLACEMENT_CTY"."UTM_NORTH" "UTM_NORTH","PLACEMENT_CTY"."DAY" "DAY","PLACEMENT_CTY"."TRAP_TYPE" "TRAP_TYPE","PLACEMENT_CTY"."SENTINEL" "SENTINEL","PLACEMENT_CTY"."AGENCY" "AGENCY","PLACEMENT_CTY"."TRAPPER" "TRAPPER","PLACEMENT_CTY"."TOTAL_CATCH" "TOTAL_CATCH","PLACEMENT_CTY"."PROJECT" "PROJECT","PLACEMENT_CTY"."GRIDNODE_UTME" "GRIDNODE_UTME","PLACEMENT_CTY"."GRIDNODE_UTMN" "GRIDNODE_UTMN","PLACEMENT_CTY"."ADD_DATE" "ADD_DATE","PLACEMENT_CTY"."SCAN_DATE" "SCAN_DATE","PLACEMENT_CTY"."GPS_DATA" "GPS_DATA","PLACEMENT_CTY"."ENTRY_TYPE" "ENTRY_TYPE","PLACEMENT_CTY"."OUTSIDE_TARGET" "OUTSIDE_TARGET","PLACEMENT_CTY"."DISTANCE_OUTSIDE" "DISTANCE_OUTSIDE","PLACEMENT_CTY"."UTM_ZONE" "UTM_ZONE" FROM "GYPSY"."PLACEMENT_CTY"@STS.WORLD "PLACEMENT_CTY";

 

CREATE UNIQUE INDEX GYPSY.PK_PLACE_CTYBLKSITE ON GYPSY.MI_PLACE_CTY

(STATE, COUNTY, BLOCK_ID, SITE)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_SITES

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 "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"@STS.WORLD "SITE_LOCATIONS";

 

CREATE UNIQUE INDEX GYPSY.PK_SITELOC_QUADSITE1 ON GYPSY.MI_SITES

(QUAD, SITE, STATE)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_SITES_CTY

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 "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"@STS.WORLD "SITE_LOCATIONS_CTY";

 

CREATE UNIQUE INDEX GYPSY.PK_SITELOC_CTYSTBLKSITE1 ON GYPSY.MI_SITES_CTY

(STATE, COUNTY, BLOCK_ID, SITE)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

CREATE MATERIALIZED VIEW GYPSY.MI_TINSPECT

TABLESPACE GMSNAP_TS

NOCACHE

LOGGING

NOPARALLEL

BUILD IMMEDIATE

USING INDEX

TABLESPACE GMUSER_TS

REFRESH COMPLETE

START WITH TO_DATE('03-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"@STS.WORLD "TEST_INSPECTION";

 

CREATE UNIQUE INDEX GYPSY.PK_TESTINSPECT_ID1 ON GYPSY.MI_TINSPECT

(ID)

LOGGING

TABLESPACE GMUSER_TS

NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_TPLACE

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 "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"@STS.WORLD "TEST_PLACEMENT";

 

CREATE UNIQUE INDEX GYPSY.PK_TESTPLACE_ID1 ON GYPSY.MI_TPLACE

(ID)

LOGGING

TABLESPACE GMUSER_TS
NOPARALLEL;

 

 

CREATE MATERIALIZED VIEW GYPSY.MI_TPLACE_CTY

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 "TEST_PLACEMENT_CTY"."ID" "ID","TEST_PLACEMENT_CTY"."COUNTY" "COUNTY","TEST_PLACEMENT_CTY"."BLOCK_ID" "BLOCK_ID","TEST_PLACEMENT_CTY"."SITE" "SITE","TEST_PLACEMENT_CTY"."STATE" "STATE","TEST_PLACEMENT_CTY"."GRID" "GRID","TEST_PLACEMENT_CTY"."UTM_EAST" "UTM_EAST","TEST_PLACEMENT_CTY"."UTM_NORTH" "UTM_NORTH","TEST_PLACEMENT_CTY"."DAY" "DAY","TEST_PLACEMENT_CTY"."TRAP_TYPE" "TRAP_TYPE","TEST_PLACEMENT_CTY"."SENTINEL" "SENTINEL","TEST_PLACEMENT_CTY"."AGENCY" "AGENCY","TEST_PLACEMENT_CTY"."INITIALS" "INITIALS","TEST_PLACEMENT_CTY"."DATE_IN" "DATE_IN","TEST_PLACEMENT_CTY"."ROW_ID" "ROW_ID","TEST_PLACEMENT_CTY"."EXTRA_VAR" "EXTRA_VAR","TEST_PLACEMENT_CTY"."GPS_DATA" "GPS_DATA","TEST_PLACEMENT_CTY"."ELEVATION" "ELEVATION","TEST_PLACEMENT_CTY"."ENTRY_TYPE" "ENTRY_TYPE","TEST_PLACEMENT_CTY"."OUTSIDE_TARGET" "OUTSIDE_TARGET","TEST_PLACEMENT_CTY"."DISTANCE_OUTSIDE" "DISTANCE_OUTSIDE","TEST_PLACEMENT_CTY"."UTM_ZONE" "UTM_ZONE","TEST_PLACEMENT_CTY"."CSV_FILE" "CSV_FILE" FROM "GYPSY"."TEST_PLACEMENT_CTY"@STS.WORLD "TEST_PLACEMENT_CTY";

 

CREATE UNIQUE INDEX GYPSY.PK_TESTPLACE_CTY_ID ON GYPSY.MI_TPLACE_CTY

(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 D:\oracle\ora92\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.

Log in to SQL*Plus as gypsy and type the following:

EXECUTE DBMS_REFRESH.MAKE(‘mi_ref_group’, ‘mi_errors, mi_esumm, mi_esumm_cty, mi_inspect, mi_inspect_cty, mi_omits, mi_omits_cty, mi_people, mi_place, mi_place_cty, mi_sites, mi_sites_cty, mi_tinspect, mi_tinspect_cty, mi_tplace, mi_tplace_cty’, TRUNC(SYSDATE)+15/24, ‘TRUNC(SYSDATE+1)+6/24’,TRUE,TRUE);

Manually refresh a refresh group:

EXECUTE DBMS_REFRESH.REFRESH(‘mi_ref_group’);

Changing or destroying a refresh group:

The refresh interval or refresh time can be changed by running the DBMS_REFRESH.CHANGE procedure in SQL*Plus (log in as gypsy). Example to change refresh interval to every three days:

EXECUTE DBMS_REFRESH.CHANGE(‘mi_ref_group’, NULL, ’TRUNC(SYSDATE+3)+6/24’);

One or more snapshots can be subtracted from the refresh group by running the DBMS_REFRESH.SUBTRACT procedure in SQL*Plus (log in as gypsy). Example to remove the ‘mi_errors’ snapshot from the refresh group:

EXECUTE DBMS_REFRESH.SUBTRACT(‘mi_ref_group’,’mi_errors’);

A refresh group can be deleted by running the DBMS_REFRESH.DESTROY procedure in SQL*Plus (log in as gypsy). Example to destroy the ‘mi_ref_group’ refresh group:

EXECUTE DBMS_REFRESH.DESTROY(‘mi_ref_group’);

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

   
  © 2012 Slow the Spread Foundation, Inc.  

Page developed and maintained by Virginia Tech

  Contact Us