Oracle Script -Materialized View & synonym
I've got a question about script in Oracle!
I have a materialized view on a prebuilt table, in my BBDD, (this MV is of the user DAT_OWN, other 2 user (APP & BO) have synonym on this MV) I have to change the MV and add a column. I know that I need to drop this MV and create a another one, but what's append with the synonym?
I have a previous script like:
DROP SYNONYM APP.STAT_VOZ; CREATE SYNONYM APP.STAT_VOZ FOR DAT_OWN.STAT_VOZ; DROP SYNONYM BO.STAT_VOZ; CREATE SYNONYM BO.STAT_VOZ FOR DAT_OWN.STAT_VOZ; DROP MATERIALIZED VIEW DAT_OWN.STAT_VOZ; CREATE MATERIALIZED VIEW DAT_OWN.STAT_VOZ ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH COMPLETE START WITH TO_DATE('21-ene-2013 19:20:00','dd-mon-yyyy hh24:mi:ss') NEXT (trunc(SYSDATE,'HH')+19/72) WITH PRIMARY KEY AS SELECT TO_CHAR (SUM (COUNT)) AS sum_count, start_date AS date_hour, input_type AS input_type FROM DAT_OWN.another_table WHERE start_date > TO_CHAR (SYSDATE - 60, 'yyyymmdd') GROUP BY start_date
Why would somebody do a drop synonym APP and create synonym APP drop synonym BO and create synonym BO BEFORE dropping of the materialized View? In my opinion, I have to do
drop synonym1 drop synonym2 drop Mview create Mview create Synonym1 Create Synonym2
I'm sure that the programmer before me did a good job, but I can't understand why they did it that way! Can somebody explain me this, please?
Your original script was probably just written by an "old school" developer. Most people are accustomed to dropping objects immediately before recreating them. As you've noticed, the sequence is not important.
Most people these days use CREATE OR REPLACE syntax instead of first dropping the object. Here is a good explanation of that concept.
There is probably no need to touch either synonym in the script.
A synonym is just a pointer. There is no requirement that the object pointed to by the synonym exists at all. If you simply drop and re-create the materialized view, the synonyms will automatically point at the newly created materialized view. Of course, after the materialized view is dropped and before it is recreated, if a session tries to use the synonym to query the materialized view, that session will get an error that the object doesn't exist. But, presumably, you're waiting to drop and recreate the materialized view until no one will be using it so that generally isn't a major concern.