How To Set Interval Time 4hrs In Dbms_jobs But Starting Time 3:00 Am

Jul 25, 2013

how to set interval time every 4hrs in dbms_jobs but starting time 3.00am.

i am set trunc(sysdate)+4/24. but it will take starting at 12.00,4.00,.....in this way..

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Time Interval In Schedule Job

Jul 17, 2012

How to set time interval in schedule job i need five minutes interval..

View 5 Replies View Related

SQL & PL/SQL :: Time Interval Group

Mar 28, 2011

IN OUT ROOM

1/20/2011 7:26:00 AM | 3/28/2011 10:34:51 AM | 1
3/23/2011 12:53:00 PM | 3/28/2011 10:34:51 AM | 2
3/14/2011 10:44:00 AM | 3/14/2011 3:05:00 PM | 3
2/24/2011 2:31:00 PM | 2/25/2011 9:02:00 AM | 1
2/22/2011 1:32:00 PM | 2/23/2011 9:56:00 AM | 2
2/22/2011 1:30:00 PM | 2/22/2011 1:32:00 PM | 1
12/9/2010 6:42:00 PM | 12/11/2010 1:38:00 PM | 3
3/8/2011 4:48:00 PM | 3/28/2011 10:34:51 AM | 1

Here I need an out put like below

room 1 2 3 Total
date 7am-12pm 12pm-7pm 7pm-7am 7am-12pm 12pm-7pm 7pm-7am 7am-12pm 12pm-7pm 7pm-7am

01/20/2011 2 3 1 1 3 3 2 4 1
01/21/2011 1 2 2 1 0 0 0 0
...........................................
.......................................
Total

i have to divide into 3 groups and take a count 7am-12pm, 12pm-7pm, 7pm-7am groups

It looks so complicated to me, because IN time and OUT time together how we do it.

suppose one person 6am IN and out 8PM means he will be in 7am-noon , noon to 7pm, 7pm-7am -- 1, 1 1 on 3 interval another scenario is if one person in 2am in the morning it has to be previous days count. Is this possible to do it in query.

View 1 Replies View Related

SQL & PL/SQL :: TIMESTAMP With Time Zone And Interval

Mar 26, 2011

I'm having trouble using interval data types in a procedure. I need to pass a number of minutes as a parameter, and then use them for arithmetic on a timestamp with time zone. This works no problem:

set serveroutput on
create or replace procedure tstz(mins varchar)
as begin
dbms_output.put_line(systimestamp - interval '10' minute);
end;
[code]...

I've tried a few variations of data type and type casting for the parameter, but I can't make it work.

View 5 Replies View Related

SQL & PL/SQL :: Creating View On Basis Of Time Interval?

Apr 27, 2010

how to create a view from table which containes rows from every minutes. In view I want to select only rows at 15 or 30 minutes interval.

eg. my base table has 60 rows for 1 hour. The created view must contain 4 rows on 15 minutes interval basis at the boundary of that interval.

O/P of view should be like this.

Value Time
100 00:00:00
200 00:15:00
300 00:30:00
400 00:45:00
500 01:00:00

View 6 Replies View Related

Restrict Shudown Database For Time Interval

Jul 30, 2012

I an using oracle10g in AIX..Can i restrict the shutdown operation within a time intervall....say for eg: "Shutdown immediate" should not be accepted by sqlplus, if it is issued after 10am and before 6:00Pm..

View 2 Replies View Related

Server Administration :: MView Not Refreshing Automatically At Time Interval Specified?

Sep 14, 2011

I have create a mview using a dblink with the refresh difference of every 10min. Suddenly I check the mview refresh date and time it was not getting refreshed automatically at the time interval specified.

drop MATERIALIZED view log on afccv.tbl_voicechat;
drop MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1 ;
CREATE MATERIALIZED VIEW SMSCHAT.TBL_VOICECHAT_NEW1
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

[code]....

I am trying to find out what is the cause of not refreshing and also googled for the same. got some link also either not linked to same problem or not cleared.. Where should i checked regarding the error cause.

View 33 Replies View Related

SQL & PL/SQL :: How To Load Flat File Into Database At Regular Interval Time

Apr 5, 2011

how can we load a Flat file into a Database At Regular Interval Time.

View 2 Replies View Related

Server Administration :: Archive Log Generation Time Interval Or Size Wise?

Oct 21, 2011

i am using oracle 10g on solaris 10 os.currently archived log is generated by size wise 52 mb.i want to know whar is the best practice for archive log generation . it should be time interval or size wise.

View 1 Replies View Related

Backup & Recovery :: RMAN - Time Interval Backup And Restore?

Mar 27, 2012

I have been updating data in my database by adding numerous rows and assume the insertion of rows started from 1st date of january month.

I have been entering somehow or the other part of data every day.And now the date is 25th of january.

How can i take a partial backup of my database of the transactions done between 15th of January to 20th of January Month

How to take Backup of a particular date say it be 12th of january month.

How to take backup of first 10 days i.e., 1st-10th of january.

View 7 Replies View Related

JDeveloper, Java & XML :: Date Time Omits Time Part

Jun 4, 2012

When I try to extract the date tag value from XML data, the time stored in 20120602153021 format i.e., YYYYMMDD24HHMISS format. The following statement extracts only date as 02-JUN-12 however do not extract the time part.

If I try the same in SQLplus with to_date it works however fails in PL/SQL.

XML data:
<?xml version="1.0"?>
<RECORD>
<REGTIMESTAMP>20120601130010</REGTIMESTAMP>
</RECORD>

PL/SQL Extract:

CURSOR c_xml_record
IS
SELECT extract(value(d), '//ACTIVATIONTS/text()').getStringVal() AS REGTIMESTAMP,
FROM t_xml_data x,
[code].......

View 3 Replies View Related

Fetch Time Greater Than Execute Time?

Dec 27, 2010

I have one inline view query which shows exec\ fetch : 2 sec\ 19 sec It gives 500 rows as final out put, when i give rownum<100 it shows exec\ fetch : 1 sec\ 000 sec, and i cannot use this rownum< 100 alternative as this is inline subquery of big query.

What does this exec and Fetch time is?

How to improve fetch time, (esp with sub-query) ?

View 2 Replies View Related

SQL & PL/SQL :: Get End Time By Query Between Start Time And Duration

Jun 16, 2013

I Have three field and first field for START TIME ,Second END TIME & Third DURATION AND Putting START TIME AND END TIME i am getting duration in minutes by using code

""SELECT TO_CHAR
(TRUNC (SYSDATE)
+ (TO_DATE (:T_DONATION_END_TIME, 'HH24MI') - TO_DATE (:T_DONATION_START_TIME, 'HH24MI')),
'HH24MI')
INTO :T_DONATION_DURATION
[code].......

View 5 Replies View Related

PL/SQL :: How To Check If Time Is Closer To Start Or End Time

Jan 31, 2013

I have a table which stores apointment start times and appointment end times. For the sake of this thread I will call them appt.start_time and appt.end_time. I then have a check in time and a check out time for the customer. The only thing is they ONLY way to distinguish between a check in time and a check out time is which one has the earlier time and which one has the later time. Obviously the earlier time will be the check and the later time will be the check out.

This is fine, however sometimes they may forget to check a person in or out and I need to determine whether the time should be insert into the check_in column or the check_out column. To do this I was thinking of comparing the time with the appointment start and end time and if it was closer to the appointment start time put it into the check_in column and if its closer to the appointment end time put it into the check_out column. But I was wondering how I would go about doing this.

The time I will want to compare against the appointment start and end time I will store in a variable called v_time and have this as part of my query, im just unsure of what way to write the query so as to check if the time is closer to the start or end time.

View 5 Replies View Related

SQL & PL/SQL :: Oracle - Compare End-time With The Start-time?

Jun 7, 2010

. I have this query:

select asl1.agentsessionid, asl1.endtime, asl2.starttime, 127 as agentstatus
from
(
select asl1.agentsessionid as sessionid1, min(asl2.agentsessionid) as sessionid2
from cti.agentsessionlog asl1

[code]...

As you can see from my where statement I want to compare the endtime with the startime. This query returns zero results. Is there a way to write the where statement different so I can have results?

View 6 Replies View Related

SQL & PL/SQL :: How To Find Time Offset For Given Time

Sep 23, 2010

My time zone has the offset of 2 hrs during summer and 1 hr during winter.If I want Oracle to tell me what was offset for particular day for example I want to know the offset for February 01, 2010 and August 01, 2010, is it possible?

View 1 Replies View Related

SQL & PL/SQL :: Database To Show Time As OS Time

Jul 4, 2011

I have an application in which time is show as . But, in the table in Oracle it is showing as 13:00. The application is taking the time from OS. OS time is 18:30 IST. Time in SYSDATE is also showing as 18:30. DBTIMEZONE is '+5:30'. what is the problem in Database and how that can be rectified to show the time as same as OS time.

View 4 Replies View Related

AWR Report Shows Wait Time Is High But OS Shows Wait Time As Normal

Oct 2, 2012

We have a Oracle 10g database with RAC and Dataguard. When we look at the AWR report, the wait time shown by Oracle for this database is very high.

Service Time : 15.36%
Wait Time : 84.64%

This would imply Oracle is waiting for resources 85% of the time and only processing SQL queries during 15% of its non-idle time. However when we check the OS (RHEL), the iowait is only about 10% and the CPU is 80% idle. This means that that processing horsepower is available.

As such, the results between the OS and Oracle database (AWR report) seems contradictory. OS says we have CPU/IO capacity, however Oracle says we don't.

View 17 Replies View Related

SQL & PL/SQL :: Create Job Using Dbms_jobs

May 30, 2011

I want to create one dummy job using dbms_jobs and execute it under BALA user.This is just for testing purpose.My goal is to test Whether the job executes successfully under the new user i created BALA. All these should happen under BALA user.

View 5 Replies View Related

SQL & PL/SQL :: DBMS_JOBS Procedure Has 3 Variable Out Arguments

Sep 16, 2011

I am using ORACLE SQL developer.. I am trying to schedule a package to run daily..here is the overview of my package..
-----
create or replace
PACKAGE BODY xxx_MTO_yyy
AS
PROCEDURE yyy_mto (p_message OUT VARCHAR2, p_detail OUT VARCHAR2, p_value OUT VARCHAR2) IS
XXXXXX
CXXXXXX
XXXXXXX
END yyy_mto;
end xxx_MTO_yyy;

Now I created schedule as shown (The sql is processed)

begin
dbms_scheduler.create_schedule
(schedule_name => 'MTO_DAILY',
start_date=> trunc(sysdate)+6/24, repeat_interval=> 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI,SAT,SUN; BYHOUR=22;',
comments=>'Runtime: Every day (Mon-Sun) at 6:00 );
END;

Now I am creating SCHEDULER PROGRAM as shown (sql processed)

begin
dbms_scheduler.create_program
(program_name=> 'mto_DATA',
program_type=> 'STORED_PROCEDURE',
program_action=> xxx_MTO_yyy.yyy_mto',
enabled=>true,
comments=>'Procedure to collect session information'
);
end;

Now i am creating the scheduler jobs as shown (here also the sql processed)

begin

dbms_scheduler.create_job
(job_name => 'str_data',
program_name=> 'mto_DATA',
schedule_name=>'MTO_DAILY',
enabled=>true,
auto_drop=>false,
comments=>'nil');
end;

Now I have two questions.

a) the job should have been visible under the "jobs" tab in sql developer. I dont see that.
b) When I tried to run the job manually using
**********
(BEGIN
DBMS_SCHEDULER.RUN_JOB (
JOB_NAME =>'STR_DATA'
);
END;

it failed , saying that "ORA-06553: PLS-ORA-06553: PLS-306: wrong number or types of arguments in call to 'yyy_MTO'". When defining the "dbms_ scheduler. create_program" object , how can I define the arguments?. My procedure has 3 variable out arguments?

View 9 Replies View Related

Last Used Time Of Index

Apr 7, 2011

I'm using Oracle 11g and I have a bunch of indexes and I want to check if they are being used. I just ran my workloads and now I want to see when each one was last used so I can see if it was during my timeframe or not.

After I ran my test, I found the below, but since I did not enable this, plus I have many indexes.

--Monitor an index to see if it's used
alter index SAMPLE_INDEX monitoring usage;
select * from v$object_usage where index_name = 'SAMPLE_INDEX';
alter index SAMPLE_INDEX nomonitoring usage;

View 4 Replies View Related

SQL & PL/SQL :: Time Not Showing

Jan 24, 2012

I have a field " Tran_date with data type Date . This field contains date as well as time both . while I run this query :

select to_char(tran_date,'DD-MON-YYYY HH24:MI:SS') from ABC
WHERE lOC='1' ;

It is showing result :

21-JAN-2012 00:00:00

Why it is showing 00:00:00 although there is time like 08:25:12 Where is the problem ? Why I can'y see time . I need to change anything ,anywhere ?

View 9 Replies View Related

SQL & PL/SQL :: Time Not Getting Captured?

Jun 17, 2010

I have an issue while converting Date. I need to compare the Time Captured while comparing Dates,

find the following scenario,

SELECT TO_CHAR(ffdh.last_update_datetime,'DD-MON-RR-HH24:MI:SS') TIME_STAMP ,
ffdh.last_update_datetime,
to_date(ffdh.last_update_datetime,'DD-MON-RR-HH24:MI:SS') DATES ,
ffdh.fiscal_doc_id INVOICE_ID

[Code]....

Note That ffdh.last_update_datetime is DATE, and the Parameter for the value '17-JUN-10-01:30:17' is VARCHAR2

I suspect when I am doing to_date above both are converting only to Date and not having Time.

I checked the NLS_DATE_FORMAT as following,

SQL> show parameter nls_date_format

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_date_format string DD-MON-RR

Do I need to Change the NLS_DATE_FORMAT ?

View 5 Replies View Related

SQL & PL/SQL :: Average Over Time

Sep 27, 2011

I have a table with products (xPRODUCT), dates (xDATE) and parameters (xPARAMETER). Now I want to calculate the rolling 12 month average of the parameters over all products for all dates. I tried this:

select xDATE,
avg(xPARAMETER) over ( order by xDATE
range between numtodsinterval(365,'day')
preceding and current row )

[Code]....

... but this does not work. How can I do this?

View 5 Replies View Related

SQL & PL/SQL :: How To Get The Date And Time Of Row

Feb 21, 2011

I have one table with 10 rows and its frequently updated now my question is that, how can it get the latest date of updation of that rows?

View 4 Replies View Related

SQL & PL/SQL :: How To Get The Time From Datetime

Oct 12, 2012

i am using one stored procedure there in one variable value is coming like this: Jan 1 1900 6:00AM

from this i want time how to get the time from that.

View 3 Replies View Related

SQL & PL/SQL :: Get Local Time

Mar 6, 2012

Any way in sql to get the data from my local machine, where only client is installed, not the database.

View 4 Replies View Related

SQL & PL/SQL :: How To Compare Time

Jun 30, 2010

Checkout the following code

CREATE TABLE CHK(dt_request_datetime DATE);

INSERT INTO CHK VALUES(TO_DATE('25-JUN-10 04.12.57 AM',
'DD-MON-YY HH:MI:SS AM'));

COMMIT;

[Code].....

TZ_OFFS TZ_OFFS GMT_TIME
------- -------
-04:00 +00:00 25-JUN-10 04.12.57.000000000 AM

EST_TIME
-----------
25-JUN-10 12.12.57.000000000 AM EST

9 am
----
25-JUN-10 09.00.00.000000000 AM EST

8 pm
-----
25-JUN-10 08.00.00.000000000 PM EST

OUTPUT
---------
30-JUN-11

My doubt here is that even though dt_request_datetime is between 9 am in EST timezone and 8 pm in EST timezone the query output is not according to that.

View 2 Replies View Related

SQL & PL/SQL :: How To Get Time A DML Was Last Ran On Table

Sep 14, 2011

Is is possible to get the time last DML was ran on a table.

My aim is to get the list of tables which were not modified(Select,insert,update,delete) within last one year.

DB version details:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Solaris: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

View 1 Replies View Related

PL/SQL :: Which Datatype Should Take Instead Of Time

Oct 4, 2012

i was using sql server there is time keyword but in oracle which keyword i should take instead of time keyword?

View 7 Replies View Related







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