Selecting Minutes From Timestamp And Scheduling Email
Nov 10, 2012
I have oracle 11g r2, I have a table emp, where i have three columns emp_no,status and creation_date, creation date is a time stamp like '02/09/2012 17:59:47'.
I have to trigger an email if the status is not updated for 30 minutes.
DECLARE
CUSROR C_GET_DATA IS
/*SELECTING RECORDS*/
SELECT * FROM EMP WHERE creation_date BETWEEN CREATION_DATE AND CREATION_dATE+30 AND STATUS IS NULL;
[Code]....
in the above code "CREATION_DATE AND CREATION_dATE+30" will give 30 days, how can i select just 30 minutes and schedule it every 30 minutes, so that email is triggered for if status is not updated for more than 30 minutes.
procedure p1 ( i_time_min number -- minutes to be substracted from timestamp ) is v_end_timeinstamp timestamp(6); begin
[code]....
The problem with above procedure is passing parameter is in minutes and i need to substract the same from sys_extract_utc(current_timestamp) and store result in v_end_timeinstamp in timestamp format only... substracting directly will reduce the days and not the minutes.
@ the end of the day means After date changes i need to populate the new row in Some of my tables with the previous date values with the new date.. In that time some other Transaction will Occur on those tables so we need to lock all the tables in that time..Even i tried by doing the Exclusive lock on the tables...
execute immediate 'LOCK TABLE CTR_SALES IN EXCLUSIVE MODE NOWAIT'; execute immediate 'LOCK TABLE CTR_STK_ASSIGN IN EXCLUSIVE MODE NOWAIT';
And how to run this function @ 12:00 in the midnight by scheduling.... And 1 more thing how to pick the recent (Transaction) row on that table. I tried from the rowid it's not working
we need to create a materialized view which should do complete refresh at pre determined interval like - 6am,12pm and 4pm every day.
Is it possible to have this option in materialized view?
As of now, I could understand we can do "start with" and "next" option which will schedule refresh with same interval but not with different time interval which I mentioned above.
i have a production database server with 2 node rac. i now have a separate database server for my report db. Now i want to do an automatic export of the production database or a schema in the production database and then import it automatically to the report db immadiately after the export job finishes at a specific time, say 12 midnight everyday. how do i go about this using crontab or any other better solution. my database is 11gr2 and operating system is rhel 5.7.
I have a shell script which triggers a batch in our application (an application which is not integrated with any other scheduler and is trying to integrate with OEM for the first time).
Is it possible to schedule a job to run this shell script in OEM ?
Oracle DB Version - 11g XE I scheduled a job using dbms_scheduler which will insert a record into table T for each minute. I didnt mention COMMIT inside my procedure but records are being commited after each successful execution. How come it is possible. Here is my code.
SQL> create table t ( empno number, creation_date date); Table created SQL> create or replace procedure test_proc 2 is 3 4 begin 5 6 insert into t values (1,sysdate); 7 8 end; 9 / Procedure created
I have requirement as follows. I need to load the data to the target table on every Saturday. My source file consists of data of several sates. For every week i have to load one particular state data to target table. If first week I loaded AP data, then second week on Saturday karnatak, etc.
Provide code also how can i schedule the data load with every Saturday with different state column values automatically.
is there a prebuilt function that will round say the time of a sysdate up or down 5 mins? so i entered 5:32pm i would want it to round it down to 5:30pm
I have two columns which I need to add together then devide by 60 in order to display that time in minutes. the problem I am facing is that at times I get numbers above 60 and my client doesn't want to see numbers above 60.
i.e (col1 + col 2)/60 = 34.87
what I need to do is make sure that when the number reaches 60 it moves on to 35.
My procedure is run for every 15 minutes. Now suppose if I am running for old dat, then I should get the difference of dates by taking
v_old_Date := v_edate - in_Sdate;
Divide this by 15 , round that value and loop to run the procedure for that n times. My doubt is when I am saying
v_old_date := v_edate - in_sdate ; I am getting expression is of wrong type. How can I take the difference of the dates and get the minutes from that ?
In one of my application oracle net connection is taking 30 to 40 ms(tnsping servicename),but in actual connection is always taking more than 45 ms, and my application requirement is below than 10 to 20 ms.
And can i use any other connection method like hostname, ezconnect etc.
I am working with a new client and am still waiting on access to systems so I'm a bit hampered in looking at details of the database and environment. The database is OLTP and typical response time for returning result sets is under 2 seconds, often less than 1 second.
A simple SELECT on a few columns of v$session_longops (no subqueries, group by, having, etc) ran for more than 5 minutes.
I have a Windows 2003 VirtualBox instance, to which I assigned 3 out of 4 cores my laptop has. This is a demonstration environment for an Oracle vertical product. I got it from my colleagues. The OS boots without starting the DB services - I did this deliberatley while trying to figure out what is happening.
About 2 and half to 3 and a half minutes after the service is started the oracle.exe "latches onto a core and does not let go" (as best as I can describe what I see). With 3 cores I see 33%-34% cprocessor use in the task manager with oracle.exe doing all the using. Nothing else is started. There is no process of which I am aware which actually uses the DB. I only start the TNS listener and the database service.
Once I start it, the demonstration software uses the database extensively for complex queries. With one of the 3 cores 100% used by the oracle.exe I am running short on CPU at times, which makes the demonstratin seem slaggish, and queries take longer than is really acceptable (not surprising seeing oracle.exe is very busy doing I know not what).
I currently have a problem where I have two date fields with time stamps. The only bit i am currently interested in in these fields is the time factor. When i display them in their field they have a format of HH24:MI .
I have a start time and end time as well as a duration and duration type. What I am trying to do is the following: when the user inputs the start time, along with the duration say 1 for example and the duration type of say HRS for example I would like to have the end_datetime default to 1 HR from the current start time. This is the code I use on a when validate item trigger to acheive this:
case :blk.duration_type when 'HRS' then :blk.end_datetime := :blk.start_datetime + ((1/24)* :blk.duration); when 'MINS' then :blk.end_datetime := :blk.start_datetime + ((1/24/60)* :blk.duration);
However, every time it triggers the value put into end_datetime is 0:00 is it something to do with the datatypes im using .
