This discussion is archived
1 2 Previous Next 21 Replies Latest reply: Oct 11, 2012 4:29 AM by 966855 Go to original post RSS
  • 16. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    please see below output

    SQL> select name from v$database;

    NAME
    ---------
    HR91DUM ------> Local db

    SQL> select * from psdbowner@apexdb;

    DBNAME OWNERID
    -------- --------
    HR91DMO SYSADM ----------> remote db psdbowner table accessed in local db

    SQL> select * from ps_job@apexdb;
    select * from ps_job@apexdb
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist ----------> remote db ps_job table not accessed in local db
    ORA-02063: preceding line from APEXDB

    please tell me why psdbowner table is accessed and why ps_job table is not accessed?
  • 17. Re: Creating Database link
    866990 Explorer
    Currently Being Moderated
    select * from user_tables;
    will show you the tables your user PSEASYRUN owns

    the other table, sysadm.ps_job, is owned by SYSADM.

    In order to view contents of the ps_job table, rights will have to be granted to PSEASYRUN by SYSADM.

    logon as sysadm and execute the following statement:
    GRANT SELECT ON PS_JOB TO PSEASYRUN;
    after this, PSEASYRUN is allowed to select from the sysadm.ps_job table.

    This is basic oracle rights stuff, are you quite new to Oracle?

    .Robin
  • 18. Re: Creating Database link
    Prabodh Guru
    Currently Being Moderated
    Hi *963852*,
    please see below output
    SQL> select name from v$database;

    NAME
    HR91DUM ------> Local db

    SQL> select * from psdbowner@apexdb;

    DBNAME OWNERID
    --------
    HR91DMO SYSADM ----------> remote db psdbowner table accessed in local db

    SQL> select * from ps_job@apexdb;
    select * from ps_job@apexdb
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist ----------> remote db ps_job table not accessed in local db
    ORA-02063: preceding line from APEXDB

    please tell me why psdbowner table is accessed and why ps_job table is not accessed?>
    Action 1.
    Connect to HR91DMO in SQLPlus as PSEASYRUN and run the following. Note it is not using DBLINK
    SQL> SELECT * FROM PS_JOBS;
    Do you see the rows? Or do you get the same ORA-00942 ?
    Action 2.
    If you get ORA-00942 then connect to HR91DMO as SYSADM and run the following
    SQL>GRANT SELECT ON PS_JOB TO PSEASYRUN ;
    Now try Action 1 again.

    Cheers,
  • 19. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    yes, i am new to this.

    I logged on to remote db(HR91DMO) as sysadm and granted following

    SQL> GRANT SELECT ON PS_JOB TO PSEASYRUN;

    Grant succeeded.

    SQL> show user
    USER is "SYSADM"

    Now i logged on to remote db as PSEASYRUN user.still that table is not updated.

    SQL> select * from user_tables;

    no rows selected

    SQL> show user
    USER is "PSEASYRUN"

    SQL> select * from ps_jobs;
    select * from ps_jobs
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist


    Now i queried in local db using db link

    SQL> select * from ps_job@apexdb;
    select * from ps_job@apexdb
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-02063: preceding line from APEXDB

    what to do?

    Edited by: 963852 on Oct 11, 2012 3:23 AM
  • 20. Re: Creating Database link
    866990 Explorer
    Currently Being Moderated
    Because there is no public synonym for sysadm.ps_jobs, you've got to specify it with the table owner.

    so:
    SQL> select * from sysadm.ps_job;
    Same goes for selecting over the databaselink:
    SQL> select * from sysadm.ps_job@apexdb;
    a fix, logged on as sysadm will be:
    CREATE PUBLIC SYNONYM PS_JOB FOR SYSADM.PS_JOB;
    this will create a public synonym for sysadm's ps_jobs table.
    When the public synonym exists, you dont need to specify sysadm.ps_jobs in your query, ps_jobs will do now.
    You can see the public synonym as a 'link' to a table in another schema.

    .Robin
  • 21. Re: Creating Database link
    966855 Newbie
    Currently Being Moderated
    getting this error when using db link in local db


    SQL> select * from ps_jobs@apexdb;
    select * from ps_jobs@apexdb
    *
    ERROR at line 1:
    ORA-01775: looping chain of synonyms
    ORA-02063: preceding line from APEXDB

    when logged to hr91dmo as PSEASYRUN user

    SQL> select * from ps_jobs;
    select * from ps_jobs
    *
    ERROR at line 1:
    ORA-01775: looping chain of synonyms

    what to do?

    for all tables,access should be enabled.

    instead of one table,please tell me what to do if i want to access all tables in remote db through db link

    Edited by: 963852 on Oct 11, 2012 4:28 AM
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points