ORA-12801 in utlrp

I tried to run a utlrp in an Oracle 11.1.0.6.0 database and saw the errors below:

ORA-12801: error signaled in parallel query server P035

ORA-00018: maximum number of sessions exceeded

ORA-06512: at "SYS.UTL_RECOMP", line 629

ORA-06512: at line 4

I checked the number of sessions in v$session but there were not very many:

SQL> select count(*) from v$session;

 

  COUNT(*)

----------

        32

 

SQL>

I checked the value of the sessions parameter and it was much higher:

SQL> l

  1  select value from v$parameter

  2* where name = 'sessions'

SQL> /

 

VALUE

----------

87

 

SQL>

I took a look in the utlrp.sql and saw the following line:

@@utlprp.sql 0

I read somewhere that the parameter supplied to utlprp.sql specifies the number of parallel processes to use when doing the recompilations. If it is zero, the value is calculated as cpu_count x parallel_threads_per_cpu. I checked these two parameters:

SQL> l

  1  select name, value

  2  from v$parameter

  3  where name in

  4* ('cpu_count', 'parallel_threads_per_cpu')

SQL> /

 

NAME                           VALUE

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

cpu_count                      16

parallel_threads_per_cpu       2

 

SQL>

I traced my session and reran the utlrp.sql. When I looked in the trace file, I saw the following piece of SQL, which I have reformatted slightly:

WITH INST_CPUS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPUS

FROM GV$PARAMETER

WHERE NAME = 'cpu_count'),

INST_THREADS AS

(SELECT INST_ID, NVL(TO_NUMBER(VALUE), 1) CPU_THREADS

FROM GV$PARAMETER

WHERE NAME = 'parallel_threads_per_cpu')

SELECT SUM((CASE WHEN CPUS <= 0 THEN 1 ELSE CPUS END)

* (CASE WHEN CPU_THREADS <= 0 THEN 1 ELSE CPU_THREADS END))

FROM INST_CPUS, INST_THREADS

WHERE INST_CPUS.INST_ID = INST_THREADS.INST_ID

So, clearly, cpu_count and parallel_threads_per_cpu are being multiplied together for some reason when you run a utlrp.sql. I changed the line above from:

@@utlprp.sql 0

to

@@utlprp.sql 1

... hoping that this would make the recompilations run one at a time. I ran the utlrp.sql again and it worked.

Recursion in Oracle 12

Oracle 12 has a new parameter called PGA_AGGREGATE_LIMIT:

SQL> conn system/manager

Connected.

SQL> l

  1  select value from v$parameter

  2* where name = 'pga_aggregate_limit'

SQL> /

 

VALUE

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

2147483648

 

SQL>

You cannot set it below 2 gigabytes:

SQL> alter system

  2  set pga_aggregate_limit = 1g;

alter system

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because

specified value is invalid

ORA-00093: pga_aggregate_limit must be between 2048M

and 100000G

 

SQL> 

In earlier versions of Oracle, if you let recursive code get out of control, it could use up all the memory on the underlying server. I decided to try this out on Oracle 12:

SQL> create or replace procedure recursion is

  2  begin

  3  recursion();

  4  end;

  5  /

 

Procedure created.

 

SQL> exec recursion();

While this was running, I found I could still use the machine so I took a look in the alert log and saw the following errors at the end:

Thu Feb 12 17:43:34 2015

Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_2796.trc  (incident=19267):

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\incident\incdir_19267\orcl1_ora_2796_i19267.trc

Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\trace\orcl1_ora_2796.trc  (incident=19268):

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

Incident details in: C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\incident\incdir_19268\orcl1_ora_2796_i19268.trc

When I looked in V$SESSION_EVENT, I saw that there had been a brief wait on the acknowledge over PGA limit event:

SQL> l

  1  select event, time_waited/100

  2  from v$session_event

  3* where sid = 11

SQL> /

 

EVENT                          TIME_WAITED/100

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

acknowledge over PGA limit                7.05

Disk file operations I/O                     0

log buffer space                             0

SQL*Net message to client                    0

SQL*Net message from client             116.29

SQL*Net break/reset to client                0

 

6 rows selected.

 

SQL>

After this, the session was killed:

 

SQL> select status from v$session where sid = 11;

 

STATUS

--------

KILLED

 

SQL>

… and there was a message to this effect in the alert log:

Thu Feb 12 17:52:59 2015

Errors in file C:\APP\ADMINISTRATOR\diag\rdbms\orcl1\orcl1\incident\incdir_19267\orcl1_ora_2796_i19267.trc:

ORA-00028: your session has been killed

ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT

The only problem I could see was that no error message was returned to the session running the recursion.

As an extra test, I used SYS to run the recursive procedure:

C:\Users\Administrator>sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.1.0 Production on Fri Feb 13 09:02:36 2015

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL> exec system.recursion();

This wrote more serious messages to the alert log:

Fri Feb 13 09:04:39 2015

PGA_AGGREGATE_LIMIT has been exceeded but some processes using the most PGA

memory are not eligible to receive ORA-4036 interrupts.  Further occurrences

of this condition will be written to the trace file of the CKPT process.

The session remained ACTIVE and V$SESSION_EVENT did not know what it was waiting for:

SQL> select sid from v$session

  2  where username = 'SYS';

 

       SID

----------

       237

 

SQL> select status from v$session

  2  where sid = 237;

 

STATUS

--------

ACTIVE

 

SQL> select event, time_waited/100

  2  from v$session_event

  3  where sid = 237

  4  /

 

EVENT                                    TIME_WAITED/100

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

Disk file operations I/O                               0

db file sequential read                              .09

SQL*Net message to client                              0

SQL*Net message from client                       101.92

 

SQL>

Global_Names and ORA-02085

This was tested on Oracle 9. I created 2 database links to the same target database:

 

SQL> conn / as sysdba

Connected.

SQL> create database link any_name.world

  2  connect to link_schema

  3  identified by link_schema

  4  using 'REMOTEDB.WORLD'

  5  /

 

Database link created.

 

SQL> create database link remotedb.world

  2  connect to link_schema

  3  identified by link_schema

  4  using 'REMOTEDB.WORLD'

  5  /

 

Database link created.

 

SQL>

 

I checked the value of the source database’s global_names parameter:

 

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

 

VALUE

----------

FALSE

 

SQL>

 

I tested both links and they worked OK:

 

SQL> select sysdate from dual@any_name.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL> select sysdate from dual@remotedb.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

I changed global_names to true in the source database:

 

SQL> alter system set global_names = true

  2  /

 

System altered.

 

SQL> select value from v$parameter

  2  where name = 'global_names'

  3  /

 

VALUE

----------

TRUE

 

SQL>

 

This stopped the any_name.world link working as the link name in the source database and the global_name of the target database must match if global_names is set to true in the source database:

 

SQL> select sysdate from dual@any_name.world

  2  /

select sysdate from dual@any_name.world

                         *

ERROR at line 1:

ORA-02085: database link ANY_NAME.WORLD connects to

REMOTEDB.WORLD

 

SQL>

 

But the remotedb.world link still worked:

 

SQL> select sysdate from dual@remotedb.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

How do I know that the check is made against the target database’s global name? I connected to the target database and changed its global name to any_name.world:

 

SQL> conn /@remotedb.world

Connected.

SQL> alter database rename global_name

  2  to any_name.world

  3  /

 

Database altered.

 

I reconnected to the source database: 

SQL> conn / as sysdba

Connected.

SQL>

 

Then the any_name.world link worked:

 

SQL> select sysdate from dual@any_name.world

  2  /

 

SYSDATE

---------

26-APR-12

 

SQL>

 

But the remotedb.world link didn’t:

 

SQL> select sysdate from dual@remotedb.world

  2  /

select sysdate from dual@remotedb.world

                         *

ERROR at line 1:

ORA-02085: database link REMOTEDB.WORLD connects to

ANY_NAME.WORLD

 

SQL>

SQL92_SECURITY

I tested the examples in this post in two Oracle 11.2 databases. In the first example, Andrew creates a table, inserts a row into it then grants UPDATE privilege on it to Fred, who is then able to work out the value in the table, without changing it permanently, using a series of UPDATE and ROLLBACK statements: 

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'sql92_security'

  3  /

 

VALUE

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

FALSE

 

SQL> create user andrew

  2  identified by reid

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant create session, create table

  2  to andrew

  3  /

 

Grant succeeded.

 

SQL> create user fred

  2  identified by bloggs

  3  /

 

User created.

 

SQL> grant create session to fred

  2  /

 

Grant succeeded.

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (5)

  2  /

 

1 row created.

 

SQL> grant update on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 10

  4  /

 

0 rows updated.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 0

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 5

  4  /

 

0 rows updated.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 3

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 > 4

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update andrew.tab1

  2  set col1 = 99

  3  where col1 = 5

  4  /

 

1 row updated.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> 

The example is trivial. Working out floating pointing numbers or long character strings in tables containing thousands of rows would take ages using this method. However, by using some clever PL/SQL, it would be possible, given time. This isn’t too much of a problem for me. After all, if a malicious person had UPDATE access to a table of mine, I’m sure he could cause far more damage by simply overwriting my data. But if you don’t like the idea of people guessing values in your tables, then the SQL92_SECURITY initialization parameter could be for you. In the example above, you may have noticed that it was turned off.

 

However, in the second example below, the parameter is turned on. It was run in a different database, as mentioned above, but the users were created in a similar fashion. This time, the UPDATE privilege does not allow Fred to run UPDATE statements with WHERE clauses. To do this, he needs SELECT access as well: 

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'sql92_security'

  3  /

 

VALUE

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

TRUE

 

SQL> conn andrew/reid

Connected.

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values (1)

  2  /

 

1 row created.

 

SQL> grant update on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL> update andrew.tab1 set col1 = 3

  2  where col1 = 2

  3  /

update andrew.tab1 set col1 = 3

              *

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL> conn andrew/reid

Connected.

SQL> grant select on tab1 to fred

  2  /

 

Grant succeeded.

 

SQL> conn fred/bloggs

Connected.

SQL> update andrew.tab1 set col1 = 3

  2  where col1 = 2

  3  /

 

1 row updated.

 

SQL>

DROP TABLE ... PURGE

I tested this on Oracle 11.2.0.2.7. If the recyclebin is in use:

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'recyclebin'

  3  /

 

VALUE

----------

on

 

SQL> 

... and you create a table in a tablespace other than system: 

SQL> create table tab1

  2  (col1 number)

  3  tablespace users

  4  /

 

Table created.

 

SQL>

... it goes in the recyclebin when you drop it:

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL> select original_name from recyclebin

  2  /

 

ORIGINAL_NAME

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

TAB1

 

SQL>

… and you can restore it from there if you need to: 

SQL> flashback table tab1 to before drop

  2  /

 

Flashback complete.

 

SQL> desc tab1

Name                       Null?    Type

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

COL1                                NUMBER

 

SQL>

If you do not want this to happen, add the word purge at the end of the drop table statement:

SQL> drop table tab1 purge

  2  /

 

Table dropped.

 

SQL>

... then it does not go into the recyclebin when you drop it: 

SQL> select original_name from recyclebin

  2  /

 

no rows selected

 

SQL>

... and you cannot restore it afterwards:

SQL> flashback table tab1 to before drop

  2  /

flashback table tab1 to before drop

*

ERROR at line 1:

ORA-38305: object not in RECYCLE BIN

 

SQL>