Re: Help with hierarchical sql
- From: Alex Fatkulin <afatkulin@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Thu, 11 Apr 2013 16:49:58 -0400
Modify the connect by to be:
connect by prior CHECKPOINT_CHANGE# = INCREMENTAL_CHANGE# and prior FILE# =
FILE#
On Thu, Apr 11, 2013 at 4:35 PM, <breitliw@xxxxxxxxxxxxx> wrote:
> I'm sure it is something simple but I just don't see it. I am running
> this query:
>
> select to_char(COMPLETION_TIME,'yyyy-mm-dd hh24:mi:ss') COMPLETION_TIME
> , file#, blocks, level, incremental_level
> from v$backup_datafile
> where file# = (select min(file_id) file_id from dba_data_files where
> tablespace_name = 'SYSTEM')
> start with INCREMENTAL_LEVEL = 0
> connect by prior CHECKPOINT_CHANGE# = INCREMENTAL_CHANGE#
>
> and get this result:
>
> COMPLETION_TIME FILE# BLOCKS CHECKPOINT_CHANGE#
> INCREMENTAL_CHANGE# INCREMENTAL_LEVEL LEVEL
> ------------------- ------ ------- ------------------
> ------------------- ----------------- ------
> 2013-04-10 14:34:57 1 53141 17932539033
> 17929881471 1 2
> 2013-04-11 12:29:05 1 52 17932568918
> 17932544593 1 4
> 2013-04-10 18:38:42 1 23 17932544593
> 17932539033 1 3
> 2013-04-11 12:29:05 1 52 17932568918
> 17932544593 1 4
> 2013-04-05 11:10:35 1 60234 17929881471
> 0 0 1
> 2013-04-10 14:34:57 1 53141 17932539033
> 17929881471 1 2
> 2013-04-11 12:29:05 1 52 17932568918
> 17932544593 1 4
> 2013-04-10 18:38:42 1 23 17932544593
> 17932539033 1 3
> 2013-04-11 12:29:05 1 52 17932568918
> 17932544593 1 4
>
> 9 rows selected.
>
> I am expecting this:
>
> COMPLETION_TIME FILE# BLOCKS CHECKPOINT_CHANGE#
> INCREMENTAL_CHANGE# INCREMENTAL_LEVEL LEVEL
> ------------------- ------ ------- ------------------
> ------------------- ----------------- ------
> 2013-04-05 11:10:35 1 60234 17929881471
> 0 0 1
> 2013-04-10 14:34:57 1 53141 17932539033
> 17929881471 1 2
> 2013-04-10 18:38:42 1 23 17932544593
> 17932539033 1 3
> 2013-04-11 12:29:05 1 52 17932568918
> 17932544593 1 4
>
> --
> Wolfgang Breitling
> Centrex Consulting Corporation
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Alex Fatkulin,
http://afatkulin.blogspot.com
Enkitec,
http://www.enkitec.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
