OraFAQ Forum: Server Administration » session parameters AUTO-change for specific user

Home » RDBMS Server » Server Administration » session parameters AUTO-change for specific user (Oracle 19c, Windows Server 2019)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

session parameters AUTO-change for specific user [message #687679]

Wed, 03 May 2023 11:25

Andrey_R
Messages: 441
Registered: January 2012
Location: Israel

Senior Member

Hi DBAs,
Strange behavior of that one specific user.

My system-wide, and therefore my session parameter values for "DB_FILE_MUTLIBLOCK_READ_COUNT" & "OPTIMIZER_MODE" , for user "OK_USER" are 8 and FIRST_ROWS_100:

Code: [Select all] [Show/ hide]

Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL>
SQL>
SQL> create user OK_USER identified by "123";

User created.

SQL> grant dba to OK_USER;

Grant succeeded.

SQL> conn OK_USER/123
Connected.
SQL>
SQL> show user
USER is "OK_USER"
SQL>
SQL> show parameter db_file_mu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
SQL>
SQL> show parameter OPTIMIZER_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_100
SQL>

If I am switching with CURRENT_SCHEMA to a different user, printing theses params and values - they obviously stays the same ...

Code: [Select all] [Show/ hide]

SQL> alter session set current_schema=OK_USER;

Session altered.

SQL> show parameter OPTIMIZER_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_100
SQL> show parameter db_file_mu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
SQL>

The strange thing occurs when I setting session current schema to a specific user, that seems to have these parameters "triggered" to show different values:

Code: [Select all] [Show/ hide]

SQL>
SQL> alter session set current_schema=SPECIFIC_USER;

Session altered.

SQL> show parameter db_file_mu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     128
SQL> show parameter OPTIMIZER_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      ALL_ROWS
SQL>

switching back to OK_USER - it's ok again..:

Code: [Select all] [Show/ hide]

SQL>
SQL> alter session set current_schema=OK_USER;

Session altered.

SQL> show parameter OPTIMIZER_MODE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                       string      FIRST_ROWS_100
SQL> show parameter db_file_mu

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count        integer     8
SQL>

When I check for triggers in DBA_TRIGGERS I don't seem to find anything with this query:

Code: [Select all] [Show/ hide]

SQL> select * FROM DBA_TRIGGERS where owner = 'SPECIFIC_USER';

no rows selected

SQL>

Any ideas what could be causing the differently displayed values ?

Thanks,
Andrey

[Updated on: Wed, 03 May 2023 11:28]

Report message to a moderator

Re: session parameters AUTO-change for specific user [message #687680 is a reply to message #687679]

Wed, 03 May 2023 12:19

John Watson
Messages: 8978
Registered: January 2010
Location: Global Village

Senior Member

I don't have an answer, but perhaps can suggest a line for research.
Remember that ALTER SESSION SET CURRENT_SCHEMA has no effect whatsoever on who you are. So logon triggers cannot be relevant. All it does is prefix every unprefixed object name with the schema nominated. So if you are logged on as SCOTT and then run ALTER SESSION SET CURRENT_SCHEMA=FREDDIE if you run SELECT * FROM EMP what you will actually run is SELECT * FROM FREDDIE.EMP. This does raise the question "what is the point of it". Well, I suppose it saves creating a lot of public synonyms, and lets lazy developers be - how can I put it - lazy. Some people say "public synonyms are evil"; perhaps so - though in your case, private synonyms might be more relevant.
So I would look at synonyms. Perhaps you have hit something weird with someone trying to be a bit too clever. Like, creating synonyms for V$ views or X$ tables.

[Updated on: Wed, 03 May 2023 12:20]

Report message to a moderator

Previous Topic:	Database 23c Free now available
Next Topic:	resource manager used with SE2

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 10:15:11 CDT 2025