Materialized View - Recreate

[Trevisolli]

Guys, good morning, beleza?
.

Scenario:
I made the creation of an MV, I used it in "n" packages and, I need to change it (drop and create again ).

Doubts:
1) From what I analyzed, do not exhibit "REPLACE" to an MV, correct? (I must always drop her and recreate it).
2) with a positive response, question 1, as DBAS behave, as to the "deadlock" error, which may arise when to drop the same? (Because it contains dependencies on other objects, as in the packages I mentioned in the scenario above).

For the little I saw here, I had to "comment" the MV call in the packages, to drop it, recreate it and, "descend" the call of her in the dependencies.

That's what happens today? It is that it is my first contact and, I had problems of "deadlock" when creating (because there are dependencies in the packages).

or, this "deadlock" I mentioned, was for use of the tables involved in MV, when tried to create it again?

Thank you,

Trevisolli

[stcoutinho]

Trevisolli,

a little time that I do not create materialized views, but I remember that I did the following:

- Created a "base table" (Table ) with the same name as MaterialIged View to create;
- Created MaterialIged View, with the option to use the base table;

did this, even if I remove the MV, I will always remain the "base table". I then think that if you repeat this procedure, you should no longer come across locks to recreate MV because the Packages dependencies will now be with the "base table" and no longer MV.

About blocking, I believe that and it occurs because MV-dependent objects must be being continuously used, preventing the command to drug MV.

Hugs,

Sergio Coutinho

[fbifabio]

Trevisolli,

answering your questions:

1- There really does not exist the Replace option in MVS, if you need to change something you have to erase and create again

2- I can not understand what you mean by Deadlock. Deadlock occurs when 2 transactions depend on one of the other tables to finalize the transaction, the 2 conflict because they are waiting for another to release the table, no one releases anything and then Oracle kills one of the transactions and everything goes back to work. I think you want to say that a blockage is occurring just, right ????? I have MVS in the bds that I administer and also objects that access them and never had these problems that you are commenting on. I always delete MV when you need to change it and re-read it then. During the time it does not exist (it was erased) the objects that access it will be invalid and can generate errors in the applications, so be quick to recreate it. A very important point that you can not fail to do before, it is save the access privileges of all users in MV before erasing it to then recreate it again, okay?

[madmax]

Hello Trevisolli,

Here I use View Materilized or Snapshopts as before in the truth and the same thing.

Well I create them with auto update in 1 hour for each, but and the taste of the fregues, I faced some deadlocks in 10g version but on 11.2.0.3 and if the links of the bank is stable This does not happen anymore.

1 - Creation of materialzed view:

Select all

CREATE MATERIALIZED VIEW XX_NOME_DOUGLAS 
REFRESH FAST ON DEMAND 
WITH ROWID 
START WITH TO_DATE('02-01-2012 08:05:52', 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1/24          
AS 
SELECT * From tabela@dblink; 

2 - If you need to make a refresh in the VM if DBLink CAIU Use this command:

Select all

BEGIN 
DBMS_SNAPSHOT.REFRESH( 
LIST => 'schema.XX_NOME_DOUGLAS' 
,METHOD => 'C' 
,PUSH_DEFERRED_RPC => TRUE 
,REFRESH_AFTER_ERRORS => FALSE 
,PURGE_OPTION => 1 
,PARALLELISM => 0 
,ATOMIC_REFRESH => TRUE 
,NESTED => FALSE); 
END; 

3 - If you have Deadlock the only way and restarting the bank or dropping and creating VM again:

Select all

DROP MATERIALIZED VIEW XX_NOME_DOUGLAS;  
 
CREATE MATERIALIZED VIEW XX_NOME_DOUGLAS 
REFRESH FAST ON DEMAND 
WITH ROWID 
START WITH TO_DATE(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') NEXT SYSDATE + 1/24  
AS 
SELECT * FROM "XX_NOME_DOUGLAS"@MEUDBLINK.REGRESS.RDBMS.DEV.US.ORACLE.COM ; 

Note: When you use Refresh Fast On Demand and created an automatic job on the bank and you can monitor and check if you are up to date with the SELECT * from ALL_JOBS.

I hope I have helped anything post aí.

ABS.

[Trevisolli]

Thanks guys!
Perfect the answers.

I really had "deadlock" a few times (perhaps for the recreation time and constant use of the BD) and some ORA-01775 (looping Chain of Synonyms), because MV used Package functions where it was called and at the time of the recreation, Synonym pointed to an invalid package.

But, everything settled here ... I will follow the tips of colleagues there ...

Thank you once again for your pristence.

Great embrace,

Trevisolli.