We are using oracle database 11g R2 on REL 5 and i have an SQL server database used by one of our application. In my Oracle database, i want to query one table in my SQL server database and to link it in a table in Oracle database to compare data.
Is there a way like database link or something else to do this need. Send a link or pots here step by step the way on how to accomplish my query.
I have a table in Oracle with a column userid and i have a userid column in Active Directory. based on this i want to query the Network ID and update in one of the Group in the Active Directory. how to get the results?
View 2 Replies View RelatedI have installed unixodbc 2.3.1, postgres odbc driver (psqlodbc-07.03) and dg4odbc 11.2..On querying : select sysdate from dual@dblink_postgresql, the following error occurs:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found {01000}
ORA-02063: preceding 2 lines from DBLINK_POSTGRES
Similar error appears in the trace file as well.
[unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a'
$ isql -v postgresql postgres postgres@2012
[01000][unixODBC][Driver Manager]Can't open lib '/oracle/psqlodbc/lib/libpsqlodbc.a' : file not found
[ISQL]ERROR: Could not SQLConnect
However the file is present in the location and has no permission related problems.
$ pwd
/oracle/psqlodbc/lib
$ ls -lrt
total 2952
-rwxr-xr-x 1 oracle oinstall 663 Mar 25 15:28 psqlodbc.la
-rw-r--r-- 1 oracle oinstall 731419 Mar 25 15:28 libpsqlodbc.a
-rw-r--r-- 1 oracle oinstall 12215 Mar 25 22:11 win_md5.o
-rw-r--r-- 1 oracle oinstall 18100 Mar 25 22:11 options.o
[code]....
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.
I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let's call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?
PTN NAME DATE STATUS
11014419 Joe Dickson 2012-06-25 14:55:58 Delivered
11014419 Mike Draia 2012-06-25 14:28:17 Loaded
11014419 Bob Geber 2012-06-25 13:14:31 Received
11014419 Bob Geber 2012-06-25 13:14:31 Picked Up
11014419 Bob Geber 2012-06-25 13:14:31 Printed
I have flattened customer dimension table and I would like to query it with other dimension table like address. I write a query, where I join address table twice to get permanent, secondary, and work addresses, but customer and address tables are huge that causing performance issue. Is any other ways to join flatten table with address dimensions than join it twice.
CREATE TABLE CUSTOMER
(
cust_sk NUMBER NOT NULL ,
cust_src_id VARCHAR2(20) NOT NULL ,
rec_eff_dt DATE NOT NULL ,
last_name VARCHAR2(75) NULL ,
first_name VARCHAR2(30) NULL
brth_dt DATE NULL ,
[code]......
I successfully created the following DBLink in my DEV environment:
CREATE PUBLIC DATABASE LINK PROD
CONNECT TO USER1 IDENTIFIED BY pwd98
USING 'PRD';
I have a table in both schemas. In PROD it has 0 recs, in DEV it has 134 recs.
select count(*) from hold@PROD.
The above query gives me 134 recs instead of 0!
Here is my TNSNAMES entry for PRD:
PRD=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1529)
)
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=PRD_USR)
))
I'm having truble by querying a column in Oracle 10g. This is my situation I wrote a standard Oracle Query to show only the domain name of user's registration. The column's alias is called MAILS so far so good, the main problem is when I tried to shortener the result to show only Gmail, Hotmail and Yahoo.
Also I had the same issue when I tried to use GROUP BY the field "MAILS", but when I use the order by clause with that field it run perfectly. I think a test case is not necesarry because there are common mails address.
SELECT
SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1)) "MAILS",
COUNT(SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))) "TOTAL"
FROM USER_REGISTRATION
WHERE TRUNC(FECHAALTA) BETWEEN TRUNC(TO_DATE('01/01/2012','DD/MM/YYYY')) AND TRUNC(TO_DATE('31/12/2012','DD/MM/YYYY'))
GROUP BY SUBSTR(SUBSTR(mail,INSTR(mail,'@',1)+1,50),1,
INSTR(SUBSTR(mail,INSTR(mail,'@',1)+2,50),'.',1))
ORDER BY MAILS DESC;
I have table with 129 million records.
If I just to select count(*) on the table its taking more than a minute in Sql Developer.
The table structure is as below, Primary key is a sequence and then 3 foriegn keys and one non-unique index on the date column.
<Table_Name>
column1 NOT NULL NUMBER ( Primary Key)
column2 NOT NULL NUMBER ( FK1)
[Code].....
The Item data for individual cycles is as below.
Item_tbl
ItemRundate StddateStatus
P103-Nov-1203-Nov-12A
P104-Nov-1204-Nov-12D
P2 04-Nov-1203-Nov-12A
The requirement is I have to get the details of all data of previous Active cycle(status A) when the Item became disabled(status = D) for Input date.
In above case,since for Item P1 and on cycle date 04-Nov-12,status is D,I have to consider the previous active cycle which is 03-Nov-12. Based on above std date,the data is queried from another table to get all the Items. Item P2 should not be considered in above case.
Below is the code which I have written which considers the rundate as Input parameter.
-- To get the Items disabled for Input date
with Itemdisabled as
(
select item,stddate maxcycledate
from Item_tbl
where rundate = stddate
[code]....
In above case,I'm querying the Item_tbl twice once for getting the disabled Items and once for getting the Previous cycle which is active.
Is there any way to query above only once and get the required results using Lag/Lead functions etc.
query to get the name and size of all the databases associated to grid..
I need to find this to do capacity planning in our environment.
I have a procedure which mainly run queries on a Table which has nearly 9.5 million recodes. This procedures takes nearly 15 min to complete execution on our main database. I exported and imported the schema to our backup database and the same procedure just took 3 seconds to complete.
I tried to analyze the table in our main database and tried to execute the procedure again but did not show any improvements. ANALYZE TABLE DN_ACTIONS COMPUTE STATISTICS;
I am not sure computing the statistics for all the tables in the schema will work. I also checked there is enough disk space where oracle data files are stored. I am also turning on the sql trace to see what sql statements in the procedure is taking longer time.
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
Can we use M.S Sql Server Database with Forms 6i with out oracle database install.
View 1 Replies View Relatedi am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
I will have to proceed with Oracle 9 database refresh from production server to integration server. 5 biggest schemas must be exported and imported. They constitute 97% space used in a database. This is very big database so I would like to be sure that everything will go smoothly. That is why i want to ask you some questions.
Have you got any clues for me before I start with exp/imp? From my side i will tell you that I will have to exp/imp schema by schema because there is small space both on production and integration disk for a dump. First thing I thought are dependencies between schemas that are exported and that which are not, and also between schemas that are exported/imported one by one.
This is procedure that I plan:
For every schema that is to be refreshed
{
1. Export schema with ROWS=N CONSTRAINTS=Y
2. EXPORT schema with ROWS=y CONSTRAINTS=N
3. Import schema from step one
4. Disable all the foreign key constraints using ALTER TABLE DISABLE CONSTRAINT.
5. Import schema with rows
}
ALTER TABLE ENABLE CONSTRAINT
With above procedure i think that I will avoid problems with dependencies between schemas exported/imported one by one. But my concern is if there are any dependencies between those schemas and schemas that are not exported. Is there an way to check it before refresh ?
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server.
2. I invoked "sqlplus" from another unix box to login to the database.
3. I found that the process id (ps -ef |grep sqlplus).
4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID,
SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM,
SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID
FROM
SYS.GV_$PROCESS, SYS.GV_$SESSION
WHERE
SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST'
and SYS.GV_$SESSION.MACHINE like '%hostname%'
I recently installed Oracle 10g on my windows Xp laptop. It has become considerably slow since then. I want to start the database server only when I need it, and not every time I start my laptop. I looked around in OEM and did found a way.
View 5 Replies View RelatedI am connected as System. It was the only user I set-up a password when installed the database on personal computer.
SQL> alter user sys identified by mypass007
2 /
User altered.
SQL> connect sys/mypass007
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
[code]...
SQL> conn sysoper/mypass007
ERROR:
ORA-01017: invalid username/password; logon denied
I have some requirement wherein I need to install Oracle 9i database server (9.2.0.7). I am not getting it on Oracle website. where can I get the download.
View 1 Replies View RelatedI have the following 3 columns in my VERSIONS table.
MAJOR
MINOR
MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
SELECT * FROM table WHERE id = $x
$x being a range of non-consecutive values like so:
1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?
How can we access a table from one database to another database on the same server.
Let us say One database is "A" and the Other database is "B" both databases are running on one server "ABCD".
My question is how can we access a table from database B to database A.
I have a CLOB column called XML_DATA that has (not-surprisingly) xml data in it that's housed inside a table called HMS_XML_TRANSFER. It has been giving me a headache because I'm unable at this point to use the xml field as a condition to get its TRANS_SEQUENCE number. The where clause doesn't work.
SELECT TRANS_SEQUENCE, XML_DATA
FROM HMS_XML_TRANSFER
WHERE EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]');
The only test that I have been able to get working is the one below.
SELECT TRANS_SEQUENCE, EXTRACTVALUE(XMLTYPE (XML_DATA), '/INTERFACES/INTERFACE/BODY/IFI0057/ACTIVITY_CODE')
FROM HMS_XML_TRANSFER
WHERE TRANS_SEQUENCE = '8191602';
It will give me the ACTIVITY_CODE element so I know I can pull data from the XML but I can't do the reverse in the first example which is what I need because I don't know the TRANS_SEQUENCE number, I just know the ACTIVITY_CODE.
I have over 202 error messages logged in Teradata SQL from my many and varied attempts to get this to work using every example I could find online.
As an example this has not worked either...
WHERE EXISTNODE(XML_DATA, '/INTERFACES/INTERFACE/BODY/IFI0057[ACTIVITY_CODE = "2201-020742"]') = 1;
So the question... How do I properly form my SQL statement so I can use the XML column's ACTIVITY_CODE element to get the TRANS_SEQUENCE column field? Oh and I'd like to see both columns in the result.
Below is the version of Oracle I'm using, the description of the Table HMS_XML_TRANSFER, and a sample of the XML that comes from XML_DATA. I can't seems to get tabs working.
===============
ORACLE VERSION
===============
SQL*Plus: Release 9.0.1.3.0 - Production on Thu Mar 17 08:18:15 2011
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
=========================
TABLE HMS_XML_TRANSFER
=========================
Name Null? Type
--------------- --------- --------------
TRANS_TYPE NOT NULL VARCHAR2(10)
DATE_IN NOT NULL DATE
DATE_PROCESSED DATE
STATUS VARCHAR2(8)
[code]....
I am inserting data into a global temporary table and then using 'parallel' hint to query from this temporary table. I remember reading that the queries on the temp table may not run in parallel as the parallel sessions may not be able to see the data in the temporary table
However the execution plan as well as px_session, v$sql indicate that the query on the temporary table in fact run in parallel mode
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 7d68g52g0mskz, child number 0
-------------------------------------
select /*+ gather_plan_statistics parallel(t,4) */ * from dbo_gtt t order by id,object_id
Plan hash value: 5815349
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------
| 1 | PX COORDINATOR | | 1 | | 99999 |00:00:01.46 | 3 | | | |
|
| 2 | PX SEND QC (ORDER) | :TQ10001 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 3 | SORT ORDER BY | | 0 | 1 | 0 |00:00:00.01 | 0 | 11M| 1311K| 424K (0)|
|
| 4 | PX RECEIVE | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 5 | PX SEND RANGE | :TQ10000 | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
| 6 | PX BLOCK ITERATOR | | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
|* 7 | TABLE ACCESS FULL| DBO_GTT | 0 | 1 | 0 |00:00:00.01 | 0 | | | |
|
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access(:Z>=:Z AND :Z<=:Z)
select px_servers_executions from v$sql where sql_text like 'select%dbo_gtt t%';
PX_SERVERS_EXECUTIONS
---------------------
0
4
select sid, qcsid, server#, degree from v$px_session where qcsid = 228;
SID QCSID SERVER# DEGREE
---------- ---------- ---------- ----------
247 228 1 4
196 228 2 4
224 228 3 4
234 228 4 4
226 228 1 4
252 228 2 4
212 228 3 4
229 228 4 4
228 228
9 rows selected.
unfortunately I do not have access to get trace (tkprof) report. What must have happened during the execution?
Our company database is Oracle based and we use SQL Developer to pull out needed data.Using a snippet borrowed from a co-worker, I have put together a query that, among other things, pulls a list value out of an xml clob field and displays it in the query results. My query as it stands right now is below, followed by an example snippet of the xml clob that I am pulling from. The reason for the "query within a query" is because the base query could return multiple entries and I only want the one with the most recent date.
select * from
(Select Wtr_Service_Tag, Wtr_Tran_Origin, Wtr_Send_Date, Wtr_Receive_Date,
to_char(substr(wtr_req_xml,instr(substr(wtr_req_xml,1,8000),'SID')+8,12)) Asset_Tag
from ws_transactions
Where Wtr_Service_Tag In ('20458749610')
And Wtr_Req_Xml Like ('%CSM%')
Order By Wtr_Receive_Date Desc)
where rownum = 1;
[code]....
This query is only able to pull the first value in the list.How can I edit this query to pull all of the list items when there are more than 1 (preferably concatenated into one column in the query results)? I have another field, in a separate table, that I can pull from to get the number of list items.
This one may be more complex. As currently written, the query pulls a fixed number of characters from the xml clob and either returns not enough data, or too much because the values I need to pull could be of varying lengths. I have no way to query what those lengths might be.
We have a table with huge data which is skewed on a 'status' column. The 'status' column has 6 distinct values with 1 particular value occupying 80-85% records.
In the batch process we query the data on the status and process the retrieved records. My senior is insisting on partitioning which I see not much feasible considering cost implications just for a part of functionality
See there are 6 status 'A','B','C','D','E','F'
with 'A' occupying 80% records
'B' to 'F' occupies 2% till 14% records in the table(approx)
1) Create a conditional index on status (using case) to have records with all statuses except 'A' Then create If-ELSE structure
IF input parameter is 'A'
select /*+ FULL Parallel(t) */ * from t where status='A';
ELSE
Select /*+ INDEX (t conditional_index) */ * from t where status in ('B','C');
END IF;
I want to create conditional index here for 2 reasons
1] since it will have values for status except 'A' this nullify the chance that this index will be picked up when status='A' will be queried
Thus making the performance worst (status ='A' is for 80% records) - The IF-ELSE is additional protection
2] Less impact on the DMLS as the index will not be on status='A' which contribute to large chunk of records
2)Populate a dummy table which would contain rowid and status. Since the business closes at 21:00 and batch process starts at 21:30
Between these times periods refresh the dummy table every day using merge (to catch business transactions during the day)
Now during the batch process retrieve records from the main table using the rowids in the dummy table depending on the input status value
3)Create index on status
Make sure hard coded status values are used in the database procedures
Gather stats with the histograms
And leave it to the Optimizer to choose the best possible path
We have license for maximum six cores CPU, and we had to choose a server for Oracle single instance database with one of these two processors:
Intel Xeon E5-2643
============
#core: 4, #Threads: 8, Clock Speed: 3.3GHz, Max Turbo: 3.5GHz, Cache: 10MB, System Bus: 8GT/s, DDR3-max 1600, Memory Bandwidth: 51.2GB/s
Intel Xeon E5-2640
============
#core: 6, #Threads: 12, Clock Speed: 2.5GHz, Max Turbo: 3GHz, Cache: 15MB, System Bus: 7.2GT/s, DDR3-max 1333, Memory Bandwidth: 42.6GB/s
For database server what is more important, number of cores, cache, or clock speed, system bus, or DDR speed and memory bandwidth?
I am trying to fetch the data from system table "FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$" in Oracle Express edition 10.2.0.3 database, using the OCI library on Windows with C++
This table has a TIMESTAMP column of date type.
I've the following query as below:
select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') order by TIME_STAMP asc;
For this, first I am preparing the query as below:
select TIME_STAMP from FLOWS_020100.WWV_FLOW_ACTIVITY_LOG1$ where TIME_STAMP > :PKVAL order by TIME_STAMP asc;
Before calling OCIExecute(), I am setting the PKVAL buffer to following value:
"to_date('31/OCT/12 23:59:59', 'DD-MON-YY HH24:MI:SS') "
& calling the OCIBindByName function with data type as SQLT_DATE.
But when the OCIExecute is called, my program crashes with access violation
what is the correct way to pass the date value in query to OCI? How do we bind the date values?