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.
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.
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.
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;
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.
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.
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).
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Sharma 01-Jul-1987 Avinash Pandey 03-Feb-1988 Ankit Gupta 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Sikha Batham 17-Apr-1988
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Manu Sharma 01-Jul-1987 Ankit Gupta 02-Feb-1988 Sikha Batham 17-Apr-1988
My result is based on the approach- if matched first_name, last_name, dob --> 1st prefrence in order if matched first_name, dob --> 2nd prefrence in order if matched first_name, last_name --> 3rd prefrence in order if matched last_name, dob --> 4th prefrence in order if matched first_name --> 5th prefrence in order if matched last_name --> 6th prefrence in order if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where first_name='Manu' and last_name='Batham' and dob=to_date('02/02/1988','dd/mm/yyyy') union Select a,b,c,2 from test Where [code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
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?
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:
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.
I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.
create table sample1 ( i number , j date, k number)
insert into sample1 values (1,'23-Apr-2010',11) insert into sample1 values (2,'22-Apr-2010',12) insert into sample1 values (3,'21-Apr-2010',13) insert into sample1 values (4,'19-Apr-2010',14) insert into sample1 values (5,'18-Apr-2010',15) insert into sample1 values (6,'17-Apr-2010',16)
I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.
I did it through "UNIX" , but it's not efficient.
The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:
select i, j , sum(k) from sample1 where j in (select to_date(sysdate - rownum) from dual connect by rownum < = 7) group by i, j
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.
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.
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.
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.
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.
WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') > TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150 NULL NULL NULL NULL 30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18', p_time in varchar2) return number $if dbms_db_version.ver_le_10 $then deterministic $elsif dbms_db_version.ver_le_11 $then result_cache $end as begin return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss') - to_date('01-01-90','dd-mm-yy')) * (86400/2400)); end ssndate; /
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS as select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT", C_IP, CS_USER_AGENT, (CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as select SESSION_DT, C_IP, CS_USER_AGENT, listagg(WEB_LINK, ' ') within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS" from WEBLOG_VIEWS group by C_IP, CS_USER_AGENT, SESSION_DT order by SESSION_DT [code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.