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>

Bug 5497611

I used Oracle Enterprise Manager to look at the execution plan for some SQL in an Oracle 10.2.0.3 database. (The SQL shown is just an example done later for the purposes of this blog post. As usual, click on the image to enlarge it and bring it into focus if necessary.):

This produced the following ORA-00600 message several times in the alert log:

 

Wed Oct  1 18:13:21 2014

Errors in file /oracle/app/oracle/product/10.2.0/admin/mrmdpt1/udump/mrmdpt1_ora_15467.trc:

ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224]

 

I looked in the trace file and saw the SQL which had caused the problem:

 

ORA-00600: internal error code, arguments: [qctVCO:csform], [0], [0], [0], [0], [112], [2], [224]

Current SQL statement for this session:

SELECT extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval, '/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type = "dyn

amic_sampling"]'), nvl(extractvalue(xmlval, '/*/info[@type = "index_size"]'), -1) from (select xmltype(other_xml) xmlval from v$sql_plan where sql_id = :1 an

d other_xml is not null and id = 1)

I extracted the SQL and ran it in a SQL*Plus session:

SQL> set head off

SQL> l

  1  SELECT

  2  extractvalue(xmlval, '/*/info[@type = "sql_profile"]'),

  3  extractvalue(xmlval, '/*/info[@type = "outline"]'),

  4  extractvalue(xmlval, '/*/info[@type = "dynamic_sampling"]'),

  5  nvl(extractvalue(xmlval, '/*/info[@type = "index_size"]'), -1)

  6  from

  7  (select xmltype(other_xml) xmlval

  8   from v$sql_plan

  9   where sql_id = 'cp5caasd2udnw'

 10   and other_xml is not null

 11*  and id = 1)

SQL> /

 

 

 

 

-1

 

 

 

 

-1

 

 

SQL>

 

Each time I did this, a similar ORA-00600 appeared in the alert log, proving that I had correctly diagnosed the cause of the problem. I looked the problem up in My Oracle Support and found that it was caused by bug 5497611.