Oracle – Query Another Schema on Different SID

oracleschema

I have 3 db's that should be in sync with each other, but there are some differences that I am trying to rectify. We try to stay out of the main schema and do all of our work on a separate user schema, the data is then streamed around if updates are made.

I need to be able to query data on a different schema on a different sid to get the tables in sync, but I get an ORA-01017: invalid username/password; logon denied when I do so.

For example when I am logged in as schema2 on sid1:

/* works */
select * from schema1.my_table@sid1

/* throws an error */
select * from schema1.my_table@sid2

Likewise when I am logged in as schema2 on sid2

/* works */
select * from schema1.my_table@sid2

/* throws an error */
select * from schema1.my_table@sid1

Is this type of query possible? There is already a database link created between the db's. I use connect through when I log on so I do not know the schema2 password and I am afraid to change it. Is it that they are different on all three nodes?

I hope this is making sense, please ask for any clarifications.

Best Answer

Your database links are configured with the wrong passwords.

Related Solutions

Write to different schema with own tablespace from another account

Quota calculation is based on table owner, not on the user that inserts (or deletes or updates) data.

For example if u1 creates a table, u2 inserts 300MB of data, then u3 deletes 100MB of that same data and u4 updates 100MB of that data, only the quota of u1 is checked, for other users, just permissions at the table level (grant insert/delete etc) are checked.

In the example you give, there is no contradiction, u2 inserts into the table owned by u1, so only the quota of u1 is checked.

Note there is no need to specify QUOTA 0M as the default is not to allow quota on tablespaces.

http://docs.oracle.com/cd/B10500_01/server.920/a96521/users.htm#15298

You can assign each user a tablespace quota for any tablespace (except a temporary tablespace). Assigning a quota does two things:

Users with privileges to create certain types of objects can create those objects in the specified tablespace.

Oracle limits the amount of space that can be allocated for storage of a user's objects within the specified tablespace to the amount of the quota.

By default, a user has no quota on any tablespace in the database. If the user has the privilege to create a schema object, you must assign a quota to allow the user to create objects. Minimally, assign users a quota for the default tablespace, and additional quotas for other tablespaces in which they can create objects.

Changing the default schema of a user

Sounds like you don't have SELECT access on the sequence. Ask the DBA to run:

GRANT SELECT ON SCHEMA_OWNER.MY_SEQ TO APP_USER;

Can you edit your post with the full error message you're receiving?

Regarding your comment. I suspect you've got SELECT ANY TABLE etc privs. These don't apply to sequences, as demonstrated below:

PHIL@PHILL11G2 > create user test1 identified by test1;

User created.

PHIL@PHILL11G2 > grant connect,select any table to test1;

Grant succeeded.

PHIL@PHILL11G2 > create sequence testseq;

Sequence created.

PHIL@PHILL11G2 > conn test1/test1;
Connected.
Loading glogin.sql

Session altered.

TEST1@PHILL11G2 > select count(*) from phil.bigtable;

  COUNT(*)
----------
   2300640

TEST1@PHILL11G2 > 

TEST1@PHILL11G2 > select phil.testseq.nextval from dual;
select phil.testseq.nextval from dual
            *
ERROR at line 1:
ORA-01031: insufficient privileges

Related Question