DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Does a SELECT get a SCN

  1. #1
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I wonder if a query gets a SCN like any other DML operations

    Also is a query considered as a transaction?

  2. #2
    Join Date
    Feb 2002
    Posts
    18
    I don't think select gets an SCN.SCN itself means a SYSTEM CHANGE NUMBER, and a normal select doesn't do any changes to the system.

    As far as I know a transaction is a change to the system, so again a normal select won't be a transaction.It can be a part of transaction

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    According to SCN, it means ' ... CHANGE number'. As query does not change any data it can not get SCN. It can get cursor number.

    Oracle automaticaly start transaction when you connect to database or after commit, rollback. Everything bettween 2 poits is in transaction.

    Originally posted by pando
    I wonder if a query gets a SCN like any other DML operations
    Also is a query considered as a transaction?
    Best wishes!
    Dmitri

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well an ILT instructor said a select is a transaction

    I do think a select get a SCN otherwise how can we get ORA-1555?

    Also in 9i there is new predicate auditing (Fine Grained Auditing) which provides a view, this audits all SELECT statement under certain predicate and in the view it stores timestamp, select statement etc... AND a SCN! So a select does get a SCN?

    But I wonder and I cant find much infos on this

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    SCN is system change number , sometimes called system commit number .
    This is number is incremented at every commit .When a select occurs , it gets the current global SCN value and uses that , now this select can cause the SCN to increment , if

    1) it causes a recursive SQL in the data dictionary , incrementing a
    sequence value , for timed_statistics=true causes the statistics to be updated etc.

    2) If it causes a block cleanout.

    3) ORA 1555 is only related to select causing SCN to change for
    fetch across commits scenario.

    thanks
    GP

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by pando
    I do think a select get a SCN otherwise how can we get ORA-1555?
    It is just the other way around: If each select would not get a SCN assigned to it we would never get ORA-1555. But then again we could also never get a read consistent view if query would not have a SCN.

    When a query begins, a current SCN is assigned to it and during execution of the query each block visited must not have its SCN newer than queries' SCN.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Feb 2001
    Location
    Bangalore, India
    Posts
    109
    I'm not sure if the Select gets SCN or not. But, continuiung the discussion, if select gets SCN, is it recorded in the Log Buffer? ( As any changes are recorded in the Log ). In that case, does Select also generate Redo?

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    "To get SCN" is something different as "To increment SCN". Queries of course do not increment SCN, but they do get assigned the system's current SCN.

    So:
    - Queries do get SCN (of course, multiple queries might have the same SCN assigned to them)
    - Queries do not change SCN and do not generate redo entries (although this last thing is not 100% true - sometimes even normal selects can create extensive ammount of redo).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    then from where and how is getting SCN, I mean SCN is constantly changing

    also is select a transaction if not I am gonna have a chat with this oracle education lady tomorrow =-)

  10. #10
    Join Date
    Jun 2000
    Posts
    117
    Pando is right . . . .

    I am just finishing an article on Oracle 9i's FGA. When the security audit is activated by a select the SCN and other data is recorded.


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