Materialized view Notes

Submitted by code_admin on Mon, 07/16/2018 - 15:46

Creating auto refresh view

Create no refresh view

  1. DROP MATERIALIZED VIEW APPS.XXGC_AUDIT_PERSON_MV;
  2. CREATE MATERIALIZED VIEW APPS.XXGC_AUDIT_PERSON_MV
  3. TABLESPACE APPS_TS_TX_DATA
  4. PCTUSED    0
  5. PCTFREE    10
  6. INITRANS   2
  7. MAXTRANS   255
  8. STORAGE    (
  9.             INITIAL          128K
  10.             NEXT             128K
  11.             MINEXTENTS       1
  12.             MAXEXTENTS       UNLIMITED
  13.             PCTINCREASE      0
  14.             BUFFER_POOL      DEFAULT
  15.            )
  16. NOCACHE
  17. LOGGING
  18. NOCOMPRESS
  19. NOPARALLEL
  20. BUILD IMMEDIATE
  21. REFRESH FORCE ON DEMAND
  22. WITH PRIMARY KEY
  23. AS
  24. SELECT *
  25. FROM apps.xxgc_audit_person_v;
  26.  
  27. COMMENT ON MATERIALIZED VIEW APPS.XXGC_AUDIT_PERSON_MV IS 'snapshot table for snapshot APPS.XXGC_AUDIT_PERSON_MV';

Refresh the view

  1.    -- Refresh the Materialized view
  2.    logf('About to refresh ' || gc_emp_mview);
  3.    execute immediate 'begin dbms_mview.refresh('''||gc_emp_mview||'''); end;';
  4.    --logf('WARNING REMMED OUT TABLE REFRESH ' || gc_emp_mview);
  5.    logf('After refresh ' || gc_emp_mview);

Tags

RJM Article Type
Quick Reference