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?

Regards

Answers


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.


Need Your Help

Android : download content from URL which returns mp3 file

android

After 2 days of research, I didn't found any solution to my problem :/

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.