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 RepliesI have an existing view that stores some data. I was asked to create a table based on the columns present in the view. I only copied the table structure from the view.
Now I would need to create a stored procedure that executes the query and stores the data in the table with a snap id and date of snap column. The reason this needs to be done is to show differences over time for historical and trending.
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].......
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..
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) ?
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].......
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.
. 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?
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 RelatedIf I like to identify the executed time of a particular SQL Statement, beside v$sql, is there any other dictionary or lookup table that have this piece of information?
Why v$sql is not sufficient, because this is a recurrsive update statement which is regularly called, and thus the last_load_time is overwritten.
My archivelog had been purged due to our scheduled backup.
Is there any other way to identify when the particular SQL statement is executed?
1) how to pass date from .net using odp.net to oracle database for storage. so that it should not be affected if any one changes os(windows xp or 2003 /2008 server) date formate.
database and application both are on different server and can be on same server also. so if any one keeps different date formate on both OS then it should not get affected.
what does mean VERSION_TIME column in V$DATABASE;i know that there is an other column which is called CREATED which indicates when the database has been created and i think it will never change,but, what about VERSION_TIME ? when does it change ?
View 6 Replies View RelatedI understand that when data is read from the disk, I/O is done..And When computations are done then CPU is used..Then where the following equation fits?
DB Time = sum of database CPU time + waits
Is I/O considered as a part of CPU time?
Does this equation changes with SAN, OS caching?
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 Relatedi'm keep on getting below error every morning in my Database.this indicate in Grid control every morning.
Metrics "Database Time Spent Waiting (%)" is at 34.336 for event class "Concurrency"
Metrics "Database Time Spent Waiting (%)" is at 100 for event class "Other"
Metrics "Database Time Spent Waiting (%)" is at 44.78 for event class "Other"
i need to know, this is something that i should consider as a critical warning and how can i solve this .
after spent a lot of time surfing the web looking for this error frm-40513 i did not found any answer about this, So I've removed the date items on my canvas and ran perfectly... after that i opened another copy of the form. In one item there was a initial value $$DBDATETIME$$ .. so that was the error.. i removed that value and i ran my form 11g (weblogic) again and the error has gone.
View 6 Replies View RelatedI have 10 users in my database, I need to find the time at which a query is fired by any of the users in the database. provide the query for this? I think i have to use V$sql,v$sqlarea or v$sql.
View 1 Replies View Relatedi have oracle 11gr2 database on linux 5.5
i have the performed level 0 rman backup on 15th feb 2013, and after that i have incremental 1 and archives and controlfile backup. Before this no backup is the database.
Now i want to restore it to 12th feb 2013.so is there any ways to restore the database to that point time.can we restore controlfile to point in time.
I am running full database backup it take 10hrs to complete.
Size of the database=590GB
Platform:Solaris 9
database version:10.2.0.5
using catalog database.
Is there any way to reduce the time ?
Is there any query to see how long does the backup runs?Monitor the backup time?
Most of my day to day work involves the support of DB2 on AIX and z/OS. I support the database infrastructure for one business system that runs on the Oracle RDBMS. The application is Oracle Transportation Management. The non-production environments don't get much traffic. If I sign into the TEST or DEV Enterprise Manager DB Control screen, the following warning is almost always listed on the main page:
Metrics "Database Time Spent Waiting (%)" is at 44.00384 for event class "Concurrency"
Enterprise Manager reports that this metric is continuously fluctuating above and below the warning threshold of 30% of db time when the environment is idle.To investigate(and I am no oracle expert, far from it), I ran the following:
SELECT * FROM v$session_event
WHERE WAIT_CLASS='Concurrency'
ORDER BY TOTAL_WAITS DESC;
By far, the top two entries in the result are the following:
SIDEVENTTOTAL_WAITSTIME_WAITEDAVERAGE_WAITMAX_WAIT
124os thread startup12041423733.5218
359os thread startup150653433.5521
[code]...
Does this indicate a possible problem with the operating system?
Is there a way where I can check users/IPs who connect to the database in a specific time 2 days ago?
View 5 Replies View RelatedI need to use Data Pump for the first time on my production Database.Currently on Testing Database, when i am taking schema level export there are no errors or warnings in the log file but when i importing it gives fallowing ORA in the import log file. i searched on google,the only way i found is to recompile the invalid objects. how to avoid this warnings in log file.
"ORA-39082: Object type ALTER_PROCEDURE:"QUANTISV4"."P_CTM_ABN_INVST_EQUITY" created with compilation warnings"
how can we load a Flat file into a Database At Regular Interval Time.
View 2 Replies View RelatedCan we make Data Guard to take clone Primary Database at Specific Time?
e.g. Clone Primary Database at 10 AM and at 6 PM?
I have a question regarding db link. I have a query that uses a database link, i speed it up by adding the DRIVING_SITE hint and it worked when executed from
sql*plus or pl/sql developer.
It returns results in 3 seconds or even less. However, when i put this query back into the APEX application, specifically into an interactive report page -- it runs for 3 mins!
How to check the listener time zone settings/values in Oracle 10g database?
View 5 Replies View RelatedI am trying to restore 11gr2 database to point in time. using following steps:
Control files are fine, that are not restored.
RMAN> run
{
set until time "to_time('2012-12-13 12:12:00','YYYY-MM-DD HH24:MI:SS')";
}
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
Here my problem is it is restore complete data what ever i was having, but i don't was it, i has to restore up to specified time only.
I have a form that the user has to enter a time of an event.
The time entered will most likely be in the past and NOT the current time.
What is the best way to set up a form and a database table for time entry only (exclude the date)?
I have tried datetime on the form with format HH12:MM PM and date on the database.
I have also tried the same with a timestamp on the database.
The time always appears to get entered correctly. However, the time is always stored as AM regardless of what is specified by the user.
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.
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;