After prepared this xml . I make some edit(like add two record) in my emp and dept table i need every one hour update of my xml . How i will do this one ?
select xmlelement("AllDepartments",
xmlagg(
xmlelement("Department", xmlattributes(d.deptno as deptno, d.dname as dname),
xmlagg(
[code].........
follow SQL query would lead to an ORA 01850: hour must be between 0 and 23 error? I'm getting this error don't not sure if this query is causing it.
select
mlh_date_begin,
mat_desc,
mlh_alert_text,
mat_code
from s_mlh_mem_alert_history,
s_mat_member_alert_type
where mlh_mem_uid=f_mem_uid
and mlh_date_begin <= trunc(sysdate)
[Code]...
I am receiving the following Oracle ERROR:
HIPDAO -> getXAlerts():SQLExceptionORA-01850: hour must be between 0 and 23
HERE is the code where it's occuring:
public List xMethod(
int memUID)
throws DAOException {
[Code].....
The error doesn't make sense because I don't think I'm setting a date anywhere in the code.
I wish to query the pro_vitual_bytes by hourly average by server name from 04/01/2010 to 04/02/2010. how i going to perform the query.
SERVER_NAME DATE_TIME PRO_VITUAL_BYTES
MLYORABA04/01/2010 00:00:402,238,312,448.00
MLYORABA04/01/2010 00:01:402,241,458,176.00
MLYORABA04/01/2010 00:02:402,238,312,448.00
MLYORABA04/01/2010 00:03:402,241,458,176.00
[Code]....
how to extract hour part from a date?
suppose my date is like
29-mar-2004 09:20:34
i wanna get only hour from the above date-
Is there any way to get the amount of redo generated in last 2 hour. which has below chareteistic
1. redo generated by currently connected session from last 2 hour.
2. redo generated by session disconnected during last 2 hour.
total_redo = disconnected sessoin during last 2 hour + connected session generating redo during last 2 hour.
i want to convert minutes that is 90 want to convert it in hours and minute that format should come in 01:30 format.
can i get in this format?
in the awr report I saw some segments in Segments by ITL Waits section. Number of waits reaches to 100 in 8-hour snapshot. Is it small/big number? Should I consider increase INITRANS or there is nothing to be worried because value 80-100 are not too high?
View 3 Replies View RelatedI have an Oracle application that deals with a 29 hour clock. so the days begin at 5am instead of midnight. I am trying to get data from a table where time is up to certain number of hours, but when it reached the 00 clock my whole query returns no records, even though there is plenty of records before the midnight hour, because the data is saved as lets say 2530 instead of 0130
I am using this
SELECT *
FROM WORK.WORK_UNIT
WHERE (to_char (current_garage) = :entry_blk.curr_garage
OR 'all' = :entry_blk.curr_garage)
AND route >= 0 AND run >= 0
AND ((package_id > 0 and work_code = 99) OR
(package_id is null and work_code = 1))
and Nvl (Emp_No, 0) = 0
AND work_date = :entry_blk.p_work_date
AND on_time <= TO_CHAR(SYSDATE + :Entry_Blk.up_to_hour/24, 'HH24MI')
since oracle deals with a 24hour clock, my code doesn't seem to work if there is data for after midnight (00 hour). I am using :Entry_Blk.up_to_hour/24 to determine up to how many hours I want to see data, i.e.
on_time <= TO_CHAR(SYSDATE + 2/24, 'HH24MI')
will give me data up to 2 hours
I'm looking for a way to say if a time range includes a specific hour.
If I was looking to work out if 01:00 to 04:00 includes 02:00, then this works:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('01:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
But, if the time go over two days (with midnight in between), it doesn't work:
CASE WHEN TO_DATE('02:00','HH24:MI') BETWEEN TO_DATE('18:00','HH24:MI') AND TO_DATE('04:00','HH24:MI') THEN 1 ELSE 0 END
However, I need to refresh the group manually sometimes. Therefore, I cannot set the interval as sysdate+1.have tried setting the interval as follows. However, they are not correct
Quote:trunc(sysdate+1) +4/24
The next interval will show 9:25:20pm.
Quote:trunc(sysdate+1) + interval '4' hour
Incorrect syntax
I have been trying to develop a script for generating ADDM Reports every hour and save it in a directory on the server. I was able to develop a script to run the AWR Reports for every hour and save them in a directory, but I ran into troubled waters in the ADDM script.
Database Version : Oracle Database Enterprise Edition 10.2.0.3
OS : IBM AIX 5.3
I'm trying to debug the script below to generate ADDM reports on a per hour basis and save them in a folder as well as mail than to a particular entity.
########################################################################################
# Set up Oracle environment variables...
#------------------------------------------------------------------------------
export PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:/oracle/app/oracle/product/10.2.0/bin:/usr/bin/X11:/sbin:.:/oracle/app/oracle/product/10.2.0/bin
export ORACLE_HOME=/oracle/app/oracle/product/10.2.0
export ORACLE_SID=sarcotest2.ora
export ORAENV_ASK=NO
[Code].....
Initially, the script did return me an ADDM report, but the problem was that it generated the report for only a set of two snapshots (eg: 410110 and 410111). later when the snapshots advanced further, it still generated the same report for the same snapshots.
Now, it seems it have made several changes to the script in the declare and dbms_advisor section and the reports are not being generated anyway.
One problem I am facing is
ORA-13605: The specified task or object ADDM_UAT does not exist for the current user.
ORA-06512: at "SYS.PRVT_ADVISOR", line 2043
ORA-06512: at "SYS.DBMS_ADVISOR", line 560
ORA-06512: at line 1
It seems the task is not being generated at all in my user's schema.
In an 11g database is it possible to create an awr report including the current hour or do I always have to wait till the full hour?
View 1 Replies View RelatedI have the following query:
SELECT
d_dtm,
BTS_ID,
CASE WHEN D_DTM = (D_DTM-24/24)
THEN sum(V_ATT_CNT)
[Code]....
But it is not returning any results because of the "having" clause. I know it should return results because all I want it to do is in one column have the V_ATT for the current time period, and in the 2nd column, have the V_ATT 24 hours ago. I've checked the data and I should get results back but can't seem to figure out why this is not working...
I have logged into Oracle and Discoverer several times and every time when I'm running a report in Discoverer, it quits responding. This is after I already have the data, and I'm scrolling to the bottom of the data to get the next 10,000 rows.
I just get the "hour glass symbol" and then i basically have to shut down and restart my computer.how to recover from these type of hour glass symbol.
our DB machines time is one hour ahead of Active Directory, as we sync our time with AD, is there any contribution of Database in the wrong time of solaris machines ? and for the resolution of problem, what can be checked at DB side.
View 1 Replies View Relatedresolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace
function fun_test_timestamp(P_HOUR varchar2) return varchar2
is
sql_stmt varchar2(1000);
begin
[Code].....
Input:-
select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace
function fun_test_timestamp(P_HOUR varchar2) return timestamp
is
sql_stmt varchar2(1000);
[Code]...
Input:-
select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character
ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8
00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain
any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
*Action:
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
the reason why this happen with our server.
View 4 Replies View Related I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
I have to update 20 and 60 million records of a table. The update statement are
1> 20 million recs
update mycustomer set update_time=add_months(sysdate,240) where seq_num = 1;
commit;
2> 60 million recs
update mycustomer set update_time=sysdate-seq_num where seq_num <> 1;
commit;
Q1> Is there any way to improve performance
Q2> Will parallel dml improve performance
Q2> Would a pl/sql cursor make any difference in speed.
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end
BEFORE INSERT OR UPDATE OF issued ON shares_amount
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO shares_amount(date_end) VALUES(SYSDATE);
END set_date_end;
/
create table test1
( ID NUMBER(11)
,MEMBER_NMBR NUMBER(10)
,CODE_NMBR NUMBER(7)
,ROW_EFCTV_DT DATE
,ROW_TRMNTN_DT DATE
[code]....
insert into test1 values (11007,7462,32,'30/sep/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
insert into test1 values (11007,7462,32,'30/oct/2012','31/dec/9999',3,'25/sep/1998','AUTUMN',1,0,344);
IDMEMBER_NMBRCODE_NMBRROW_EFCTV_DTROW_TRMNTN_DTFLAG_NMBRBRTH_DTNAMECLAIM_CDAMT1AMT2
1100774623209/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
1100774623210/30/2012 00:0012/31/9999 00:00309/25/1998 00:00AUTUMN10344
I have to update the row_trmntn_dt of first row to row_efctv_dt of 2nd row which is 30th Oct 2012 - 1 day i.e. 29th Oct 2012
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
View 5 Replies View RelatedI have one doubt about update command in sql. How to update the multiple rows with different values using update statment.
Eg:-
SQL> set linesize 500;
SQL> set pagesize 500;
SQL> select * from emp;
SQL> select empno,ename,sal from emp;
SQL> select empno,ename,sal from emp;
EMPNO ENAME SAL
---------- ---------- ----------
7839 KING 5000
7698 BLAKE 2850
7782 CLARK 2450
7566 JONES 2975
7654 MARTIN 1250
[Code]....
The above table contains 14 records. Now i would like to update the salary column with different values like
EMPNO SAL
===========
7839 18000
7698 20000
7782 5000
...
...
...
7934 25000
How to update above values with single update query.
After many tests I can't make work and update of the same table inside the same table.
Trying to avoid Mutating Table Error now I have
ORA-00036: maximum number of recursive SQL levels (50) exceeded
Sample Data :
create table test_compound (USERID VARCHAR2(10),APP VARCHAR2(15),LAST_UPDATED_ON TIMESTAMP);
insert into test_compound values ('user1','1',systimestamp);
insert into test_compound values ('user2','2',systimestamp-4);
insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test
FOR UPDATE ON test_compound
COMPOUND TRIGGER
TYPE t_tab IS TABLE OF VARCHAR2(50);
l_tab t_tab := t_tab();
[code].......
When I execute :
update test_compound
set last_updated_on=systimestamp
where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
View 4 Replies View RelatedI have table test1(id,name) and table test2(id,,name)
Now when I update name column of a row on test1 I want the same value to be updated for the same id in test2.
So I wrote this trigger but its not working
create trigger test_trigger after update on test1 for each row
begin
update test2 set name=new.name where test2.id=id
end
/
I have an employee table that has a paygrp_id that will be used for my subset of employees. For all the employees that have the paygrp_id = 10212 on the employee table I need to update the workbrain_user table to set the flag wbu_cansee_self to 'N'. The join between the employee table and the workbrain_user table is the emp_id.I get the following error when I run this cursor.
[error]
Error on line 0
DECLARE
CURSOR wbuFlag_cur
IS SELECT e.emp_fullname, e.paygrp_id,wbu.WBU_CANS
[code]...