Same User Database Links

I’m not sure how many people know this but it’s possible to create a database link without specifying a username or password. If you do this then the link will take the username and password from the currently connected user. I like the idea of this as it reduces the likelyhood of having scripts lying around that contain connection details. However, they don’t work if used in scheduled jobs, including materialized view scheduled refreshes.

To demonstrate this I created 3 equivalent materialized views – one not using a materialized view, another using a fully qualified database link and another omitting the usrname and password information:

10:22:59 TONY_DBAP> create database link loopback_with_password connect to tony identified by webb using 'dbap.world';

Database link created.

10:23:35 TONY_DBAP> create database link loopback_without_password using 'dbap.world';

Database link created.

10:35:15 TONY_DBAP> CREATE MATERIALIZED VIEW fred BUILD IMMEDIATE REFRESH COMPLETE START WITH sysdate NEXT sysdate + (1/24/6) AS SELECT * FROM user_tables;

Materialized view created.

10:35:28 TONY_DBAP> CREATE MATERIALIZED VIEW barney BUILD IMMEDIATE REFRESH COMPLETE START WITH sysdate NEXT sysdate + (1/24/6) AS SELECT * FROM user_tables@loopback_with_password;

Materialized view created.

10:35:46 TONY_DBAP> CREATE MATERIALIZED VIEW wilma BUILD IMMEDIATE REFRESH COMPLETE START WITH sysdate NEXT sysdate + (1/24/6) AS SELECT * FROM user_tables@loopback_without_password;

Materialized view created.

NAME			       START_WITH	    LAST_REFRESH
------------------------------ -------------------- --------------------
FRED			       26-MAR-2012 10:45:29 26-MAR-2012 10:35:29
BARNEY			       26-MAR-2012 10:45:49 26-MAR-2012 10:35:49
WILMA			       26-MAR-2012 10:36:03 26-MAR-2012 10:36:03

10:36:50 TONY_DBAP> select name, start_with, last_refresh from user_snapshots where name in ('FRED', 'BARNEY', 'WILMA');

NAME			       START_WITH	    LAST_REFRESH
------------------------------ -------------------- --------------------
FRED			       26-MAR-2012 10:55:30 26-MAR-2012 10:45:30
BARNEY			       26-MAR-2012 10:55:50 26-MAR-2012 10:45:50
WILMA			       26-MAR-2012 10:36:03 26-MAR-2012 10:36:03

The materialized view ‘WILMA’ (the materialized view using the unqualified database link) can still be refreshed if it is not a scheduled refresh, however:

11:16:45 TONY_DBAP> drop materialized view fred;

Materialized view dropped.

11:16:52 TONY_DBAP> drop materialized view barney;

Materialized view dropped.

11:16:58 TONY_DBAP> drop materialized view wilma;

Materialized view dropped.

CREATE MATERIALIZED VIEW fred BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM user_tables;

Materialized view created.

11:36:05 TONY_DBAP> CREATE MATERIALIZED VIEW barney BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM user_tables@loopback_with_password;

Materialized view created.

11:36:10 TONY_DBAP> CREATE MATERIALIZED VIEW wilma BUILD IMMEDIATE REFRESH COMPLETE ON DEMAND AS SELECT * FROM user_tables@loopback_without_password;

Materialized view created.

CREATE OR REPLACE PROCEDURE test_refresh
IS
BEGIN
   dbms_snapshot.refresh('WILMA','c');
   dbms_snapshot.refresh('FRED','c');
   dbms_snapshot.refresh('BARNEY','c');
END test_refresh;
11:38:54   8  /

Procedure created.

exec test_refresh;

select sysdate, name, start_with, last_refresh from user_snapshots where name in ('FRED', 'BARNEY', 'WILMA');

PL/SQL procedure successfully completed.

11:39:05 TONY_DBAP> 11:39:05 TONY_DBAP>
SYSDATE 	     NAME			    START_WITH		 LAST_REFRESH
-------------------- ------------------------------ -------------------- --------------------
26-MAR-2012 11:39:05 FRED						 26-MAR-2012 11:39:05
26-MAR-2012 11:39:05 BARNEY						 26-MAR-2012 11:39:05
26-MAR-2012 11:39:05 WILMA						 26-MAR-2012 11:39:05

BEGIN
  DBMS_SCHEDULER.create_program (
    program_name        => 'call_test_refresh',
    program_type        => 'STORED_PROCEDURE',
    program_action      => 'test_refresh',
    number_of_arguments => 0,
    enabled             => FALSE,
    comments            => 'Program to call test_refresh');
11:39:51   9
  DBMS_SCHEDULER.enable (name => 'call_test_refresh');
11:39:51  11
END;
11:39:51  13  /

PL/SQL procedure successfully completed.

BEGIN
  DBMS_SCHEDULER.create_job (job_name => 'run_test_refresh',
                             program_name  => 'call_test_refresh',
                             start_date => SYSTIMESTAMP,
                             repeat_interval => 'freq=minutely; interval=10',
                             end_date  => NULL,
                             enabled => TRUE,
                             comments  => 'Automated refresh of my materialised views');
END;
11:40:03  10  /

PL/SQL procedure successfully completed.

11:55:45 TONY_DBAP> select sysdate, name, start_with, last_refresh from user_snapshots where name in ('FRED', 'BARNEY', 'WILMA');

SYSDATE 	     NAME			    START_WITH		 LAST_REFRESH
-------------------- ------------------------------ -------------------- --------------------
26-MAR-2012 11:55:53 FRED						 26-MAR-2012 11:39:05
26-MAR-2012 11:55:53 BARNEY						 26-MAR-2012 11:39:05
26-MAR-2012 11:55:53 WILMA						 26-MAR-2012 11:39:05

The job is failing when scheduled. Let’s try without scheduling:

12:04:20 TONY_DBAP> exec dbms_scheduler.run_job('RUN_TEST_REFRESH',TRUE);

PL/SQL procedure successfully completed.

12:04:26 TONY_DBAP> select sysdate, name, start_with, last_refresh from user_snapshots where name in ('FRED', 'BARNEY', 'WILMA');

SYSDATE 	     NAME			    START_WITH		 LAST_REFRESH
-------------------- ------------------------------ -------------------- --------------------
26-MAR-2012 12:04:31 FRED						 26-MAR-2012 12:04:26
26-MAR-2012 12:04:31 BARNEY						 26-MAR-2012 12:04:26
26-MAR-2012 12:04:31 WILMA						 26-MAR-2012 12:04:26

Here are the errors we are getting when the job is scheduled:

select * from user_scheduler_job_run_details where job_name ='RUN_TEST_REFRESH'

..edited highlights!

ORA-12008: error in materialized view refresh path
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from LOOPBACK_WITHOUT_PASSWORD
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2566
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2779
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2748
ORA-06512: at "TONY.TEST_REFRESH", line 4

(This is also in the alert log)

If we remove the offending materialized view and try again, everything works fine.

BEGIN
 DBMS_SCHEDULER.drop_job (job_name => 'run_test_refresh');
END;
12:19:59   4  /

PL/SQL procedure successfully completed.

CREATE OR REPLACE PROCEDURE test_refresh
IS
BEGIN
   -- dbms_snapshot.refresh('WILMA','c');
   dbms_snapshot.refresh('FRED','c');
   dbms_snapshot.refresh('BARNEY','c');
END test_refresh;
12:20:28   8  /

Procedure created.

BEGIN
  DBMS_SCHEDULER.create_job (job_name => 'RUN_TEST_REFRESH',
                             program_name  => 'CALL_TEST_REFRESH',
                             start_date => SYSTIMESTAMP,
                             repeat_interval => 'freq=minutely; interval=1',
                             end_date  => NULL,
                             enabled => TRUE,
                             comments  => 'Automated refresh of my materialised views');
12:20:52   9  END;
12:20:54  10  /

PL/SQL procedure successfully completed.

12:20:54 TONY_DBAP> select sysdate, name, start_with, last_refresh from user_snapshots where name in ('FRED', 'BARNEY', 'WILMA');

SYSDATE 	     NAME			    START_WITH		 LAST_REFRESH
-------------------- ------------------------------ -------------------- --------------------
26-MAR-2012 12:22:03 FRED						 26-MAR-2012 12:21:55
26-MAR-2012 12:22:03 BARNEY						 26-MAR-2012 12:21:55
26-MAR-2012 12:22:03 WILMA						 26-MAR-2012 12:19:08

I’ve also tried this with a job that deletes and then inserts records into a table using a select across a database link – essentially duplicating a materialized view refresh and I get exactly the same behaviour.

Advertisements

About dnadba

I work with databases and stuff
This entry was posted in Oracle. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s