Home » SQL & PL/SQL » SQL & PL/SQL » Views underlying table on separate machine
Views underlying table on separate machine [message #364322] Mon, 08 December 2008 00:32 Go to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
I have installed Oracle 10G on two machines

machine1:schema con20
CREATE TABLE con20.FT_PRODUCT_NM (PRODUCT_ID VARCHAR2(7), PRODUCT_NAME VARCHAR2(312))

CREATE OR REPALCE VIEW con20.V_FT_PRODUCT_NM
SELECT PRODUCT_ID, PRODUCT_NAME
FROM con20.FT_PRODUCT_NM;


Now from machine 2, I need to create a view that will access the view in machine 1 (con20.V_FT_PRODUCT_NM)
Please tell me how can I create the link in Oracle from one system to another.
Thank you

Re: Views underlying table on separate machine [message #364331 is a reply to message #364322] Mon, 08 December 2008 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CREATE DATABASE LINK

Regards
Michel
Re: Views underlying table on separate machine [message #364343 is a reply to message #364331] Mon, 08 December 2008 01:10 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
Schema on the machine 2 is called ril. On machine 2, I logged in from system and did

Grant create public database link to ril;

Now I logged in to ril and executed
"CREATE PUBLIC DATABASE LINK product1 USING 'CONTENTM.con20';"

where CONTENTM is the machine name (machine 1) and content20 is the schema name.
Now when I did
select * from con20.V_FT_PRODUCT_NM
it gave me an error
ORA-00942: table or view does not exist
Please help


Re: Views underlying table on separate machine [message #364345 is a reply to message #364343] Mon, 08 December 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ Why public database link and not private one?
2/ The user to which you connect to the target database must have the necessary privileges to access the remote object.
3/ You statement make no reference to the database link, so it queries the local database.

Please carefully read the whole page I provided.

Regards
Michel
Re: Views underlying table on separate machine [message #364364 is a reply to message #364345] Mon, 08 December 2008 03:15 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
Now I am not trying across separate machine. In the same machine I have created 2 schema. One called 'seag' and other called 'ril'.

In 'seag' schema, I did the following
CREATE OR REPLACE VIEW V_FB_PRODUCT_NM AS SELECT PLAN_PRODUCT_ID , PLAN_PRODUCT_NAME FROM FB_PRODUCT_NM;

Now in 'ril' schema, I did the following

CREATE DATABASE LINK product_lnk USING 'seag';

and then

select * from V_FB_PRODUCT_NM@product_lnk

but it gives me an error
ORA-12154: TNS:could not resolve the connect identifier specified

Please help
Re: Views underlying table on separate machine [message #364365 is a reply to message #364364] Mon, 08 December 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you clear on what are database and schema on Oracle?
It seems not.
Please explain what you understand about these terms and then we can start.

Regards
Michel
Re: Views underlying table on separate machine [message #364367 is a reply to message #364364] Mon, 08 December 2008 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Given the 2 questions you posted today, I recommend you to read Database Concepts

Regards
Michel
Re: Views underlying table on separate machine [message #364372 is a reply to message #364365] Mon, 08 December 2008 03:36 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
Thanks for replying.

In SQL Server, we create a Database and then tables and views inside it. In Oracle I guess we create a Schema. My understanding is that Schema in Oracle equivalent to Database in SQL Server? Not sure what a database refers to in Oracle.
Re: Views underlying table on separate machine [message #364376 is a reply to message #364372] Mon, 08 December 2008 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database is about the same as Server in MS SQL Server.

So read Database Concepts at least the first chapter, without the basic notions you can't do anything.

Regards
Michel
Re: Views underlying table on separate machine [message #364378 is a reply to message #364376] Mon, 08 December 2008 03:54 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
Thanks, I'll go through it and get back.
Re: Views underlying table on separate machine [message #364382 is a reply to message #364322] Mon, 08 December 2008 03:59 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
>>>In the same machine I have created 2 schema. One called 'seag' and other called 'ril'.

In this case, I think I won't need a database link, since I need to access different schema over the same database.


Database link will be required when I need to access a schema on a different database (on may be a different machine). In this case I'll need a mechanism to specify <Database name>/<schema name>/<view name>
Re: Views underlying table on separate machine [message #364384 is a reply to message #364382] Mon, 08 December 2008 04:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In this case, I think I won't need a database link, since I need to access different schema over the same database.

This is correct you only need to grant appropriate privileges.

Quote:
Database link will be required when I need to access a schema on a different database

Correct.

Quote:
In this case I'll need a mechanism to specify <Database name>/<schema name>/<view name>

Inside the database just <schema>.<object>
Outside the database: <schema>.<object>@<dblink>

Regards
Michel
Re: Views underlying table on separate machine [message #364391 is a reply to message #364384] Mon, 08 December 2008 04:40 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
Thanks a lot for this valuable information.

Database instance : orcl
Machine name : contentm (not sure, if this needs to be used)
Service name (as running in windows services): OracleServiceORCL

so I tried the following

CREATE DATABASE LINK product_lnk2 USING 'orcl'
select * from cont20.V_FB_PRODUCT_NM@product_lnk2

where cont20 is the schema on orcl
But it gives me this error
ORA-12154: TNS:could not resolve the connect identifier specified


Please guide me further
Re: Views underlying table on separate machine [message #364398 is a reply to message #364391] Mon, 08 December 2008 05:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Once again, you need some basic knowledge to use Oracle.
Service names (in Oracle meaning of the word) are recorded in tnsnames.ora. A process named Oracle listener allows you to remotely access to database. You have to configure it with listener.ora file.

For information, there is an Error Messages book:
ORA-12154: TNS:could not resolve the connect identifier specified
 *Cause:  A connection to a database or other service was requested using
 a connect identifier, and the connect identifier specified could not
 be resolved into a connect descriptor using one of the naming methods
 configured. For example, if the type of connect identifier used was a
 net service name then the net service name could not be found in a
 naming method repository, or the repository could not be
 located or reached.
 *Action:
   - If you are using local naming (TNSNAMES.ORA file):
      - Make sure that "TNSNAMES" is listed as one of the values of the
        NAMES.DIRECTORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA)
      - Verify that a TNSNAMES.ORA file exists and is in the proper
        directory and is accessible.
      - Check that the net service name used as the connect identifier
        exists in the TNSNAMES.ORA file.
      - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA
        file.  Look for unmatched parentheses or stray characters. Errors
        in a TNSNAMES.ORA file may make it unusable.
   - If you are using directory naming:
      - Verify that "LDAP" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Verify that the LDAP directory server is up and that it is
        accessible.
      - Verify that the net service name or database name used as the
        connect identifier is configured in the directory.
      - Verify that the default context being used is correct by
        specifying a fully qualified net service name or a full LDAP DN
        as the connect identifier
   - If you are using easy connect naming:
      - Verify that "EZCONNECT" is listed as one of the values of the
        NAMES.DIRETORY_PATH parameter in the Oracle Net profile
        (SQLNET.ORA).
      - Make sure the host, port and service name specified
        are correct.
      - Try enclosing the connect identifier in quote marks.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.


Regards
Michel

[Updated on: Mon, 08 December 2008 05:08]

Report message to a moderator

Re: Views underlying table on separate machine [message #364520 is a reply to message #364398] Tue, 09 December 2008 00:08 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
I added the following entry to tnsnames.ora

rem_orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = contentm )(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
and then did

CREATE DATABASE LINK product_lnk2 CONNECT TO system IDENTIFIED BY hello123 USING 'rem_orcl';

Now when I do
select * from cont20.V_FB_PRODUCT_NM@product_lnk2;
it returns 0 rows.
There are rows in the view (on the other database) that I am trying to access. Is there any other privilege also required?
Thank You
Re: Views underlying table on separate machine [message #364535 is a reply to message #364520] Tue, 09 December 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any other privilege also required?

No.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Views underlying table on separate machine [message #364536 is a reply to message #364520] Tue, 09 December 2008 00:36 Go to previous messageGo to next message
anshulk
Messages: 12
Registered: December 2008
Junior Member
Sorry, it was my mistake. It's working perfectly fine now.
Just one more thing. Is SID the same as Service name. In the above scenario can we say that SID is orcl
Re: Views underlying table on separate machine [message #364541 is a reply to message #364536] Tue, 09 December 2008 00:49 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not necessary.

Regards
Michel
Previous Topic: Need Hlp on Sql
Next Topic: Trigger error
Goto Forum:
  


Current Time: Wed Dec 07 18:45:58 CST 2016

Total time taken to generate the page: 0.12520 seconds