Oracle database link and view status

 

結論: 

1. view 裡面有使用到 database link

2. create view 之後，drop database link，view testdb88_hr1_usertables 還是顯示為 VALID

3. 但是 select from testdb88_hr1_usertables 顯示錯誤 ORA-04063: view "HR1.TESTDB88_HR1_USERTABLES" has errors

4. 重新compile TESTDB88_HR1_USERTABLES COMPILE 時，報錯 Warning: compiled but with compilation errors

5. 這時 view status 顯示為 INVALID

CREATE DATABASE LINK HR1_TESTDB88

 CONNECT TO HR1

 IDENTIFIED BY "password"

 USING 'testdb88_DB11G';

CREATE OR REPLACE FORCE VIEW HR1.TESTDB88_HR1_USERTABLES

AS

    SELECT *

      FROM user_tables@HR1_TESTDB88;

  

  

select * from HR1.TESTDB88_HR1_USERTABLES   

  

select OWNER, object_name, OBJECT_TYPE , status

from dba_objects 

where  owner = 'HR1' and object_type ='VIEW'; 

DROP DATABASE LINK HR1_TESTDB88;

select OWNER, object_name, OBJECT_TYPE , status

from dba_objects 

where  owner = 'HR1' and object_type ='VIEW'; 

select * from HR1.TESTDB88_HR1_USERTABLES;

ORA-04063: view "HR1.TESTDB88_HR1_USERTABLES" has errors

SELECT * 

FROM user_tables@HR1_TESTDB88;

Error at line 2

ORA-02019: connection description for remote database not found

ALTER VIEW TESTDB88_HR1_USERTABLES COMPILE;

Warning: compiled but with compilation errors

select OWNER, object_name, OBJECT_TYPE , status

from dba_objects 

where  owner = 'HR1' and object_type ='VIEW'; 

OWNER       OBJECT_NAME                OBJECT_TYPE      STATUS        

--------    ------------               ------------     -------

HR1         TESTDB88_HR1_USERTABLES    VIEW             INVALID

                                                                                

1 row selected.