Strange deadlock problem across DB Link

From: De DBA <dedba_at_tpg.com.au>
Date: Thu, 12 Apr 2018 18:45:48 +1000
Message-ID: <94feac6c-0206-1ebf-9401-c42ffad16b7f_at_tpg.com.au>

Posting again, just in case this was missed.. If you have seen this before and a hint how to avoid, I'd be glad to hear it.

This has had me stumped all day. I have a requirement to copy new users, change account statuses and adjust grants across a number of databases. There is one source (the production database) which is the point of truth and all lower environments are managed from this source.

Up to now, the process simply drops all users and runs an import from the production database. As this is a bit crude, I am trying to device a more gentle process where we can have users in lower environments that don't necessarily exist in the source, e.g. for new projects.

The solution that I am working towards is a pl/sql block that uses a number of dictionary tables, both over a database link and local. The problem that I encounter is this:

declare
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object SYS.DBA_ROLE_PRIVS_at_DB_LINK ORA-04020: deadlock detected while trying to lock object SYS.USER$_at_DB_LINK

It is not consistent - sometimes I can run the program for an hour or so without the deadlock occurring. I have determined that this occurs in the declaration of the cursors, it even happens when the body is null. Commenting one or more cursors will make the error also go away.

Even stranger is that although Oracle reports a deadlock, no message is logged in the alert log on the local database or the remote.

Below is the code:

  1 declare

  2      -- 0.0 list roles that do not exist in this db
  3      cursor cNewRole is
  4          select 'create role '||role as stmt
  5            from sys.dba_roles_at_DB_LINK
  6          minus
  7          select 'create role '||role as stmt
  8            from dba_roles;
  9      -- 0.1 identify remote ordinary users, and account status
10      cursor cRmtUsr is
11          select u.name                    as username
12               , u.password||';'||u.spare4 as pwdhash
13               , m.status                  as actst
14               , case
15                   when u.astatus in ( 1,  2 )    then 'PASSWORD EXPIRE'
16                   when u.astatus in ( 8 )        then 'ACCOUNT LOCK'
17                   when u.astatus in ( 5,6,9,10 ) then 'ACCOUNT LOCK PASSWORD EXPIRE'
18                   else 'OPEN'
19                 end                       as actact
20               , dts.name                  as deftbs
21               , tts.name                  as tmptbs
22          from sys.user$_at_DB_LINK u
23             , sys.user_astatus_map m
24             , sys.ts$_at_DB_LINK   dts
25             , sys.ts$_at_DB_LINK   tts
26         where m.status#  = u.astatus
27           and dts.ts#    = u.datats#
28           and tts.ts#    = u.tempts#
29           and dts.name   not in ( 'SYSTEM', 'SYSAUX', 'TOOLS', 'XDB', 'ADHOC' )
30           and u.name     not in ( 'DBSNMP', 'XS$NULL' )
31           and u.name     not like 'ABC-%'
32         ;
33      -- 0.2 check if a user exists in this DB and get the account status
34      cursor cExist ( bUid varchar2 ) is
35          select account_status
36            from sys.dba_users
37           where username  = bUid;
38      -- 0.3 Retrieve a list of privileges that are granted to the user locally
39      cursor cPrivs ( bUid varchar2 ) is
40          select granted_role as priv
41            from sys.dba_role_privs
42           where grantee = bUid
43         union
44          select privilege
45            from sys.dba_sys_privs
46           where grantee = bUid
47         union
48          select listagg(privilege, ', ') within group ( order by owner, table_name )
49                 || ' on ' ||owner||'.'||table_name
50            from sys.dba_tab_privs
51           where grantee = bUid
52             and owner   not in ( 'SYS', 'SYSTEM' )
53           group by owner, table_name
54         ;
55      -- 0.4 Create a table of privileges for the user in the source (remote) database
56      cursor cPrivsRem ( bUid varchar2 ) is
57          select granted_role as priv
58            from sys.dba_role_privs_at_DB_LINK
59           where grantee = bUid
60         union
61          select privilege
62            from sys.dba_sys_privs_at_DB_LINK
63           where grantee = bUid
64         union
65          select listagg(privilege, ', ') within group ( order by owner, table_name )
66                 || ' on ' ||owner||'.'||table_name
67            from sys.dba_tab_privs_at_DB_LINK
68           where grantee = bUid
69             and owner   not in ( 'SYS', 'SYSTEM' )
70           group by owner, table_name
71         ;

72 begin
73 null;
74 end;
75 /

Cheers,

Tony

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 12 2018 - 10:45:48 CEST