SQLPLUS Select Statement To Get Last Failed Time From Each User
Aug 13, 2010
I'm new to the sqlplus,
User ID Failed_timestamp
----------------------------------
userA 20100801 11:22:33
userB 20100802 11:22:33
userA 20100802 12:22:44
userA 20100803 13:34:55
userC 20100803 20:21:23
userB 20100804 16:34:56
I wanna to get the last failed time from each user. what is the select statement?
my expected result will be:
userA 20100803 13:34:55
userB 20100804 16:34:56
userC 20100803 20:21:23
View 3 Replies
ADVERTISEMENT
Aug 27, 2009
My company use a sybase database that runs business jobs. Currently we run SQL queries from Perl to gather time information on the jobs. Now we have an application that is using Oracle. The server it is on, doesn't have perl, so I am using a shell script to login to sqlplus and run a query for a job and it's end time. I have accomplished this. However, here is the 2 problems I am having.
1. The query reults are returned in Scientific time, I'm able to convert that to EPOCH time in the SQL syntax, however, it comes back with a 13 digit time, instead of 10. The last 3 digits are zero. How can you remove the last 3 digits in the query or convert the 13 digits to Human Time. Right now when you see the select statement, I am doing a to_char to get it to EPOCH time.
2. How to only show the latest time in the query and not show ALL job end times from it's past runs.
Here is my shell script, and I do realize this maybe a select statement syntax solution to one or both, but the UNIX time stamp is puzzling.
#!/usr/bin/sh
sqlplus -S username/password@JAWSPROD <<eof> myfile
set heading off feedback off verify off
select JAWS_APP.JAWSJOB.JOBNAME, to_char(JAWS_APP.JOBRUN.ENDTIME) from JAWS_APP.JAWSJOB, JAWS_APP.JOBRUN where JAWS_APP.JAWSJOB.JOBID = JAWS_APP.JOBRUN.JOBID and JAWS_APP.JAWSJOB.JOBNAME in ('pa_box_settle');
exit
View 1 Replies
View Related
Oct 21, 2011
I have a requirement while A4 printing..When printing each time user have to select file -> page setup -> size(A4)..THEN printing format become A4 Size. How to achieve this one without user have to go on setting.
View 1 Replies
View Related
Aug 3, 2010
I have installed oracle 10g on opensuse. After i logon as normal user and type sqlplus, it was display an error message as below:-
login: nv2am
Password for nv2am:
login: Configuration file does not specify default realm when parsing name nv2am
Last login: Tue Aug 3 16:33:19 from 10.69.20.25
Have a lot of fun...
Directory: /home/nv2am
Tue Aug 3 17:36:23 MYT 2010
nv2am@ARMSDEV:/home/nv2am> sqlplus
-ksh: sqlplus: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am> sqlplus "/ as sysdba"
-ksh: sqlplus: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am> set ORACLE_SID="ARMSDEV"
nv2am@ARMSDEV:/home/nv2am> svrmgrl
-ksh: svrmgrl: not found [No such file or directory]
nv2am@ARMSDEV:/home/nv2am>
View 1 Replies
View Related
Oct 7, 2010
I ran one long running update statement in my sqlplus session, where it took hours together since we have millions of rows on the table.lock the system and left the office , the next day I found my system is rebooted, hope the session would have performed the long running update statement.
Now, I need to issue a commit for the session, since my system is rebooted I don't have opened session with me. so, How to issue commit to the session, which I have issued long running update statement.
View 3 Replies
View Related
Mar 28, 2008
I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?
ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");
View 5 Replies
View Related
Sep 13, 2013
In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition
ON(source.DNO = target.DNO
AND source.BNO=target.BNO);
I thought that using UNIONALL for select statement of the schemas as below.
SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....
View 5 Replies
View Related
Apr 22, 2013
I am in the process of converting my skill in oracle and this time PRO*C from Windows to Linux. I have oracle 11g R2 installed on a UBUNTU (12.04) server and have installed the instant client as described in
[URL]
This has a query which will run in SQLPLUS but fails with PRO*C
:~/Projects/proc/proctest1$ proc INAME=proctest.pc SQLCHECK=NONE ONAME=proctest.c LNAME=proctest.lis LTYPE=long
Pro*C/C++: Release 11.2.0.3.0 - Production on Mon Apr 22 21:00:18 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
System default option values taken from: /home/neill.rutherford/instantclient_11_2/precomp/admin/pcscfg.cfg
PCC-W-02109, SQLCHECK=NONE is no longer supported.. using SYNTAX
[code]....
View 0 Replies
View Related
Apr 11, 2007
I'm trying to perform a SELECT INTO in a SQL*Plus script.the script i'm working towards to, looks something like this;
set term on
set echo off
set serveroutput off
set verify off
ACCEPT ReportName PROMPT 'Please enter a report name; '
PROMPT
PROMPT retrieving current settings for &ReportName:
select STARTDATE, ENDDATE, SUFFIX
into pStart, pEnd, pSuffix
from MyTable
where NAME = '&ReportName';
[code]....
I'm basically trying to perform a select into based in user input and then put the retrieved data back to the console with a prompt. Is this possible within a sql script running on SQL*Plus?
View 3 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Mar 2, 2012
I have a situation where when I login as a user to my DBvia sqlplus no service name it takes about 20 secs to connect.Yet when I login as a user with DBA privs it logs in immediately.
Is there something I can do to trace what is happneing behind scences to determine what the login delay may be..
View 9 Replies
View Related
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 Replies
View Related
Apr 28, 2011
While importing, I got the following error. How to resolve it?
IMP-00017: following statement failed with ORACLE error 1950: CREATE TABLE "table_name".....
View 2 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
Jun 17, 2009
I am trying to write an update statement which updates the User IDs in one table with the User IDs in another table. However I need to update statement to ignore any duplicates that are in the tables.
View 4 Replies
View Related
Oct 23, 2008
If I like to identify the executed time of a particular SQL Statement, beside v$sql, is there any other dictionary or lookup table that have this piece of information?
Why v$sql is not sufficient, because this is a recurrsive update statement which is regularly called, and thus the last_load_time is overwritten.
My archivelog had been purged due to our scheduled backup.
Is there any other way to identify when the particular SQL statement is executed?
View 4 Replies
View Related
Jun 3, 2010
The code written in PL SQL.I am using '&' operator to enter the values at run time in nested case statements.But when I chosen a value for case statement even though it is asking all the values which are not part of the selected case statement.I need only the values related to my choice in case statement.
choice1:= &CHOICE_NO1;
CASE(choice1)
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('** ENTER YOUR CHOICE TO PERFORM THE OPERATION: **');
DBMS_OUTPUT.PUT_LINE('** (1)INSERTION (2)DELETION (3) UPDATION **');
[code]....
that is my exact code.when I enter value 1 for choice1 and 2 for choice2 It should ask the values only for student_ scorecard. delete_ record (& STUDENTID); &studentid. But it is asking all the functions (student_scorecard.existing_marks(&ID,&SUBJECTNAME));(student_ scorecard. std_ major_ name (&STD_ID)) and procedures.I dont want that.
View 4 Replies
View Related
Jan 11, 2012
I would like to do a select to get the current date&time in this format:
2011-10-04T04:20:35.2313103+01:00
do you know how can I do that?
I want to convert this sql statement in this format.
View 12 Replies
View Related
Mar 22, 2013
simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?
View 4 Replies
View Related
Jan 17, 2013
Imagine a table below and I want to write a statement that shows time,name,ip,received and sent (SELECT *). (time, city, ip are primary key lets say).However there is the condition that I want:
It should be grouped for each CITY and I want to get max(RECEIVED) by descending order for each days. How can I do that?
select *
from table
group by city
having max(received)
Is it possible to get output without giving condition to other elements (IP, SENT... etc)
TIME- NAME- IP- RECEIVED- SENT-
10.01.2013,LON,10,2342 ,326
10.01.2013,PAR,10,563463 ,3
10.01.2013,LON,30,23412 ,574
11.01.2013,NY,20,36545 ,246357
11.01.2013,NY,40,23423 ,5
11.01.2013,TOK,40,45475 ,3453
11.01.2013,TOK,30,23423 ,574574
12.01.2013,LON,10,574 ,2342
[code]....
View 5 Replies
View Related
Nov 3, 2012
in one short i want to drop more than one user?
View 6 Replies
View Related
May 5, 2010
I have a question about select statements, as I am new to them and don't know how to work all the commands yet.
I'm making a select statement that is about half right... it is shown below:
select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);
I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.
View 2 Replies
View Related
Jun 15, 2007
decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,
After executing this statement. I get negative values if cancel time is null
I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time
View 1 Replies
View Related
Aug 23, 2008
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies
View Related
Sep 1, 2011
I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).
Ex:
Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;
Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?
View 12 Replies
View Related
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
View 4 Replies
View Related
May 6, 2011
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
View 5 Replies
View Related
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
