OraFAQ Forum: SQL & PL/SQL » How to select distinct sql

Home » SQL & PL/SQL » SQL & PL/SQL » How to select distinct sql_id together with clob column (11.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to select distinct sql_id together with clob column [message #630490]

Mon, 29 December 2014 04:55

kytemanaic
Messages: 55
Registered: February 2009

Member

Hi all

this sql is working


SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY dba_history_sql.sql_id) rn, dba_history_sql.* FROM(
SELECT dhs.SQL_ID, SQL_TEXT FROM DBA_HIST_SQLTEXT dhs
 JOIN DBA_HIST_SQLSTAT dhstat
ON dhstat.SQL_ID = dhs.sql_id
) dba_history_sql
) WHERE rn>=1 AND rn<=15;

this sql is not working


SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY dba_history_sql.sql_id) rn, dba_history_sql.* FROM(
SELECT DISTINCT dhs.SQL_ID, SQL_TEXT FROM DBA_HIST_SQLTEXT dhs
 JOIN DBA_HIST_SQLSTAT dhstat
ON dhstat.SQL_ID = dhs.sql_id
) dba_history_sql
) WHERE rn>=1 AND rn<=15;

found from http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_sql_semantics.htm#ADLOB45592

Quote:

Unsupported Use of LOBs in SQL
SQL Operations Not Supported include
SELECT DISTINCT

from http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4053.htm#REFRN23448

Quote:

DBA_HIST_SQLTEXT
This view captures information from V$SQL and is used with the DBA_HIST_SQLSTAT view.

How do I join both tables together since I cannot use DISTINCT?

thanks a lot!

Report message to a moderator

Re: How to select distinct sql_id together with clob column [message #630501 is a reply to message #630490]

Mon, 29 December 2014 06:38

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First select the distinct sql_id then join to get the text.

Maybe you should first learn how to format a SQL query to make it readable and maintainable.
If you don't know how to do it, learn it using SQL Formatter.

[Edit: missing letter]

[Updated on: Thu, 01 January 2015 01:34]

Report message to a moderator

Re: How to select distinct sql_id together with clob column [message #630520 is a reply to message #630490]

Mon, 29 December 2014 15:15

LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL

Senior Member

The primay key of the base table for DBA_HIST_SQLTEXT is: DBID + SQL_ID
You can confirm with this query which will return no rows:

  SELECT Sql_Id, COUNT ( * )
    FROM Sys.DBA_HIST_SQLTEXT
GROUP BY Sql_Id
  HAVING COUNT ( * ) > 1
/

[Updated on: Mon, 29 December 2014 15:17]

Report message to a moderator

Re: How to select distinct sql_id together with clob column [message #630544 is a reply to message #630520]

Tue, 30 December 2014 01:18

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

The problem is not there.
How many cases had you see with several DBID in the view?

Report message to a moderator

Previous Topic:	Syntax for creating oracle type
Next Topic:	Update multiple rows based on duplication

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 12:31:23 CDT 2024