Re: remote table? how do you get info?

In article <c2ec2c5b.0110271454.7513ed66_at_posting.google.com>, throne7_at_my-deja.com says...
>
>I am trying to select a table from perlscripts and it keep crapping
>out. So I use SQLPLUS to select on the table and this is what I got.
>Someone in my group told me that this table is probably a remote table
>that is somehow logically linked into our database. I am not an
>oracle guru, so need some help. This is what I am seeing.
>
>--exerpt--
>SQL> select * from somename_view;
>select * from somename_view
> *
>ERROR at line 1:
>ORA-12519: TNS:no appropriate service handler found
>
>SQL> desc somename_view;
>Name Null? Type
>PHUB NOT NULL VARCHAR2(8)
>IP_ADDRESS NOT NULL VARCHAR2(15)
>SHUB NOT NULL VARCHAR2(10)
>--exerpt end--
>
>What can I do to restore this table so that I can select data from it?
> It was working before, the guy who worked with it i never met and he
>is long gone.
>Anyway, I can select to find out more info about this table? like
>which remote database is it refering to etc. Basically want to get
>back this table and its data. Thanks.

Your view (somename_view) is accessing a remote table via a database link.

To find out what the view is actually doing:

select text from all_views where view_name = 'SOMENAME_VIEW';

Now, for each table you see in the from list -- try to "select * from table_name where rownum = 1" to see if you determine what table is in error.

Once you find it -- it should have the database link in it (eg: table_name_at_something). Now you can

select * from dba_db_links where db_link = 'SOMETHING';

look at the host column -- thats the tnsnames.ora entry that is "broken". Either someone reconfigured the listener on that remote site, someone changed the tnsnames.ora on the local site, or someone renamed the service on the remote database. You need to find out what changed and either adjust your entries or put it back the way it was.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp