When executing a SELECT with JOIN in oracle error as I am having the title. If I change the JOIN to where the procedure works normally. When installing my application, it works normally for a day or two, then it only shows the end of communication error. What I do is select the following:
We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.
I need to write a select statement that shows what customers are taking what drugs and how many mgs they take
I have a table that contains a CLOB column with pseudo-XML in it. I want to keep this data in an XMLType column so that I can leverage some of Oracle's built-in XML features to parse it more easily.
The source table is defined as: CREATE TABLE "TSS_SRM_CBEBRE_LOGS_V" ( "INCIDENT_ID" NUMBER, "EVENT_TYPE" VARCHAR2(100 BYTE) NOT NULL ENABLE, "EVENT_KEY" VARCHAR2(100 BYTE), "CREATION_DATE" TIMESTAMP (6) NOT NULL ENABLE, "CREATED_BY" VARCHAR2(100 BYTE) NOT NULL ENABLE, "LOG_MSG" CLOB);
The target (for testing this problem) table is defined as: CREATE TABLE "TESTME" ( "LOG_MSG" "XMLTYPE") My query is: insert /*+ APPEND */ into testme ("LOG_MSG")select XMLTYPE.createXML("LOG_MSG") as LOG_MSG from "TSS_SRM_CBEBRE_LOGS_V" b; In SQL*Developer, my error is: Error report:SQL Error: No more data to read from socket In SQL*PLUS and Toad, my error is: ORA-03113: end-of-file on communication channelProcess ID: 13903Session ID: 414 Serial number: 32739
We are running Oracle 11.1.0.6 on CentOS Linux at our local network. Two dozen users work from client's network to access this database. There are firewall and switches between the local network and client network. Everything run smoothly for last two years until about 10 days ago, something changed. The client on the client's network can still connect to this database, but they can not run large queries. Their client connection hangs and time out. I can see TNS-12535: TNS:operation timed out and TNS-00505: Operation timed out in the alertlog file. They even had problem to load stored procedures and views onto TOAD from your PC. All the user on the local network can work on this database normally.
We consulted network team and firewall team, none of them said they make any changes recently and have no firewall rule or others to block the traffic.
I have a strong feel that this is a network related problem. But don't know how to provide solid evidence to make network people/firewall people to buy it.
You can connect to the database but can't run query on large table/view?
I have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:
select * from myschema.view_1 acct Left JOIN myschema.view_2 freq
Having production system: 11.2.0.1 on Windows Server x64 Test system: 9.2.0.1 on Windows XP
Problem preface: to get all unique CASEID which should be checked up by biometric system.What i should check - all CASEs for different PERSONs having same PHONEs at least among one phone type (1..4).Real table contains little bit more than 10 million records.I made test scripts.
Below the DDL for test table creation: ------------------------------------------ -- Create CASEINFO test table ------------------------------------------ DROP TABLE CASEINFO; CREATE TABLE CASEINFO
[code]...
Below i've put SQL/DLL to make test data.number of records inserted 2 millions. PERSON_COUNT := #/8; ------------------------------------------ -- fill CASEINFO with sample data ------------------------------------------ DECLARE I INTEGER;
[code]...
Below SQL select to check the data in created table. ------------------------------------------ -- Check test data counters ------------------------------------------ SELECT 'TOTAL',count(*) from CASEINFO UNION ALL SELECT 'LEGAL',count(*) from CASEINFO where
[code]...
The PROBLEM is that i am experiencing HUGE perfomance problems on both test and production systems with that query:
select distinct b.caseid from CASEINFO a, CASEINFO b where (a.person<>b.person) and (a.sex=b.sex) and ( (a.phone1=b.phone1) or (a.phone1=b.phone2) or (a.phone1=b.phone3) or
[code]...
This query takes almost 90 minutes to execute.And i do not know how to avoid this.Full SQL file to make test attached.
I'm trying to create a relation from child block to the master block that I've created . Foreighn key is there from child to parent table.The error I get is below:
FRM-15004: Error while parsing join condition
The join condition is correct, but I'm still clueless as to what it could be.Is it a form bug?
I receive a "FRM-15004 Error while parsing join condition" when attempting to create a relation between block1 (parent table) and block2 (child table). If I do a simple straight join the statement is accepted but if I use a Decode then it results in an error. How to successfully use a decode or a nvl in a join statement of a relation?
I have an app that collects data and after some time submits the data to Oracle.
The app. has worked flawlessly for years in different setups.
Now, I have a setup where the clients are in a separate LAN behind a NAT router and the server is on the main LAN.
I get "ORA-03113: end-of-file on communication channel" in different circumstances.
Sometimes it happens, when the app has not been in contact with the database for 15-20 minutes. This can probably be handled by disconnect/connect mechanisms.
But whats worse, sometimes ORA-03113 appears in the middle of continuously submitting hundreds/thousands of rows.
The server does not have routes to access the clients separate LAN, is that part of the reason?
The problem concerns distributed transactions.There are 2 databases that communicate with each other through a database link.We call Xa transactions from our weblogic application. Both databases work in a shared server mode.
Everything works until we run some performance tests on the evironment where the data volume is much bigger then the one on the other environments where the same tests succeeded. We're getting the following error: ORA-03113: end-of-file on communication channel
The error get registered in a local database alert.log when accessing data through the database link while there's no trace corresonding to this error as far as the second database is concerned. We're almost sure there's no network problem. The error occurs in only 1% of a total executions number. We also observe the inactive sessions number increasing on the remote database.
Both databases use ASMM by having set the SGA_TARGET and PGA_AGGREGATE_TARGET parameters and proper level of the STATISTICS_LEVEL parameter, so some hot elements like SORT type operations should execute fine.Any Oracle parameter should be tunned or we should take into consideration something else...
I start sql*plus and start working. after 2-3 minutes i get error -
ORA-03113: end-of-file on communication channel.
I googled and found to see alert.log.(but i found no such file on my system) but in my system i found some info at C:\app
ishiproduct11.1.0db_1log ishi-pcclientclsc42.txt
the content of the file is ;
******************************************** Oracle Database 11g CRS Release 11.1.0.6.0 - Production Copyright 1996, 2007 Oracle. All rights reserved. 2011-01-27 12:35:03.512: [ OCROSD][3332]utgdv:1:could not open registry key SOFTWAREOracleocr os error The system could not find the environment option that was entered. 2011-01-27 12:35:03.512: [ OCRRAW][3332]proprinit: Could not open raw device 2011-01-27 12:35:03.512: [ default][3332]a_init:7!: Backend init unsuccessful : [33] 2011-01-27 12:35:04.617: [ CSSCLNT][3332]clsssinit: error(32 PROC-32: Cluster Ready Services on the local node is not running Messaging error [9]) in OCR initialization *************************************************
I am getting the same error evert time i login.Tried to stop/start oracle services but problem persists...
I got a primary database which ships logs successfully to a standby database where it is applied successfully as well.
I can connect using the tns name defined for data guard (which are already used for log shipping) in sqlplus.
Yet, at the time of configuring data guard broker,the command add database 'standby' connect identifier is 'to_standby'; fails.
Looking into drc<instance>.log, I see the error ora-03113 network I/O.
I haven't seen any error like that online and I can't explain why it does that when logs can ship already with the same connection identifier.
I have edited dest_2 with and without parameters LGWR and SYNC and reconfigured the broker without success. I've removed the configuration files and tried again.
I've checked that the password file is the same on both servers as well.Nothing seems to work. Is there a parameter I'm missing somewhere.
We had power failure, and i had to restore the database from offline backup.after restore i tried to start the database and i received following errors.
SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 663961600 bytes Fixed Size 2184056 bytes Variable Size 394267784 bytes Database Buffers 260046848 bytes Redo Buffers 7462912 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
[code]....
I reviewed the /oracle/PRD/saptrace/diag/rdbms/prd/PRD/incident/incdir_583361/PRD_ora_7179_i583361.trc, it hugh file with alot of Jargon.
My requirement is to match the records in MTCH_TBL and HIST_TBL on the basis of joining S_SEC(MTCH_TBL) with SEC_ALIAS(HIST_TBL) and INSTANCE (HIST_TBL) as 100 and choosing the record with max EFF_DATE from HIST_TBL.
I have come up with a query as:
select a.h_sec, a.s_paid, a.h_paid FROM MTCH_TBL a, HIST_TBL b where a.S_SEC=b.sec_alias(+) and b.instance(+)=100 and b.EFF_DATE = [code]...
join using the EFF_DATE field also and get the expected results.My results are appearing as BLANK. However I need to produce the results as stated below:
UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));
This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?
Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" *Cause: *Action:
UPDATE XXX_CURR_EOM SET ID = (select CAPITALPLAN.ID from capitalplan, XXX_CURR_EOM where XXX_CURR_EOM.ID = CAPITALPLAN_id)
how do I create a procedure for a SELECT query like the following?
When I create a procedure; I get an error "Error(80,1): PLS-00428: an INTO clause is expected in this SELECT statement" PROCEDURE MyProcISBEGINselect 'Dakota' as ALIAS ,A.StartDate ,B.EndDatefrom Customer A ,Clients bwhere a.cType = b.cTypeand b.Active =0ORDER BY StartDate, EndDateEND MyProc;
