Re: Help with hierarchical sql

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Thu, 11 Apr 2013 14:42:07 -0700 (PDT)
Message-ID: <1365716527.15141.YahooMailNeo_at_web121602.mail.ne1.yahoo.com>

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 nocycle prior CHECKPOINT_CHANGE# = INCREMENTAL_CHANGE#;
 

I think that's what you're missing.
David Fitzjarrell

---

From: "breitliw_at_centrexcc.com" <breitliw_at_centrexcc.com> To: "List, Oracle Discussion" <oracle-l_at_freelists.org> Sent: Thursday, April 11, 2013 2:35 PM
Subject: Help with hierarchical sql

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


--
http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Apr 11 2013 - 23:42:07 CEST