sikyala
asked on
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_MARKI NGS, 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_a dm.BIB_HOL DINGS_MVIE W','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_MARKI
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_a
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');
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.