Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Cannot connect to Oracle XDB service

user1434332Mar 6 2015 — edited Mar 10 2015

Cannot connect to Oracle XDB service

Error Messages:

===============

C:\Users\shelleyd>sqlplus system/xxxxxxx@localhost:1521/dunkxdb

SQL*Plus: Release 12.1.0.2.0 Production on Fri Mar 6 08:51:39 2015

ERROR:

ORA-12518: TNS:listener could not hand off client connection

Error Message in listener.log:

==============================

06-MAR-2015 08:51:39 * (CONNECT_DATA=(SERVICE_NAME=dunkxdb)(CID=(PROGRAM=C:\app\shelleyd\product\12.1.0\dbhome_1\bin\sqlplus.exe)(HOST=xxxxxxxx)(USER=shelleyd))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58784)) * establish * dunkxdb * 12518

TNS-12518: TNS:listener could not hand off client connection

TNS-12533: TNS:illegal ADDRESS parameters

From listener.ora file:

=======================

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = CLRExtProc)

(ORACLE_HOME = C:\app\shelleyd\product\12.1.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS=ONLY:C:\app\shelleyd\product\12.1.0\dbhome_1\bin\oraclr12.dll")

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

From tnsnames.ora file:

=======================

LISTENER_DUNK =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

ORACLR_CONNECTION_DATA =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

(CONNECT_DATA =

(SID = CLRExtProc)

(PRESENTATION = RO)

)

DUNK =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = dunk)

)

DUNKXDB =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dunkxdb)

)

LSNRCTL STATUS:

===============

C:\Users\shelleyd>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 06-MAR-2015 08:48

:12

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ

ction

Start Date 06-MAR-2015 08:46:39

Uptime 0 days 0 hr. 1 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File C:\app\shelleyd\product\12.1.0\dbhome_1\network\admin\

listener.ora

Listener Log File C:\app\shelleyd\diag\tnslsnr\SHELLEYD2\listener\alert\

log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx)(POR

T=5500))(Security=(my_wallet_directory=C:\APP\SHELLEYD\admin\dunk\xdb_wallet))(P

resentation=HTTP)(Session=RAW))

Services Summary...

Service "CLRExtProc" has 1 instance(s).

Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...

Service "dunk" has 1 instance(s).

Instance "dunk", status READY, has 1 handler(s) for this service...

Service "dunkXDB" has 1 instance(s).

Instance "dunk", status READY, has 1 handler(s) for this service...

The command completed successfully

Making Sure That the XML Database option is installed and valid:

================================================================

sys@dunk> DESC RESOURCE_VIEW

Name Null?

Type

------------------------------------------------------------------------- -----

--- -------------------------------------------------

RES

XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/XD

BResource.xsd" Element "Resource")

ANY_PATH

VARCHAR2(4000)

RESID

RAW(16)

sys@dunk> -- Check status of XDB

sys@dunk> select comp_name, version, status

2 from dba_registry

3 where comp_id = 'XDB';

COMP_NAME

--------------------------------------------------------------------------------

-----------------------------------------------------

VERSION STATUS

------------------------------ --------------------------------------------

Oracle XML Database

12.1.0.2.0 VALID

sys@dunk> -- Check for invalid objects

sys@dunk> select owner, object_name, object_type, status

2 from dba_objects

3 where status = 'INVALID'

4 and owner in ('SYS', 'XDB');

no rows selected

sys@dunk> -- Show dispatchers

sys@dunk> show parameter dispatchers

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dispatchers string (PROTOCOL=TCP) (SERVICE=dunkXDB)

sys@dunk> -- Show local_listener

sys@dunk> show parameter local_listener

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

local_listener string LISTENER_DUNK

Aketi Jyuuzou

**************************
For exsample3 (collect Rows continued Months)

create table Ex3 (DateVal primary key) as
select date '2009-09-01' from dual union
select date '2009-10-01' from dual union
select date '2009-12-01' from dual union
select date '2010-01-01' from dual union
select date '2010-02-01' from dual union
select date '2010-04-01' from dual;

select min(DateVal),max(DateVal),count(*)
from (select DateVal,
       extract(year  from DateVal)*12
      +extract(month from DateVal)
      -Row_Number() over(order by DateVal)
      as disTance
      from Ex3)
group by disTance
order by min(DateVal);

MIN(DATE MAX(DATE   COUNT(*)
-------- -------- ----------
09-09-01 09-10-01          2
09-12-01 10-02-01          3
10-04-01 10-04-01          1

*****************************************************************
3. What is complex usage of "Tabibitosan method" ? ?:|

**************************
For exsample4 (collect Rows continued and ID is equal)
from 450745

create table Ex4 (ID,Val,SortKey) as
select 1, 5, 1 from dual union all
select 1,10, 2 from dual union all
select 2, 2, 3 from dual union all
select 2, 5, 4 from dual union all
select 1,15, 5 from dual union all
select 3,25, 6 from dual union all
select 3,10, 7 from dual union all
select 3, 5, 8 from dual union all
select 3,15, 9 from dual union all
select 4, 5,10 from dual;

select ID,min(Val),max(Val),count(*)
from (select ID,Val,SortKey,
       Row_Number() over(order by SortKey)
      -Row_Number() over(partition by ID order by SortKey)
      as disTance
      from Ex4)
group by ID,disTance
order by min(SortKey);

ID MIN(VAL) MAX(VAL) COUNT(*)
-- -------- -------- --------
 1        5       10        2
 2        2        5        2
 1       15       15        1
 3        5       25        4
 4        5        5        1

In above solution,
I imagined that there are 5 people called X,A,B,C, and D.

X always walks 1m. (Row_Number() over(order by SortKey))
When ID = 1, A walks 1m. (Row_Number() over(partition by ID order by SortKey))
When ID = 2, B walks 1m. (Row_Number() over(partition by ID order by SortKey))
When ID = 3, C walks 1m. (Row_Number() over(partition by ID order by SortKey))
When ID = 4, D walks 1m. (Row_Number() over(partition by ID order by SortKey))

Then,
I derived distance between X and A.
I derived distance between X and B.
I derived distance between X and C.
I derived distance between X and D.

Then, I made group using these distances. (group by ID,disTance)

**************************
For exsample5 (collect Rows continued and Val1 is equal and Val2 is equal)
from 941878

create table mytable (sortKey,Val1,Val2) as
select 1,'A','X' from dual union all
select 2,'A','X' from dual union all
select 3,'B','Y' from dual union all
select 4,'B','Y' from dual union all
select 5,'A','X' from dual union all
select 5,'B','X' from dual union all
select 6,'A','Y' from dual union all
select 7,'B','Y' from dual union all
select 7,'A','Y' from dual union all
select 8,'A','Y' from dual;

select Val1,Val2,min(sortKey) as sta,max(sortKey) as end
from (select sortKey,Val1,Val2,
       dense_rank() over(order by sortKey)
      -Row_Number() over(partition by Val1,Val2
                         order by sortKey)
      as distance
       from mytable)
group by Val1,Val2,distance
order by min(sortKey);

Val1 Val2 sta end
---- ---- --- ---
A    X      1   2
B    Y      3   4
B    X      5   5
A    X      5   5
A    Y      6   8
B    Y      7   7

In above solution,
I imagined that there are 5 people called X,A,B,C, and D.

X always walks 1m. (dense_rank() over(order by sortKey))
When Val1 = 'A' and Val2 = 'X', A walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
When Val1 = 'A' and Val2 = 'Y', B walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
When Val1 = 'B' and Val2 = 'X', C walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))
When Val1 = 'B' and Val2 = 'Y', D walks 1m. (Row_Number() over(partition by Val1,Val2 order by SortKey))

Then,
I derived distance between X and A.
I derived distance between X and B.
I derived distance between X and C.
I derived distance between X and D.

Then, I made group using these distances. (group by Val1,Val2,disTance)

*****************************************************************
4. What threads are using "Tabibitosan method" ?:|

I will write that threads sorted by easy.

basic usage
1005633
866707

complex usage
920572
450745
1028579
941878

*****************************************************************
5. What URLS mention "Tabibitosan method" ?:|

Boneist's Oracle blog
http://boneist-oracle.livejournal.com/7389.html

Alex Nuijten's Presentation and that PDF document.
http://caat.odtug.com/odtug_caat/caat_abstract_reports.display_presenter_abstract?conference_id=81&presenter_id=117&abstract_id=186

*****************************************************************
OracleSQLPuzzle (MyHomePage)
http://www.geocities.jp/oraclesqlpuzzle/

sister thread
Boolean algebra in some Rows tutorial by Aketi Jyuuzou 1007678

Karthick2003

Thanks, Learned something new today. This kind of problem comes up quite often.

Keep up the good work. Much Appreciated!!

743014

here is another example using this with dates. It groups ids by consecutive weeks

WITH A AS
( SELECT 1 ID,5 Val, to_date('2009-08-24','yyyy-mm-dd') StartWeek, to_date('2009-08-30','yyyy-mm-dd') EndWeek FROM dual UNION ALL 
  SELECT 1,10, to_date('2009-08-31','yyyy-mm-dd'), to_date('2009-09-06','yyyy-mm-dd') FROM dual UNION ALL
  SELECT 1,2, to_date('2009-09-07','yyyy-mm-dd'), to_date('2009-09-13','yyyy-mm-dd') FROM dual UNION ALL  
  SELECT 2,2, to_date('2009-09-07','yyyy-mm-dd'), to_date('2009-09-13','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 2,5, to_date('2009-09-14','yyyy-mm-dd'), to_date('2009-09-20','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 3,5, to_date('2009-09-14','yyyy-mm-dd'), to_date('2009-09-20','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 1,15, to_date('2009-09-21','yyyy-mm-dd'), to_date('2009-09-27','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 3,25, to_date('2009-09-28','yyyy-mm-dd'), to_date('2009-10-04','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 3,10, to_date('2009-10-05','yyyy-mm-dd'), to_date('2009-10-11','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 3,5, to_date('2009-10-12','yyyy-mm-dd'), to_date('2009-10-18','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 3,15, to_date('2009-10-19','yyyy-mm-dd'), to_date('2009-10-25','yyyy-mm-dd') FROM dual UNION ALL 
  SELECT 4,5, to_date('2009-10-26','yyyy-mm-dd'), to_date('2009-11-01','yyyy-mm-dd') FROM dual)
,
B AS
( SELECT ID,        Val,        StartWeek,        EndWeek,
         (Row_Number() over(ORDER BY StartWeek) - Row_Number() over(partition BY ID ORDER BY StartWeek)) AS makeGroup
 FROM A)
SELECT ID,       SUM(Val) AS SumVal,       MIN(StartWeek),       MAX(EndWeek)
FROM B
GROUP BY ID,makeGroup
ORDER BY MIN(StartWeek);

ID            SumVAL        MIN(STARTWEEK)      MAX(ENDWEEK)
1             17            8/24/2009           9/13/2009
2             7             9/7/2009            9/20/2009
3             5             9/14/2009	        9/20/2009
1             15            9/21/2009	        9/27/2009
3             55            9/28/2009	        10/25/2009
4             5             10/26/2009          11/1/2009

1 person found this helpful

Frank Kulash

Hi,

Aketi Jyuuzou wrote:
...
1. What is Tabibitosan ? ?:|

"Tabibitosan" is Japanese language.
"Tabibitosan" is one of math problem.

What is the math problem? It sounds interesting.

http://www.manabinoba.com/index.cfm/4,757,73,html?year=2002
http://yslibrary.cool.ne.jp/sansub1801.html

I love the graphics on the second link!

AlanWms

Okay, I am a little dense, I was going to ask for an explanation, in very simple terms, then I figured it out. Basically it is subtracting (or adding if your list is descending) the row number from an ordered list of numbers (or dates) to find consecutive ranges of numbers (or dates). Pretty slick.

As an example, lets take some integers with gaps in the sequence:

Num      Row    Distance (Num-Row)
===      ===    =============
2        1        1
3        2        1
4        3        1
5        4        1
6        5        1
7        6        1
12       7        5
13       8        5
14       9        5 
15       10       5
16       11       5
17       12       5
20       13       7
21       14       7
etc ...

Note that the "Distance" delineates consecutive groups, you then group by this etc. Maybe everyone else who viewed this thread got it immediately...

Thanks Aketi, this is a neat trick!

Solomon Yakobson

AlanWms wrote:
Okay, I am a little dense, I was going to ask for an explanation, in very simple terms, then I figured it out.

It looks like one of the simplest cases of grouping "loosely grouped" data.

SY.

730428

Hi, here is another way to solve example 1 using hierarchical queries:

SQL> select min(CONNECT_BY_ROOT NUMVAL) minval, NUMVAL maxval, max(NUMVAL-CONNECT_BY_ROOT NUMVAL+1)  countval
  2  from ex1
  3  where connect_by_isleaf=1
  4  connect by numval=prior numval+1
  5  group by numval
  6  order by 1;

    MINVAL     MAXVAL   COUNTVAL
---------- ---------- ----------
         1          3          3
         5          7          3
        10         12          3
        20         21          2

The other examples can be easily derived...

Max
[My Italian Oracle blog|http://oracleitalia.wordpress.com/2009/12/27/inviare-email-dal-db-utilizzando-utl_smtp/]

Aketi Jyuuzou

@Massimo Ruocchio

Nice one :-)
Hehe I have arranged your solution B-)

select min(CONNECT_BY_ROOT NUMVAL) minval,
NUMVAL maxval, max(Level) countval
from ex1
where connect_by_isleaf=1
connect by numval=prior numval+1
group by numval
order by minval;

MINVAL  MAXVAL  COUNTVAL
------  ------  --------
     1       3         3
     5       7         3
    10      12         3
    20      21         2

Aketi Jyuuzou

5. What is rival solution of Tabibitosan method ? ?:|

There is the rival solution.
Rival solution derives willSum using Lag function and case expression
And Then derive runSum of willSum using sum function.

select min(NumVal),max(NumVal),count(*)
from (select NumVal,
      sum(willSum) over(order by NumVal) as GID
      from (select NumVal,
            case when NumVal-1
               = Lag(NumVal) over(order by NumVal)
                 then 0 else 1 end as willSum
            from Ex1))
group by GID
order by GID;

MIN(NUMVAL)  MAX(NUMVAL)   COUNT(*)
-----------  -----------  ---------
          1            3          3
          5            7          3
         10           12          3
         20           21          2

But This rival solution needs 2 InLineView.
Altough TabibitoSan method needs only 1 InLineView.

Therefore if we can use TabibitoSan method,we should use TabibitoSan method.

for example
In below cases,we cannot use TabibitoSan method.
969005
956106

1 person found this helpful

Etbin

Hats off. A clear and concise treatment of a non elementary group of problems. I like the pilgrim interpretation. Never thought about using it when I was asked to explain a solution of this kind. Tabibitosan - a word to remember.

Regards

Etbin

user503699

Aketi Jyuuzou wrote:
**************************
For exsample3 (collect Rows continued Months)

create table Ex3 (DateVal primary key) as
select date '2009-09-01' from dual union
select date '2009-10-01' from dual union
select date '2009-12-01' from dual union
select date '2010-01-01' from dual union
select date '2010-02-01' from dual union
select date '2010-04-01' from dual;

select min(DateVal),max(DateVal),count(*)
from (select DateVal,
extract(year  from DateVal)*12
+extract(month from DateVal)
-Row_Number() over(order by DateVal)
as disTance
from Ex3)
group by disTance
order by min(DateVal);

MIN(DATE MAX(DATE   COUNT(*)
-------- -------- ----------
09-09-01 09-10-01          2
09-12-01 10-02-01          3
10-04-01 10-04-01          1

Aketi,

First of all, thanks a lot for this great thread. It is in my top bookmarks.
In above example, you have showed how this technique can be applied to group the rows with difference of a month.
I was wondering if this technique can be applied to group rows within a specific interval.
For e.g. is it possible to group the rows in the following resultset, within interval of 1 minute?

30-JULY-2010 05:10:00
30-JULY-2010 05:50:00
30-JULY-2010 06:55:00
30-JULY-2010 07:54:00
30-JULY-2010 08:58:00

The above data should be grouped as follows (group number is only an indicator and need not be continuous)

30-JULY-2010 05:10:00      1
30-JULY-2010 05:50:00      1
30-JULY-2010 06:55:00      2
30-JULY-2010 07:54:00      2
30-JULY-2010 08:58:00      3

Can you please help?

Aketi Jyuuzou

Nice question :-)
But this problem needs rival solution of "Tabibitosan method"
Like this thread 956106

with t(Val) as(
select to_date('20100630 05:10:00','yyyymmdd hh24:mi:ss') from dual union
select to_date('20100630 05:50:00','yyyymmdd hh24:mi:ss') from dual union
select to_date('20100630 06:55:00','yyyymmdd hh24:mi:ss') from dual union
select to_date('20100630 07:54:00','yyyymmdd hh24:mi:ss') from dual union
select to_date('20100630 08:58:00','yyyymmdd hh24:mi:ss') from dual)
select to_char(Val,'yyyymmdd hh24:mi:ss') as Val,
sum(willSum) over(order by Val) as GID
from (select Val,
      case when Lag(Val) over(order by Val) + InterVal '1' hour > Val
           then 0 else 1 end as willSum
        from t)
order by Val;

VAL                GID
-----------------  ---
20100630 05:10:00    1
20100630 05:50:00    1
20100630 06:55:00    2
20100630 07:54:00    2
20100630 08:58:00    3

user503699

Aketi Jyuuzou wrote:
Nice question :-)
But this problem needs rival solution of "Tabibitosan method"
Like this thread 956106

Aketi,
Many Thanks.
My sincere apologies as I realised that my description of the problem and test data (in earlier post) were not consistent with each other.
Please allow me to rephrase my question.
With the following data, I would like to group the records within one minute interval and get the first record within the group.
Here is the test case

SQL> create table t1 (x date, y int, id int) ;

Table created.

SQL> insert into t1 values (to_date('10/06/2008 03:34:40 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 03:34:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 03:34:50 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 04:28:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 04:29:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 04:30:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 04:30:29 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 04:27:35 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 12, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 03:33:34 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 21, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/06/2008 03:34:15 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 228)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/07/2008 12:59:40 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/07/2008 12:59:50 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/07/2008 01:00:05 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/07/2008 01:00:30 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/07/2008 01:00:49 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
  2  /

1 row created.

SQL> insert into t1 values (to_date('10/07/2008 01:00:55 PM', 'MM/DD/YYYY HH12:MI:SS AM'), 10, 229)
  2  /

1 row created.

SQL> commit ;

Commit complete.

SQL> select id, x, y, row_number() over (partition by id order by x) rn from t1 order by id, x
  2  /

        ID X                             Y         RN
---------- -------------------- ---------- ----------
       228 06-OCT-2008 15:33:34         21          1
       228 06-OCT-2008 15:34:15         10          2
       228 06-OCT-2008 15:34:30         10          3
       228 06-OCT-2008 15:34:40         10          4
       228 06-OCT-2008 15:34:50         10          5
       228 06-OCT-2008 16:27:35         12          6
       228 06-OCT-2008 16:28:30         10          7
       228 06-OCT-2008 16:29:30         10          8
       228 06-OCT-2008 16:30:29         10          9
       228 06-OCT-2008 16:30:30         10         10
       229 07-OCT-2008 12:59:40         10          1
       229 07-OCT-2008 12:59:50         10          2
       229 07-OCT-2008 13:00:05         10          3
       229 07-OCT-2008 13:00:30         10          4
       229 07-OCT-2008 13:00:49         10          5
       229 07-OCT-2008 13:00:55         10          6

16 rows selected.

Expected output

        ID              X                      Y
---------- --------------------  ----------
       228 06-OCT-2008 15:33:34         21
       228 06-OCT-2008 15:34:40         10
       228 06-OCT-2008 16:27:35         12
       228 06-OCT-2008 16:29:30         10
       228 06-OCT-2008 16:30:30         10
       229 07-OCT-2008 12:59:40         10
       229 07-OCT-2008 13:00:49         10

Aketi Jyuuzou

About 2 years before,I solved it :D
703724

with t(Val) as(
select to_date('10/06/2008 03:34:40', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 03:34:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 03:34:50', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 04:28:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 04:29:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 04:30:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 04:30:29', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 04:27:35', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 03:33:34', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/06/2008 03:34:15', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/07/2008 12:59:40', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/07/2008 12:59:50', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/07/2008 13:00:05', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/07/2008 13:00:30', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/07/2008 13:00:49', 'MM/DD/YYYY HH24:MI:SS') from dual union
select to_date('10/07/2008 13:00:55', 'MM/DD/YYYY HH24:MI:SS') from dual)
select to_char(Val,'yyyy-mm-dd hh24:mi:ss') as Val
from (select Val,min(Val) over() as startVal,
      min(Val) over(order by Val
                    range between InterVal '1' minute following
                              and unbounded following) as nextVal
      from t)
start with Val = startVal
connect by prior nextVal = Val;

VAL
-------------------
2008-10-06 03:33:34
2008-10-06 03:34:40
2008-10-06 04:27:35
2008-10-06 04:29:30
2008-10-06 04:30:30
2008-10-07 12:59:40
2008-10-07 13:00:49

user503699

Aketi Jyuuzou wrote:
About 2 years before,I solved it :D
703724

Aketi,

Many thanks for your response.
Is this the only way (using SQL) to solve this problem?
The main reason for asking for alternative solution is this solution requires accessing and sorting the table twice.

SQL> select * from table(dbms_xplan.display_cursor) ;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  9skznz4u401t1, child number 0
-------------------------------------
select id,x,y from (select id,x,y,min(x) over (partition by id) startx,
min(x) over (partition by id order by x range between interval '1' minute
following and unbounded following) nextval from t1) start with x = startx
connect by prior nextval = x

Plan hash value: 775368986

----------------------------------------------------------------------------------
| Id  | Operation                 | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |       |       |     4 (100)|          |
|*  1 |  CONNECT BY WITH FILTERING|      |       |       |            |          |
|*  2 |   VIEW                    |      |    16 |  1136 |     4  (25)| 00:00:01 |
|   3 |    WINDOW SORT            |      |    16 |   240 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL     | T1   |    16 |   240 |     3   (0)| 00:00:01 |
|*  5 |   HASH JOIN               |      |       |       |            |          |
|   6 |    CONNECT BY PUMP        |      |       |       |            |          |
|   7 |    VIEW                   |      |    16 |   704 |     4  (25)| 00:00:01 |
|   8 |     WINDOW SORT           |      |    16 |   240 |     4  (25)| 00:00:01 |
|   9 |      TABLE ACCESS FULL    | T1   |    16 |   240 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("X"=PRIOR NULL)
   2 - filter("X"="STARTX")
   5 - access("X"=PRIOR NULL)

p.s. Please don't suggest MODEL clause ;)

Edited by: user503699 on Aug 2, 2010 6:52 PM

Aketi Jyuuzou

user503699 wrote:
Is this the only way (using SQL) to solve this problem?
The main reason for asking for alternative solution is this solution requires accessing and sorting the table twice.

Ummm

I think that there are 2 solutions.
One is recursive with clause insted of above HierarchicalQuery.
And The other is PipeLineTableFunction.

783956

Hi Alan,

>
I am a little dense,...
>

No, you're not... I had to think about it too ;)

Indeed, it is very slick. A known monotonous sequence (the row numbers) applied to a sparse sequence of monotonous values (the other sequence) reveals the sparse groups in the sequence because their difference is constant in each group.

Very nice and elegant! :)

Thank you Aketi !!

John.

1 - 17

Locked Post

New comments cannot be posted to this locked post.

Locked on Apr 7 2015

Added on Mar 6 2015

#general-database-discussions

4 comments

2,097 views

Oracle Database Discussions

Cannot connect to Oracle XDB service

Comments

Post Details