SQL & PL/SQL :: Query To Find History In Oracle 11?

Jul 29, 2011

find the recent SQL history from Oracle 11. could the following SQL retrieve all SQL history happened in last 5 mins?

SELECT h.user_id,
s2.username,
h.sample_time,
s1.sql_text
FROM v$active_session_history h,
v$sqlarea s1,

[code]....

View 3 Replies


ADVERTISEMENT

How To Find SQL Text History

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

Which Query Give Lock Conflicts History In Database

Aug 29, 2013

I would like to know 2 days back lock conflicts session information which user held the lock,sql text,when the lock released. any sql query or view in oracle 11g db.

View 1 Replies View Related

Server Administration :: Query To Find Hostname For Oracle Session

May 20, 2010

How I can find the hostname for an oracle session id?

View 4 Replies View Related

SQL & PL/SQL :: How To Find The Count In The Query

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

SQL & PL/SQL :: Query To Find MIN Date

Nov 5, 2010

building SQL query to get the result as shown below.

Create Table Temp

CREATE TABLE TEMP
(
CASEID NUMBER,
SATUS VARCHAR2(1 BYTE),
TRANS_DATE DATE
)
Insert data

[Code]...

I want to build a query which should give output as shown below. Basically i want to select those rows which having minimum trans_date for a given CASEID & Status.

OUTPUT:

CASEIDSATUSTRANS_DATE
100A11/02/2010
100B11/07/2010
100A11/12/2010
200A11/02/2010
200B11/07/2010

View 6 Replies View Related

SQL & PL/SQL :: Query To Find Usernames From Their IDs

Oct 23, 2012

the below requirement.

I have 2 tables, first one MAIN_T, which contains, user_id and username.

The second table is a relationship table which contains the referal details.

The requirement is to convert the user_id and frnd_id from RELATION_T to their respctive names using MAIN_T.

-- Test Case

CREATE TABLE main_t (user_id NUMBER, username VARCHAR2(10));
CREATE TABLE relation_t(user_id NUMBER, frnd_id NUMBER);

INSERT INTO main_t VALUES(1, 'u1');
INSERT INTO main_t VALUES(2, 'u2');
INSERT INTO main_t VALUES(3, 'u3');
INSERT INTO main_t VALUES(4, 'u4');
INSERT INTO main_t VALUES(5, 'u5');

[Code]....

I have written this query, which gives me the expected output.

SELECT membr.username member_name , frnd.username friend_name
FROM
main_t membr,
main_t frnd,
relation_t rltn
WHERE
rltn.user_id=membr.user_id
AND rltn.frnd_id=frnd.user_id
/

MEMBER_NAM FRIEND_NAM
---------- ----------
u1 u2
u2 u3
u5 u4
u1 u5

4 rows selected.

a better way for this requirement.

View 2 Replies View Related

PL/SQL :: Query To Find Single Row?

Oct 5, 2012

i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.

ID Course
103812     CFH     
102968     REP     
103812     DFH     
102968     DFH     
103071     DFH     
102968     CFH

View 5 Replies View Related

SQL & PL/SQL :: Query To Find Empty Column?

Aug 3, 2010

I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.

I need a query that should return the column which is empty but its next and previous column is not empty.

View 13 Replies View Related

SQL & PL/SQL :: Query To Find Last 6 Month Records?

Aug 23, 2010

SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID

There is no date column in table using sysdate alone need to retrieve last 6 month records

how to use in where condition

View 13 Replies View Related

SQL & PL/SQL :: Query To Find The Number Of Alphabets?

Apr 20, 2013

I need to find out that assume i have a table having 2 column

Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani

I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets

View 6 Replies View Related

SQL & PL/SQL :: Query To Find Relevant Table Name

Jan 16, 2013

i need a SQL query which should return me relevant table names. i.e. if there is table 'EMP' , then query should give table names with below result:

EMP
EMP_1
EMP_2
EMP_3
EMP_4

i.e. All tables which is starting with EMP (No Hardcoding of table, It should be dynamic way).I know we can achieve through SELECT * FROM USER_ OBJECTS WHERE OBJECT_NAME LIKE 'EMP%'. But here object_name i will passing dynamically.

View 10 Replies View Related

Forms :: Triggers Regarding Find Query?

Oct 24, 2013

I am working on a form, whose one block is query_find, so on clicking find button it will open another block.the find block contain 4 column and second block contain around 10 column.but the column present in find block are associated to standard tables and the column present in block-2 are from custom tables.there is a query which link those column.

I am not sure where to use that query, so that it will take input from the find block and then populate the result into the block-2.

View 13 Replies View Related

SQL & PL/SQL :: How To Find The Query Which Is Taking Most Time

Jul 4, 2011

I want to know how I can find which query is taking more time , for example some query's are run from unix, java and from toad,sqlplus. and one query is taking much more time to execute, so how i can get that query and all the details.

View 2 Replies View Related

SQL & PL/SQL :: Analytical Query To Find First And Last Value In Date?

Jul 2, 2013

My table has two date columns EFF_DT which is the start date and TERM_DT is the end date. The EFF_DT of the next record should be the next date of the TERM_DT record.

My table looks like this.

Input Table:
-----------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1331-Mar-13
Mem1ABC1-Apr-1331-May-13
Mem1ABC1-Jun-1330-Sep-13
Mem1ABC15-Oct-1331-Dec-13
Mem1ABC1-Jan-1431-Mar-14
Mem1XYZ1-Apr-1430-Jun-14
Mem1XYZ1-Jul-1431-Dec-14

Expected Output:
----------------
CK_IDPI_IDEFF_DT TERM_DT
Mem1ABC1-Jan-1330-Sep-13
Mem1ABC15-Oct-1331-Mar-14
Mem1XYZ1-Apr-1431-Dec-14

In the fourth record, the effective date should be 1-Oct-13 which is the next date to the last TERM_DT 30-Sep-13.As the is the break in the date, the output should show 15-Oct-13 sa the second start date.

Note: Refer to the PI_ID columns, there is a break in the date for the sale PI_ID 'ABC'.

Here I am trying to generate a pseudo column, so that the table with the pseudo column looks like as shown below. and I can use first_value and LAST_value by partitioning on the pseudo column to get the desired output.

1) CNT_VAL is the pseudo column:
-----------------------------
CK_IDPI_IDEFF_DT TERM_DT CNT_VAL
Mem1ABC1-Jan-1331-Mar-131
Mem1ABC1-Apr-1331-May-131
Mem1ABC1-Jun-1330-Sep-131

[code].....

My Query :
----------

I not getting the desired output here as the value in pseudo column is 3.

select CK_ID, PI_ID,EFF_DT,TERM_DT,
(case
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) = 0 then to_char(case_CONT)
when case_CONT - LAG(case_CONT,1) over (ORDER BY EFF_DT) <> 0 then to_char(LAG(case_CONT,1) over (ORDER BY EFF_DT) + 1)
else to_char(nvl(case_CONT,0))

[code].....

Scripts:
--------------------
Create table lead_test(
CK_ID varchar2(10),
PI_IDvarchar2(10),
EFF_DTDate,
TERM_DT date);

[code].....

View 1 Replies View Related

SQL & PL/SQL :: Query To Find The Time Difference

Aug 21, 2010

I need to query a table to get the time difference based on different dates.

SAMPLE DATA:

JOB ID start_time end_time
1 201008190056 19/08/2010 01:23:12
2 201008190123 19/08/2010 01:50:12
2 201008200045 19/08/2010 01:50:12
3 201008070345 7/08/2010 04:50:12
3 201008070345 7/08/2010 04:50:12
1 201008070003 7/08/2010 00:30:12

in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as

01:23:12-00:56:00 and the difference should be in minutes.

for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.

View 2 Replies View Related

PL/SQL :: Query To Find The Sum Of Different Groups Of Same Column

Feb 23, 2013

I have a table as follows:

customers
------------------------------------------------------------------------------------------
custid credit amt month
--------------------------------------------------------------------------------------------
001 C 2000 Jan-2012
001 D 5000 Feb-2012
001 C 3000 Mar-2012
001 C 3000 Apr-2012
001 D 7000 May-2012

I Have to write a single query to calculate the sum of credit and sum of debit value separately.

View 8 Replies View Related

SQL & PL/SQL :: Query To Find Is Employee A Manager

Feb 26, 2013

Refer the table below and provide me the query to find given employee is manager or not.

Refer the table below, emp#100 is manager, emp#300 is not a manager.

<emp> <emp_name> < manager >
100Smith null
200Ram 500
300Shankar 500
400Madhur 200
500Suman 100
600Sundar 100

View 2 Replies View Related

PL/SQL :: Query To Find Table And Column Name By Using Value

Sep 3, 2013

I google to find the Table Name and Column Name by having a value(Number/String). And my where clauses are  

where owner NOT IN ('SYS','SYSTEM') and      data_type IN ('CHAR','VARCHAR2','NUMBER') 

My query as follows 

select a.owner, c.column_name, c.data_type, c.owner, c.table_namefrom dba_objects a, all_tab_cols c where a.owner NOT IN ('SYS','SYSTEM') and where c.owner NOT IN ('SYS','SYSTEM') and where c.data_type IN ('CHAR','VARCHAR2')order by a.owner

View 3 Replies View Related

Query To Find Out Whether Exactly 2 Columns Are Used In Any Index

Dec 17, 2012

Suppose i have two columns ID and Status (or any number of columns ) belongs to table customer. Now I want to create below index

Ex. create index test1 on customer (ID , Status )

But before creating this index i want to check that whether there is already index on these 2 columns ? May be by other name ?

View 23 Replies View Related

Query To Find Current Queries Not Using Indexes?

Mar 29, 2012

hint of which DBA views we need to query to find current running queries which are not using indexex?

I suppose v$session is one , what else we need to look to?

View 1 Replies View Related

SQL & PL/SQL :: Query To Find Number Of Nulls For Each Record

Aug 18, 2013

We have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.

provide a simple query using case in ORDER BY clause.

ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP

We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as

1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME

for the following three records number of NULL values are same. but positions are different.

0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP

Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17

Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as

ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL

View 15 Replies View Related

SQL & PL/SQL :: Find Query To Create Gap In Continuous Series?

Jan 16, 2013

I need to find a query to create a gap in continuous series. So that i can fill this gap with a new record.

Attached here is the test sql.

Sample Data is

test@orcl>select * from sales_mst;

NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N

[code]...

10 rows selected.

test@orcl>select * from sales_dtl;

NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1249 12 55 450

[code]...

16 rows selected.

Required Out Put

test@orcl>select * from sales_mst;

NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N
1251 08-JAN-13 N
1250 08-JAN-13 R
1253 08-JAN-13 N
1252 09-JAN-13 R
1254 09-JAN-13 N
1255 09-JAN-13 N

10 rows selected.

test@orcl>select * from sales_dtl;

NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1251 12 55 450
1251 11 45 600

[code]...

16 rows selected.

Update or move only those records which have Type 'N'. I have tried the following query but it is giving me an error.

test@orcl>ALTER TABLE
sales_dtl
DISABLE CONSTRAINT
fk_sales_dtl_no;
test@orcl>update sales_mst set no=no+1 where type='N' and vdate > '07-JAN-13';
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_SALES_MST_NO) violated

View 15 Replies View Related

SQL & PL/SQL :: Query To Find Out Employees Who Are All Joined Before Manager

Jun 27, 2011

Query to find out employees who are all joined before Manager.

For Example the Table may look like.

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E106Manager 11-Oct-08
E107Programmer20-Nov-09
E108Coordinator12-Dec-10
E109DB Admin10-Feb-07
E110DB Analyst10-Aug-05

The out put must be..

EMPIDDesignationDateJoined
E101Programmer10-Jan-04
E102Programmer22-Mar-04
E103Analyst 14-Jan-05
E104Designer20-Dec-06
E105Tester 20-Nov-07
E109DB Admin10-Feb-07

View 4 Replies View Related

SQL & PL/SQL :: Query To Find Count Of Delimiter In Column Val?

Jun 29, 2010

Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.

Table:

CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)

Data:

INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;

Required output:

num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4

View 5 Replies View Related

SQL & PL/SQL :: Query To Find Values Are Not Present In Table

Nov 28, 2010

I have table called test script for table is given below

create table TEST
(
col1 Number
);

[Code]...

For these values Query is not returning values(3 and 4). So i want generic query to get this result. I am working on it but not able to generate proper query.

View 2 Replies View Related

PL/SQL :: How To Find Special Characters In A Single Query

Sep 12, 2012

My query is "How to find all special characters like (%$*&@,;'/+- etc. in a single query?"

e.g.

A_MIR
A%SIM
A*SIM
A)SIM

View 6 Replies View Related

PL/SQL :: Query To Find Number Of Days Across Each Month

Oct 1, 2012

We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.

We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.

We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below

Number of Working days in March is 31 - 3 + 1 = 29

Allowance A1 = (300 * 29 )/31

Number of Working days in April is 5 ( this also needs to find logical I am guess )
Allowance A2 = (300 * 5 )/30

Then A1 + A2.

The A(n) would be the total allowance where provided the number of month across.

View 10 Replies View Related

SQL & PL/SQL :: Query To Find User Who Did Not Login From Past 3 Years?

Nov 2, 2012

I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.

The requirement is to find those users who have not logged-in in the last 3 years.

-- Test Case

DROP TABLE application_1;
DROP TABLE application_2;
CREATE TABLE application_1
(
seq NUMBER ,
user_id VARCHAR2(30),

[code].....

Using the below query I'm able to get the desired output.

SELECT user_id, MAX(login_date) last_login_date FROM (
SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id
UNION ALL
SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id
) GROUP BY user_id

[code].....

View 10 Replies View Related

SQL & PL/SQL :: Query To Find List Of Tables Contains Particular Data In Schema?

Feb 3, 2012

I want to list the tables in a schema which contains a particular value or data.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved