SUCCESS and FAILURE Columns in DBA_STMT_AUDIT_OPTS

I tested this in Oracle 10.2. DBA_STMT_AUDIT_OPTS records auditing options which are currently in effect. If no auditing has been requested, it will be empty:

 

SQL> select user_name, audit_option, success, failure

  2  from dba_stmt_audit_opts

  3  /

 

no rows selected

 

SQL>

 

You can audit successful connections as follows:

 

SQL> audit create session by system whenever successful

  2  /

 

Audit succeeded.

 

SQL>

 

This will then be recorded in DBA_STMT_AUDIT_OPTS. The SUCCESS column will be set to BY ACCESS to show that only successful connections are being audited:   

 

SQL> select user_name, audit_option, success, failure

  2  from dba_stmt_audit_opts

  3  /

 

USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE

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

SYSTEM     CREATE SESSION  BY ACCESS  NOT SET

 

SQL>

 

You can stop the auditing like this:

 

SQL> noaudit create session by system whenever successful

  2  /

 

Noaudit succeeded.

 

SQL>

 

…and the entry will disappear from DBA_STMT_AUDIT_OPTS:

 

SQL> select user_name, audit_option, success, failure

  2  from dba_stmt_audit_opts

  3  /

 

no rows selected

 

SQL>

 

Here is how you audit unsuccessful connections. Note that in the context of the AUDIT and NOAUDIT commands, CREATE SESSION and CONNECT have the same meaning:

 

SQL> audit connect by system whenever not successful

  2  /

 

Audit succeeded.

 

SQL>

 

This will also be recorded in DBA_STMT_AUDIT_OPTS but the FAILURE column will be set to BY ACCESS to show that only failed connections are being audited:

 

SQL> select user_name, audit_option, success, failure

  2  from dba_stmt_audit_opts

  3  /

 

USER_NAME  AUDIT_OPTION    SUCCESS    FAILURE

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

SYSTEM     CREATE SESSION  NOT SET    BY ACCESS

 

SQL>

 

As before, stopping the auditing removes the entry from DBA_STMT_AUDIT_OPTS:

 

SQL> noaudit connect by system whenever not successful

  2  /

 

Noaudit succeeded.

 

SQL> select user_name, audit_option, success, failure

  2  from dba_stmt_audit_opts

  3  /

 

no rows selected

 

SQL>

Segment Creation Deferred and ORA-02266

If you try to truncate a table with a primary key which is referenced by an enabled foreign key, you usually get an ORA-02266 error. This happens straight away if the table is set up with segment creation immediate. However, if the table is set up with segment creation deferred, the error is not reported until the segment has been created. You can see what I mean in the example below, which I tested on Oracle 11.2:

 

SQL> create table dept1

  2  (dept_code varchar2(4),

  3   dept_desc varchar2(20),

  4   constraint dept1_constraint

  5   primary key(dept_code))

  6   segment creation deferred

  7  /

 

Table created.

 

SQL> create table emp1

  2  (empno varchar2(6),

  3   dept_code varchar2(4),

  4   constraint emp1_constraint

  5   foreign key (dept_code)

  6   references dept1(dept_code))

  7  /

 

Table created.

 

SQL> select count(*) from dba_segments

  2  where segment_name = 'DEPT1'

  3  /

 

  COUNT(*)

----------

         0

 

SQL> truncate table dept1

  2  /

 

Table truncated.

 

SQL> insert into dept1 values('0001','SALES')

  2  /

 

1 row created.

 

SQL> select count(*) from dba_segments

  2  where segment_name = 'DEPT1'

  3  /

 

  COUNT(*)

----------

         1

 

SQL> truncate table dept1

  2  /

truncate table dept1

               *

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by

enabled foreign keys

 

SQL> create table dept2

  2  (dept_code varchar2(4),

  3   dept_desc varchar2(20),

  4   constraint dept2_constraint

  5   primary key(dept_code))

  6   segment creation immediate

  7  /

 

Table created.

 

SQL> create table emp2

  2  (empno varchar2(6),

  3   dept_code varchar2(4),

  4   constraint emp2_constraint

  5   foreign key (dept_code)

  6   references dept2(dept_code))

  7  /

 

Table created.

 

SQL> truncate table dept2

  2  /

truncate table dept2

               *

ERROR at line 1:

ORA-02266: unique/primary keys in table referenced by

enabled foreign keys

 

SQL>

Rollback to Savepoint Does Not Release Locks

I read that rolling back to a savepoint releases locks. This sounded reasonable so I decided to check it out in an Oracle 11.2 database. I logged in as user John (in blue) and noted my SID for future reference. Then I created a table, inserted a row, committed the change and created a savepoint. Finally I updated the row but did not commit the change, thus setting up a lock:

 

SQL> conn john/smith

Connected.

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

       683

 

SQL> create table tab1

  2  (col1 number)

  3  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> select * from tab1

  2  /

 

      COL1

----------

         1

 

SQL> savepoint sp1

  2  /

 

Savepoint created.

 

SQL> update tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL> select * from tab1

  2  /

 

      COL1

----------

         2

 

SQL>

 

I logged into a new session as user Fred (in red), noted my SID again and tried to update the same table. This did nothing, as you might expect:

 

SQL> conn fred/bloggs

Connected.

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

         5

 

SQL> update john.tab1 set col1 = 3

  2  /

 

I returned to John’s session and checked that I could see the lock in the DBA_WAITERS view. Then I rolled back to the savepoint and checked that the original value had reappeared in the table (it had). However, the lock was still shown in DBA_WAITERS:

 

SQL> show user

USER is "JOHN"

SQL> select holding_session, waiting_session

  2  from dba_waiters

  3  /

 

HOLDING_SESSION WAITING_SESSION

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

            683               5

 

SQL> rollback to savepoint sp1

  2  /

 

Rollback complete.

 

SQL> select * from tab1

  2  /

 

      COL1

----------

         1

 

SQL> select holding_session, waiting_session

  2  from dba_waiters

  3  /

 

HOLDING_SESSION WAITING_SESSION

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

            683               5

 

SQL>

 

I guessed that Oracle must still be holding some kind of lock between the two transactions. There was clearly no lock on the data any more as Donald was able to start a new session (in green) and update it with no problems:

 

SQL> conn donald/duck

Connected.

SQL> update john.tab1 set col1 = 4

  2  /

 

1 row updated.

 

SQL> select * from john.tab1

  2  /

 

      COL1

----------

         4

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

User John finished his transaction and the lock disappeared:

 

SQL> show user

USER is "JOHN"

SQL> commit

  2  /

 

Commit complete.

 

SQL> select holding_session, waiting_session

  2  from dba_waiters

  3  /

 

no rows selected

 

SQL>

 

… and this allowed Fred’s earlier update to finish:

 

SQL> conn fred/bloggs

Connected.

SQL> select distinct sid from v$mystat

  2  /

 

       SID

----------

         5

 

SQL> update john.tab1 set col1 = 3

  2  /

 

1 row updated.

 

SQL>

DBMS_SYSTEM.KCFRMS

 I tested this on Oracle 11.2.

 

V$SESSION_EVENT holds similar information to V$SYSTEM_EVENT but it is broken down by session (only currently logged in sessions appear - there is no history). There is a MAX_WAIT column which shows the maximum time a session has had to wait for a particular event.There is no timestamp on this so you cannot tell when the longest wait took place. However, if you have a session which is about to start another step in a process, you can zeroise MAX_WAIT so you know the maximum wait time in that step once it has finished. This zeroises all MAX_WAIT values for all events in all sessions. It also resets MAXIORTM and MAXIOWTM in V$FILESTAT. I decided to give it a try. First I checked the current values:

SQL> conn / as sysdba

Connected.

SQL> select sum(max_wait) from v$session_event

  2  /

 

SUM(MAX_WAIT)

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

      1323729

 

SQL> select sum(maxiortm), sum(maxiowtm)

  2  from v$filestat

  3  /

 

SUM(MAXIORTM) SUM(MAXIOWTM)

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

         2972          8171

 

SQL>

Then I ran the command to zeroise them:

SQL> exec dbms_system.kcfrms();

 

PL/SQL procedure successfully completed.

 

SQL>

Finally I checked the figures again:

SQL> select sum(max_wait) from v$session_event

  2  /

 

SUM(MAX_WAIT)

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

            0

 

SQL> select sum(maxiortm), sum(maxiowtm)

  2  from v$filestat

  3  /

 

SUM(MAXIORTM) SUM(MAXIOWTM)

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

            0             0

 

SQL>

ORA-02205

I found some notes from a course I took in 1990. They said that it was only possible to GRANT ALTER or GRANT SELECT on a sequence. This seemed reasonable to me but I wanted to check if it was still the case. I did this test on Oracle 12.1. First I created a user who would own a sequence:

SQL> create user u1 identified by pw1

  2  /

 

User created.

 

SQL> grant create session, create sequence to u1

  2  /

 

Grant succeeded.

SQL>

Then I created a user who would be granted access to the sequence:

SQL> create user u2 identified by pw2

  2  /

 

User created.

SQL>

The first user created a sequence then did a GRANT ALL on it to the second user:

SQL> conn u1/pw1

Connected.

SQL> create sequence s1

  2  /

 

Sequence created.

 

SQL> grant all on s1 to u2

  2  /

 

Grant succeeded.

SQL>

I looked for the privileges which had been given to the second user but only found ALTER and SELECT. This confirmed what I had read in my notes:

SQL> select privilege from all_tab_privs

  2  where grantor = 'U1'

  3  and grantee = 'U2'

  4  and table_name = 'S1'

  5  /

 

PRIVILEGE                                             

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

ALTER                                                 

SELECT

SQL>

Finally I tried to GRANT UPDATE on the sequence but this failed with an ORA-02205:

SQL> grant update on s1 to u2

  2  /

grant update on s1 to u2

                *

ERROR at line 1:

ORA-02205: only SELECT and ALTER privileges are valid

for sequences

 

SQL>

How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -

> ('uimsmgr','ubbchst_serv_index');

 

PL/SQL procedure successfully completed.

 

SQL>

 

Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)

  2  from dba_indexes

  3  where owner = 'UIMSMGR'

  4  and index_name = 'UBBCHST_SERV_INDEX'

  5  /

 

NVL(BLEVEL,999)

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

            999

 

SQL>

 

I recalculated the index's statistics:

 

SQL> exec dbms_stats.gather_index_stats -

> ('uimsmgr','ubbchst_serv_index');

 

PL/SQL procedure successfully completed.

 

SQL>

 

After that, the BLEVEL column in DBA_INDEXES showed the index’s height:

SQL> select nvl(blevel,999)

  2  from dba_indexes

  3  where owner = 'UIMSMGR'

  4  and index_name = 'UBBCHST_SERV_INDEX'

  5  /

 

NVL(BLEVEL,999)

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

              3

 

SQL>

 

I used to calculate an index's height by analyzing it then looking in INDEX_STATS. First I checked that INDEX_STATS was empty (you will see why I showed the username later):

SQL> select name, height from index_stats

  2  /

 

no rows selected

 

SQL> show user

USER is "ORACLE"

SQL>

 

Then I analyzed the index:

SQL> analyze index uimsmgr.ubbchst_serv_index

  2  validate structure

  3  /

 

Index analyzed.

 

SQL>

 

... and checked its height in INDEX_STATS again. This height comes out as BLEVEL + 1. I guess Oracle starts to count it from a different place as this happened in Oracle 9 too:

 

SQL> select name, height from index_stats

  2  /

 

NAME                               HEIGHT

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

UBBCHST_SERV_INDEX                      4

 

SQL>

 

INDEX_STATS is a public synonym for the SYS.INDEX_STATS view. It is far too complicated for me to follow but I know that it never has more than one row, which holds details for the index you analyzed most recently. It is also session specific so, if you start a new session, it will be empty again:

 

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> select name, height from index_stats

  2  /

 

no rows selected

 

SQL>

Bug 8477973

I ran the following query on an Oracle 11.1.0.6 database but it failed with an ORA-02020:

 

SQL> SELECT ppc.sttlmnt_dt day_date

  2  FROM   vrm_d18_ppc   ppc,

  3         meter_nhh     mtr,

  4         mtd_registers reg

  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr

  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id

  7  AND    ppc.prfl_clss_id = mtr.profile_class_id

  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id

  9  AND    reg.mpan_core = mtr.mpan_core

 10  AND    reg.mpan_core = '1900008212269'

 11  AND    mtr.meter_id = 3232

 12  AND    reg.meter_serial_id = 'L8103349'

 13  AND    reg.meter_register_id = 'TO'

 14  AND    ppc.sttlmnt_dt BETWEEN '01-AUG-14' AND '31-AUG-14'

 15  /

ERROR:

ORA-02020: too many database links in use

 

no rows selected

 

SQL>

 

I checked the value of the open_links initialization parameter:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'open_links'

SQL> /

 

VALUE

----------

4

 

SQL>

 

I looked at the execution plan and saw that it accessed several tables down the same database link. It also used nested loops:

********************************************************************************

 

SELECT ppc.sttlmnt_dt day_date

FROM   vrm_d18_ppc   ppc,

       meter_nhh     mtr,

       mtd_registers reg

WHERE  ppc.tm_pttrn_rgm = reg.tpr

AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id

AND    ppc.prfl_clss_id = mtr.profile_class_id

AND    ppc.gsp_grp_id = mtr.gsp_group_id

AND    reg.mpan_core = mtr.mpan_core

AND    reg.mpan_core = '1900008212269'

AND    mtr.meter_id = 3232

AND    reg.meter_serial_id = 'L8103349'

AND    reg.meter_register_id = 'TO'

AND    ppc.sttlmnt_dt BETWEEN '01-AUG-14' AND '31-AUG-14'

 

call     count       cpu    elapsed       disk      query    current        rows

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

Parse        1      0.04       0.19          0          0          1           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        2      0.05       0.52          7          8          0           1

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

total        4      0.09       0.72          7          8          1           1

 

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 78  (SRCE)

 

Rows     Row Source Operation

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

      1  FILTER  (cr=7 pr=7 pw=7 time=0 us)

      1   NESTED LOOPS  (cr=7 pr=7 pw=7 time=0 us cost=10 size=216 card=1)

      1    NESTED LOOPS  (cr=7 pr=7 pw=7 time=0 us cost=9 size=190 card=1)

      1     NESTED LOOPS  (cr=3 pr=3 pw=3 time=0 us cost=8 size=156 card=1)

      1      NESTED LOOPS  (cr=3 pr=3 pw=3 time=0 us cost=7 size=139 card=1)

      1       NESTED LOOPS  (cr=3 pr=3 pw=3 time=0 us cost=6 size=125 card=1)

      2        NESTED LOOPS  (cr=3 pr=3 pw=3 time=379976 us cost=5 size=113 card=1)

      2         NESTED LOOPS  (cr=3 pr=3 pw=3 time=255776 us cost=4 size=86 card=1)

      2          NESTED LOOPS  (cr=3 pr=3 pw=3 time=127596 us cost=3 size=66 card=1)

      2           NESTED LOOPS  (cr=3 pr=3 pw=3 time=972 us cost=2 size=40 card=1)

      1            TABLE ACCESS BY INDEX ROWID METER_NHH (cr=3 pr=3 pw=3 time=0 us cost=1 size=23 card=1)

      1             INDEX UNIQUE SCAN METER_NHH_PK (cr=2 pr=2 pw=2 time=0 us cost=1 size=0 card=1)(object id 106394)

      2            REMOTE  D18_780 (cr=0 pr=0 pw=0 time=967 us cost=1 size=17 card=1)

      2           REMOTE  D18_781 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)

      2          REMOTE  D18_782 (cr=0 pr=0 pw=0 time=0 us cost=1 size=20 card=1)

      2         REMOTE  D18_783 (cr=0 pr=0 pw=0 time=0 us cost=1 size=27 card=1)

      1        REMOTE  D18_784 (cr=0 pr=0 pw=0 time=0 us cost=1 size=12 card=1)

      1       REMOTE  D18_787 (cr=0 pr=0 pw=0 time=0 us cost=1 size=14 card=1)

      1      REMOTE  D18_789 (cr=0 pr=0 pw=0 time=0 us cost=1 size=17 card=1)

      1     TABLE ACCESS BY INDEX ROWID MTD_REGISTERS (cr=5 pr=4 pw=4 time=0 us cost=1 size=34 card=1)

      1      INDEX RANGE SCAN MR_PK (cr=4 pr=3 pw=3 time=0 us cost=1 size=0 card=1)(object id 106689)

      1    REMOTE  D18_790 (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)

 

 

Rows     Execution Plan

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

      0  SELECT STATEMENT   MODE: ALL_ROWS

      1   FILTER

      1    NESTED LOOPS

      1     NESTED LOOPS

      1      NESTED LOOPS

      1       NESTED LOOPS

      1        NESTED LOOPS

      2         NESTED LOOPS

      2          NESTED LOOPS

      2           NESTED LOOPS

      2            NESTED LOOPS

      1             TABLE ACCESS   MODE: ANALYZED (BY INDEX

                        ROWID) OF 'METER_NHH' (TABLE)

      1              INDEX   MODE: ANALYZED (UNIQUE SCAN) OF

                         'METER_NHH_PK' (INDEX (UNIQUE))

      2             REMOTE OF 'D18_780' (REMOTE)

                        [PQE_TO_DMV_LINK]

                       SELECT "D18_780_ID","STTLMNT_DT" FROM "DMV"."D18_780"

                       "A" WHERE TO_CHAR("D18_780_ID") LIKE '%' AND

                         "STTLMNT_DT">=:1 AND "STTLMNT_DT"<=:2

      2            REMOTE OF 'D18_781' (REMOTE) [PQE_TO_DMV_LINK]

 

                      SELECT "D18_781_ID","D18_780_ID" FROM "DMV"."D18_781"

                      "B" WHERE TO_CHAR("D18_781_ID") LIKE '%' AND :1=

                        "D18_780_ID"

      2           REMOTE OF 'D18_782' (REMOTE) [PQE_TO_DMV_LINK]

                     SELECT "D18_782_ID","D18_781_ID" FROM "DMV"."D18_782"

                     "C" WHERE TO_CHAR("D18_782_ID") LIKE '%' AND

                       "D18_781_ID"=:1

      2          REMOTE OF 'D18_783' (REMOTE) [PQE_TO_DMV_LINK]

                    SELECT "D18_783_ID","D18_782_ID","GSP_GRP_ID" FROM

                    "DMV"."D18_783" "D" WHERE TO_CHAR("D18_783_ID") LIKE

                      '%' AND "GSP_GRP_ID"=:1 AND :2="D18_782_ID"

      1         REMOTE OF 'D18_784' (REMOTE) [PQE_TO_DMV_LINK]

                   SELECT "D18_784_ID","D18_783_ID","PRFL_CLSS_ID" FROM

                   "DMV"."D18_784" "E" WHERE TO_CHAR("D18_784_ID") LIKE '%'

                     AND "PRFL_CLSS_ID"=:1 AND "D18_783_ID"=:2

      1        REMOTE OF 'D18_787' (REMOTE) [PQE_TO_DMV_LINK]

                  SELECT "D18_787_ID","D18_784_ID",

                  "STNDRD_STTLMNT_CNFGRTN_ID" FROM "DMV"."D18_787" "F"

                  WHERE TO_CHAR("D18_787_ID") LIKE '%' AND

                  TO_NUMBER("STNDRD_STTLMNT_CNFGRTN_ID")=:1 AND :2=

                    "D18_784_ID"

      1       REMOTE OF 'D18_789' (REMOTE) [PQE_TO_DMV_LINK]

                 SELECT "D18_789_ID","D18_787_ID","TM_PTTRN_RGM" FROM

                 "DMV"."D18_789" "G" WHERE TO_CHAR("D18_789_ID") LIKE '%'

                   AND :1="D18_787_ID"

      1      TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF

                 'MTD_REGISTERS' (TABLE)

      1       INDEX   MODE: ANALYZED (RANGE SCAN) OF 'MR_PK' (INDEX

                  (UNIQUE))

      1     REMOTE OF 'D18_790' (REMOTE) [PQE_TO_DMV_LINK]

               SELECT "D18_790_ID","D18_789_ID" FROM "DMV"."D18_790" "H"

                 WHERE TO_CHAR("D18_790_ID") LIKE '%' AND "D18_789_ID"=:1

 

********************************************************************************

 

According to My Oracle Support:

              

This issue can appear as a regression introduced in 11.1.0.6 due to improved nested loops code added in 11g.

 

and

 

If a SQL is accessing multiple remote tables at the same remote site then depending on the execution plan chosen it is possible to get multiple database link connections opened when this did not occur in earlier releases. The opening of more than one database link for the SQL can lead to ORA-2020 errors.

 

I increased the value of the open_links parameter:

 

SQL> conn / as sysdba

Connected.

SQL> alter system set open_links = 10

  2  scope = spfile

  3  /

 

System altered.

 

SQL> startup force

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

 

Total System Global Area  630702080 bytes

Fixed Size                  2091232 bytes

Variable Size             381687584 bytes

Database Buffers          243269632 bytes

Redo Buffers                3653632 bytes

Database mounted.

Database opened.

SQL> select value from v$parameter

  2  where name = 'open_links'

  3  /

 

VALUE

----------

10

 

SQL>

 

I checked that the query then worked:

 

SQL> SELECT ppc.sttlmnt_dt day_date

  2  FROM   vrm_d18_ppc   ppc,

  3         meter_nhh     mtr,

  4         mtd_registers reg

  5  WHERE  ppc.tm_pttrn_rgm = reg.tpr

  6  AND    TO_NUMBER(ppc.stndrd_sttlmnt_cnfgrtn_id) = mtr.std_stlmnt_config_id

  7  AND    ppc.prfl_clss_id = mtr.profile_class_id

  8  AND    ppc.gsp_grp_id = mtr.gsp_group_id

  9  AND    reg.mpan_core = mtr.mpan_core

10  AND    reg.mpan_core = '1900008212269'

11  AND    mtr.meter_id = 3232

12  AND    reg.meter_serial_id = 'L8103349'

13  AND    reg.meter_register_id = 'TO'

14  AND    ppc.sttlmnt_dt BETWEEN '01-AUG-14' AND '31-AUG-14'

15  /

 

DAY_DATE

---------

01-AUG-14

02-AUG-14

03-AUG-14

04-AUG-14

05-AUG-14

Etc

 

Once the query had finished, I checked the number of links the session had opened:

 

SQL> select db_link from v$dblink

  2  /

 

DB_LINK

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

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

PQE_TO_DMV_LINK

 

8 rows selected.

 

SQL>

 

It should have needed only one, not eight. This is another feature of this bug.

 

I reran the query in a copy of the same database on Oracle 11.2.0.4, which still had its open_links parameter set to the default of four. It ran successfully so I checked the explain plan. It still did as many nested loops but, when I checked the number of links the session had opened, I only saw one so the bug does not appear to affect this version of Oracle:

 

SQL> select db_link from v$dblink

  2  /

 

DB_LINK

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

PQE_TO_DMV_LINK

 

SQL>