I want to write a procedure in such a way that the code in the proceduce should finish its work with in a given amount of time other wise it should exit from the procedure.
For example....
i am fetching data from a table and inserting into another table and i want ot finish this task within 10 mins if it does not i need to exit from the procedure.
I have connected to the database from two sessions
Box 1 : i have executed a procedure.it took few seconds to get executed
Box 2 : I have executed a block of procedure.It is taking 1 min 40 secs to get executed.
why it is taking much time from Box2(for executing a block from procedure) , as the same code executed successfully in a procedure from Box 1.
I am using oracle 9.2.0.8 on RHEL 4.8 (64-bit). I am facing a strange problem. I have this one job in database that takes almost 12-15 minutes to execute but when I execute procedure in that job manually, it executes in one minute. Even when no other job is running in database, it takes more than 10 minutes to execute.
View 1 Replies View RelatedI'm creating a stored procedure wherein I'm the field wherein I will use for date input parameter is having a datetime format.
The format that we need for user's to input is 'DD-MON-YYYY' (without the minutes and seconds.)
how should I proceed with the creation of stored procedure for this?
I need to schedule a procedure on SUN, MON,TUE, WED and THU at 6:30 AM. make sure the folloiwng code is o.k.
begin
dbms_scheduler.create_job
(job_name => 'Load-Data_Calc',
job_type => 'STORED_PROCEDURE',
[code]...
I am using oracle 9.2.0.8 on RHEL 4.8 (64-bit). I have this one job in database that takes almost 12-15 minutes to execute but when I execute procedure in that job manually, it executes in one minute. Even when no other job is running in database, it takes more than 10 minutes to execute.
View 1 Replies View RelatedThis is my stored procedure
I have below store procedure:
create or replace
PROCEDURE TESTPERFORMANCE (
o_statuscode OUT NUMBER,
o_statusdescription OUT VARCHAR2,
starttime out timestamp,
time_after_query_TESTJOB out timestamp,
[Code]...
This procedure is taking around 35 minutes when there are 35000 records to loop over (i.e cursor has 35000 records) and TESTJOBTRANSACTIONS table has 90000 records. How to reduce execution time.
I am trying to invoke program from my local Windows machine (exactly java executor, which is in my environment on c:javajdkinjava) using database package with Java stored procedure.
Everything is fired by SQLPLUS script, this script invokes package with Java stored procedure (jar loaded into Oracle) and that procedure is trying to invoke runtime process (syntax:
c:javajdkinjava -cp
and here ara my classes and main jar) using my local java executor. I added all permissions using dbms_java.grant_permisions (execution forced me to give permissions java.io.FilePermission to <<ALL_FILES>> for execute), moreover I added to my database user JAVASYSPRIV and JAVAUSERPRIV role. Unfortunately, beside this I am still getting error:
C:javajdkinjava not found (Note that lookup with PATH isn't done due to the oracle executable being setuid.)
I've written a .net program for inserting blobs into our database, and it works fine, except if the path has more than 30(+) characters. I've read that this is a limitation in oracle, but the actual contents being stored is just the filename, extension, and blob itself, so I'm not actually storing anything more than 30 characters.
View 4 Replies View RelatedI have just created a profile as below.
create profile test_idle limit idle_time 360;
When i queried the dba_profiles view, I observed that the LIMIT value mentioned as DEFAULT. what is the Default values for every resource name.
TEST_IDLE COMPOSITE_LIMITKERNEL DEFAULT
TEST_IDLE SESSIONS_PER_USERKERNELDEFAULT
TEST_IDLE CPU_PER_SESSIONKERNEL DEFAULT
TEST_IDLE CPU_PER_CALL KERNELDEFAULT
[code]...
For the another profile LIMIT value is mentioned as UNLIMITED.
Working on building an application which will be in C# (.NET 3.5) and some PL/SQL. However, for the tables which have to store the data, I added a column, comments, to the table and assigned it a data type CLOB. No big deal, except my PL/SQL function is giving me errors if the CLOB built from several varchar2 rows exceeds 4000 characters. I understand the varchar2 field is restricted in 10g, but I was under the impression there wasn't such a restriction on a CLOB (hence the reason we're using it.)
I end up getting:
ORA-6502: PL/SQL: numeric or value error: character string buffer too small
ORA-6512: at line 1
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause:
*Action:The db is 10g (will be upgrading to 11 in October, needs to work on both) on a Windows Server 2003 box (Archive/backup server and db)
I am using UTL_TCP package of Oracle to connect to remote server. As per my architecture, I am opening multiple connections TCP in the same Oracle session.
I have come arround the limiation of 16 connections with this package.
Consider this query
select deptno, wm_concat(student_name) from id_student group by deptno;
I want to change this query in such a way that only 3 students per dept are listed.
I want to read a number of rows into an array of records.
I keep getting this error:
ERROR at line 1:
ORA-06532: Subscript outside of limit
here is the procedure:
CURSOR get_all_locations (type IN VARCHAR2) IS
SELECT
*
FROM group
WHERE type = type;
[code]...
I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
View 9 Replies View RelatedI have a problem with one table.. First of all:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
The problem table resides in a locally managed tablespace. About 10 millions records is added in this table every day. After 36 hours all these records moved to another (partitioned) table, so the size of data in the problem table always about 75 Gb. But the size of table is reached 157 Gb today, and it still growing. The results of dbms_space.space_usage are showed below:
Size of blocks with:
0-25% free space: 4726784
25-50% free space: 17301504
50-75% free space: 24920064
75-100% free space: 102418669568
full blocks: 54761594880Thus, a lot of blocks have 75-100% free space but the table constantly growing: during last 9 days the size increased from 123 to 157 Gb.
how to stop the table growing? It there any way to limit the table size in locally managed tablespace?
I'm looking for the information on:
•Limit of number of sub programs in a package.
•Limit of number of lines in a sub-program.
•Limit of number of statements in a sub-program.
I searched on net and found below useful information.
The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.
The most closely related number that a user can access is the PARSED_SIZE in the static data dictionary view *_OBJECT_SIZE. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.
is there any recommendation on limit of number of subprograms should exist in a package exist, If there is any guidelines/recommendation exist for these.
The following link states
Quote:
SESSIONS_PER_USER
Each instance maintains its own SESSIONS_PER_USER count. If SESSIONS_PER_USER is set to 1 for a user, the user can log on to the database more than once as long as each connection is from a different instance.
[URL].....
Of course the following is not working even when resource_limit is TRUE
ALTER PROFILE DEFAULT LIMIT SESSIONS_PER_USER 2;
How can I restrict a user to have limited sessions say 2 sessions across 4 node cluster? Presently I am checking the sessions logged in using sql+ and no connection pooling of front end etc. is involved.
In regard to the Oracle 4GB limit, what exactly is this limit? Is this the total size of the datafiles of the user defined tablespaces (excluding SYSTEM and UNDO) so if, for example, we have a 2.8Gb datafile, can we only add another 1.2Gb datafile.
or is it the total size of extents/segments in the user defined tablespace i.e. if we have a 2.8Gb datafile but the database data comes to only 1.7Gb, can we add another tablespace with a new 2.8Gb datafile to allow for another 1.7Gb schema.
I have a question regarding a selection limit from a list of values. The table where my IDs (unit_codes) come from has another column called 'points'. each ID has its own number of points. Is there a way to put a selection limit on the display field that points will be placed on?
FOR EXAMPLE: a course block has a selection limit of 70 points, A person named someguy selects :
music = 20 points
maths = 20 points
mathsXTRA = 40 points
**ALERT!!! - U can ONLY select 70 points worth of units**
I need to display the data as range , i have created one table along with inserts as below , i need the desired output as mentioned below.The range will start from field STMH_PM_CODE , it will get the first and last stmh_pm_code and stmh_pm_desc which are in the same combination of STMH_NO,STMH_JOB_NO,STMH_batch_no and stmh_rev_no,
SQL> CREATE TABLE OT_SHOP_TRANSMITTAL_TEST
2 (
3 STMH_NO VARCHAR2(15),
4 STMH_JOB_NO VARCHAR2(12),
5 STMH_BATCH_NO VARCHAR2(12),
6 STMH_PM_CODE VARCHAR2(35),
7 STMH_PM_DESC VARCHAR2(240),
8 STMH_REV_NO VARCHAR2(12)
9 );
Table created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1001','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002',FRAME','0');
ERROR:
ORA-01756: quoted string not properly terminated
SQL> ED
Wrote file afiedt.buf
1* INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002','FRAME','0')
SQL> /
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1003','COLUMN','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0002','1107040-1004','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1005','FRAME','0');
1 row created.
SQL> COMMIT;
Commit complete.
-- i want it as below see the line in stmh_pm_desc the description gets added until there is a change in either stmh_batch_no or stmh_rev_no since we have stmh_pm_code 1107040-1004 with stmh_batch_no 0002 it goes to other line as a seperate new range and first one will stop with 1107-1003 as that is the last combination and new line or range will get added over again.
STMH_NO STMH_BATCH_NOSTMH_PM_CODE STMH_PM_DESCSTMH_REV_NO
PR-1107-000100011107040-1001 - 1107040-1003FRAME, COLUMN0
PR-1107-000100021107040-1004 FRAME 0
PR-1107-000100011107040-1005 FRAME 0
The procedure uses bulk collect to fetch from a normal cursor, Then I am using for all to insert into target table, The number of rows are 234965470
Question: What should ideally be the limit for my bulk collect ?
According to below, it should be in hundreds[URL]...
I put a bulk collect limit of 50000 - took close to 2 hours then i tried 10000 - just 3 mins shorter than the above time But if you commit every 500 rows, Then is there not another theory that frequent commits is not good ?
Is there something I have to ask the DBAS ?
Here is my query to fetch only 10 records from order by result set.
select *from (select * from EOE_POC.PRODUCT_TEST_REPORT where PRODUCT_CODE='214d' order by CREATE_DATE desc ) where ROWNUM <= 10
I am having problem binding it with java API . how to query this without using sub query ?
I would like to know if it's possible to limit access to isqlplus to only one user in my case. I need to give access in read only mode to user in isqlplus.For that, I will create a oracle user with only select privilege on the tables, but I want to be sure that only this user will be used in isqlplus and not another.
View 1 Replies View RelatedI have a dynamic query that is ending up getting larger than 32k and this query is the base of a ref cursor that would in turn return the results to the application. How can I solve this problem since the largest content of a pl sql variable or literal is 32k ( said by oracle ) ?
PROCEDURE sp_large_query (c1 OUT sys_refcursor)
IS
BEGIN
OPEN c1 FOR ' SELECT STATEMENT WITH MORE THAN 32K ';
END;
i have a database nd only 10 users must allowed to access db at any point of time and sessions per user must be 1.that is only 10 users must be able to access the database.
View 6 Replies View RelatedI have to optimize a batch job which returns > 1 lakh records . I have a commit limit being passed . I am planning to divide the cursor records for processing as follows. If the cursor suppose returns 1000 rows and the commit limit passed is 200 , then i want to fetch 200 records first , bulk collect them into associative arrays and then bulk insert into target table.
After this is done, i will fetch the next 200 records from the cursor and repeat the processing. I would like to know how i can divide the cursor records, and fetch "limit" number of records at a time and also be able to go to the next 200 recs of the cursor next time.
I got a below error while running a procedure!
ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
at that time i am using below parameters in that script:
set serveroutput on size 1000000
DBMS_OUTPUT.ENABLE(1000000);
But still getting same error.. how can i resolve this issue..
What is the limit on number of partitions on a table.on many forums , 1024k-1 is given the maximum limit.But Exactly , I am not able to understand this 1024k-1.
View 2 Replies View Relatedi want to know the limit of " max pool size" attribute in connection string. and at what basis it should be set. and i am using oracle 11.0.2.0.1.
View 4 Replies View Related