Is CHECKPOINT_CHANGE# in V$DATABASE ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Is CHECKPOINT_CHANGE# in V$DATABASE ...

Hybrid View

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Is CHECKPOINT_CHANGE# in V$DATABASE ...

    the current SCN? If it isn't where can I find it?

    (I should know this, shouldn't I? Be gentle, it is Friday ...)

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    yep, you're right, thats the current scn#

  3. #3
    Join Date
    Jan 2004
    Location
    Russia
    Posts
    4
    Originally posted by fraze
    yep, you're right, thats the current scn#
    Are you sure?

    Code:
    SQL> select checkpoint_change# from v$database;
    
    CHECKPOINT_CHANGE#
    ------------------
               2078477
    
    SQL> create table t (n number);
    
    Table created.
    
    SQL> insert into t values(1);
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> select checkpoint_change# from v$database;
    
    CHECKPOINT_CHANGE#
    ------------------
               2078477
    
    SQL>
    To JMac

    Code:
    SQL> select dbms_flashback.get_system_change_number from dual;
    
    GET_SYSTEM_CHANGE_NUMBER
    ------------------------
                     2079493
    
    SQL>

  4. #4
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi,

    how do i take current scn from oracle 8i?

    -Raja

  5. #5
    Join Date
    Jan 2004
    Location
    Russia
    Posts
    4
    Originally posted by rajabalachandra
    hi,

    how do i take current scn from oracle 8i?

    -Raja
    Hi! Try this:

    Code:
    SQL> create global temporary table t ( x number );
    
    Table created.
    
    SQL> create or replace function get_scn return number
      2  as
      3      pragma autonomous_transaction;
      4      l_scn number;
      5  begin
      6      insert into t values ( userenv('commitscn') ) returning x into l_scn;
      7      commit;
      8      return l_scn;
      9  end;
     10  /
    
    Function created.
    
    SQL> select get_scn from dual;
    
       GET_SCN
    ----------
       2086229
    
    SQL>

  6. #6
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    hi,
    this is the response i get.
    what shall i do?

    SQL> create global temporary table t ( x number );

    Table created.

    SQL>
    SQL>
    SQL> create or replace function get_scn return number
    2 as
    3 pragma autonomous_transaction;
    4 l_scn number;
    5 begin
    6 insert into t values ( userenv('commitscn') ) returning x into l_scn;
    7 commit;
    8 return l_scn;
    9 end;
    10
    11 /

    Function created.

    SQL> select get_scn from dual;
    select get_scn from dual
    *
    ERROR at line 1:
    ORA-14551: cannot perform a DML operation inside a query
    ORA-06512: at "SYS.GET_SCN", line 7
    ORA-06512: at line 1

  7. #7
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    You can get the SCN of the most recently committed (local) transaction with the following query:

    select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;
    Remember the Golden Rule - He who has the gold makes the rules!
    ===================
    Kris109
    Ph.D., OCP 8i, 9i, 10g, 11g DBA

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    ------------
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Thomasps :

    I think SMarkelenkov has pointed out that...!!
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    select max(ktuxescnw * power(2, 32) + ktuxescnb) from x$ktuxe;

    it works.

    thank you kris!

    -Raja

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width