tparvaiz
asked on
"execute DBMS_MVIEW.REFRESH"... Need to understand it in detail
I need to understand what this code is doing
execute DBMS_MVIEW.REFRESH('briir. cust_produ ct');
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
execute DBMS_MVIEW.REFRESH('briir.
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
ASKER
how can I find out what this view is doing (which tables are joined on what fields)...
Thanks
Thanks
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' ).
You can check out the sys table ALL_MVIEWS e.g. SELECT * FROM all_mviews WHERE mview_name = upper('briir.cust_product'
sorry, quick correction
SELECT * FROM all_mviews WHERE owner = upper('briir') AND mview_name = upper('cust_product')
SELECT * FROM all_mviews WHERE owner = upper('briir') AND mview_name = upper('cust_product')
ASKER
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
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
yes ... it would work (as long as you don't quote the mview).
ASKER
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...
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...
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.
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.
ASKER
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_PAR ENT 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_POR TF 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_PRO D 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...?
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
(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_PAR
(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_POR
(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_PRO
(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...?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot for your assistance...
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