Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

"execute DBMS_MVIEW.REFRESH"... Need to understand it in detail

Posted on 2009-03-28
11
Medium Priority
?
3,158 Views
Last Modified: 2013-12-18
I need to understand what this code is doing

execute DBMS_MVIEW.REFRESH('briir.cust_product');

seems like it's excuting oracles view... I need to know what exactly view/triger is doing...

someone has been using this code (via Toad for oracle) to refresh database (unfortunately he left)...
I am a new bee and would require step by step assistance...

Thanks again for your assistance
0
Comment
Question by:tparvaiz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 24011627
Hi tparvaiz,

Oracle has views ... where a view is a stored SQL that creates the output by running the SQL when it is accessed in another SQL etc.  Hence they are dynamic ... as the underlying data changes so does the view.

It also has Materialized views ...(in simple terms)  these are are also stored SQL's that are permanent in that they act like tables.  These are static and do not change until refreshed.  So even if the unlying tables change, the materialised view remains the same until refreshed.

That execute statement you showed is an on demand refresh of a materialized view.

For some documentation, see http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm

lwadwell
0
 

Author Comment

by:tparvaiz
ID: 24011636
how can I find out what this view is doing (which tables are joined on what fields)...

Thanks
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24011654
In TOAD in the shcema browser you may find it under "snapshots" I think (instead of views) ... depends on your version.  

You can check out the sys table ALL_MVIEWS e.g. SELECT * FROM all_mviews WHERE mview_name = upper('briir.cust_product').
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 25

Expert Comment

by:lwadwell
ID: 24011658
sorry, quick correction

SELECT * FROM all_mviews WHERE owner = upper('briir') AND mview_name = upper('cust_product')
0
 

Author Comment

by:tparvaiz
ID: 24011688
Sure... I'll give it a try and will let you know my findings...

Another quick one... Before refreshing this view... How can I create a copy of it and save it as a table

would this work...?

create table temp_copy
as
select * from 'briir.cust_product';

Thanks
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24011721
yes ... it would work (as long as you don't quote the mview).
0
 

Author Comment

by:tparvaiz
ID: 24011735
I am able to read the view (though not through toad)

how can I modify the view... want to change year 2008 to 2009...

Thanks again for your assistance... I think we are getting somewhere now...
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24011769
I am surprised you cannot see it through TOAD. What version are you using?

Is the year 2008 hard-coded?  To change the definition, i.e. the SQL, you will need to DROP MATERIALIZED VIEW and recreate it via a CREATE MATERIALIZED VIEW.  I don't believe you can ALTER the SQL.
0
 

Author Comment

by:tparvaiz
ID: 24011825
I am using toad version 9.1... I can view "MATERIALIZED VIEW" (by going through schema browser) and can open the script in the editor too... But when I am saving, it saves as local file (on my PC)... Is there a way to save it on the server...

Script reads something like this...




DROP MATERIALIZED VIEW briir.cust_product;
CREATE MATERIALIZED VIEW BRIIR.CUST_PRODUCT
TABLESPACE BI_DATA
PCTUSED    0
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
/* Formatted on 2009/03/29 02:44 (Formatter Plus v4.8.8) */
SELECT   (CASE
             WHEN seg_sales = 'ABC'
                THEN 'ABC'
             ELSE 'OTHER'
          END
         ) AS mkt_segment,
         parent_name, product_portfolio, product_description,
         ROUND (SUM (m_01jan)) AS jan, ROUND (SUM (m_02feb)) AS feb,
         ROUND (SUM (m_03mar)) AS mar, ROUND (SUM (m_04apr)) AS apr,
         ROUND (SUM (m_05may)) AS may, ROUND (SUM (m_06jun)) AS jun,
         ROUND (SUM (m_07jul)) AS jul, ROUND (SUM (m_08aug)) AS aug,
         ROUND (SUM (m_09sep)) AS sep, ROUND (SUM (m_10oct)) AS oct,
         ROUND (SUM (m_11nov)) AS nov, ROUND (SUM (m_12dec)) AS DEC,
         ROUND (SUM (  m_01jan
                     + m_02feb
                     + m_03mar
                     + m_04apr
                     + m_05may
                     + m_06jun
                     + m_07jul
                     + m_08aug
                     + m_09sep
                     + m_10oct
                     + m_11nov
                     + m_12dec
                    )
               ) AS ytd
    FROM brr.t_brr
   WHERE data_type = 'Actual' AND data_year = 2009 AND profitability = 'TRUE'
GROUP BY (CASE
             WHEN seg_sales = 'ABC'
                THEN 'ABC'
             ELSE 'OTHER'
          END
         ),
         parent_name,
         product_portfolio,
         product_description;

COMMENT ON MATERIALIZED VIEW BRIIR.CUST_PRODUCT IS 'snapshot table for snapshot BRIIR.CUST_PRODUCT';

CREATE BITMAP INDEX BRR.IDX_MV_SEGCUSTPROD_SEG ON BRIIR.CUST_PRODUCT
(MKT_SEGMENT)
LOGGING
TABLESPACE BI_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX BRR.IDX_MV_SEGCUSTPROD_PARENT ON BRIIR.CUST_PRODUCT
(PARENT_NAME)
LOGGING
TABLESPACE BI_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE BITMAP INDEX BRR.IDX_MV_SEGCUSTPROD_PORTF ON BRIIR.CUST_PRODUCT
(PRODUCT_PORTFOLIO)
LOGGING
TABLESPACE BI_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE BITMAP INDEX BRR.IDX_MV_SEGCUSTPROD_PROD ON BRIIR.CUST_PRODUCT
(PRODUCT_DESCRIPTION)
LOGGING
TABLESPACE BI_INDEX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


how to create this view... any suggestion...?
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 200 total points
ID: 24011917
TOAD works on the PC and saves files only on the visisble PC filesystem.

What you have is a complete script.  It should be able to be run 'as is' in TOAD, SQLplus etc.

A quick run through is that it will delete the MV (DROP) and then recreate it (CREATE MATERIALIZED VIEW) and then add 4 indexes (CREATE [BITMAP] INDEX).

The 'important' part of the MV is the SELECT statement ... I would not recreate the view without testing the SQL first.
0
 

Author Closing Comment

by:tparvaiz
ID: 31563982
Thanks a lot for your assistance...
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question