Created Database Link But Does Not Function

Jun 19, 2012

in a 9.2.0 db I create a db link but does not function :

CREATE DATABASE LINK "ONEDB"
  CONNECT TO "user1" IDENTIFIED BY "****" USING 'ONEDB';

SQL> select * from dual@"ONEDB";
select * from dual@"ONEDB"
                   *
ERROR : ORA-12154: TNS:could not resolve service nameBut on the same server I can connect to 11g in sqlplus :

Z:>sqlplus /nolog
SQL*Plus: Release 11.2.0.3.0 Production on Mar. Juin 19 10:13:45 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect user1@ONEDB
password :
Connected.Of cours the other DB links toward 9.2.0 databases work well.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Created Function For Knowing Status Of Link Which Connect With Remote Database

May 22, 2013

I have created a function for knowing the status of link which connect me with remote database.function is as follows

---------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION get_status_db_link (p_db_link_in VARCHAR2)
RETURN NUMBER AS
rows NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);
[code]....

Now functin should returns 1 when link is up and it should return 0 when link down but sometime when link is down it continues showing executing it hang my pc.

View 1 Replies View Related

Security :: View / Obtain Password Once Database Link Has Been Created?

Dec 24, 2011

Is there a way to view/obtain the password once a database link has been created. Some sys dba table?

View 7 Replies View Related

Executing Stored Procedure / Function From Database Link?

Feb 24, 2011

I have 2 databases:

Database A
Database B

In Database A I have a series of config tables and stored procedures/functions In Database B I have a lot of tables.

I would like to execute my stored procedures and all associated functions in database A on my data in database B.

Now I've figured out that creating a database link enables me to do SQL selects on data in both A and B...but how do I run SP/Funcs ? I've read something about packages but not sure if I'm heading in the right direction.

Do I need to create a simple synonym ? Can I use the existing DB link ? or is there a special way of calling them, or...

I like the A/B database set up since I can keep battle tested code in one location and have it work across multiple dbs...saves me having to create everything in every database.

View 2 Replies View Related

Server Administration :: Find When Database Role Created And Who Has Created?

Feb 14, 2013

Is there a way to find when was a database role created and who has created?

View 5 Replies View Related

SQL & PL/SQL :: Exception Handling In A Created Function

Dec 8, 2010

Basically I've created a function, when I run it there is a user input. Mine is a customer number between 1-10.

I was wondering is there a way to add in error check so if I typed in an invalid number it would give me a message saying "Wrong customer_number" or something along the lines of that?

I was told I wasn't able to use "DBMS_OUTPUT.PUT_LINE" in the function I need to tamper with the function header?

Here is my header -

CREATE OR REPLACE FUNCTION hours (custid customer.cust_id%TYPE)

RETURN NUMBER IS

multiply NUMBER;
returnanswer NUMBER;

View 2 Replies View Related

SQL & PL/SQL :: Transpose Function In Link

Jul 5, 2010

my table is like this.

id,ordnum,User
1 ON1 Abc
2 ON2 Bcd
3 ON3 Xyz
4 ON4 Bcd
5 ON5 Abc
6 ON6 Pqr

I want to write select query to get result in below format:

User Ordnum
Abc ON1,ON5
Bcd ON2,ON4
Xyz ON3
Pqr ON6

I am using Oracle 8i, I tried transpose function in the link URL.....

View 24 Replies View Related

Application Express :: How To Add Delete Function To A Form With Report That Is Already Created

Apr 11, 2013

I am not familiar with form with report in APEX. I have a lot of forms with reports that was created before, but they have only create buttons. Now I need to add edit and delete buttons. I wish I do not have to recreate forms with reports. But when I tried to add delete function by comparing a form with delete button created by APEX, but it does not work. the delete button does not react at all.

setting is as follows:

button style: template based button
button template: Button
button type: normal
action: redirect to URL
execute validation: NO
URL target: javascript:apex.confirm(htmldb_delete_message,'DELETE');
database action: SQL delete action
condition type: value of item / column in expression1 is not null
expression1: P2172_ALIAS_ID

I also enable the delete in the process of :"Process Row of SOR_ALIAS"

If I set action of delete button as submit page, I can delete the row, but no standard popup warning,

View 2 Replies View Related

Server Utilities :: Import Table From Another Database By Using Database Link?

Mar 17, 2010

i would like to import a table from another database by using database link.

impdp muba/muba tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1

i created the public database link as system user

it gave error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:07:02
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Starting "MUBA"."SYS_IMPORT_TABLE_01": muba/******** tables=FUNCTION_NO directory=testdump NETWORK_LINK=DBLINK1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

[code]....

after that i try to import as system user like

impdp system/passwd tables=FUNCTION_NO schemas=muba directory=testdump NETWORK_LINK=DBLINK1

but error like

Import: Release 10.2.0.1.0 - Production on Wednesday, 17 March, 2010 11:27:43
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
UDI-00010: multiple job modes requested, schema and tables.

when i use IMP utility

imp system/passwd file=dump.dmp fromuser=userA touser=userB log=dmplog.log but when i use IMPDP what is the parameter equal to fromuser & touser

View 3 Replies View Related

Application Express :: Database Authentication Via Database Link?

Jun 26, 2013

Is it possible to use Database authentication in APEX through database link, and how?Also is it posible to read roles from users through database link?

View 3 Replies View Related

REFRESH GROUP Is Automatically Created When A Materialized View Is Created?

Aug 17, 2012

tell me if a REFRESH GROUP is automatically created when a materialized view is created?

View 3 Replies View Related

SQL & PL/SQL :: When Database Trigger Created

Jun 28, 2010

if i want to know when (date/time) that the Database Trigger created? I am using Oracle 8i, so is it possible to check when (date/time) ?

View 2 Replies View Related

PL/SQL :: Created Two Jobs In Sys Database

Mar 11, 2013

I have created two jobs in sys database as follows,

BEGIN
sys.dbms_scheduler.create_job(
job_name => 'JOB_FCDB_CHARGES',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN FCDBCHARGES; END;',
repeat_interval => 'FREQ=DAILY;BYHOUR=19',
job_class => 'DEFAULT_JOB_CLASS',
comments => 'Job to process Internet Banking Charges.',
auto_drop => FALSE,
enabled => FALSE);
[code]....

It has been successfully created.Even i get the entry in dba_objects table as follows,

select owner, object_name, object_type from dba_objects where owner = 'SYS' and object_name like '%JOB_FCDB_CHARGES%';

But, my problem is when i log in to PL/SQL developer i am not able to see anything under job.

View 9 Replies View Related

PL/SQL :: Database Link From 11g To 10g?

Jul 5, 2012

I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;

It Returns
Database link created.

select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

what changes I need to make to tnsnames and listener at both servers.

View 1 Replies View Related

SQL & PL/SQL :: Created Table With Date In Database

Feb 22, 2011

Me working on Oracle application Express,there are lots of tables created in it, how will i get to know who created which table and on which date.

View 7 Replies View Related

Database Link Not Active

May 15, 2013

I am newbie to oracle database.

I am using oracle 9i version and in EMCA satndalone application . I have created database links.

The database links have been created, and three databases are local.

When i am pressing the test button in emca . It gives notification database link not active.

What should i do?

View 3 Replies View Related

SQL & PL/SQL :: Using A Database Link In A Scheduled Job?

May 12, 2010

A procedure in a package uses a database link. The database link is defined without a username and a password. The user that uses the database link is supposed to be present in the opposite database as wel.

When the procedure is started in SQL*Plus or TOAD it runs perfect. But the procedure has to run in a scheduled job. And that doesn't work. The procedure fails because of: ORA-01017 invalid username/password. The user that is used to run the scheduled job is the right one.

View 5 Replies View Related

SQL & PL/SQL :: Database Link Creation

Jan 28, 2013

I have installed oracle database 11gr2 in laptop1 and installed oracle database 11gr2 in laptop2.Both the laptop's are connected to same internet connection through wifi. So my question is can i create a database link between these two databases using this wifi? give some steps in creation of a database link.

View 13 Replies View Related

DB LINK To External Database?

Jul 10, 2013

i need access to a view running on another database server. i configured my db link and tried to compile the view. but the following message occurs: ora-12154: TNS: Connection Identifier..

View 16 Replies View Related

10g Materialized View Using A Database Link

May 20, 2009

In Oracle 10gR2.If a materialized view uses a database link for the query in order to create a snapshot of data on a remote instance, does the name of the database link have to be an entry in the tnsnames.ora file?

The following link suggests not, but is not version specific: Materialized View - Oracle Wiki FAQ

However, the following 11g documentation suggests that the database link name must be the same as the global name of the target database.URL..
I can't any info specific to 10gR2.

We have three instances. Our application metadata is stored in a schema in B.METADATA. There is a shell schema (B.METADATASHLL) that provides access to the tables to remote applications.

On instance A, we have a shell schema (A.METADATASHLL) that creates materialized views as follows:
CREATE DATABASE LINK METADATA_PRIME
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';

Our materialized view is created using the query SELECT * FROM METADATA.APPLICATIONS@METADATA_PRIME WHERE Application = 'A';

The query works, but the materialized view does not.I'm being told that the database link has to be named B as follows

CREATE DATABASE LINK B
CONNECT TO metadatashll IDENTIFIED BY password
USING 'B';

Which means that I can only have one public link to instance B, and I'm in a pickle if I create a second application (which I have) using the same model).

View 6 Replies View Related

Link Server Database To Oracle DB?

Jan 9, 2012

I found a link to a topic concerning this issue (click here), but the question mentions that the poster was looking for a "band-aid" fix...if this method will work for a more long-term solution

View 1 Replies View Related

Forms :: Where To Create Database Link

Jun 18, 2013

Usually where should we create the database link? In the production or development? create public database link dblinkname connect to user_in_db2 identified by password_in_db2 using 'tnsentry name in db2'; In production or development?

View 2 Replies View Related

Networking And Gateways :: Database Link From 11g To 10g?

Jul 5, 2012

I am trying to create a database link from the 11g database to the 10g database using:

create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;

It Returns

Database link created.

select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

what changes I need to make to tnsnames and listener at both servers.

View 8 Replies View Related

SQL & PL/SQL :: Dynamic Database Link In Procedure?

Jun 7, 2011

Is it possible create dynamically database link within procedure.

It give me the following error

ORA-01031: insufficient privileges

But i can drop database link within procrdure.

Is there any way to do this?

View 4 Replies View Related

SQL & PL/SQL :: Getting Hostname / Systimestamp Over Database Link

Oct 26, 2013

I have a staging which connects to RAC clusters via dblink..I have a scenario to collect the hostname,systimestamp whenever i do a query from this rac clusters

So in general below would be cool

select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric would work perfect

But on dblink..

select UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink.is returning local timestamp and hostname which is not intended

So i even tried subquery..

select (select host_name,systimestamp from v$instance),t UTL_INADDR.GET_HOST_NAME,sysdate,x,y,z from gv$symmetric@dblink

this would work in certain case ...But it certain cases it would throw an error saying mutiple rows from asubquery .All ineed is to get hostname,systimestamp append to all sql i fetch from any this rac clusters through this dblinks.

View 8 Replies View Related

Server Administration :: Connect To Newly Created Database?

Jan 16, 2012

i create new database (Student) using command line, now i want to connect with newly created database (using sys as sysdba), but again it directly connected with old database (ORCL). So what should i do to connect with newly created database (Student).

View 3 Replies View Related

SQL & PL/SQL :: Created 2 Users Named User_a And User_b In Database

Mar 2, 2012

I have created 2 users named "user_a" and "user_b" in my database and i gave "connect","dba" and "resource" roles to both users,then i created a table,synonym in user_a and i observe that user_b can also access this table and synonym of user_a without "granting" to "user_b", how it is possible. is it because of any of above role?

View 12 Replies View Related

Security :: Fire Trigger If And Database Object Will Created?

Nov 7, 2013

How to write and fire trigger if any database object will created?

View 1 Replies View Related

SQL & PL/SQL :: Created Trigger On Database Level In System Schema

Jun 10, 2010

I have created trigger on database level in system schema. While i am creating new tables in system schema, trigger logged the entry but when i am creating table in scott schema it is not working for that.

CREATE OR REPLACE TRIGGER ddltrigger
AFTER DDL ON DATABASE
BEGIN
INSERT INTO aud_log
(user_name, ddl_date, ddl_type, object_type,
owner, object_name
)
VALUES (ora_login_user, SYSDATE, ora_sysevent, ora_dict_obj_type,
ora_dict_obj_owner, ora_dict_obj_name
);
END;

View 3 Replies View Related

Procedure To Link Oracle 9i Database Server?

Jun 17, 2009

the procedure to link oracle 9i database server withi 10i.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved