 

        select v.SQL_TEXT,
               v.PARSING_SCHEMA_NAME,
               v.FIRST_LOAD_TIME,
               v.DISK_READS,
               v.ROWS_PROCESSED,
               v.ELAPSED_TIME,
               v.service
          from v$sql v
    where to_date(v.FIRST_LOAD_TIME,&#39;YYYY-MM-DD hh24:mi:ss&#39;)&gt;ADD_MONTHS(trunc(sysdate,&#39;MM&#39;),-2)

`where` clause is optional. You can sort the results according to FIRST_LOAD_TIME and find the records up to 2 months ago.

For recent SQL:

    select * from v$sql

For history:

    select * from dba_hist_sqltext

You can use this sql statement to get the history for any date:

    SELECT * FROM V$SQL V where  to_date(v.FIRST_LOAD_TIME,&#39;YYYY-MM-DD hh24:mi:ss&#39;) &gt; sysdate - 60


I&#39;m facing the problem of designing methods that with performs network I/O (for a reusable library). I&#39;ve read this question

[c# 5 await/async pattern in API design][1]

and also other ones closer to my issue.

So, the question is, if I want provide **both async and non-async** method how I&#39;ve to design these?

For example to expose a non-async version of a method, I need to do something like

    public void DoSomething() {
      DoSomethingAsync(CancellationToken.None).Wait();
    }

and I feel it&#39;s not a great design. I&#39;d like a suggestion (for example) on how to define private methods that can be wrapped in public ones to provide both versions.

  [1]: https://stackoverflow.com/questions/6528517/c-sharp-5-await-async-pattern-in-api-design

Write a well designed async / non-async API

I understand that the &quot;java&quot; in a dalvik vm is different from Java se in terms of API and architecture etc. But the syntax has always been the same. (Please correct me if I am wrong)

Now the support of [lambda expression in java 8][1] is about to be released, will android java follow it? did google or aosp promise on making dalvik consistent with java se?


UPDATE: Now as in Android 4.4 Kit Kat (API 19), Java 7 syntax has been OFFICIALLY supported.[Source from android.com][2]


  [1]: http://openjdk.java.net/projects/lambda/
  [2]: http://tools.android.com/tech-docs/new-build-system/user-guide#TOC-Using-sourceCompatibility-1.7

will android java support lambda expression in java 8?

An update SQL query was executed on the server, which caused many problems later.

How can I get the list of update queries executed in last 2 months, so that I can trace the exact problematic SQL query?

Find out the history of SQL queries

<p>An update SQL query was executed on the server, which caused many problems later.</p>
<p>How can I get the list of update queries executed in last 2 months, so that I can trace the exact problematic SQL query?</p>


I am using Linux, Oracle10g.
I have created one user called test. and granted create session and select any dictionary permission to the same user.

i also granted sysdba and sysoper roles to the same users.

Now i want to display all the privileges and roles granted to the user. 
I found following query but it shows only create session and select dictionary privileges.

    select privilege 
    from dba_sys_privs 
    where grantee=&#39;SAMPLE&#39; 
    order by 1;

please help to resolve the issue.

Thanks

 

How to find the privileges and roles granted to a user in Oracle?

I am having a hard time converting stored procedures from SQL Server to Oracle to have our product compatible with it.

I have queries which returns the most recent record of some tables, based on a timestamp :

**SQL Server:**

    SELECT TOP 1 *
    FROM RACEWAY_INPUT_LABO
    ORDER BY t_stamp DESC

=&gt; That will returns me the most recent record

But **Oracle:**

    SELECT *
    FROM raceway_input_labo 
    WHERE  rownum &lt;= 1
    ORDER BY t_stamp DESC

=&gt; That will returns me the oldest record (probably depending on the index), regardless the `ORDER BY` statement!

I encapsulated the Oracle query this way to match my requirements:

    SELECT * 
    FROM 
        (SELECT *
         FROM raceway_input_labo 
         ORDER BY t_stamp DESC)
    WHERE  rownum &lt;= 1

and it works. But it sounds like a horrible hack to me, especially if I have a lot of records in the involved tables.

What is the best way to achieve this ?

How to use Oracle ORDER BY and ROWNUM correctly?

I have a query that looks something like this:
    
    SELECT DISTINCT share.rooms
    FROM Shares share
      left join share.rooms.buildingAdditions.buildings.buildingInfoses as bi
	... //where clause omitted
	ORDER BY share.rooms.floors.floorOrder, share.rooms.roomNumber,
             share.rooms.firstEffectiveAt, share.shareNumber, share.sharePercent

Which results in the following exception:

    Caused by: org.hibernate.exception.SQLGrammarException: ORA-01791: not a SELECTed expression

If I remove the DISTINCT keyword, the query runs without issue.  If I remove the order by clause, the query runs without issue.  Unfortunately, I can&#39;t seem to get the ordered result set without duplicates.  

Using the DISTINCT keyword causes this error: not a SELECTed expression

I get this when running a lot of liquibase-scripts against a Oracle-server. SomeComputer is me.

&lt;!-- language: none --&gt;

    Waiting for changelog lock....
    Waiting for changelog lock....
    Waiting for changelog lock....
    Waiting for changelog lock....
    Waiting for changelog lock....
    Waiting for changelog lock....
    Waiting for changelog lock....
    Liquibase Update Failed: Could not acquire change log lock.  Currently locked by SomeComputer (192.168.15.X) since 2013-03-20 13:39
    SEVERE 2013-03-20 16:59:liquibase: Could not acquire change log lock.  Currently locked by SomeComputer (192.168.15.X) since 2013-03-20 13:39
    liquibase.exception.LockException: Could not acquire change log lock.  Currently locked by SomeComputer (192.168.15.X) since 2013-03-20 13:39
            at liquibase.lockservice.LockService.waitForLock(LockService.java:81)
            at liquibase.Liquibase.tag(Liquibase.java:507)
            at liquibase.integration.commandline.Main.doMigration(Main.java:643)
            at liquibase.integration.commandline.Main.main(Main.java:116)


Could it be that the number of simultaneous sessions/transactions are reached? Anyone has any ideas?

Liquibase lock - reasons?

Hi I have simply select and works great:


    select &#39;CARAT Issue Open&#39; 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,&#39; (&#39;)=0 then 100 else instr(gcrs.stream_name,&#39; (&#39;)-1 end) ISSUE_DIVISION,
 
    case when gcrs.STREAM_NAME like &#39;NON-GT%&#39; THEN &#39;NON-GT&#39; ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
 
 
    from table(f_carat_issues_as_of(&#39;31/MAR/2013&#39;)) i
    inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
 
    where UPPER(ISSUE_STATUS) like &#39;%OPEN%&#39;

Now I want to call 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 ? 

---

my full code:

    select &#39;CARAT Issue Open&#39; 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,&#39; (&#39;)=0 then 100 else instr(gcrs.stream_name,&#39; (&#39;)-1 end) ISSUE_DIVISION,

    case when gcrs.STREAM_NAME like &#39;NON-GT%&#39; THEN &#39;NON-GT&#39; ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2


    from table(f_carat_issues_as_of(&#39;31/MAR/2013&#39;)) i
    inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id

    where UPPER(ISSUE_STATUS) like &#39;%OPEN%&#39; and 

    CASE WHEN ISSUE_DIVISION = ISSUE_DIVISION_2 THEN 
         CASE WHEN  ISSUE_DIVISION is null then &quot;Null Value found&quot;
         Else 1 End
    ELSE 0 END As Issue_Division_Result

but I get error on line:
    ELSE 0 END As Issue_Division_Result

ORA-00920: invalid relational operator :(
     
       

If statement in select (ORACLE)

Is it possible to do an UPDATE query in MySQL which updates field value only if certain condition is met?  Something like this:

    UPDATE test
    SET
        CASE
            WHEN true
            THEN field = 1
        END
    WHERE id = 123

In other words:

    UPDATE test
    SET
        something = 1,        /*field that always gets updated*/
        CASE
            WHEN true
            THEN field = 1    /*field that should only get updated when condition is met*/
        END
    WHERE id = 123

What is the proper way to do this?

MySQL: update a field only if condition is met

It seems I have big problems with *conditional queries*.

I have to do a *conditional update*. I write here what I would like to do:

     IF(SELECT tipo FROM abbonamento WHERE idU = 17) = &#39;punti&#39; THEN
         UDPATE abbonamento SET punti = punti - 1 
     ELSE
         UPDATE abbonamento SET bonus = bonus - 1

Obviously this doesn&#39;t work.  
Any idea?

Update MySQL with if condition

    UPDATE dbo.TestStudents  
    SET LASTNAME = 
    ( CASE  
    WHEN (LASTNAME = &#39;AAA&#39;) THEN &#39;BBB&#39; 
    WHEN (LASTNAME = &#39;CCC&#39;) THEN &#39;DDD&#39; 
    WHEN (LASTNAME = &#39;EEE&#39;) THEN &#39;FFF&#39; 
    ELSE  (LASTNAME)
    END )
The statement work for the purpose but the else condition scan through every record in the table. Is there any way I can leave the unaffected rows as they are?


I want to use CASE statement to update some records in sql server 2005

I am trying to create a query for entity framework that will allow me to take a list of ids and update a field associated with them. 

Example in SQL:
 
    UPDATE Friends
    SET msgSentBy = &#39;1234&#39;
    WHERE id IN (1, 2, 3, 4)

How do I convert the above into entity framework?

Update Multiple Rows in Entity Framework from a list of ids

I am trying to understand how to UPDATE multiple rows with different values and I just don&#39;t get it. The solution is everywhere but to me it looks difficult to understand.

For instance, three updates into 1 query:

	UPDATE table_users
    SET cod_user = &#39;622057&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;student&#39;
        AND cod_office = &#39;17389551&#39;; 

	UPDATE table_users
    SET cod_user = &#39;2913659&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;assistant&#39;
        AND cod_office = &#39;17389551&#39;; 

	UPDATE table_users
    SET cod_user = &#39;6160230&#39;
        , date = &#39;12082014&#39;
    WHERE user_rol = &#39;admin&#39;
        AND cod_office = &#39;17389551&#39;; 

I [read][1] an example, but I really don&#39;t understand how to make the query. i.e:

    UPDATE table_to_update
    SET cod_user= IF(cod_office = &#39;17389551&#39;,&#39;622057&#39;,&#39;2913659&#39;,&#39;6160230&#39;)
        ,date = IF(cod_office = &#39;17389551&#39;,&#39;12082014&#39;)
    WHERE ?? IN (??) ;

I&#39;m not entirely clear how to do the query if there are multiple condition in the WHERE and in the IF condition..any ideas?

  [1]: http://www.karlrixon.co.uk/writing/update-multiple-rows-with-different-values-and-a-single-sql-query/

MySQL - UPDATE multiple rows with different values in one query

I was trying to delete PostgreSQL user:

    DROP USER ryan;

I received this error:

&gt;     Error in query:
&gt;     ERROR: role &quot;ryan&quot; cannot be dropped because some objects depend on it
&gt;     DETAIL: privileges for database mydatabase

I looked for a solution from these threads:

 - https://stackoverflow.com/questions/3023583/postgresql-how-to-quickly-drop-a-user-with-existing-privileges
 - https://stackoverflow.com/questions/32988702/how-to-drop-user-in-postgres-if-it-has-depending-objects

Still have the same error.

This happens after I grant all permission to user &quot;ryan&quot; with:

    GRANT ALL PRIVILEGES ON DATABASE mydatabase ON SCHEMA public TO ryan;

Content Type	Original Author	Original Content on Stackoverflow
Question	sumit vedi	View Question on Stackoverflow
Solution 1 - Oracle	bonsvr	View Answer on Stackoverflow
Solution 2 - Oracle	grokster	View Answer on Stackoverflow
Solution 3 - Oracle	Ashish Pandey	View Answer on Stackoverflow

Find out the history of SQL queries

Oracle Problem Overview

Oracle Solutions

Solution 1 - Oracle

Solution 2 - Oracle

Solution 3 - Oracle

will android java support lambda expression in java 8?

Write a well designed async / non-async API

Attributions