But RECORDTIMESTAMP data looks like '4/8/2009 5:48:00.000000 PM' I would like to supress 3 zeros in milli seconds section.( 3 more zero in milli seconds section)
I have a date column in a table, I need to fetch records from 27:11:11 00:00:10 to 30:11:11 00:00:10 (DD:MM:YY HH:MI:SS). i.e between seconds/min/hrs.
expecting Something like this,
SELECT * FROM TABLENAME WHERE MODEDATE BETWEEN TO_CHAR('27/11/11 00:00:10','DD-MN-YY HH24:MI:SS') AND TO_CHAR('30/11/11 01:10:10','DD-MN-YY HH24:MI:SS')
I have a date field which is passed as in parameter defaulted to sydate With the passed date as input I need to run an eod for every 15 mins by calculating
Using the above v_sdate and v_edate I am calculating the start date and end date and compare these in my final select query.Now in case of rerun of old date, I am calculating start date as : v_sdate := TRUNC (in_sdate, 'MI') - 15 / 1440;
But in the above calculation, I am not considering seconds, but I need to consider those also, because say I ran the eod at 23-oct-2012 12:23:13 then my start date in case of re run should start from 12:23:14 secs , how can I achieve that?
SQL> SELECT MAX (upd_time), MIN (upd_time), COUNT (serial) FROM (SELECT * FROM trans UNION ALL SELECT * FROM trans_archive); MAX(UPD_T MIN(UPD_T COUNT(SERIAL) --------- --------- ------------- 23-OCT-13 01-JAN-11 5289261
I need to calculate seconds between MAX (upd_time) and MIN (upd_time) and then calculate trans/sec. Number of trans COUNT (serial).
SQL> desc trans; Name Null? Type ----------------------------------------- -------- ---------------------------- SERIAL NOT NULL NUMBER(11) UPD_TIME NOT NULL DATE MESSAGE NOT NULL VARCHAR2(255 CHAR) ENTITY_TABLE NOT NULL VARCHAR2(32 CHAR) ACTION NOT NULL VARCHAR2(12 CHAR)
[code]....
trans_archive the same DDL.
my first try to get intervall between max and min date in secons:
SQL> SELECT EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 24 * 60 * 60 + EXTRACT (HOUR FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 60 * 60 + EXTRACT (MINUTE FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 60 + EXTRACT (SECOND FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) DELTA FROM (SELECT * FROM TRANS UNION ALL SELECT * FROM TRANS_ARCHIVE); SELECT EXTRACT (DAY FROM (MAX (UPD_TIME) - MIN (UPD_TIME))) * 24 * 60 * 60 * ERROR at line 1: ORA-30076: invalid extract field for extract source
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade. The field trade_date belongs to the moment of the trade. The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE'). The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600. -> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800. -> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
Our database was generating archivelog(50MB) every 30seconds! I think this is not normal because what I did is open our database, I was the only one who is connected, I'm not running anything, but our database is still generating archivelogs!
Our redo logs: 6groups 3members.
This are the things I saw on our alert logs: - advanced to log sequence - cannot allocate new log, sequence - checkpoint not complete - private strand flush not complete
What I did is change the log mode of our database to noarchivelog then open the database, then returned it to archivelog mode then that fixed the problem. But the thing is after 6hours its abnormal behavior goes back again.
I'm using Apex 4.2.1 against Oracle 11gR2 and mod_plsql.create a date picker item that allows users to select seconds as well as hours and minutes.
I have searched this Forum and see that others have asked this question. The answers have all been "No". However, I've seen no such question since version 4.1 has been released, and so, am hoping there now is a way to do this.
I have adjusted the Date Format field to be "DD-Mon-YYYY HH24:MI:SS", both under the date picker item itself as well as under the "Global" parameters section of my application. All to no avail. The date picker shows only hours and minutes for the time portion.
I have written a stand alone (Java SE 1.6) JMS client program to consume AQ's messages via Oracle JMS API (aqapi.jar). The queue is a multiple consumer queue, and i just created one subscriber on it. My JMS client program receives messages asynchronously by setting the MessageListener using the setMessageListener method.
Watching the work of the program, I found significant delays in receiving messages that are up to several seconds. When I turned on the diagnostic trace, I found that in the absence of messages listener (AQjmsSimpleScheduler) gradually increases the delay time up to 15 seconds:
Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsSimpleScheduler.feedData: Got a non null message, the sleep time is reset to 0 Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.run: sleep 0 millisecond. Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsSimpleScheduler.feedData: Got a null message, the sleep time is doubled to 1000 Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.run: sleep 1000 millisecond. Thread-1 [Mon May 06 22:14:23 MSK 2013] AQjmsListenerWorker.doSleep: try to wait for 1000 milliseconds
[code]....
Thus, in the worst case, the delay between placing the message in the queue and receiving it by the JMS client is 15 seconds.
Can I control this latency? For example, I would like to explicitly set the levels of the time delays.
I have a multimaster advance replication environment and we have less than 10 transaction per day...I want to propagate data as soon as possible like sync if I setup the schedule push to propagate transaction every 5 seconds as below
in other hand ,book(I mean Advance Replication) has written that for simulating continuous push we should setup it as below so it will propagate transaction every 1 minute.
My second question is:I know interval >= 'SYSDATE + (1/144)' means every 10 minutes a job will start and delay_seconds => 1200 means each job remain aware for 20 minutes, but I can't understand the logic?why it can simulate 1 minute propagation?
I need to move one of my LMT tablespace to DMT, Can I do it , I know that the DMT's are depreciated since Oracle 9i but still need to know this.I am trying the below mentioned method to achieve the same.
SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');But I am facing the below mentioned error: BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;
* ERROR at line 1: ORA-10616: Operation not allowed on this tablespace ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216 ORA-06512: at line 1
I have a table datatype number (12,10) that I am reading out of. I am taking the value from this source table and inserting it into a destination table of datatype number (12,15).
I do not have the ability to alter the tables. How can i convert this number so i can insert. I am currently getting the error "ORA-01438: value larger than specified precision allowed for this column"
I am trying to use the to_number, but it not working. How can i format this number field so i can read it from source where i have number (12,10) and insert it successfully in a higher precision table of number(12,15)
Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.
My requirement is to converts row to Column information and sum of column in Weight kg in below manner Data in columns are not of same nature they can keep on changing
Item Supplier Consumer Package DWP_NO DWP_ED Quality_code Pakaging Material WEIght kg 80026020 13984 90225217 21385 1 1 3 Not Applicable 22-paper .001 90218863 16578 1 1 1 Not Applicable 04-low density polyethylene (0.002+.002+.002)=.008 90218349 14507 1 1 1 Not Applicable 05-polypropylene,22-paper (.02+.05) =.07 40221108 21519 3,2,1 1 2 IKEA-CB-60 22-paper (.32+0.3+0.45)=1.07
I have one query in SQL server. I want to convert that query to Oracle. Well i am not that good with writing queries in Oracle. Following is the query in SQL server.
DECLARE @StartYear AS INT = 2010; DECLARE @EndYear AS INT = 2014;
WITH years AS (SELECT YYYY = @StartYear UNION ALL SELECT yyyy + 1 FROM years WHERE yyyy < @EndYear) [code].......
I have to use the "4*3600" in order to get the date to show up correctly, but even then the date sometimes comes up wrong. If the date occurs in the morning, then the date shows up as the previous day. I am sure this is probably due to the offset I am adding in the above formula. If I don't add the 4 hour offset, then the date shows up 4 hours off.
is there any licensed version tool available to convert objects from sql server to oracle especially procedures? I have tried with open source tools but that tool didn't convert properly.
DELETE FROM GR_GUEST WHERE GUEST_NAME='Ambassador Jack Binns bring Patrick Clawson' AND RESERVATION_ID_FK=21635
It gives an ORA-01460 which is "unimplemented or unreasonable conversion requested"Removing the " AND RESERVATION..." gives the same error.
The problem is that GUEST_NAME is a VARCHAR (150) and RESERVATION_ID_FK is an INT so I don't see where any conversion is coming in.I had changed the name a little bit as it used to a (, ) and a : in the GUEST_NAME. I thought that might be causing the issue so I removed them with a REPLACE through an UPDATE query. However, the record still needs to be deleted.
It should be a simple query as it happens on this table all the time, so I don't know why this one is different.
This is unix time where 1075329297 is seconds 572 is milliseconds.
first il store this time in oracle database.when i am retrieving it i want this date into yyyy-dd-mm format. Is it possible to do it in oracle.(using convert function) or is there some other way?
I have 2 tables.The column in table A is number and Column in table B is a varchar2 datatype.I have to use the Column of table B as a filter to column of Table A.Below is the example.
create table A(Col1 number); Inert into A values(1); Inert into A values(2); Inert into A values(3); Inert into A values(4);
Create table B(Col1 Varchar2(100)); Insert into b value ('1,2,3');
Select * from A where col1 in (select col1 from b) Error: Invalid Number
Is there a way to convert the varchar to number.The varchar field have multiple characters (numbers) seperated by commas.
I have a problem i need to convert a blob column contains pic file to long row
i had many tries but no one succeeded
----------------------------------- Source table | destination table id number | id number img blob | img long raw ------------------------------------
1 - INSERT INTO destination table SELECT id , img FROM Source table WHERE ROWNUM < 2
i have this error ORA-22835 Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 146092, maximum: 2000)
2 - INSERT INTO destination table SELECT id , dbms_lob.SUBSTR(img,0,2000) FROM Source table WHERE ROWNUM < 2