ORA-02020 / Too Many Database Links In Use But OK With Where Clause
May 16, 2013
I have a view called V_MEDGEN_LISTRADIO but I can't run SELECT * FROM V_MEDGEN_LISTRADIO
ORA-02020: too many database links in useThe cause is that we call in the view 5 tables over dblink and the open_links parameter is set to 4.
What is strange is that when I run this select by adding a where clause then it works!
SELECT * FROM V_MEDGEN_LISTRADIO WHERE SPRR = 3645;
(use of index so all tables are not called ?)
View 4 Replies
ADVERTISEMENT
Jun 10, 2013
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....
View 1 Replies
View Related
Jan 25, 2012
I'm currently assessing the design/performance of a Distributed System in which hundreds of Field reps have local Oracle DBs (10.2.0.4) on laptops & have to update a remote database (11.2.0.1) via a PUBLIC database link. Field data (millions of records) collected daily is synched from the local to the remote DB & vise versa through this database link. I have 2 concerns here:
1. Is the database link the best option for such a configuration? (recently field reps have been complaining about the slowness in synchronizing data between local & remote DBs). If not, what other options are available for such processing?
2. I've read a lot about security concerns with using PUBLIC database links, but haven't seen any documents to proof they're a majority security issue. why PUBLIC database links are considered not to be very secure?
View 39 Replies
View Related
Nov 14, 2011
I am using SQL Developer.I am self-teaching myself PL/SQL. What I am trying accomplish is to run a select query across many database links at runtime and to insert that query onto a local table. So far, I have only gotten as far as querying the database link names. I am stuck at where my variable calls the database link name. It does not recognize the database link name, and I can't quite grasp the reason why. Below is the first part of my script which does retrieves the column values no problem:
001 SET SERVEROUTPUT ON
002 DECLARE
003 db_link_varVARCHAR2(30);
004 source_cursorINTEGER;
005 destination_cursorINTEGER;
006 src_csrINTEGER;
007 dst_csrINTEGER;
008 rundate_varDATE;
009 instance_name_var VARCHAR2(30);
[code]....
If I break the script down to the bare select query, it will query absolutely fine.If I run the script in its entirety, it will not pick up the variable dbl as a dynamic database link. 02019. 00000 - "connection description for remote database not found"
If I comment out line 031 and prevent the looping of querying of database links (which will only fetch the first value of db_link from dba_db_links), the script will complete and I will have a row inserted into my local table.
I am suspecting it's the looping that is incorrect but I understand it is not going to do anything beyond line 059.
View 8 Replies
View Related
Jul 21, 2011
how to find out what are all the objects referring to particular database links in database?
View 6 Replies
View Related
Jul 2, 2011
There is a user account called 'BALA' in my database.I want to find out the database links that uses this 'BALA' account.
View 2 Replies
View Related
Jul 23, 2013
is there any way to refer a table which is in different schema other than using DB links.
View 5 Replies
View Related
Oct 14, 2013
A website requires to display consolidated data from databases located in different geographical regions (India, London and New York). The application server for the website is hosted only in one location India. What are the techniques that can be used for faster retrieval of data from all 3 databases?
Note: There is no need of real time data retrieval from different regions; however the user should able to view the updated data at predefined intervals.
View 9 Replies
View Related
Jun 15, 2013
I know how to use database links in various forms, but I've been trying to think through how the authentication works for a connected user link in 11g. If I create the link like this,
create public database link using 'orcl';
then any user can use the link, provided they have an identical username/password in the two databases. With pre-11g passwords, it was understandable: the password was salted with the username, so the hash of the password would be the same in both databases, and I assumed that the logon through the link used some sort of IDENTIFIED BY VALUES mechanism. But in 11g, the salt will different in the two databases. So the hash will be different. And of course Oracle never stores the actual password. So I don't see how the authentication works.
View 1 Replies
View Related
Dec 19, 2011
I would like to know if we can insert 300 million records into an oracle table using a database link. The target table is inproduction and the source table is in development on different servers.The target table will be empty and have its indexes disabled before the insert. if this can be accomplished in less than 1 hour.
View 26 Replies
View Related
Jun 16, 2013
I know how to use database links in various forms, but I've been trying to think through how the authentication works for a connected user link in 11g. If I create the link like this,create public database link using 'orcl';then any user can use the link, provided they have an identical username/password in the two databases. With pre-11g passwords, it was understandable: the password was salted with the username, so the hash of the password would be the same in both databases, and I assumed that the logon through the link used some sort of IDENTIFIED BY VALUES mechanism. But in 11g, the salt will different in the two databases. So the hash will be different. And of course Oracle never stores the actual password. So I don't see how the authentication works.
View 4 Replies
View Related
Jan 2, 2013
how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?
View 4 Replies
View Related
Jan 10, 2013
1. How can we find out what all are the SQL Queries/Procedures/Functions/<Oracle DB Objects> are running long time and affecting the Performance in each schema. If they affecting how to resolve it?
Here Need to Display the Queries and the method to increase the performance of the query.
2. How can we find out what all are the DB links are Out Facing and In facing? ( In Each Schema)
View 8 Replies
View Related
Jul 17, 2013
i want to use more than four db links in my stored procedure to retrieve data from different databases. but it says ORA-02020: too many database links are opened. i also wrote execute immediate to close some links but its not working.
View 16 Replies
View Related
May 24, 2010
We have two databases A and B, both are 10g Enterprise edition and we have to replicate only one table from A to B. A is the source and B needs a read only copy. The source of A is modified by User through Oracle Forms.
A latency of 1day can be accepted though the lesser the better. The problem is that this company does not approve of DB links in any form or any tool which in the end has to create a DB link.
View 2 Replies
View Related
Jul 17, 2013
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER );
ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId);
INSERT INTO UserLink VALUES (1, 1);
INSERT INTO UserLink VALUES (1, 2);
INSERT INTO UserLink VALUES (3, 1);
INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink
WHERE LinkId IN (1,2)
The correct query should output:
USERID
------
1
And it should work for any size > 0 list of values.
View 6 Replies
View Related
Apr 1, 2013
Basically, we have 2 procedures in 2 different databases. One of them makes a call to the other over a database link passing an associative array as a parameters.
This has woked fine for many years 9.2.0.8.0 but we are in the process of migrating to 11.2.0.2.0 and have started getting compilation errors.
1 > Create TYPE in Database_1
=====================
CREATE OR REPLACE PACKAGE DB1_Pkg
IS
type x_tab is table of number index by binary_integer;
v_tab x_tab;
END DB1_Pkg;
[code]....
View 3 Replies
View Related
Sep 7, 2012
How can I determine what views have a dblink hard coded in them? I am talking about a large number of views so bringing up each view's DDL in a GUI data dictionary tool to look is not an option. I tried running the query below unfortunately "text" is a LONG and doesn't allow the use of the "like" statement.
select * from dba_views where text like '%@%' ;
View 4 Replies
View Related
Nov 29, 2010
the thread title was a bit confusing, couldn't come up with anything short to describe the question. What I am looking for is a query which will put records into groups based on matching values in one of two columns. So if two records have a matching value in column 1 or column 2 they are in the same group. See the example bellow and expected output for a "better" explanation:
--setup
CREATE TABLE foo
(foo_id NUMBER NOT NULL PRIMARY KEY,
record_number NUMBER,
record_value VARCHAR2(1));
[Code]...
--expected output
group# foo_id record_number record_value
1 1 1 A
1 2 1 B
1 3 2 B
1 4 2 C
2 5 3 D
3 6 4 E
3 7 5 E
My initial thought is that is feels a little bit like the sequential seat problem but not quite close enough. I know it could be done iteratively with PL/SQL but I am thinking there must be a way to do it in SQL I am not seeing yet.
View 6 Replies
View Related
Mar 28, 2012
I've begun experiencing a very odd issue with one instance and all of their their db links. When I'm connected via telnet to the unix server, and I connect:
connect myuser/password via sql plus, I connect fine, and the link works.
but when I connect this way:
connect myuser/password@db.tnsname.entry.com using the TNS name specified, I again connect fine but the link calls fails with a TNS cannot resolve service name issue.
This is an existing instance with existing db links, but I repeat the issue on this machine alone with new links. the fully qualified name is the same name entry I'm using to create the link, both when it succeeds and when it fails. This is an 11g instance and the TNSnames file is in the ASM home.Also, i f I connect
connect sys as sysdba I get success as well. This behavior happens with both private and public database links. I'm not aware of any setting changes within this instance, and I've modified (after backing up) the TNSNames file with values from another that works perfectly well. Connecting via remote sqlplus or other JDBC connections gives the same error. Success connecting locally but failure connecting to DB Links when I'm remote. Connecting to the instance shows no issue.
Another instance on the network? An IP config issue in DNS? We have 100's of servers so it's needle in a haystack time. Can I run some sort of trace to see what it's calling to when it works and when it doesn't? (It selects another db's single table and I've confirmed the target is correct and the data is correct).
View 4 Replies
View Related
Oct 1, 2012
Is it possible to use the Oracle HAIP feature to bond multiple network links between Active and Standby database ?
Does this require a seperate license ?
Basically, I was looking for a solution for bonding the muliple WAN links that we have between the active and Standby database.
View 0 Replies
View Related
Mar 25, 2013
Application which I`m creating would work only inside domain. In the report I would like to have a link to files that stored outside from oracle server ( so apex cannot see the file but knows the absolute path to the file ).
And when I click in the report on the link to the file I would like to have this file opened in the application controlled from my OS ( naturally if I have rights to the path specified given from domain level).
I have been trying with column link ( a href, file:// ) and none of them works. I want to avoid APEX from copying files to local path or opening using java or creating directory.
View 2 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
May 21, 2013
In the login page, I want to add a text "Register" that when clicked will send the user to another page to register.
With a button, its simple because its inbuilt there,but I do not want a button,just this: Register
I want also to have the text in color not black.
View 6 Replies
View Related
Nov 9, 2011
what is the use of 1=1 in the where clause?
View 10 Replies
View Related
Jul 30, 2010
I have a exp .dmp file of a database that has over 1000 tables. I would like to import all tables that don't begin with 'C'. Is this possible with IMP (without listing each table I want)?
View 1 Replies
View Related
Jul 31, 2013
>select level ,empno,ename,mgr
from emp
connect by prior empno=mgr
start with mgr is null;
Output:-
>
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
4 7876 ADAMS 7788
3 7902 FORD 7566
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
2 7782 CLARK 7839
3 7934 MILLER 7782
Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.
View 4 Replies
View Related
Aug 3, 2013
I have a scenario where in report table I need to get
IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.
I tried using decode in WHERE CLAUSE , but not successful.
Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.
Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.
Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.
Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...
View 14 Replies
View Related
Mar 31, 2011
This time, I am going to provide the DML statements.
I have a simple table with 3 fields in it.I want to group by ACCT_NUMBER and sum of BALANCE field. But I want to get the description of the first row in a group.
the statements below. Here there are two groups of records 2001 and 2002. My sql(which I am working on) should return the following :
2001 EMPL TRAINING-MIS 20
2002 OTHER PROF SERV-HR 40
The following query will group by ACCT_NUMBER and sum of the BALANCE field. But how can I get the DESCRIPTION?
SELECT ACCT_NUMBER, SUM(BALANCE)
FROM TEST
GROUP BY ACCT_NUMBER
CREATE TABLE "TEST"
("ACCT_NUMBER" VARCHAR2(20 BYTE),
"DESCRIPTION" VARCHAR2(20 BYTE),
[code]...
View 5 Replies
View Related
Jul 24, 2010
I has a table with team names like
Table name:- Teams
Structure:- name
Ind
Aus
Pak
I want the o/p as:- Fixtures
Ind Vs Aus
Ind Vs Pak
Aus Vs Pak
.......
I tried it but failed in what condition to use in the where clause.I did up to this:-
select name||' Vs ' || name from teams where .......;
how to get the result.
View 2 Replies
View Related
