Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 538
  • Last Modified:

how do I configure a materialized view when I want to use the refresh command

I have a materialized view:

CREATE MATERIALIZED VIEW
   BIB_HOLDINGS_MVIEW
AS
    SELECT SITE||'-' ||PDF_NAME as ACCESSION_NUMBER, ID, SITE, PDF_NAME, PDF_EXISTS, TITLE, SUBJECTKEYWORDS, IAC_REPORT_ID, CATEGORY, TYPE, LOCATION, AUTHOR, PUBLISHER, PUBLICATION_DATE, COPIES, CLASSIFICATION, ORG_PERFORMING, SECONDARY_DIST, ORG_MONITORING, ABSTRACT, ABSTRACT2, NUM_PAGES, COMMENTS, ENTERED_BY, COPYRIGHT_EXISTS, DISTRIBUTION_CODE, COPYRIGHT_INFO, SUPPLEMENTARY_NOTES, MODIFIED_DATE, HIERARCHY_1, HIERARCHY_2, HIERARCHY_3, HIERARCHY_4, HIERARCHY_5, HIERARCHY_6, CONTRACT_GRANT_ID, AD_ID, PERFORMING_REPORT_ID, MONITOR_REPORT_ID, PURCHASE_PRICE, PURCHASE_INFO, FILE_NAME, EXPORT_CONTROL, FOR_PAY, OTHER_CLASSIFICATION_MARKINGS, EXTRA_TEXT_FIELD1, EXTRA_TEXT_FIELD2, EXTRA_TEXT_FIELD3, EXTRA_TEXT_FIELD4, EXTRA_TEXT_FIELD5, EXTRA_DATE_FIELD1, EXTRA_DATE_FIELD2, EXTRA_NUMBER_FIELD1, EXTRA_NUMBER_FIELD2 from bib_holdings where site in ('AMMTIAC','CBIAC','CPIA', 'DACS','HSIAC','IATAC', 'MSIAC', 'RIAC','SENSIAC','SURVIAC','WSTIAC');

The client wants to control when a refresh is done on it. by using this command in a unix cron job execute DBMS_MVIEW.REFRESH('tems_adm.BIB_HOLDINGS_MVIEW','F');

How should I word the materialized view so that it will allow for this command to succeed?
CREATE MATERIALIZED VIEW
   BIB_HOLDINGS_MVIEW
AS
    SELECT SITE||'-' ||PDF_NAME as ACCESSION_NUMBER, ID, SITE, PDF_NAME, PDF_EXISTS, TITLE, SUBJECTKEYWORDS, IAC_REPORT_ID, CATEGORY, TYPE, LOCATION, AUTHOR, PUBLISHER, PUBLICATION_DATE, COPIES, CLASSIFICATION, ORG_PERFORMING, SECONDARY_DIST, ORG_MONITORING, ABSTRACT, ABSTRACT2, NUM_PAGES, COMMENTS, ENTERED_BY, COPYRIGHT_EXISTS, DISTRIBUTION_CODE, COPYRIGHT_INFO, SUPPLEMENTARY_NOTES, MODIFIED_DATE, HIERARCHY_1, HIERARCHY_2, HIERARCHY_3, HIERARCHY_4, HIERARCHY_5, HIERARCHY_6, CONTRACT_GRANT_ID, AD_ID, PERFORMING_REPORT_ID, MONITOR_REPORT_ID, PURCHASE_PRICE, PURCHASE_INFO, FILE_NAME, EXPORT_CONTROL, FOR_PAY, OTHER_CLASSIFICATION_MARKINGS, EXTRA_TEXT_FIELD1, EXTRA_TEXT_FIELD2, EXTRA_TEXT_FIELD3, EXTRA_TEXT_FIELD4, EXTRA_TEXT_FIELD5, EXTRA_DATE_FIELD1, EXTRA_DATE_FIELD2, EXTRA_NUMBER_FIELD1, EXTRA_NUMBER_FIELD2 from bib_holdings where site in ('AMMTIAC','CBIAC','CPIA', 'DACS','HSIAC','IATAC', 'MSIAC', 'RIAC','SENSIAC','SURVIAC','WSTIAC');

Open in new window

0
sikyala
Asked:
sikyala
1 Solution
 
gatorvipCommented:
create materialized view
...
REFRESH ON DEMAND
...
as
...


see here:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6002.htm#i2106785
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now