Я тоже не понимаю, зачем. Но кому-то хочется быть не такими, как все.

Для этого случая есть пиво Тинькофф:-)

"ALTER SESSION SET CURRENT_SCHEMA"

вообще звучит как то алогично - ведь CURRENT_SCHEMA есть схема текущего юзера. /* to softbuilder */ Что current это просто слово, понимаю.

Хорошо хоть аналога переменной PATH нету - прописываешь список схем где Ораклу следует искать объекты если их нет в схеме юзера:-) Вот бы была потеха!

С точки зрения программиста на стадии разработки, согласна, очень удобно, сама часто этим пользуюсь. Но когда начинают это закладывать в механизм работы приложения, ИМХО это уже плохо.

Интересная дока по этому поводу из Оракл Management of Schema Objects. Может кому поможет избежать граблей.

Because of how Oracle resolves references, it is possible for an object to depend on the nonexistence of other objects. This situation occurs when the dependent object uses a reference that would be interpreted differently were another object present. For example, assume the following:

At the current point in time, the company schema contains a table named emp.
A PUBLIC synonym named emp is created for company.emp and the SELECT privilege for company.emp is granted to the PUBLIC role.

The jward schema does not contain a table or private synonym named emp.
The user jward creates a view in his schema with the following statement:



When jward creates the dept_salaries view, the reference to emp is resolved by first looking for jward.emp as a table, view, or private synonym, none of which is found, and then as a public synonym named emp, which is found. As a result, Oracle notes that jward.dept_salaries depends on the nonexistence of jward.emp and on the existence of public.emp.

Now assume that jward decides to create a new view named emp in his schema using the following statement:



Notice that jward.emp does not have the same structure as company.emp.

As it attempts to resolve references in object definitions, Oracle internally makes note of dependencies that the new dependent object has on "nonexistent" objects--schema objects that, if they existed, would change the interpretation of the object's definition. Such dependencies must be noted in case a nonexistent object is later created. If a nonexistent object is created, all dependent objects must be invalidated so that dependent objects can be recompiled and verified and all dependent function-based indexes must be marked unusable.

Therefore, in the previous example, as jward.emp is created, jward.dept_salaries is invalidated because it depends on jward.emp. Then when jward.dept_salaries is used, Oracle attempts to recompile the view. As Oracle resolves the reference to emp, it finds jward.emp (public.emp is no longer the referenced object). Because jward.emp does not have a sal column, Oracle finds errors when replacing the view, leaving it invalid.

to iNT

Дискуссии о CURRENT_SCHEMA в форуме уже встречались, советы которыя я сочла полезными следующие

1) Создавать единый account для приложения а потом раздавать привилегии включая или не включая определенные роли
2) Использовать синонимы

Странно что у тебя EXECUTE IMMEDIATE 'ALTER SESSION SET ... не работает. Возможно это из за триггера. Проверила на процедуре, схема меняется