ALTER SESSION SET CURRENT_SCHEMA - Secrets of the Oracle Database - page 136

The database objects were created in schema HR. It is undocumented that restrictions
pertaining to certain database objects apply when using ALTER SESSION SET CURRENT_SCHEMA .
Restrictions of ALTER SESSION SET
CURRENT_SCHEMA
Switching to a different parsing schema identifier cannot be used with Advanced Queuing.
Furthermore it is impossible to create a private database link in a foreign schema. It is possible
to create a stored outline in a foreign schema. The next sections provide the details on these
restrictions.
Advanced Queuing
ALTER SESSION SET CURRENT_SCHEMA has no effect on name resolution by the Advanced Queuing
(AQ) packages DBMS_AQADM and DBMS_AQ . It is documented that synonyms cannot be used with
advanced queues. Hence, the only way to use queue tables and queues in a foreign schema in
conjunction with the PL/SQL packages DBMS_AQ and DBMS_AQADM is to qualify the object names
with the schema name, in the same way that a table in a foreign schema needs to be qualified
as long as there is no synonym in the current schema. The following code sample assumes that
the queue created in Chapter 16 resides in schema NDEBES.
$ sqlplus / as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
SQL> SELECT owner FROM dba_queues WHERE name='CAUGHT_IN_SLOW_Q_AGAIN';
OWNER
---------
NDEBES
SQL> ALTER SESSION SET CURRENT_SCHEMA=ndebes;
Session altered.
SQL> EXEC dbms_aqadm.stop_queue('CAUGHT_IN_SLOW_Q_AGAIN')
BEGIN dbms_aqadm.stop_queue('CAUGHT_IN_SLOW_Q_AGAIN'); END;
*
ERROR at line 1:
ORA-24010: QUEUE SYS.CAUGHT_IN_SLOW_Q_AGAIN does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4913
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 1
SQL> CONNECT ndebes/secret
Connected.
SQL> EXEC dbms_aqadm.stop_queue('CAUGHT_IN_SLOW_Q_AGAIN')
PL/SQL procedure successfully completed.
The reason for the failure of STOP_QUEUE executed by SYS is not a lack of privileges. When
SYS qualifies the queue name with the correct schema name, STOP_QUEUE works flawlessly.