Thanks Tom for your quick response. We also have been considering dropping public synonyms and using set current_schema instead, but we have the following questions:

1. If end user only need to access one application schema like batch loading user, then the easiest way to set current_schema is to be done by using "after logon database trigger". Is this true?
2. If end user need to access different application schemas through different application modules, like web module, power builder, Oracle Forms/Reports. The "after logon database trigger" will be unable to handle this situation. Do you think that creating a stored procedure, let's say 'pr_set_schemaid', and execute in each application module is the only way to go? Because we have hundreds of forms and reports, where is the best place the procedure called by Oracle Forms/Reports? Do we need to include this set in every *.fmt or *rpt?
3. How to handle the scheduled jobs in job queue? Do you prefer to modify PL/SQL procedure using schema.object format or using "alter session set current_schema"?

Again, thank you for your time.

Dear Tom,

Here is a new problem I have while eliminating public synonym. Let’s say, I have user SCOTT and TEST, and the following is what I have done:
Connected to Oracle9i Enterprise Edition Release 9.0.1.0.0 
Connected as SCOTT
SQL> create role access_scott;
Role created
SQL> grant select on emp to access_scott;
Grant succeeded
SQL> grant access_scott to test;
Grant succeeded
SQL> GRANT CREATE SESSION TO TEST;
Grant succeeded
SQL> GRANT ALTER SESSION TO TEST;
Grant succeeded
SQL> CREATE OR REPLACE TRIGGER set_scott_schema AFTER logon ON DATABASE
   BEGIN
if user = 'TEST' then
execute immediate 'alter session set current_schema = SCOTT';
end if;
END;
/
Trigger created

SQL> connect test/xxxxx;
Connected to Oracle9i Enterprise Edition Release 9.0.1.0.0 
Connected as test
SQL> select count(*) from emp;
  COUNT(*)
----------
        14
SQL>


So far, everything works fine. However, when I create a simple Oracle form which has only a button together with a text item using Oracle Form6i. The button in the form has a WHEN-BUTTON-PRESSED trigger using embedded the following PL/SQL statement:
Begin

    Select count(*) into :totalnumber
    From   emp;
End;

Login test to compile the form, always got error ‘identifier EMP must be declare’, and I login SQL plus check the schema for the test, it shows the test in scott schema, shown as following:

SQL> SELECT USERNAME,SCHEMANAME FROM V$SESSION;

USERNAME                       SCHEMANAME
------------------------------ ------------------------------
                               SYS
                               SYS
                               SYS
                               SYS
                               SYS
                               SYS
                               SYS

TEST                           SCOTT

Any idea and solution? Thanks in advance.
 

I am finishing an application for an Electrical Regulation Company composed of 3 schemas:

1.- Schema A: Objects for Electrical Network (tables, views etc for the network elements, etc)
2.- Schema B: Proceses objects (tables, etc for processes that are applied over the electrical networks they built )
3.- Schema C: Tables for menus, app specific messages, app parameters, error logs, etc

How can I perform, "alter session set current_schema = APP_SCHEMA" if I need one user connected and accesing objects of the 3 schemas (A,B,C) defined in the database.

I think the only way to solve it (we are doing this now in the development schema) is to use public synonyms...

Thank you very much. I expect some comments or an alternate solution, becase I think the only way to solve this is with public synonyms. Even though you recommend not to use them.

Carlos Gongora

Hi tom,

Just wanted to clarify something with setting the current_schema.

1. What could be the best approach for granting proper privileges to the user that need to access my application?

a. grant all my schema objects to the user directly?
b. create a role then grant the privileges to this role then grant the role to that user.
create role app_rw; create role app_ro;
For read only (RO) and Read Write (RW) privileges .

If i create role and my application is access v$ views, I still need to explicitly grant the privileges directly to the user.

OR

create a public synonym for the v$session and only grant select to public.

Hope you can clarify things for me...

Cheers,
NOTNA

Tom,
While compiling some invalid synonyms, I got "Operation 192 succeeded." What does it mean?
sys@MTRAIN> ALTER SYNONYM SYNCHRO.AUDIT_RESULT_READABLE_V compile;
Operation 192 succeeded.

I got this for the first time. After that the synonyms' status became VALID. My SQLplus client is 9.0.1.0.1 and server is 10.1.0.4.0
Thanks,
Rana

 
ops$tkyte@ORA9IR1> alter synonym s compile;
alter synonym s compile
      *
ERROR at line 1:
ORA-00940: invalid ALTER command
 


in 9i r1 that command did not exist, sqlplus -- trying to be "very smart" looks at the statment so it knows what to print out afterwards, eg:

ops$tkyte@ORA9IR2> alter synonym s compile;
 
Synonym altered.
 

Now, 9iR1 sqlplus has no idea what command it just ran because it didn't recognize an alter synonym, it just ran it and reported back that the last operation, internal code 192, succeeded.


Fire up an 8.x sqlplus and try this query:

with x as (select * from dual) select * from x;


sqlplus didn't know about "with", it has no idea what it "is" 

Thanks Tom,
I tried the "with" in 8i as well as in 9i
For Your Information only I am putting the results.
In 8i
=========
SQL> with x as (select * from dual) select * from x;
SP2-0734: unknown command beginning "with x as ..." - rest of line ignored.
In 9i
=========
dba_group@DEVL.MONSANTO.COM> with x as (select * from dual) select * from x;

D
-
X

1 row selected.
dba_group@DEVL.MONSANTO.COM> with x as (select * from v$instance) select * from x;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME
--------------- ---------------- ---------------------------------------------------
              1 devl             faunus
....
1 row selected.

Thanks for your help.
Rana
 

In Postgresql for example you can execute this command:

ALTER USER kamal SET search_path = scott, kamal, public;

Would'nt it be useful something similar in Oracle too?

I couldn't find any documentation on ALTER SYNONYM. I searched in 9iR2, 10gR2, 11gR1 and none of them mention ALTER SYNONYM exists ... Is it that i just couldn't find it or is it a documentation bug or is there some reason why alter synonym is undocumented?

Hi,
im trying to import synonym created on my Source schema DB into OWB10gR1 source module. i couldnt able to see in OWB import schema objects 'Synonym'(where i could see Tables, Views...)when im trying to Import.
My source Schema is in remote database.i could see the other objects like tables,views of my source schema through OWB, but Synonym not...why so?
i created the Synonym on my source schema DB by remote DB link. when we were trying to do the same on remote DB box, its working fine. why not here?

is that possible to import the synonym created by remote DB into OWB10gR1?
kindly help me

with thanks
Sarvan

Hi Tom,
In the below example, could you please let me know what has SYSDBA more than a user having DBA role to compile a synonym belonging to other users? I do not see SYSDBA having anything like "ALTER ANY SYNONYM" privilege.

SQL> grant create session, create synonym to user1 identified by user1;

Grant succeeded.

SQL> grant dba to user2 identified by user2;

Grant succeeded.

SQL> conn user1/user1
Connected.
SQL> create synonym syn1 for dual;

Synonym created.

SQL> alter synonym syn1 compile;

Synonym altered.

SQL> conn user2/user2
Connected.
SQL> alter synonym user1.syn1 compile;
alter synonym user1.syn1 compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> select * from session_privs where PRIVILEGE like '%SYNONYM%';

PRIVILEGE
----------------------------------------
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM

SQL> conn / as sysdba
Connected.
SQL>
SQL> alter synonym user1.syn1 compile;

Synonym altered.

SQL> select * from session_privs where PRIVILEGE like '%SYNONYM%';

PRIVILEGE
----------------------------------------
CREATE SYNONYM
CREATE ANY SYNONYM
DROP ANY SYNONYM
CREATE PUBLIC SYNONYM
DROP PUBLIC SYNONYM


Regards,
Prajjwal