Prev: Copy 100GB MSSQL 9.0 Database to Oracle 10g
Next: Security Analyst , Franklin Lakes, New Jersey
From: News on 16 Jun 2010 06:35
Hi,

I have strange problem with one user that cannot query a table
anymore. Th eproblem appeared suddenly while no change was made to
primary. DB is running 10.2.0.2 on Solaris

SQL> alter session set current_schema=stymmz;
SQL> select count(*) from historique_inventaire where 1=2;
....
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access


public synonym for table historique_inventaire is defined
it works when schema is specified:

SQL> select count(*) from ORAGP20C01.HISTORIQUE_INVENTAIRE where 1=2;

COUNT(*)
----------
0

it works with another user:
SQL> alter session set current_schema=SUPPORTDEV;

Session altered.

SQL> select count(*) from historique_inventaire where 1=2;

COUNT(*)
----------
0

it works on primary ..
From: ddf on 16 Jun 2010 07:17
On Jun 16, 6:35 am, News <ben1245_...(a)hotmail.fr> wrote:
> Hi,
>
> I have strange problem with one user that cannot query a table
> anymore. Th eproblem appeared suddenly while no change was made to
> primary. DB is running 10.2.0.2 on Solaris
>
> SQL> alter session set current_schema=stymmz;
> SQL> select count(*) from historique_inventaire where 1=2;
> ...
> ORA-00604: error occurred at recursive SQL level 1
> ORA-16000: database open for read-only access
>
> public synonym for table historique_inventaire is defined
> it works when schema is specified:
>
> SQL> select count(*) from ORAGP20C01.HISTORIQUE_INVENTAIRE where 1=2;
>
> COUNT(*)
> ----------
> 0
>
> it works with another user:
> SQL> alter session set current_schema=SUPPORTDEV;
>
> Session altered.
>
> SQL> select count(*) from historique_inventaire where 1=2;
>
> COUNT(*)
> ----------
> 0
>
> it works on primary ..

If you actually login as this problem user do you still see the same
errors?


David Fitzjarrell
From: News on 16 Jun 2010 07:24
On 16 juin, 13:17, ddf <orat...(a)msn.com> wrote:
> If you actually login as this problem user do you still see the same
> errors?
>
> David Fitzjarrell

yes .. the code provided is a simulation

SQL> connect stymmz/stymmz(a)oragpd10
Connected.
SQL> select count(*) from historique_inventaire where 1=2;
select count(*) from historique_inventaire where 1=2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

SQL> select count(*) from oragp20c01.historique_inventaire where 1=2;

COUNT(*)
----------
0
From: News on 16 Jun 2010 07:52
On 16 juin, 12:35, News <ben1245_...(a)hotmail.fr> wrote:
> Hi,
>
> I have strange problem with one user that cannot query a table
> anymore. Th eproblem appeared suddenly while no change was made to
> primary. DB is running 10.2.0.2 on Solaris
>
> SQL> alter session set current_schema=stymmz;
> SQL> select count(*) from historique_inventaire where 1=2;
> ...
> ORA-00604: error occurred at recursive SQL level 1
> ORA-16000: database open for read-only access
>
> public synonym for table historique_inventaire is defined
> it works when schema is specified:
>
> SQL> select count(*) from ORAGP20C01.HISTORIQUE_INVENTAIRE where 1=2;
>
> COUNT(*)
> ----------
> 0
>
> it works with another user:
> SQL> alter session set current_schema=SUPPORTDEV;
>
> Session altered.
>
> SQL> select count(*) from historique_inventaire where 1=2;
>
> COUNT(*)
> ----------
> 0
>
> it works on primary ..

the problem was related to invalid private synonyms
 | 
Pages: 1
Prev: Copy 100GB MSSQL 9.0 Database to Oracle 10g
Next: Security Analyst , Franklin Lakes, New Jersey