How To Find 95th Percentile
Jul 14, 2012
I have a table as:
ID VALUE
---------- ----------
1 2
1 14
1 41
1 42
1 42
1 48
1 56
1 65
1 86
1 93
2 15
2 36
2 42
2 48
2 62
2 75
2 75
2 85
2 85
2 99
There are millions of distinct ID and more than 5000 VALUE's(in ascending order) for each ID.
I need to find the VALUE stored at [int((95 / 100) * COUNT(ID)) - 1] column for each ID, where COUNT(ID) is the no of rows for each ID.
The result will be as
ID VALUE
---------- ----------
1 86
2 85
View 1 Replies
ADVERTISEMENT
Apr 18, 2013
I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:
- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU?
- What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?
View 4 Replies
View Related
May 17, 2013
I have a table with 50 columns, lets call it Table_A . There is a identical table called Table_B. The data in Table_B gets flushed every night and data from Table_A is moved to Table_B. A new set of data is inserted into Table_A. Now I need to find out which field value is changed and what is changed . A minus command can give the changed records, but how to get the what was prior value for each changed field . Here is what I am looking for
WITH TABLE_A AS ( SELECT 1 ID, 'JOHN' NAME, 'SALES' DIV FROM DUAL
UNION
SELECT 2 ID, 'MARRY' NAME, 'ACCOUNTS' DIV FROM DUAL
UNION
SELECT 3 ID, 'KIM' NAME, 'SERVICE' DIV FROM DUAL),
[code]...
what i want is something that will spit out this (only one record, because there is only one diff record found in the above query.)
ID, OLD_ID, NAME, OLD_NAME , DIV , OLD_DIV
-- ------ ---- -------- --- -------
3 3 KIM KIM SERVICE SALESThe above one is for sample purpose, The actual table has 50 fields, and except the ID field, all other field values might change.
View 10 Replies
View Related
Mar 28, 2011
i need more important to know about this query,below query in where clause i have one condition like 'pk1_value =New_Deliveries.DELIVERY_ID" but in my New_Deliveries table i don't have pk1_value column,how to find that column.
select MAX(DECODE(fndcatusg.format,'H', st.short_text,NULL,st.short_text, NULL))
FROM fnd_attachment_functions fndattfn,
fnd_doc_category_usages fndcatusg,
fnd_documents_vl fnddoc,
fnd_attached_documents fndattdoc,
fnd_documents_short_text st,
fnd_document_categories_tl fl
[code]....
View 3 Replies
View Related
May 30, 2012
i m create a table emp . all user of database have privileged to read and write the emp table. now How can identify that which user have insert row in emp file ?
View 4 Replies
View Related
Sep 25, 2012
I am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer
select to_char(last_day(sysdate),'DD-MON-YY')from dual
ans : 30-SEP-2012
But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.
View 2 Replies
View Related
Feb 14, 2012
I have to load 50 Million records in a table. I also need to ensure that no duplicate records enter. Business demand is that It may send 2 Million records today, 1 Million records tomorrow, 3 million records on the next day and so on...
I have loaded 6 Million records into the table, but onwards speed of loading records (with duplicate check) is decreasing.
View 16 Replies
View Related
Mar 28, 2012
I need to find the maxmimum value in a row, which consists 4 fields.
Columns: field_1, field_2, field_3, field_4
values : 2 , 4, 5, 1
the output should be field_3 =5
View 2 Replies
View Related
Feb 16, 2010
I want to find out the name of objects like procedures,functions which are using a specific column of table.
for example i have a table 'Orafaq' which has column 'Iss_Ide', i want to know which objects like procedures,functions,views are using column "Iss_Ide" of table orafaq
View 3 Replies
View Related
Apr 27, 2013
How to find out the no.of records in a cursor for loop without using counter variable. can i use %rowcount inside for loop?
declare
cursor dept_cur
is
select deptno,dname,loc
from dept;
[code]...
View 10 Replies
View Related
Oct 9, 2012
on 11g R2 on Win 2008
How to find ddl for any indexes built on MY_VW ?
View 6 Replies
View Related
Jul 25, 2012
how can I find SessionID of Oracle Job? In Oracle 9i Jobs were running under Sessions with SID = 0. This seems to be not the case under Oracle 11g.
View 10 Replies
View Related
Jul 22, 2011
A query to find the table fragmentation.
View 1 Replies
View Related
Aug 19, 2010
i have table like TWRCHAIN_WS_SUM. it is regularly updated by another object like procedure, function. how can i find which object is updating the particular table across the database.
View 4 Replies
View Related
Feb 26, 2009
From database server, I need to monitor the details about the sql statements which are being currenlt running in client machines.
I tried with V_$SQLTEXT view where I can only see the SQL statements, hash value,address,SQL_id. but I'm not able to get the user name,name the client machine .
find out these details?.Which Data Dictionary i need to use ?.
View 2 Replies
View Related
Mar 22, 2011
why am i getting this?
SQL> SELECT TABLE_NAME,owner from dba_TABLES where TABLE_NAME in ('BSNL_ACCOUNT_DATA','BSNL_SERVICE_DATA','BSNL_PKG_DETAILS');
TABLE_NAME OWNER
------------------------------ ------------------------------
BSNL_ACCOUNT_DATA BPREAD
BSNL_SERVICE_DATA BPREAD
but the table exists
SQL> select count(*) from BPREAD.BSNL_PKG_DETAILS;
COUNT(*)
----------
3704
SQL> select TABLE_NAME,owner from dba_TABLES where TABLE_NAME='BSNL_PKG_DETAILS';
no rows selected
why is it showing no rows even if the table exists
View 3 Replies
View Related
May 6, 2009
I have written a script to get count of each tables of a schema.
DECLARE CURSOR c1 IS
select distinct table_name from all_tab_columns where OWNER='PLAY';
countrows NUMBER;
[Code].....
View 1 Replies
View Related
Jul 22, 2011
this is my table
create table sequence ( id int not null primary key);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
View 1 Replies
View Related
Jul 18, 2011
How does the following underlined query works to find the nth highest salary
SELECT DISTINCT(A.SALARY) FROM EMPLOYEE A
WHERE &SALARY = (SELECT COUNT(DISTINCT(B.SALARY)) FROM EMPLOYEE B
WHERE A.SALARY<=B.SALARY);
View 3 Replies
View Related
May 27, 2011
I have SQL ID , sql with bind variables ("SYS_B_005")from AWR report which is taken last week.I need find the full exact sql text with bind variable value (sql ran 1 week before).
View 4 Replies
View Related
Sep 5, 2010
I'm connecting from SQL Server to ORACLE (ORACLE is linkedserver) and I'm trying to find out when ORACLE started and how long it has been running. Is there query or command in ORACLE that I can use to check this? Is there more than one option to find this out and is it same for all ORACLE versions or diffferent from version to version?
View 1 Replies
View Related
Mar 31, 2009
SQL> select count(*) from tab;
COUNT(*)
----------
63
I need to find out how many rows in each table.I've tried with cursor.
View 9 Replies
View Related
Oct 22, 2010
I am having to pull out all the dependencies for a column as below.
When I select a column in a table, it should list
1. All Views that use this column and table
2. All Stored procs that use this column and table
3. All Functions that use this column and table
4. All Pl/SQL scripts that use this column and table.
Is there a easy way of getting this information from the Oracle dba tables?
View 3 Replies
View Related
Jul 22, 2011
this is my table
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20),(22);
i need the answer to group the sequence like this
start_number | end_number | count
1 | 4 | 4
6 | 10 | 5
15 | 20 | 6
22 | 22 | 1
what should i do?
View 7 Replies
View Related
Apr 16, 2010
from the below string i want to find the count of the "<-" using a single query.
string is "aaaa<-bbbb<-ccccc<-ddddd<-eeeeee<-ffffff<-"
If not the query, pointer on using SQL functions.
View 3 Replies
View Related
Oct 20, 2010
How can i find out or list all the privileges that were given to a role with sqlplus cmd?
View 2 Replies
View Related
Feb 9, 2011
Say i have a Procedure or package or any other stored subprogram, I need a script say to which i can pass this Procedure/Package/View etc name and then the code should give me the data results as which all other stored subprograms are used within the particular stored program name which we mentioned in our WHERE or Predicate clause
View 4 Replies
View Related
Jul 1, 2013
How to find in the oracle10g database if there is any column which is not being used at all.If the data is there in the column,how to check when was it last populated.
View 3 Replies
View Related
Nov 9, 2011
To find out column in which table it is located.
View 1 Replies
View Related