Re: Snapshot empty if databaselink not present

odi <odsi_at_microresearch.be> wrote in message news:<2003513-153224-325340_at_foorum.com>...
> Hello
>
> We have here a problem with our snapshot on a Oracle8.1.7 database.
>
> So, i have a snapshot A on host H1 declared on a table T on host H2 with a
> dblink.
>
> Here's the sequence of events (on H1)
>
> select * from A;
> -- 1000 records
>
> drop dblink;
>
> refresh snapshot A (fast or force)
> --> error because there's no dblink (quite normal)
>
> select * from A;
> -- no rows.
> Why no rows ? It seems that in Oracle7.3.4 (our previous database), the latest
> select still gave 1000 records.

Are you sure? Can not reproduce with 9i...

SQL> create materialized view test3_mv as select * from test3_at_test_link;

Materialized view created.

SQL> select * from test3_mv;

C1 C2 C3
-- ---------- ----------

a           1
b           2
c           3
e           4

SQL> drop database link test_link;

Database link dropped.

SQL> exec dbms_mview.refresh('test3_mv'); BEGIN dbms_mview.refresh('test3_mv'); END;

*
ERROR at line 1:

ORA-04054: database link TEST_LINK.US.ORACLE.COM does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

SQL> select * from test3_mv;

C1 C2 C3
-- ---------- ----------

a           1
b           2
c           3
e           4

SQL> exec dbms_mview.refresh('test3_mv','?'); BEGIN dbms_mview.refresh('test3_mv','?'); END;

*
ERROR at line 1:

ORA-04054: database link TEST_LINK.US.ORACLE.COM does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

SQL> select * from test3_mv;

C1 C2 C3
-- ---------- ----------

a           1
b           2
c           3
e           4

SQL> exec dbms_mview.refresh('test3_mv','c'); BEGIN dbms_mview.refresh('test3_mv','c'); END;

*
ERROR at line 1:

ORA-04054: database link TEST_LINK.US.ORACLE.COM does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 832
ORA-06512: at line 1

SQL> select * from test3_mv;

C1 C2 C3
-- ---------- ----------

a           1
b           2
c           3
e           4

SQL>

• Jusung Yang