PL/SQL :: Sys_refcursor Spawning Maximum Threads

Jun 23, 2013

i have a procedure which outputs 10 refcursors. It is called called by a .NET utility which then writes the data to 18 different flat files. The proc is of below format

CREATE OR REPLACE PROCEDURE FILE_EXTRACT
(OUTREC1 OUT SYS_REFCURSOR,
OUTREC2 OUT SYS_REFCURSOR,)
AS
BEGIN
OPEN OUTREC1 FOR
SELECT /*+PARALLEL(A,8)*/
COL1,COL2
FROM TABLE A;
 [code]....

 I have used parallel hint because all the tables used in the queries a huge tables and this is a nightly batch job. The issue here is that i have expected oracle to use 8 processes for this execution so all my select statements have parallel hint with degree 8 , but unusaully the procedure is erroring out on production databases because of maximum number of connections are being spawned and the database is not able to create any new session.  When i debuged and did a quick test proc where i used only one out refcursor it ran only 9 threads . then i ran by adding one more out ref cursor it spawn 17 threads. I think its the way .NET is fetching data from each of these cursors.When i print the first refcursor i do see queries which are running for the second along with the first causing the parallel queries run more than expected. The cause of the problem here is all the refcursors are executed and waiting to return data. SO when .net starts reading first cursor the other queries also run. 

View 0 Replies


ADVERTISEMENT

Advantages Of Redo Log Threads

Sep 16, 2010

tell me the advantages of using more than one redo log thread? Currently i am setup with 3 logfile groups and 3 members in each group.Ie

logfile group 1 ('/u01/oradata/pri/redo01a.dbf',
'/u02/oradata/pri/redo01b.dbf',
'/u03/oradata/pri/redo01c.dbf') size 30M,
group 2 ('/u01/oradata/pri/redo02a.dbf',
'/u02/oradata/pri/redo02b.dbf',
'/u03/oradata/pri/redo02c.dbf' ) size 30M,
group 3 ('/u01/oradata/pri/redo03a.dbf',
'/u02/oradata/pri/redo03b.dbf',
'/u03/oradata/pri/redo03c.dbf' ) size 30M

View 4 Replies View Related

PL/SQL :: Multiple Threads In For Loop

Jan 16, 2013

Here is my code looks like

FOR i IN (SELECT table_name, sqlstring
FROM table_list) -- returns maximim 30 tables
loop
execute immediate 'update '||i.table_name||' set status=''done'' ';
end loop;

in this all tables are independent, it is a bit slow as update queries are getting executed sequentially. .

where i can execute update statements in parallel.

View 6 Replies View Related

Semantic Technologies :: Maximum Node ID Larger Than Java Maximum Integer Value

Mar 7, 2013

I'm currently doing migration from Oracle 10gR2 RDF to Oracle 11gR2 Semantic Technology.I followed the steps on the documentation and successfully created the network using the following:

-----
EXECUTE SEM_APIS.CREATE_SEM_NETWORK('rdf_tblspace');
CREATE TABLE rdf_network_trace (id NUMBER, triple SDO_RDF_TRIPLE_S);
--Created SEQUENCE andTRIGGER FOR rdf_network_trace id
[code]....

when I looked at my Node Ids, they were like +635762253807433724+, +6118969225776891730+. The problem is, I am not the one who is assigning Node Ids, They were automatically generated when inserting TRIPLE data to the rdf table.

Did I miss something when I created my network?

View 11 Replies View Related

PL/SQL :: Difference Between Ref Cursor And Sys_refcursor?

Dec 6, 2012

i found this difference between ref cursor and sys_refcursor.

If you specify return_type, then the REF CURSOR type and cursor variables of that type are strong; if not, they are weak. SYS_REFCURSOR and cursor variables of that type are weak.

my situation is i have to write many stored procedure for reports and also for entery pages. few enter pages use typed datasets and few does not.

which one is better in terms of performance and maintainability in above three conditions?

View 2 Replies View Related

SQL & PL/SQL :: How To Use Dbms_profiler For A Procedure Returning Sys_refcursor

May 23, 2011

using dbms_profiler for a package having procedure returning sys_refcursor.

I have 2 procedure in a package. let say, proc1 and proc2 ( also proc1 is called inside from proc2)

proc1 has no parameter

proc2 has 2 paramter proc2(p_num in integer, p_data out sys_refcursor) [ in which we pass the p_num (ex: 1) and it run to get the data from the tab le and return that data through sys_refcursor.

for proc1, I am able to use dbms_profiler as below and it is working fine

-------------
DECLARE
l_res BINARY_INTEGER;
BEGIN
l_res := DBMS_PROFILER.start_profiler(run_comment => 'package.proc1: ' || SYSDATE);
package.proc1;
l_res := DBMS_PROFILER.stop_profiler;
END;
/
-------------

but for proc2, i am unable to use dbms_profiler, how to use dbms_profiler for procedure returning sys_refcursor.

i tried using as below:
-------------------------------
DECLARE
l_res BINARY_INTEGER;
P_NUM NUMBER;
P_DATA SYS_REFCURSOR;

[code]....

getting error: PLS-00312: a positional parameter association may not follow a named association

View 1 Replies View Related

Forms :: Invalid Cursor Using SYS_REFCURSOR

Apr 25, 2012

i have created a procedure to open a query using a reference sys_refcursor as output parameter. i referenced the cursor in a pl/sql and is working correctly. however it throws error "ORA-01001: invalid cursor ORA-01403: no data found" when use on the oracle forms builder.

create or replace procedure mrch.prc_proj_list (p_add_clause in varchar2, p_rec_set out sys_refcursor) as
begin
Open p_rec_set for
'Select mp2.REGION region, '||
' mp2.ID proj_id, '||
' mppm2.PHASE phase, '||
' mm2.muni muni_id, '||
' mm2.MUNINAME muni_name, '||
' nvl((select Sum(ma.AMOUNT) From MRCH.MRC_AGREEMENT ma '||
' Where ma.PROJECT_ID = mp2.ID '||
' And ma.PHASE = mppm2.phase '||
' And ma.MUNI = mm2.MUNI),0) agreement, '||
' nvl((Select Sum(NVL(ma.GREEN_BOOK_AMOUNT,0) + NVL(ma.GB_ADJUST,0)) From MRCH.MRC_ALLOCATION ma '||
[code]....

View 3 Replies View Related

SQL & PL/SQL :: Error While Using Sys_refcursor In Anonymous Block

Sep 13, 2013

For my task I tried to write a package that has sys_refcursor in it.It compiled well.Now I want to see the results of it and wrote a anonymous block which gave errors.

CREATE OR REPLACE
PACKAGE xyz
IS

[Code]....

Error:Invalid reference to variable 'OBJ_CUR'.

View 4 Replies View Related

SQL & PL/SQL :: Oracle Dynamic Query Returning To Out Sys_refcursor

Nov 8, 2011

I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:

CREATE TABLE "TFWDB_ENTIDADE"
(
"CD_ENTIDADE" NUMBER(10,0) NOT NULL,
"DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL,
"NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL,
"CD_TIPO" NUMBER(5,0) NOT NULL,
"ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL,
CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"),
CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1'))
);

Below is the procedure I build to SELECT it:

create or replace procedure spfwdb_sl_entidade_1(
p_cd_entidade in number,
p_dc_entidade in varchar2,
p_nm_entidade in varchar2,
p_cd_tipo in number,
p_id_status in char,
[code]........

I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:

string pConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASERVER)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORASID)));User Id=user;Password=password;";
OracleConnection oConn = new OracleConnection(pConnectionString);
oConn.Open();
string pNomeProc = "spfwdb_sl_entidade_1";
[code]........

View 7 Replies View Related

SQL & PL/SQL :: Find Maximum Value In A Row?

Mar 28, 2012

I need to find the maxmimum value in a row, which consists 4 fields.

Columns: field_1, field_2, field_3, field_4

values : 2 , 4, 5, 1

the output should be field_3 =5

View 2 Replies View Related

SQL & PL/SQL :: Nvarchar2 Maximum Size

May 11, 2011

I'm having some issues with my NVarchar2 columns that I want to solve

My current NLS_CHARACTERSET is 'AL16UTF16', and therefore I can only create NVarchar2 columns with an maximum extension of 2000 bytes. But I do need a column with 4000 bytes length. How can I create an 4000 NVarchar2 column?

(I do not want to use a CLOB column...only a NVarchar2)

View 5 Replies View Related

SQL & PL/SQL :: Maximum Length Of Table Name

Jun 10, 2013

I want to create a table with a length greater than 30.I Thought there was a way to override the max length for for a table name in Oracle 11.2.0.2.I cant find a documentation that states how to get it done.

View 1 Replies View Related

SQL & PL/SQL :: Display The Maximum Sum Of Salary?

Aug 14, 2010

I want to display the maximum sum of salary among sum of salary of each department.

deptno sal
10 1000
10 500
10 100
20 2000
20 200
30 500
30 1500
30 2000
30 200

Sum of salary for each department.
10 1600
20 2200
30 4200

The output should be
30 4200

Because this the highest sum of salary compare to sum of salary of reaming departments.

View 4 Replies View Related

Maximum Tables Supported

Jan 3, 2013

We are having a production database configured in oracle 10.2.0.4 ( standard edition) and its contains near to 50000 tables . The database is accumulated by more than 100 tables everyday, and my question is is there is any table limits in oracle database ? especially in standard edition ??

View 8 Replies View Related

Maximum Length Of Table Name?

Jan 31, 2013

I think the maximum length of table and column name in oracle 11g is 30 characters.I want to increase the limit as i want to import a mysql database that is having bigger table names.Can i preset the table name and column name length??

View 2 Replies View Related

How To Select Maximum Percentage

Aug 10, 2012

In my table three column are there, structure_code, attribute_code and percentage. one project have many attribute_codes, each attribute code have percentage value. The total of percentage value for a project is 100.

data like follows

structure_code Attribute_code percentage

160025 2531 30
160025 2536 20
160025 2537 50
160025 2538
162061 1468 0

Now i need to select which attribute_Code have maximum percentage for each project(structure_code).

View 3 Replies View Related

Maximum TEMP Space Ever Used

Oct 2, 2012

how to check the maximum space ever used for TEMP. I want to know it because I need to resize the TEMP and I want to know how small it can be. As I can see from a documentation hURL....max_size is max number of extens ever used in a segment

I could multiply max_size by extent_size and it would give me the max size of temp ever used

SQL> select segment_file, extent_size, max_size from v$sort_segment;

SEGMENT_FILE EXTENT_SIZE MAX_SIZE

0 128 23625
0 128 753

View 7 Replies View Related

PL/SQL :: How To Fetch A Row With Maximum Rownum

Jan 15, 2013

In my sql query, how can i fetch the row with max row count? the query has around 10 columns.

View 2 Replies View Related

PL/SQL :: Get Asset_type For Which Count Is Maximum?

Jun 20, 2013

select SEARCH_ID, SEARCH_KEYWORD, COUNT, ASSET_TYPE from RELEVANCY_TABLEwhere SEARCH_KEYWORD = 'search_keyword'and ASSET_TYPE is not null  558search_keyword3desk559search_keyword7table 

I actually need to get the asset_type for which count is the maximum. In this case it should be 'table'.

View 4 Replies View Related

SQL & PL/SQL :: Find Maximum Value Within A Group For Corresponding Types

Oct 25, 2010

Test data for the problem is as follows.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> with t
2 as
3 (
4 select 1 id, 12 compid, 1 rel_type, null enddt from dual union all
5 select 1, 13, 1, to_date('31.12.1993','dd.mm.yyyy') from dual union all
6 select 1, 14, 1, to_date('12.06.1996','dd.mm.yyyy') from dual union all
7 select 1, 15, 1, to_date('23.04.2003','dd.mm.yyyy') from dual union all
[code].......

I want to find the the latest compid for individual types 1 and 2 only within a list of ids. Latest compid is defined as the compid associated with the latest end date (null is treated as the latest end date).

So in the above example for rel_type = 1, compid : 12 is latest, for rel_type : 2, compid : 6 is latest.

Datatype for the tables are :

Id : Number
compid : Number
Rel_Type : Number
enddt : Date

View 4 Replies View Related

SQL & PL/SQL :: Table Column Name - Maximum Length

Apr 8, 2011

What is the Maximum Charter length can be given as a column name in a table?

View 3 Replies View Related

SQL & PL/SQL :: Finding Nth Maximum Or Minimum From A Table?

Sep 17, 2011

how can i find nth maximum or minimum salary from a employee table? here i want only one row as output.

View 16 Replies View Related

SQL & PL/SQL :: How Many Maximum Number Of Bind Variables

Mar 10, 2010

How many maximum number of bind variables,can we use in Execute Immediate.

View 2 Replies View Related

PL/SQL :: Finding The Maximum Truncate (date)

Jul 31, 2012

I've the following data set

with t as
(
select 1 cor_id , 'SR' rt_cd,TO_Date( '05/12/2010 01:12:19 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 2 cor_id , 'SL' rt_cd,TO_Date( '05/12/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 3 cor_id , 'SR' rt_cd,TO_Date( '04/10/2010 02:11:15 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual union all
select 4 cor_id , 'SL' rt_cd,TO_Date( '04/10/2010 01:12:18 PM', 'MM/DD/YYYY HH:MI:SS AM') dt,100 iss_id from dual
)
select t1.*
from t t1for each iss_id =100 ,

there could me more than one record in the above data set, The requirement is i need to get the records where the trunc(dt) is maximum

I need to get 2 records with cor_id= 1 and 2.

how can i get those records

View 3 Replies View Related

SQL & PL/SQL :: DBMS_JOB / Maximum Number Of Failures A Job Can Allow

Mar 9, 2010

What is the maximum number of failures a job can allow,when we are scheduling jobs using DBMS_JOB.

View 1 Replies View Related

SQL & PL/SQL :: Function To Find The Maximum Value Out Of Collection?

Aug 9, 2010

any inbuilt function available for finding out the maximum value of a column out of a plsql collection.

View 15 Replies View Related

SQL & PL/SQL :: Function To View Maximum Salary

Dec 16, 2011

I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.

here is my select statement:

select distinct d.deptno, d.deptname, max(e.salary)
from employee e join department d
on e.deptno=d.deptno
where d.deptname=upper('finance')
group by d.deptno, d.deptname;
[code]...

View 9 Replies View Related

ORA-00020 Maximum Number Of Processes

Aug 23, 2012

select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - ProductionI got below error on the datases.

ORA-00020: maximum number of processesBased on the suggestion got from previous posts, I did kill some (LOCAL=NO) processes to get myself in. When I looked at the number of processes the number is 150, I changed it to 1000. After a day I got the same error.

ORA-00020: maximum number of processes (1000) exceededI killed processes again to let my self in, bases on the post below, I queried database

ORA-00020: maximum number of processes (%s) exceededHere is the query to find out if there are any processes with out session attached to it.

select spid from v$process where addr not in (select paddr from v$session);Result

SPID
null
35259840
36897866
13369762

[Code]...

To determine the from where greatest number of processes originated

SELECT MACHINE, COUNT(*) FROM V$SESSION GROUP BY MACHINE;Machine     Count(*)
     2
CAPITAL-D3F0092     1
DQAKDCDKLCSW01     14
desmond     20
DQAKDCDKLCSA01     52

My question is why I am keep getting this? Why do increasing process from 150 to 1000 did not resolve the issue?

What can I do to make sure that I don't get this error again?

View 1 Replies View Related

Maximum Number Of Processes (1100) Exceeded

Sep 16, 2011

i got this error..

ORA-00020: maximum number of processes (1100) exceeded

my oracle version is 10.2.0.2.0

when i was trying to login to oracle i got :ERROR at line 1: ORA-01012: not logged on

lucky i was having another sqlplus login ..

wht i should do to bring the number of process down without increasing the process value and shuting/restaring down db.

"Usually, when this happens, I want to get connected to the instance, so that I can try to figure out which user is causing the problem. One way to do that is to use the 'ps' command to identify some of the oldest server processes, and kill the 3-5 oldest processes, and then quickly try connecting to the database. Once you're in, you can look at things like V$SESSION to try and determine which user(s) is (are) consuming so many connections, and then go from there."

this is what i got from [URL]....

1.if i am giving ps -ef in OS it will give lot of detail ...
2. in v$session wht i should look for ....

View 4 Replies View Related

ORA 0100 - Maximum Open Cursors Exceeded?

Oct 23, 2006

I think this is the scariest error I've encountered in oracle. After some thime of developing/debuggin I've always reached this error. This leaves me no choice but to restart the java app server, in that case I'll be affecting all the other applications deployed on it which is a very bad thing for me.

Is there anyway I could solve this, I've made it sure that I've handled all errors so that I could always close the connection. Simply put, for every termination case of my program I've placed a closing statement just to be sure that no connections are left open. But even after this, I still experience the ORA 0100 error.

View 8 Replies View Related







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