select a.EV_ACTOR_NAME, round((b.total_transfer/(24 * a.Max_Throughput)) * 100) AVG_ThroughPut,round(((b.total_transfer/a.Max_Throughput) * 100 )) MAX_Throughput from (select distinct EV_ACTOR_NAME,((max(EV_TRANSFER_RATE) over ( partition by EV_ACTOR_NAME)) * 60 * 60) /1024 Max_Throughput from dprt_events where EV_ACTOR_NAME is not null ) a, (select distinct EV_ACTOR_NAME, (sum(EV_TRANSFER_SIZE) over( partition by EV_ACTOR_NAME)/1024/1024/1024) total_transfer from dprt_events where EV_ED_ID in (40,105,112) and EV_END_TIME between (sysdate - 20) -12/24 and sysdate - 20) b where a.EV_ACTOR_NAME = b.EV_ACTOR_NAME
I have a small requirement for report, i want to develop a report based on dates and employee performance.Suppose i give the parameter from date as 01'st jan 2011 and end date as 31'st Jan 2011 then employee hrs will be as summary as rows distributed among days with the given date range as columns.How to make each day as column automatically.
MISSING_DATES EMPNO ---------------------- ---------- 09-SEP-12 TO 11-SEP-12 7499 23-SEP-12 TO 26-SEP-12 7499 01-sep-12 TO 30-SEP-12 7521 01-sep-12 TO 30-SEP-12 7788
I am experiencing somewhat same issue...but have been unable to resolve it(new to Oracle) I am getting the infile from flat file(data dump from SQL) using sqlldr to upload data to the Oracle table...since the data is already in the flat file...I cannot do anything in the SQL to pre-format the data...
Sample of ERROR I am getting - Column CREATE_DATE which has date and time - happens to other date time columns also if remove the CREATE_DATE from Control file(happens to every single line of record): ========================================== Record 2: Rejected - Error on table LGCY_CHS.METS_CHS_USER_PRIV, column CREATE_DATE. ORA-01841: (full) year must be between -4713 and +9999, and not be 0
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel ( start_date, end_date ) AS SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE A | 2010 11 01 | 2011 02 11 B | 2011 01 20 | 2011 02 15 C | 2011 03 13 | 2011 04 10 D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]------------------------- B : ------[---]------------------------ C : --------------[---]---------------- D : ----------------[------]-----------
Travel Date : -[--------------------------------]--
BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]-----------
Result X1 : -[-]------------------------------- Result X2 : -----------[--]-------------------- Result X3 : -----------------------------[--]--
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003 12/3/2003 12/4/2003 12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
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