Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Sajjad.

Asked: September 18, 2005 - 4:55 pm UTC

Last updated: November 01, 2013 - 6:45 pm UTC

Version: 9.2.0.0

Viewed 100K+ times! This question is

You Asked

Hi Tom,can u plz help me on this
what is SCN,why it is used?
what is checkpoint?is checkpoint is related to SCN?
when database has no transactions going on,when i do checkpoint using
alter system checkpoint; why checkpoint number get increased non sequencely to higher value.


and Tom said...

"U"? "U" isn't available - but I'll take a try at answering.

the system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.

A checkpoint is the act of writing dirty (modified blocks from the buffer cache to disk.

The database ALWAYS has transactions going on, ALWAYS. SMON and many other background processes are always doing work, the database (unless it is opened read only) is always doing transactions.

Rating

  (79 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

SCN

Arun, October 03, 2006 - 9:56 am UTC

Hello,


Can you let me know where and how can I read the SCN related to a transaction (DML). i.e. if I issued an update statement and later i want to use flashback on the same, how would I know the SCN corresponding to the same transaction?

Tom Kyte
October 03, 2006 - 10:59 am UTC

you cannot really - the SCN is assigned AFTER THE COMMIT (well, as part of the commit processing).

So, best you can do is grab the scn right after you commit.


Besides, flashback will never see any uncommitted work (even your own!)

SCN Change

Devendra, November 10, 2006 - 8:40 am UTC

Dear Tom
I would like to know more about SCN .
The SCN is incremented whenever a transaction commits.
Suppose i do update in one table which is stored in two different datafiles it will update all datafiles header & write information in control file after commit.
Before opening the database SMON will check the control file & datafile headers for the same SCN.
If the SCN in datafile header is not matching with the Control file that means datafile need recovery.

From where i can get the current scn number.
select dbms_flashback.get_system_change_number from dual;

version (oracle 9i)
Thanks in advance
Devendra

Tom Kyte
November 10, 2006 - 9:08 am UTC

scn is a number incremented with the completion of each and every transaction.

a commit does not write to datafiles at all. it does not update control files at all.


If you are interested in "how Oracle works" at this level - caching of blocks, the interaction between redo and undo and so on - you might be interested in my current book "Expert Oracle Database Architecture", I cover all of this stuff with examples and so on.

SCN Change

Devendra, November 10, 2006 - 10:05 am UTC

Dear Tom,
Thanks for your reply.
Then when oracle will change the SCN number
Where i can get this book(Hard copy).Is this book published

Thanks in advance
Devendra

Tom Kyte
November 10, 2006 - 2:40 pm UTC

when will oracle change the SCN? It will do that every time a transaction ends..


see home page for link to book.

Amazon.com has it

Charlie B., November 10, 2006 - 11:19 am UTC

SCN for select statements

Beebo, November 20, 2008 - 7:08 am UTC

Hello Tom ,
I read your explanation regarding SCN, you mentioned that it is generated for committed transactions, but I read that , quote "The System Change Number is assigned many times through the course of a session. Each DML (including SELECT) statement will get a SCN. And each DDL will get a SCN. The statement receives a SCN when it starts. The assignment does not wait for commit or rollback. The system uses the SCN to know the order the statements were given to the database" end Quote
my question is, SCN number, is it a number to identify a committed transaction? or is it a number just to identify the sequence of statements executed against the database ?

The link is down below
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1246224,00.html
Tom Kyte
November 24, 2008 - 11:45 am UTC

see, this is exactly why I like to provide a little "evidence that what I'm about to say is probably true"

Ok, say every DML was assigned an SCN. How would or could we show that is probably true?

Well, by reading out the SCN, doing a ton of DML, rereading the current SCN and outputing the difference between the two. We would presume that if an SCN was assigned to a sql statement (and it must be unique by statement if we were to use it to 'order' the sql) than the difference would be greater than or equal to the number of DML statements...

So, let us try:

ops$tkyte%ORA9IR2> create table t ( x int );

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> column scn new_val scn
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  79178265

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  insert into t values ( i );
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number - &SCN from dual;
old   1: select dbms_flashback.get_system_change_number - &SCN from dual
new   1: select dbms_flashback.get_system_change_number -   79178265 from dual

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178265
------------------------------------------------
                                               5


It only advanced by 5 - but we did over 1,000 dml statements (our plsql block did some dml to parse itself...). The SCN is not assigned to a SQL statement (more on this later, there is a NUGGET OF TRUTH hidden in that statement, but it got a little twisted here).

The SCN is incremented upon commit - and the SCN might jump large increments in a distributed environment as the instances "align" themselves with each other.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> column scn new_val scn
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
  79178271

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  insert into t values ( i );
  5                  COMMIT;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select dbms_flashback.get_system_change_number - &SCN from dual;
old   1: select dbms_flashback.get_system_change_number - &SCN from dual
new   1: select dbms_flashback.get_system_change_number -   79178271 from dual

DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER-79178271
------------------------------------------------
                                            1016


See, now if you COMMIT 1,000 times, the SCN does jump by 1,000 (the other jumps are the background processes, they are always doing stuff - smon, mmom, pmon, etc - they do SQL all of the time - the database never rests :) )




Now, for the nugget of truth. Oracle does this thing called mutliversioning and read-consistency. So, what I think the author of that answer might have meant was "when you execute a query, Oracle looks at the current SCN and ensures that the data returned/touched by that query is as of that SCN - you get a read consistent result set"

If you

a) open a cursor as 9am when the SCN is 1234
b) go away for 3 hours
c) come back and see that the SCN is now 1000 (700+ transactions later)
d) start fetching the data - any block NOT COMMITTED as of SCN 1234 will be rolled back until we have the committed version of the block as of SCN 1234


This is true for all DML - and can be slightly altered if you use read only or serializable transactions.

If you use those, then the SCN your data will be 'as of' is fixed at the time your transaction begins - NOT as of the time it is executed (which is the default read committed isolation)



So, when reading information on the internet look for

a) version information (very relevant)
b) a date/timestamp - so we can see if this is from 1995 or 2005
c) AN EXAMPLE DEMONSTRATING THAT WHAT IS SAID IS PROBABLY TRUE
d) the ability TO COMMENT on the material or at least provide feedback to the author (prefer COMMENT, heavily prefer commenting)

I stress the probably - because a test case can be flawed, many of mine are from time to time :) but it gives *everyone* a chance to review the conditions and say "yeah, but...."


If I see stuff without a version, without a date, without an example - I tend to either ignore it, or set out to show it is probably right/wrong.

Unless there is no ability to comment - then I might just ignore it utterly since if I show it wrong - what then?? I cannot fix it.





A reader, March 30, 2009 - 2:35 am UTC

You mentioned that
the SCN is assigned AFTER THE COMMIT
However even there is no commit in my personal database,
When I query
"select current_scn from v$database;"
the scn changes every second...

I also want to ask something else.
After a checkpoint, this number is stored in the headers of controlfiles and datafiles.
Is that correct?
or scn is always stored in control files which changes every second?

Tom Kyte
March 30, 2009 - 4:12 pm UTC

I said that in response to

Can you let me know where and how can I read the SCN related to a transaction (DML). i.e. if I
issued an update statement and later i want to use flashback on the same, how would I know the SCN
corresponding to the same transaction?



they wanted the SCN for *their* current transaction, that doesn't exist until their transaction completes. THEIR scn isn't available yet.


checkpoints are almost always happening these days, it is a continuous process, and we update datafile headers from time to time yes - you can see them in the control files and datafile headers (change#'s are the fields to look for)

A reader, March 31, 2009 - 4:03 pm UTC

Thanks Tom, Does the scn that changes every second (select current_scn from v$database) stored in controlfile everytime or only after the checkpoint. I mean if the current scn is 100, it is written controlfile, when it is 101, again it is written controlfile,and so on... One more thing is the scn also written to redologs and archivelogs...
Tom Kyte
April 01, 2009 - 8:32 am UTC

the scn increments with every commit, not every second. It is not a "ticker".

It is written to the controlfiles when the control files are updated - which happens as the result of a few things, one being "end of official checkpoint".


A reader, April 01, 2009 - 3:16 pm UTC

Even there is no commit in my database( I am the only user). The scn again always increments. Why Tom? One more thing, the chechpoint scn of the datafiles stored in controlfile, does controlfile also store checkpoint change# for itself?


Tom Kyte
April 01, 2009 - 4:49 pm UTC

hah, you are NEVER the only user.

ops$tkyte%ORA10GR2> select username, program from v$session;

USERNAME                       PROGRAM
------------------------------ ------------------------------------------------
OPS$TKYTE                      sqlplus@dellpe (TNS V1-V3)
                               oracle@dellpe (q001)
                               oracle@dellpe (q000)
                               oracle@dellpe (QMNC)
                               oracle@dellpe (MMNL)
                               oracle@dellpe (MMON)
                               oracle@dellpe (CJQ0)
                               oracle@dellpe (RECO)
                               oracle@dellpe (SMON)
                               oracle@dellpe (CKPT)
                               oracle@dellpe (LGWR)
                               oracle@dellpe (DBW0)
                               oracle@dellpe (MMAN)
                               oracle@dellpe (PSP0)
                               oracle@dellpe (PMON)

15 rows selected.



the database never sleeps. Most of those other "programs" do transactions and commit.

A reader, April 02, 2009 - 5:36 am UTC

hey Tom
Does the scn stored in redologs or archivelogs?
I know it is stored in controlfile and datafiles but Im not sure about redo and archives
Tom Kyte
April 02, 2009 - 10:06 am UTC

yes, it is. archives are just copies of redo, so it is in both

Prasad, April 03, 2009 - 1:16 am UTC

Hi Tom,
Is there a limitation on the number of scns that can be generated in a second ? Thanks
Tom Kyte
April 03, 2009 - 7:29 am UTC

physics and the speed of light mostly.

how many commits can you do in a second on your machine?

a second is a rather arbitrary unit of time - one that a computer doesn't really care about. The number of commits per second is regulated by your hardware and the amount of work you try to do on that hardware.

A reader, April 03, 2009 - 5:12 am UTC

so,
after checkpoint issued, scn is written to both datafiles,controlfiles and redologs.

Tom Kyte
April 03, 2009 - 7:52 am UTC

scn's are written to redo logs continuously - when you commit they are emitted into the redo stream.

and semantically speaking, after the checkpoint COMPLETES, not after the checkpoint is INITIATED

Aman...., April 03, 2009 - 9:26 am UTC

Sir, 
You have mentioned somewhere above (and I have read the same in oracle docs as well) that the now the precision of the scn timing is +/- 3 seconds compared to 5 minutes <10g releases. How does we can see it? The result that I am seeing from smon_scn_time is some what not matching with it, the difference of teh time is 5 seconds. 

Please have a look here,
SQL> select time_mp,time_dp,  scn_wrp, scn_bas, scn from smon_scn_time
  TIME_MP TIME_DP               SCN_WRP    SCN_BAS        SCN
--------- ------------------ ---------- ---------- ----------
683207443 03-apr-09:11:50:43          0     751619     751619
683207755 03-apr-09:11:55:55          0     751743     751743
683208043 03-apr-09:12:00:43          0     751904     751904
683208355 03-apr-09:12:05:55          0     752029     752029
683208643 03-apr-09:12:10:43          0     752188     752188
683208955 03-apr-09:12:15:55          0     752329     752329
683209243 03-apr-09:12:20:43          0     752449     752449
683209555 03-apr-09:12:25:55          0     752595     752595
683209843 03-apr-09:12:30:43          0     752737     752737
683210164 03-apr-09:12:36:04          0     752963     752963
683210443 03-apr-09:12:40:43          0     753082     753082

  TIME_MP TIME_DP               SCN_WRP    SCN_BAS        SCN
--------- ------------------ ---------- ---------- ----------
683210744 03-apr-09:12:45:44          0     753228     753228
683211068 03-apr-09:12:51:08          0     753601     753601
683211344 03-apr-09:12:55:44          0     753733     753733
683211662 03-apr-09:13:01:02          0     753873     753873
683211944 03-apr-09:13:05:44          0     754011     754011
683212265 03-apr-09:13:11:05          0     754173     754173
683212544 03-apr-09:13:15:44          0     754288     754288
683212862 03-apr-09:13:21:02          0     754437     754437
683213144 03-apr-09:13:25:44          0     754549     754549
683213465 03-apr-09:13:31:05          0     754780     754780
683213540 03-apr-09:13:32:20          0     754811     754811

How do we see that the precision is now to 3 seconds or is it some thing that is used by Oracle itself? 

In the last, is there any ML which depicts smon_scn_time table in some detail? I tried a lot to search for its description but got no success.

Best regards
Aman....

Tom Kyte
April 03, 2009 - 9:30 am UTC

It is internally done. if you look at that table - all of the columns - there is a field TIM_SCN_MAP, it is hidden in there.

"ML" ? not even a shadow of a clue what ML is or means.


But no, smon_scn_time is just an internal table, your interface to it are the API's we provide to convert scn's into times

Aman...., April 03, 2009 - 10:01 am UTC

Sir,
I am sorry, I meant Metalink, really sorry.
So the tim_scn_map column contains that mapping but its a RAW column and as you mentioned, oracle has "hacked" the mapping in this column. I did try to get that mapping out but somehow , I got no where. My intention is to see that 3 seconds precision some how, is there any way I can get that sir?

Thanks and regards
Aman....
Tom Kyte
April 03, 2009 - 11:15 am UTC

I told you how.

by using the APIs to access that information.


ops$tkyte%ORA10GR2> select scn_to_timestamp(scn) ts, min(scn), max(scn)
  2    from (
  3  select dbms_flashback.get_system_change_number()-level scn
  4    from dual
  5  connect by level <= 100
  6         )
  7   group by scn_to_timestamp(scn)
  8   order by scn_to_timestamp(scn)
  9  /

TS                                MIN(SCN)   MAX(SCN)
------------------------------- ---------- ----------
03-APR-09 10.00.32.000000000 AM   82525082   82525120
03-APR-09 10.00.38.000000000 AM   82525121   82525121
03-APR-09 10.00.41.000000000 AM   82525122   82525122
03-APR-09 10.00.44.000000000 AM   82525123   82525123
03-APR-09 10.00.47.000000000 AM   82525124   82525124
03-APR-09 10.00.50.000000000 AM   82525125   82525125
03-APR-09 10.00.53.000000000 AM   82525126   82525126
03-APR-09 10.00.56.000000000 AM   82525127   82525127
03-APR-09 10.00.59.000000000 AM   82525128   82525128
03-APR-09 10.01.02.000000000 AM   82525129   82525129
03-APR-09 10.01.05.000000000 AM   82525130   82525130
03-APR-09 10.01.08.000000000 AM   82525131   82525131
03-APR-09 10.01.11.000000000 AM   82525132   82525132
03-APR-09 10.01.14.000000000 AM   82525133   82525133
03-APR-09 10.01.17.000000000 AM   82525134   82525134
03-APR-09 10.01.20.000000000 AM   82525135   82525135
03-APR-09 10.01.23.000000000 AM   82525136   82525136
03-APR-09 10.01.26.000000000 AM   82525137   82525137
03-APR-09 10.01.29.000000000 AM   82525138   82525138
03-APR-09 10.01.32.000000000 AM   82525139   82525139
03-APR-09 10.01.35.000000000 AM   82525140   82525140
03-APR-09 10.01.38.000000000 AM   82525141   82525141
03-APR-09 10.01.41.000000000 AM   82525142   82525142
03-APR-09 10.01.44.000000000 AM   82525143   82525143
03-APR-09 10.01.47.000000000 AM   82525144   82525144
03-APR-09 10.01.50.000000000 AM   82525145   82525145
03-APR-09 10.01.53.000000000 AM   82525146   82525146
03-APR-09 10.01.56.000000000 AM   82525147   82525147
03-APR-09 10.01.59.000000000 AM   82525148   82525148
03-APR-09 10.02.02.000000000 AM   82525149   82525149
03-APR-09 10.02.05.000000000 AM   82525150   82525150
03-APR-09 10.02.08.000000000 AM   82525151   82525151
03-APR-09 10.02.11.000000000 AM   82525152   82525152
03-APR-09 10.02.14.000000000 AM   82525153   82525153
03-APR-09 10.02.17.000000000 AM   82525154   82525154
03-APR-09 10.02.20.000000000 AM   82525155   82525155
03-APR-09 10.02.23.000000000 AM   82525156   82525156
03-APR-09 10.02.26.000000000 AM   82525157   82525157
03-APR-09 10.02.29.000000000 AM   82525158   82525158
03-APR-09 10.02.32.000000000 AM   82525159   82525159
03-APR-09 10.02.35.000000000 AM   82525160   82525160
03-APR-09 10.02.38.000000000 AM   82525161   82525161
03-APR-09 10.02.41.000000000 AM   82525162   82525162
03-APR-09 10.02.44.000000000 AM   82525163   82525163
03-APR-09 10.02.47.000000000 AM   82525164   82525164
03-APR-09 10.02.50.000000000 AM   82525165   82525165
03-APR-09 10.02.53.000000000 AM   82525166   82525166
03-APR-09 10.02.56.000000000 AM   82525167   82525167
03-APR-09 10.02.59.000000000 AM   82525168   82525168
03-APR-09 10.03.02.000000000 AM   82525169   82525169
03-APR-09 10.03.05.000000000 AM   82525170   82525170
03-APR-09 10.03.08.000000000 AM   82525171   82525171
03-APR-09 10.03.11.000000000 AM   82525172   82525172
03-APR-09 10.03.14.000000000 AM   82525173   82525173
03-APR-09 10.03.17.000000000 AM   82525174   82525174
03-APR-09 10.03.20.000000000 AM   82525175   82525175
03-APR-09 10.03.23.000000000 AM   82525176   82525176
03-APR-09 10.03.26.000000000 AM   82525177   82525177
03-APR-09 10.03.29.000000000 AM   82525178   82525178
03-APR-09 10.03.32.000000000 AM   82525179   82525179
03-APR-09 10.03.35.000000000 AM   82525180   82525180
03-APR-09 10.03.38.000000000 AM   82525181   82525181

62 rows selected.



see how they are all 3 seconds apart?

aman, April 03, 2009 - 2:07 pm UTC

Sir,
Its my heartiest wish to know SQL even just 1% of how much you know! Thanks a bunch, I got it.

regards
Aman....

A reader, April 27, 2009 - 2:16 am UTC

Dear Tom
You told me that;

Even if there is no users in the database, there are always transactions therefore commits and SCN increments after every commit.

I am just wondering, if these transactions recorded in redologs ?
or
only users transactions recorded in redologs

Tom Kyte
April 27, 2009 - 2:19 pm UTC

every transaction is recorded there, yes.

The background processes are just users in a special sense.

A reader, May 10, 2009 - 10:10 pm UTC

Sir you mentioned that
"scn's are written to redo logs continuously - when you commit they are emitted into the redo stream."
Since SCN changes every commit, it will be written to redologs every commit.However,I dont understand the what does emmitting into redo stream means.Can you explain it?
Tom Kyte
May 11, 2009 - 5:24 pm UTC

emit just means to "put into", "to send forth", "discharge"

we put them into the redo stream continuously - we 'emit' them continuously.


someone wrote:
... after checkpoint issued, scn is written to both datafiles,controlfiles and redologs. ...

I just said "we are doing that all of the time"

A reader, May 11, 2009 - 5:53 pm UTC

Thanks sir I understand that SCN is written redologs continuesly. Is it same for datafiles and controlfiles ? or is it written after checkpoint


more about SCN

Rainey, May 13, 2009 - 10:42 pm UTC

dear TOM :

When we dump a block,we can got the scn information about the block.if this scn is the same as the one in the datafile,controlfile or redo logfile,or if the scn information are same in all the blocks of datafiles

Thanks a lot
Tom Kyte
May 14, 2009 - 10:28 am UTC

you'll never find them all to be the same/to match up. They are all recorded at differing points in time.

Rainey, May 14, 2009 - 10:18 pm UTC

Dear Tom

As you say above,the SCN information in the block is the the time when the block was changed and commited,it is the transaction time of that time,but the SCN information in the datafile head,controlfile time or redo logfile is about the current time. Is that right?

Best regards
Tom Kyte
May 15, 2009 - 1:29 pm UTC

think about it - if the files are not continuously updated in a "quantum mechanics way" (eg: truly simultaneously and constantly) - they can never be "current time"

The information in the datafiles, the controlfiles provide a baseline, a marker such that "stuff that happened before this need not be applied to us again"

Scofield, May 31, 2009 - 7:09 am UTC

sir;

The checkpoint scn in datafile headers are updated after checkpoint.

As far as I read from this thread;
scn's are written to redo logs continuously, it should also stored in controlfile continuesly(every commit) so that controlfile will know where redochain ends ..

Is that right?
Tom Kyte
June 01, 2009 - 7:56 pm UTC

... The checkpoint scn in datafile headers are updated after checkpoint. ...
sure, and lots of other times - many millions of times - but sure.

... scn's are written to redo logs continuously, it should also stored in
controlfile continuesly(every commit) so that controlfile will know where
redochain ends ..

.....


no, not at all. the stuff in the control file talks about the DATAFILES - not the redo log files. why would the stuff in the redo log files affect the processing of the controlfiles which show information about DATAFILES?


A reader, June 06, 2009 - 9:47 pm UTC

Sir
you mentioned that:
"the stuff in the control file talks about the DATAFILES - not the redo log files"
But controlfile should know the info in redologs because when I the control file is older than redologs , only way to open to db is with resetlogs option,since it doesnt know where redo chain ends. How come this happen?

Tom Kyte
June 08, 2009 - 1:06 pm UTC

there is a ton of information in the control files, the backup history, the archive history, stuff about datafiles. A ton of stuff.

someone wrote above:

... scn's are written to redo logs continuously, it should also stored in
controlfile continuesly(every commit) so that controlfile will know where
redochain ends ..



and I said "no, not really, control file scn related stuff that is recorded - about the datafiles - we do not write stuff in there every commit"


that is all.

if you use a backup controlfile - we lost history (of archives) we lost backup information and so on - that is why you need to "guide us" if you nuke your control files - because we lost information about the archives/redo.

A reader, June 10, 2009 - 4:08 am UTC

Sir, In the following scenerios, I have to use "recover using backup controlfile"
1-) if the controlfile is older than redo (doesnt know redo chain ends)

or

2-) datafile CKPT SCN ahead to the controlfile CKPT SCN.
Are there any scenerios? In short what is purpose of using "recover database using backup controlfile"?
I want to know your enlightining explanation rather than reading same things from manuels..
Tom Kyte
June 10, 2009 - 12:34 pm UTC

1) how would the controlfile be older than the redo - unless you restored an older controlfile - in which case "of course you would say 'using backup controlfile' since you obviously restored a backup????

So, the answer is "sure", but also "huh? it should be obvious since the way to get a control file older than redo is to.... well... restore it from a backup...


2) again, how would this happen? if you restore a backup controlfile. so once again, it is a backup.



... I want to know your enlightining explanation rather than reading same things
from manuels.....



you should rethink that position, I'm flattered and all - but seriously, start with the documentation - ask questions after you read it.

We'll both be a lot happier that way.

A reader, July 13, 2009 - 5:45 am UTC

Sir;
In Oracle we can issue "recover until time".
Does oracle store time as well as scn for every transaction or simply convert scn to time?
Tom Kyte
July 14, 2009 - 5:49 pm UTC

it converts time into an SCN that is approximately the scn in place at that time.

time based recovery is very "approximately", it will be "around that time". We do not have a precise mapping of time to SCN - and recovery is always SCN based. It just determines the nearest SCN it knows about close to that time.

A reader, October 10, 2009 - 2:33 am UTC

Hi sir;
Even there is no active user in the database, there is always activity by oracle processes and scn increments frequently.
What kind of action oracle perfoms in the background so that current_scn increments every commit?

To verify dataguard sync

Deep, October 24, 2009 - 2:33 am UTC

Thanks Tom for good explaination on SCN.

We have maximize availabilty dataguard configuration and want to check how many SCNs we lost when do the failover.So we were trying to see how much SCN difference we get between two databases.So My question is,

1. Lets say primary crashed when SCN was 1000 and at standby the SCN 995 is available.Now if we open the standby database in read only mode then will it have 995 SCN and will it keep on incrementing thereafter?
2. Primary database crashed at 1000 SCN, so when we bring up the database will it have 1000 SCN in the beginning when its open. OR will the SCN keep incrementing during db open process even though all the transactions have assigned SCN before crash. I mean will there be SCN increments during the db open process?

Do you suggest anything else to measure the data loss due to failover?
Thanks...Deep
Tom Kyte
October 26, 2009 - 1:47 pm UTC

1) if you open read only, it is read only - a commit increments the system change number, you don't commit in a read only database.

2) when you recover the primary database, the scn will almost immediately increase - yes.




But, to answer your question - what would the fact that primary was scn 10000 and standby was at 10000-n tell you as far as data loss goes?

In fact, if you open standby read only, and then you recover primary - the primary will start sending data to standby again - meaning there is NO data loss?

edwin, November 06, 2009 - 10:54 pm UTC

Hi Tom;
If I take the datafile offline, I will need recovery until stop scn of the datafile.
The reason behind this, checkpoint is not issued.
I know the task of checkpoint (write dirty blocks to disk,etc) but what is the relation between
checkpoint and recovery?
Dirty blocks can be written after some other checkpoints, why do I need recovery here?

Tom Kyte
November 11, 2009 - 1:48 pm UTC

... If I take the datafile offline, I will need recovery until stop scn of the
datafile.
...

not if you take it offline normal? Not sure what you mean. If you take it offline immediate (can only be done in archive log mode) then it will need recovery because blocks that are in the cache but not yet on disk in that datafile will need to be recreated when you want to online it again - and we need the redo to do that (that is why you have to be in archive log mode for offline immediate to work - we need the archives to recover that file later)


so, no, in general offline of a datafile will NOT necessitate recovery - in most cases it will not.

only if you offline immediate would we need to do that.

A reader, November 08, 2009 - 9:53 am UTC

Dear Tom,
I have a table that has no date column at a remote site. The remote site is on 9i. I need to select data of that table on daily basis and insert that into another table at another server which is on 10g through database links.
Lets suppose i configured jobs at hourly basis to select data and insert it to 10g table.But how should i compare the time.
For example.
insert into t1_10g select * from t1_9i@db_lnk where ???
Can I use SCN for this purpose?
I cannot add a new column in the table.

Thanks
Tom Kyte
November 11, 2009 - 2:04 pm UTC

you are out of luck then.

Please think about this - it is the equivalent of "I need to do magic here"


a) I have no date columns
b) but how should I compare time

(a) and (b) conflict with eachother.



Data replication cannot be performed without lots of work on BOTH SIDES of the network. The source system must help you and the target system must do what it needs to do. You cannot magically do something without changing *something*.

ora_rowscn didn't exist way back in 9i
even if it did, it isn't at the row level by default, you'd need to rebuild a table to get it.


If you cannot look at your data and tell me "what day was it inserted", we cannot tell you either.


You'll need to use your knowledge of the data to figure this out and figure out if you can even do it.

Suppose you only need to capture inserts, not updates, not deletes. Suppose the table in question has a primary key populated by a sequence. You could:

a) remember the last primary key you pulled in your data - start with 0 the first time.

b) go to the source database and insert into your_table select * from their_table@remote where key > :that_value

c) remember the highest key you just pulled - eg: update some_local_table set high_key = (select max(key) from your_table)


But you will have to analyze your data and understand how YOU can find out what has been "added, changed, removed" on your own


Or you can use replication (streams for example, materialized views, whatever) if you really need this data copied


Or - best way every - move the stuff from your database into that other database (consolidate) and just have access to the current stuff

Edwin, November 14, 2009 - 3:42 pm UTC

Thanks Tom;

You said that:

"If you take it offline immediate (can only be done in archive log mode) then it will need recovery because blocks that are in the cache but not yet on disk in that datafile will need to be recreated when you want to online it again - and we need the redo to do that"


1-)
Suppose,I forced checkpoint and perfom offline immediate immediately? Why do I again need recovery?
or
Suppose there is no block in buffercache and I perform offline immediate, why do I again need recovery?

2-)Suppose I have a dirty block in buffer cache and I perform offline immediate, does this dirty block remain in
buffer cache until I bring up the datafile?


Sorry Tom, it is little bit difficult to grasp
Tom Kyte
November 15, 2009 - 3:27 pm UTC

1) because you cannot atomically "checkpoint + offline immediate"

In fact, you cannot atomically "checkpoint"

suppose the checkpoint take 5 seconds. During that 5 seconds - would you like to guess how many blocks I can dirty in the cache for that datafile? Answer: it is a lot, only 1 is necessary, but it is greater than 1.

You cannot say what you say, because you don't know if there are any dirty blocks or not and neither do we.


2) no, it would not. You could shutdown and restart - blowing the SGA entirely away.


needed magic thats why went to the best magician!!

A reader, November 16, 2009 - 6:07 am UTC

Dear Tom,
I needed a magic and I came to the best magician. :-)
I have configured something like this.It solves the purpose with approximation.
I have used composite primary key as filter.Whats I a m doing is:
insert into t1 select * from t1@dblnk where (col1,col2,col3,col4) not in (select col1,col2,col3,col4 from t1);

Tom Kyte
November 23, 2009 - 9:36 am UTC

I'm not a magician - not by any means. Actually, if you ask me, that is fairly close to an insult :)


that approach will work for teeny tiny bits of data, but not long term.


and I hope that table is never deleted from nor updated.

Actually, since you are pretty much pulling the entire table locally every time anyway - why not just refresh full - just create a materialized view refresh complete and be done with it. No need to filter anything, just *replace* the entire set - you are pulling the entire set.

How is SCN tracked in case of Memory process commit

Abhisek, December 14, 2009 - 8:25 am UTC

Hi Tom,

I understand when you say that I am not the lone user in the system. Other background process are there which commit the special changes that are bound to.

My question is:

1. If commit through memory process all along while a user is reading or writing data, how is non-dirty read ensured? Is there a role for undo as well?

2. Can I see the process name that are doing commit everytime?

3. Do we have a function that acts just opposite to scn_to_timestamp means converts a timestamp to SCN.. I know its unnecessary but I am just curious..

Please help me in resolving the queries
Tom Kyte
December 14, 2009 - 10:03 am UTC

1) I could not parse that sentence.

What does "if commit through memory process" mean?

What does "is there a role for undo as well?" mean?


Basically, you open a query. We ask ourselves "what time is it", we remember that time (scn) - say 12350.

As we are hitting database blocks for your query, we look at the transaction header in the block - we look at the last transaction(s) to hit that block and ask the undo segment headers "when did this transaction commit". The undo segment header can tell us

a) it committed a long time ago, this block is good enough for you.
b) it committed at scn 12345 (and 12345 <= 12350 - so this block is good)
c) it committed at scn 12455 (and 12555 > 12350 - this block will be rolled back)
d) it has not committed yet (and this block will be rolled back)

In the cases of (a) and (b), we use the block as is

In the cases of (c) and (d), we ask the undo segment for the rollback data needed to put the block back the way it was before the last change - and we REPEAT the process until we get an (a) or (b) answer - then we use that block version.




2) you can use log miner to see what is in the redo, information will be in there - might not be the process, but it really isn't necessary to know anyway.


3) http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions176.htm#SQLRF06326

How is SCN tracked in case of Memory process commit

Abhisek, December 15, 2009 - 12:01 am UTC

Thanks Tom for the reply. I am sorry for a confusing question. With your reply to a query in reference, I meant we know that we are NEVER the only user.

select username, program from v$session;

Now, since the database never sleeps. Most of those other "programs" do transactions and commit.

So does that mean that after the commit due to these commits as well, the data is written to the datafiles from the dirty/pinned blocks? Or are these special commit that dont have any relation with database writes?
Tom Kyte
December 15, 2009 - 9:00 am UTC

I have no clue still what "Memory process commit " is, does, or means.



A commit by any other name would still persist data.


A commit is a commit is a commit - period. I don't care who issues it, it is a commit - whether it comes from PMON, SMON, or you.

SCN

Abhisek, December 15, 2009 - 10:07 am UTC

Hi Tom,

I am so sorry for being dumb.. By Memory process I meant the process like SMON, PMON etc..

Thanks a lot.
Tom Kyte
December 15, 2009 - 10:25 am UTC

those are called background processes for the database - or by their names. There is no such thing as a "memory process"

SCN

Manoj, December 17, 2009 - 11:15 pm UTC

Hi Tom,

As far as I could understand going through replies, that SCN number is incremented by background processes, even though I am single user and not doing anything. Please correct me if I am wrong.

If I am correct, then once user start transaction and they also commit, how are the commits from background processes and users handled?

How is dirty read avoided? Can you please explain with some example so that I can easily understand.
Tom Kyte
December 18, 2009 - 12:32 pm UTC

that scn number is being incremented by some process issuing "commit". The background processes - all of them - have the ability to do that, SMON will do it regularly, like clockwork.


A commit is a commit, like a rose is a rose is a rose. I don't care if a 'background' does it, or a foreground, or above ground, below ground, in ground - it doesn't matter. A commit is a commit is a commit is a commit.

... How is dirty read avoided? Can you please explain with some example so that I
can easily understand. ...


https://www.oracle.com/technetwork/issue-archive/2005/05-nov/o65asktom-082389.html

A reader, January 30, 2010 - 12:58 am UTC

Hi Sir;

What if the transaction is rolled-back? Does the scn again increase?
Tom Kyte
February 01, 2010 - 9:43 am UTC

yes:

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
 155294980

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  insert into t values ( i );
  5                  rollback;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select scn, scn-&s from (
  2  select dbms_flashback.get_system_change_number scn from dual
  3  );
old   1: select scn, scn-&s from (
new   1: select scn, scn- 155294980 from (

       SCN SCN-155294980
---------- -------------
 155297004          2024



ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
 155297004

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 10000
  3          loop
  4                  insert into t values ( i );
  5                  rollback;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select scn, scn-&s from (
  2  select dbms_flashback.get_system_change_number scn from dual
  3  );
old   1: select scn, scn-&s from (
new   1: select scn, scn- 155297004 from (

       SCN SCN-155297004
---------- -------------
 155317184         20180



even more than if you do not rollback - but commit instead

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
 155317244

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  insert into t values ( i );
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select scn, scn-&s from (
  2  select dbms_flashback.get_system_change_number scn from dual
  3  );
old   1: select scn, scn-&s from (
new   1: select scn, scn- 155317244 from (

       SCN SCN-155317244
---------- -------------
 155318290          1046



ops$tkyte%ORA11GR2> column scn new_val s
ops$tkyte%ORA11GR2> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
 155318290

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 10000
  3          loop
  4                  insert into t values ( i );
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select scn, scn-&s from (
  2  select dbms_flashback.get_system_change_number scn from dual
  3  );
old   1: select scn, scn-&s from (
new   1: select scn, scn- 155318290 from (

       SCN SCN-155318290
---------- -------------
 155328505         10215

ops$tkyte%ORA11GR2>




we are definitely optimized to commit, rolling back is expensive.

A reader, February 06, 2010 - 12:12 am UTC

Thanks for the example sir;
I understand that Scn increases both commit and rollback.
Regarding the rollback, scn increases much more than commit.

One last question:
Is there any difference between:

select CURRENT_SCN from v$database;

and

select dbms_flashback.get_system_change_number scn from dual;
Tom Kyte
February 09, 2010 - 6:47 pm UTC

for a "normal" database (not standby), they are for all intents and purposes the same - they could be a LITTLE different if you do something like:

select current_scn, dbms_flashback.get_system_change_number from v$database;


since they would be evaluated at two slightly different points in time, but consider them "the same"

Oracletube

Oracletube, February 11, 2010 - 12:33 pm UTC

Hi Tom,

What is the easiest way to track changes to SCN?

Thanks,
Oracletube

www.oracletube.com
Social networking and knowledge sharing portal for Oracle professionals.
Tom Kyte
February 16, 2010 - 10:58 am UTC

no idea what you mean by "tracking changes to SCN"

every time a transaction ends - SCN goes up. What more do you need to know?

SCN & Rollback

Saptarshi, February 15, 2010 - 10:49 am UTC

Sorry Tom...

I could not understand why a Rollback is generating 2 SCNs. The purpose of SCN is multiversioning concurrency control. I am just trying to understand how it works internally and why the architecture required two more SCNs for rollback. What will the select query do when it finds a block on which a transaction started and rolled back since the query started?

I always thought the scenarious would be like this:

1. The query finds the block (to be read) has an SCN greater than than the query start time. It means the block has undergone commited changes since the query started and hence rollback.
2. The query finds that the block has smaller SCN but some transaction is going on (i.e. the block is dirty) and the ongoing transaction is not commited, hence rollback.
3. Before the query can read the block some transaction started on that block and rolled back. So use the block as is.
Tom Kyte
February 16, 2010 - 5:08 pm UTC

... The purpose of SCN
is multiversioning concurrency control....

it is not, that is one of the USES, it is not the reason for being.

The reason for being is recovery and consistency of data - something which rollback plays a big part in (as does commit)


1) has undergone CHANGES - committed or rolled back OR NOT (or not, could be not yet committed/rolled back)

any block that was modified since the query began, keyword = modified.

OK

Saptarshi, February 17, 2010 - 5:58 am UTC

Okay, that makes sense. Yes, a rollback is really two changes in the system. One is the modification and then reverting it back to the original state.

A reader, March 12, 2010 - 8:22 pm UTC

Hi Sir;

You mentioned that scn increments every commit or rollback.

How about scn of blocks?
When we issue commit does all block scn increments?
Tom Kyte
March 15, 2010 - 9:53 am UTC

blocks do not have SCNs that they 'own'.

the scn related to a block is just like a timestamp, it tells us when the block was last modified.

Just like as the seconds tick away on a clock (scn increments), you would not update every row in your database that has a "last modified" column (we do not modify all timestamps)

A reader, April 10, 2010 - 4:12 pm UTC

Does it mean that, snc in blocks increments when we touch the block.
(Not every commit or not every checkpoint)
Tom Kyte
April 13, 2010 - 9:05 am UTC

what did I say above to make you think that? Please point it out so I can clarify it and make sure no one else comes to that conclusion.


The SCN advances as transactions complete. Transactions are like our "seconds", they are what makes the clock tick.


tick-tock-tick-tock is like commit-commit-rollback-commit

A reader, April 24, 2010 - 9:32 pm UTC

Thanks sir;
You mentioned that scn increments every commit or rollback.
I am just confused with block scn.
When a commit is issued, does scn of all blocks incremenets?
or
only the scn of modified block increments?

Regards
Tom Kyte
April 26, 2010 - 9:00 am UTC

the block timestamp is the timestamp of the modification of the block itself (and it is a logical timestamp, not a literal one). The information on the block is a transaction history, from which we can determine if the transactions that last affected the block are still in process (rows locked), commited (rows not locked anymore) and if committed "when" it committed - either exactly by knowing the SCN of the committed transaction OR just by knowing "it committed a long time ago".


The 'scn' of a block is not touched during a commit, it is a logical thing - not physically there.

Now, that said, during a commit - we do a thing called block cleanout (see expert Oracle database architecture for details, or search for "block cleanout" on this site - or google around for it) - and that is used to say "hey, that transaction that had locked rows - it committed". That cleanout does NOT have to happen, will not always happen - it is totally optional - we'll clean out the block later if we don't do it during commit.

But it isn't incrementing anything, it is just saying "transaction x.y.z is done"

Rollback ==> SCN

Vis, May 07, 2010 - 1:49 pm UTC

Hi Tom,
I am still confused why a Rollback operation increaments SCN by 2 as compared to 1 by commit.
What is special in the Rollback operation which cause this

Thanks

Tom Kyte
May 08, 2010 - 7:22 am UTC

I haven't ever realized the need to know, so I'm happy just with the knowledge that "rollback", like commit, means 'end of transaction' and hence we know for a fact that the scn will advance - by how much, I don't care (I can make the SCN jump around by hundreds of thousands if I want, I just need a distributed database environment....)

SCN increases, like a sequence.
SCN is not gap free, like a sequence by default.
SCN is a lot like a sequence.

SCN jumps and gap

Albert Nelson A, May 14, 2010 - 5:32 am UTC

Hi Tom,

You have mentioned that you can make SCN to jump in a distributed environment. In that context, can you please elaborate on the following

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/ds_txnman.htm#sthref4650

<quote>
For example, you can issue an update statement and then commit the update on the remote database. When you issue a SELECT statement on a view based on this remote table, the view does not show the update to the row. The next time that you issue the SELECT statement, the update is present.
</quote>

The above seems to mean that in certain circumstances, after we update a remote table and commit the transaction, the very first time we query the same table we will not be able to see our change. Is my understanding correct?

Regards,

Albert Nelson A.

SCN Generation is within +/- 3 or 6?

Aman...., August 30, 2010 - 5:33 am UTC

Sir,

This is regarding my post on April 3, 2009 where I asked that how can we see that SCN to time mapping is +/-3 seconds for which you have given a query as well. But this Metalink Note 281510.1, its mentioned that its +/- 6 seconds compared to 5 minutes of 9.2. I am a bit confused reading this. Can you please confirm that whether the info in the document is correct or not?

Regards

Tom Kyte
September 09, 2010 - 9:12 am UTC

that note says it is updated (the timing information) every six seconds. No where does it say "it is within +/- 6 seconds", they say "updated every six seconds.

which means, it is accurate to within plus or minus 3 seconds.

ORA-01466

Amardeep Sidhu, August 30, 2010 - 6:02 am UTC

Hi Tom,

Related to the above post by Aman, here is the link to a thread on the OTN forum, where we were discussing ORA-01466 while flashbacking a table and then hit that Metalink document while searching for ORA-01466.

http://forums.oracle.com/forums/thread.jspa?threadID=1121415&tstart=0

Regards,
Amardeep Sidhu

Aman...., August 30, 2010 - 6:08 am UTC

I just was going to add the link for the OTN thread but Amardeep already has done it.


Regards

Tom Kyte
September 09, 2010 - 9:13 am UTC

ok, that is a big thread, can you tell me what the relevance is? what we should be looking at.

Aman...., September 10, 2010 - 12:38 am UTC

Sir,

In that thread, the confusion did arise when the Flashback command was sometimes working and sometimes, didnt! And when we tried to find the answer, it came out that only after a 6seconds delay after the DDL to the object, it would be working. But even this suggestion was sometimes working, and sometimes it was not in action. So the question that did arise out of all this that one)what's that "magic number" after which flashback command would be working for sure" second) Why such number even is there?

To put more precisely, here is the original poster John's question,
{quote}
That's the part that concerns me the most. If the wait isn't placed just in the right place, after that, waiting doesn't seem to work. It's hard to feel comfortable with a feature that seems to be so sensitive.

I hope that Tom Kyte can provide a solid method to make the feature work predictably and, hopefully he'll mention what to do to correct the situation when that error is encountered.{quote}


This is the confusion point/question for which we were looking for your help.
Tom Kyte
September 13, 2010 - 6:57 am UTC

who is John and where is the context for this???

I don't see the name John on this entire page.

Amardeep Sidhu, September 16, 2010 - 3:21 am UTC

Hi Tom,

John was the guy who asked this question on the OTN forum (link to that thread i posted above).

He is not there on this AskTom thread :)

Regards,
Amardeep Sidhu
Tom Kyte
September 16, 2010 - 7:03 am UTC

Like I said, "big thread, I did not read it". So, give context - put it here.

SCN and ORA_ROWSCN

Som Debnath, September 21, 2010 - 2:34 am UTC

Hi Tom,
What is the difference or similarity between SCN and ORA_ROWSCN? Where does oracle store SCN?

Regards,
SomD
India
Tom Kyte
September 21, 2010 - 3:48 pm UTC

the SCN is like a clock - it is always advancing (print out dbms_flashback.get_system_change_number, wait a few seconds, print it again - it'll have advanced)...

So, just think of the SCN like a ticker, like time - every time a transaction ends - another unit of time is added, like adding seconds to time...

ora_rowscn is an observed point in time. Like "lunch" , or 3am, or your birthday. The ora_rowscn is a value associated to a block or a row on a block that represents the "time" the block/row was last modified.

SCN is not "stored" anywhere really - it just "is" - like time "just is"

oracle scn

Arun, October 11, 2010 - 11:27 pm UTC

hi sir,
can u give me a brief about the scn numbers in oracle 9i and their types
Tom Kyte
October 12, 2010 - 7:25 am UTC

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo


(this leads me to believe you might not have actually read the article above?...)



the are no different in 9i then in ... well - the original answer where the question was "what is SCN"

and the answer was already printed...


there are not "types" of SCN's - there is the SCN. It is a ticker, a clock, a number that advances with time as you end transactions. There is just "the SCN"

SCN

Ik, November 30, 2010 - 11:57 am UTC

Hi Tom,

In the thread it was mentioned that SCN is crucial for recovery. Assuming that my oracle database has 5 datafiles. Would every ONLINE datafile have the same SCN in all the headers? When would a datafile header be updated with a newer SCN? What SCN will be recorded in the control files. When would all datafile headers have the same SCN - is that during checkpointing ?

thanks
Ik
Tom Kyte
November 30, 2010 - 12:19 pm UTC

... Would every ONLINE datafile have the same
SCN in all the headers? ...

maybe, maybe not. it would be impossible to update all 5 files simultaneously - so there would be time lags. some could be read only.

They do not have to have the same header information - they can all be at different stages of "fuzziness", it is expected. Think about a big checkpoint - we cannot write all of the blocks out in one atomic statement so all of the files will be at varying levels of "point in time"

Understanding SCN

Kangy, January 29, 2011 - 6:39 am UTC


1. There is only one SCN across the system. It increments whenever a transaction is committed, during a checkpoint and during other events (like commit) as well.

2.However, when the SCN incremented during checkpoint is stored only in data files and control files (not in redo log files). This SCN we are calling as checkpoint SCN.

3. The SCN incremented during a commit is stored only in redo log files. We call it as Commit SCN.

Please correct my understanding
Tom Kyte
February 01, 2011 - 3:51 pm UTC

Just please think of it like this:

The scn advances over time like a clock, it is a clock. We record the time (the scn) in various places so we know what to apply and to what to apply it to.



There are many things that advance the SCN - a distributed transaction can for example. Listing an exhaustive list is not fruitful. Just remember - the SCN advances over time like a clock. It is a clock. It is time, it is like looking at a watch. that is all it is.

Header Block SCN and Data Block SCN

Parul, February 21, 2011 - 4:30 am UTC

Hi Tom,

When we have few dirty buffers of one table in the buffer cache and we issue the command :-

"alter system checkpoint"

The checkpoint SCN of the data block is updated and ITL is also updated as :-

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.020.00002b46 0x00c00235.0d0f.15 --U- 3 fsc 0x0000.00ddffee
0x02 0x0001.012.00002088 0x00c0021d.0b70.07 --U- 1 fsc 0x0000.00df1407

But the header block (after we dump and see ) of the file still contains the same scn as before irrespective of the change in the data block .

I have two points here :-

1. When is the header block scn updated ?

2. The checkpoint_change# column in v$datafile,v$datafile_header doesn't match with scn number in the header block ( after we dump and see ) .
So from where does v$datafile_header picks up checkpoint_change# column value ?

Thanks in advance.
Tom Kyte
February 22, 2011 - 9:43 pm UTC

those things on the ITL are transactions that have touched the block, they do not reflect the block itself - that is just a history of what has affected the block.

1) when the block is modified. why would writing it out make a difference to it - it (writing it) doesn't make it any "newer" than it was before

2) think about the fact that writing N blocks to disk is not an event that happens in a quantum instant - it happens over time... One value is the "farthest back in time we have to go for the file", the other is "this is the value for this specific block, which may be less than the file since I was written out later"

SCN NUMBER RESIDES WHERE?

P.Shashikanth, March 16, 2012 - 7:52 am UTC

Hi tom, i want to know the place where the SCN number resides?
As far as i know SCN is found at datafile header and control files.
But the Datafile header will have all the SCNs and control file will have only current SCN number.

Then how come we recover the database from redo logs and archive.
Does archive and redo logs also contail scn numberes?
Please reply me as i'm struck at this point
Tom Kyte
March 16, 2012 - 8:44 am UTC

Hi tom, i want to know the place where the SCN number resides?


nowhere really, it is like time itself.


A value of the SCN, taken at various times, representing the time something happened is stored in many many many places, sort of like a timestamp would be.


Datafiles have SCNs associated with them (times of various operations)
control files have them (times of various operations)
log files have them (to record times of various operations)
undo segments have them (......)

they are littered all over the place, they are like timestamps.

Flashback Recovery Area and Undo Tablespace

Vimal, March 19, 2012 - 3:54 am UTC

Hi Tom, I have read your explanation about SCN. But still, if you don't mind I have some questions to you.

Where do the data corresponding to each SCN get stored? In FB_Recovery Area or UNDO Tablespace?

Secondly, What's the difference between UNDO and FB_Recovery Area?

Thirdly, For example, when we create an object, how oracle writes it to the Tablespace? I mean is there any background process that deals with it? Also, will the UNDO tablespace contain only the recently COMMITTED undo data or it contains the data even before that?

Thanks in advance!

Tom Kyte
March 19, 2012 - 10:18 am UTC

Where do the data corresponding to each SCN get stored?

there is not any such concept. That is like asking "where does the data for 10:02:57am get stored"? It is a timestamp. A reference to a point in time.


Undo is used to rollback, it is a transaction thing mostly - you rollback a failed statement/transaction.

the fast recovery area is use to restore - to recover with, to roll forward in general. or to put the database back the way it was (flashback database). It is a recovery structure for the entire database, not a single statement, not a single transaction.


when you create an object - we run internal code to insert/update/delete the data dictionary to register that object in the dictionary and if necessary perform disk operation to initialize space for it. It is pretty much done in the session that initiated the DDL itself.


Undo has uncommitted stuff (you generate a block of undo as soon as your modification starts and keep generating them as your statement progresses). It will also contain "committed" transactions undo for read consistency purposes.

Flashback Recovery Area and Undo Tablespace

Vimal, March 19, 2012 - 3:54 am UTC

Hi Tom, I have read your explanation about SCN. But still, if you don't mind I have some questions to you.

Where do the data corresponding to each SCN get stored? In FB_Recovery Area or UNDO Tablespace?

Secondly, What's the difference between UNDO and FB_Recovery Area?

Thirdly, For example, when we create an object, how oracle writes it to the Tablespace? I mean is there any background process that deals with it? Also, will the UNDO tablespace contain only the recently COMMITTED undo data or it contains the data even before that?

Thanks in advance!

Almost cleared doubt

Vimal, March 19, 2012 - 11:44 pm UTC

Hi Tom,
First of all I am glad that you have answered my question. Thanks!

I think I should have asked my first question like

"Where Oracle retrieves the corresponding data at a particular timestamp from?"

However I know that Oracle keeps each and every updates to the database with a unique SCN. But I like to know where it retrieves those from when we query for data at particular SCN. Maybe in some tablespace it keeps all the updates with SCN?

Secondly, one of my trainers said me that when we query for updating an object, the query goes through SGA and to the Disk to bring the data back into the DB_buffer and performs the updates and writes it back into the disk.

So, if this is the case what will happen if we create a new table? Will it create a new table in the buffer and write it to the disk?

Also, I know that from 10G, Oracle maintains a Recyclebin for the dropped objects. But my doubt is, which tablespace contains this RecycleBin?

Thanks again Tom.
Tom Kyte
March 20, 2012 - 9:16 am UTC

However I know that Oracle keeps each and every updates to the database with a
unique SCN.


No, not really, an SCN is assigned to a transaction (not the individual updates) at commit time of that transaction.

Maybe in some tablespace it keeps all the updates with
SCN?


Nope, again - SCN is like a "time". What we do when you ask for data "as of SCN 'x'" is get a block and look at the SCN associated with that (we do that by looking at the transaction information in the block header).

If that block is "newer" than time 'X' - we roll it back 'once' (apply undo to it from the undo segment) and inspect it again. If this rolled back version is still newer than 'X' - we do it again, and again, as many times as we need to in order to get the version of the block that has an SCN associated with it that is less then or equal to 'X'.



As for the SGA/disk question, the SGA is just a buffer for data on disk. If this buffer gets full - we write some blocks out to disk to make room. When you go to get a block for a query - we look in the cache first and if we don't find it - we read it in from disk, put it in the cache and return it to you.

So, if you create a new table and start inserting into it, the inserts will probably go into the SGA and get written out by DBWR at a later point in time (assuming normal inserts and not a direct path load).


As for the recyclebin, it is not in a tablespace - rather it is in every tablespace. when you drop a table, the table and indexes stay right where they are - they are "flag" deleted. We rename them and 'hide' them from you a little. They are not entirely hidden, you can find them, you can query them, you can even flashback query them - but they might disappear at the drop of a hat if we need to reuse their space. But they stay right exactly where they were before they were dropped.

Redo Record

Vimal, March 21, 2012 - 3:37 am UTC

Hi Tom,
I have something to get cleared.

People use to say that Oracle writes all the committed and uncommitted changes(Redo entry) to the log buffer? My question is does really the uncommitted transaction change the data-block of particular table in the datafile?

Secondly, Where does the redo record (or) redo entry in the log buffer gets generated from? From the updated Buffer Cache or from the updated datafile?

Thanks!
Tom Kyte
March 21, 2012 - 9:49 am UTC

when you update a row, you will

a) modify the block (that generates redo that goes into the redo log buffer and eventually gets written out to the redo log - that write can happen before the transaction commits)

b) modify an undo block, this too generates redo that goes to the log buffer that goes to disk (either before or during the commit of the transaction)


That block is now in the buffer cache 'dirty'. That block make well be written to disk (as well as the undo block) at any point in time - either before, during or after the commit takes place.


Otherwise, we could never update a table that was larger than your buffer cache! The buffer cache is just a cache of the datafiles. the datafiles are 'fuzzy' during normal operations - containing a mixture of committed and uncommitted changes


(note: with private redo, we might not be using the official log buffer but the concept is the same)

VISWAJEET SAMAL, March 21, 2012 - 3:56 am UTC

Hi Tom,

We had a patching last night, and now we are getting some alert in the alert log like

Wed Mar 21 12:30:51 2012
Advanced SCN by 76 minutes worth to 0x0ba7.3f89292f, by distributed transaction logon, remote DB: DBNAME.
Client info : DB logon user USERNAME, machine HOSTNAME, program oracle@HOSTNAME(TNS V1-V3), and OS user ora10g


THE SAME WAS NOT COMMING PRIOR TO THE PATCHING, CAN YOU PLEASE GUIDE US WHY THE SAME IS HAPPENING.

VISWAJEET SAMAL, March 21, 2012 - 3:58 am UTC

Hi Tom,

We had a patching last night, and now we are getting some alert in the alert log like

Wed Mar 21 12:30:51 2012
Advanced SCN by 76 minutes worth to 0x0ba7.3f89292f, by distributed transaction logon, remote DB: DBNAME.
Client info : DB logon user USERNAME, machine HOSTNAME, program oracle@HOSTNAME(TNS V1-V3), and OS user ora10g


THE SAME WAS NOT COMMING PRIOR TO THE PATCHING, CAN YOU PLEASE GUIDE US WHY THE SAME IS HAPPENING.
Tom Kyte
March 21, 2012 - 9:50 am UTC

BECAUSE IT WAS ADDED. There was an issue with SCNs and they've put in some diagnostics

please contact support and open an SR for something like this in the future, it is so obviously a support question.

SCN Checkpoint, Redo

Vimal, March 26, 2012 - 4:25 am UTC

Hi Tom, you said me that,

When we do update operation, it will change the db block and the undo block as well. So both generate redo entry will will be buffered in Log Buffer and then to the Logfile. So, I feel that the change made to the data block will be exactly same as change made to the undo block.

1) For example, if we update a row, both db block and undo block will be modified in the buffer cache and then written to the disk(Into both default and undo tablespace). Here generated redo entry contains vector changes of both the undo and db block. So, the changes must be same..Is it right Tom?

2) When we shutdown the database, will the Archiver write all the "Active" redo files into Archive log before the DB shuts down?

3) My trainer told that when we shutdown the DB normally, the SCN in the datafile header will be copied by the Control File. And while starting up both must be checked by Oracle for the equality. If not same then Database starts Recovery. My Doubt is when not same, how it manages the recovery using the current redo log file? What actually happens during Instance Recovery?

4)How Abnormal shutdown differs from Normal One. What things in database get messed up. I mean what actually happens.

5)Where are the checkpoints generated(in which area)?

6)How can you differentiate Clear logfile and the Resetlogs when altering database especially after recovering controlfile from backup after losing it? I mean why can't we use clear logfile instead of Resetlogs during control file recovery?

Sorry for bunches of sentences.
Thanks Tom.


Tom Kyte
March 26, 2012 - 7:17 am UTC

So, I feel that the change made to the data block will be
exactly same as change made to the undo block.


tell me how you came to that conclusion???? really???

We have a database block with a row "X" on it.

We do an update so that row "X" becomes now row "Y" with different values. On the database block, we'll have "Y". In the UNDO block, we'll have "the Nth row on block M in file F used to be 'X' ". the undo block has what was there before, the data block has what is now there. The redo for the data block will have the "after image", the redo for the undo block will have the "before image"


1) but the changes are entirely different. One has the "post modification" image and one has the "pre-modification image" (you know, so we can UNDO the operation - hence the name)

2) no, we generate archives when

a) an online redo log fills
b) you tell us to manually
c) based on a time period via an init.ora parameter

a shutdown doesn't trigger an archive

3) During recover, we can peek at the datafile to see how current it is (its timestamp - the SCN associated with it). If it is not "entirely current" (needs recovery) all we have to do is find all of the redo generated AFTER the time of the datafile. The redo logs are a sequential history of what happened - so we just read them in order and apply the redo that happened AFTER the time of the datafile to that datafile, until we run out of redo and then that file is 'current'

4) we just crash the instance, we don't perform a checkpoint and make the files consistent first.

During a 'normal' shutdown (or immediate) - we checkpoint and make it so that all datafiles are "current" and consistent.

During an abort shutdown, we just crash the instance, we don't checkpoint, we know we'll have to redo the online redo log files to make the files consistent during startup.


5) checkpoints are not generated into an area. A checkpoint is a thing, an activity. It is the act of writing to disk all of the blocks in the buffer cache that are protected by some bit of online redo log. it is getting the dirty blocks out of the cache and onto disk so that we don't need their redo anymore for instance crash recovery (we still need that redo for MEDIA recovery, but not for crash recovery)

5) http://docs.oracle.com/cd/E11882_01/server.112/e25494/onlineredo008.htm#ADMIN11327

clear is used to clear out a logfile group in the event it gets corrupted. It would hardly ever be used.

reset logs does just that. resets all of the log groups and begins anew

Crash Recovery SCN

Vimal, March 26, 2012 - 10:42 pm UTC

Hi Tom,
I know that a checkpoint is a process of writing all the modified buffers from buffer to disk.

1)But What triggers this checkpoint process to occur?

2)What are the areas where this occurrence will be updated?? Will this include Redolog file, datafile etc?
Will this generate SCN as well there?

3)I like to know 'in steps' about what exactly Oracle does (especially things like what it compares & so on..) before & during recovering instance during next startup. In the same contest, what tests does oracle make to decide that it needs recovery.

4)If database crashes when there is uncommitted changes which will be in Redolog, how oracle identifies these uncommitted changes without an SCN, that a recovery must be done until this point?

5)This question is from different context. Where is the location of the base tables of the dynamic performance views? I hope they are not in any of the tablespaces since they can be seen at the time of database mount itself.

Thanks Tom
Regards.
Tom Kyte
March 27, 2012 - 7:41 am UTC

1) many things, they are almost always going on at some level these days.

Major events are

a) log switches
b) based on time (archive_lag_target)
c) based on amount of dirty blocks (fast_start_mttr_target)
d) buffer cache getting full
.....


2) everywhere basically. data files, control files, logs. SCNs are generated in response to commits/roll backs, checkpoints record the "time" (and since and SCN is just "time"

3) that would be thousands of pages in length probably. All we need to know is

a) we look at the datafiles/controlfiles and determine how far back in the online redo logs we need to go (how far back in SCN time we need to go)

b) read the redo starting at that point in time and read the redo from there upto the last bit of redo

c) apply those redo changes to the datafiles if necessary (some of them may already have been applied)

d) open the database and start rolling back anything that hadn't committed (this is the fast start recovery - we can open the database after rolling forward, but before we finish rolling back)



4) we can identify them by the lack of a commit scn in the undo segment associated with the transaction that generated the changes. After we roll forward - we can tell if a transaction is committed or not (we do that all of the time - every time - even without crash recovery - we can tell you if transaction X is still going or has committed)

5) mostly they do not exist, the dynamic performance views are mostly views on memory structures in the SGA.

SCN DB Buffer

Vimal, March 27, 2012 - 2:42 am UTC

HiTom I read this from oracledoc
Oracle Database increments SCNs in the system global area (SGA). When a transaction modifies data, the database writes a new SCN to the undo data segment assigned to the transaction. The log writer process then writes the commit record of the transaction immediately to the online redo log. The commit record has the unique SCN of the transaction. Oracle Database also uses SCNs as part of its instance recovery and media recovery mechanisms.

My doubt: when there is any modification to data both dbblock and undo block will be in buffer cache for the requested update. So both will generate redo record which will be in log buffer. So this shows that Redo file will be filled first before the undo tablespace or the db tablespace.

1)If this is so how they say that SCN for the transaction will be generated first in Undo segment and then in redofile when commit issued?

2) if I update a row of data X with data Y and again after sometime with Z, the db tablespace now contains the updated value Z and not anything that was before. At the same time undo tablespace contains what? Only X and Y? or what. Tell me at the novice level Tom since I am not very clear in this.

3)I hear many saying that dbwriter regardless of user issuing commit, writes the data anytime as its wish. If this is so, the read consistency will be affected till it writes into database even after the user issued commit. Isn't it? I mean that I'd someone queries the data he will see only the old data even after commit, till it writes to database. Am I right?

4)Consider data was committed at SCN 100. Then an uncommitted transaction was going on at SCN 104. if I query as of 104, which data do I get?

Thanks Tom.

Tom Kyte
March 27, 2012 - 8:17 am UTC

So this shows that Redo file will be filled
first before the undo tablespace or the db tablespace.


how so? we have to modify a block in order to generate redo for the block. How can we have redo for something we haven't done (chicken and egg).

Not that is matters - what happens is

a) we get a block
b) we modify block
c) that generates redo (to redo the modification to the block)
d) that generates undo (to undo the modification to the block)
e) which in turn generates redo (to redo the modification to the undo block)

and that redo is constantly being streamed to disk - to the online redo log files, and the data/undo blocks are buffered in the buffer cache (but could easily be written out to disk by DBWR if we need more space or a checkpoint forcing them to disk comes along).

That is what happens.

then later, you commit and the whole SCN stuff comes into play - at the commit.


1) because your conclusion wasn't a valid conclusion. You said "so this shows..." but I don't see how anything you wrote "shows" that.

2) the undo tablespace would contain information to change Z into Y and Y into X to get back to what you had in the first place. If you update a single row over and over - you'll generate gobs of undo:

ops$tkyte%ORA11GR2> create table t ( x int, y char(2000) );

Table created.

ops$tkyte%ORA11GR2> insert into t values ( 1, '2' );

1 row created.

ops$tkyte%ORA11GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
         1

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 1000
  3          loop
  4                  update t set y = to_char(i);
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select used_ublk from v$transaction;

 USED_UBLK
----------
       335



so in that undo there is a command to turn y=1000 into y=999, then y=999 into y=998 and so on, until y ultimately becomes '2' again.

3) how would that affect read consistency? If the version of the block we need isn't in the buffer cache (because dbwr got rid of it, because it aged out, whatever), then we put it there again. Where the block currently exists doesn't change the fact that we need "version x" of the block - we'll get it again - even if we have to go to disk to get it.


4) If you are not that transaction, you'll get the SCN=100 data. If you *are* that transaction - you may see your own uncommitted data (you are the only one allowed to do that) - and you'll see the change.

SCN

Vimal, March 27, 2012 - 10:16 am UTC

Hi Tom, regarding my first question in the previous review, I will say again;

Actually, I have so far understood that when transaction commits and when the changes are made to the undo and db block in buffer cache, a redo entry generates which will be written into redolog with a new SCN. Also, DBwriter before writing to the database signals the Logwriter to put the redo entry into the redolog. Now I can say that this shows Logfile fills first before Datafile!

But in the Oracle DOC, they say "When a transaction modifies or inserts data, Oracle first writes a new SCN to the rollback segment. The log writer process then writes the commit record of the transaction immediately to the redo log, and this commit record will have the unique SCN of the new transaction."

As they say that Oracle first writes a new SCN to the rollback segment, I think it means that some process is writing the SCN into Undo Segment(which I think is in the buffer cache now) before Logwriter writes into redolog.

Clear me Tom.
Regards,
Viaml.
Tom Kyte
March 27, 2012 - 11:12 am UTC

Now I can say that this shows Logfile fills first before Datafile!

you are confusing the flushing of a dirty block to disk with the modification of a block. If we modify the block in memory - the block is as good as modified. it does not have to be written out to disk.

So, while you can say "logfile is written to before datafile" - and be correct - it is not relevant to the order of operations of a modification of data and the subsequent commit.


Timestamp

Vimal, March 27, 2012 - 8:40 pm UTC

Hi Tom, 
       I was trying to change the Sysdate format in 11G using the command: Alter System set NLS_DATE_FORMAT='DD:MM:YYYY HH:MI:SS'; But it says,

SQL> alter system set nls_date_format='DD:MM:YYYY HH:MI:SS';
alter system set nls_date_format='DD:MM:YYYY HH:MI:SS'
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option  

Can you give me an idea to recover from this please?

Tom Kyte
March 28, 2012 - 8:41 am UTC

first - you don't want to use hh, use hh24 (or you'll need an am/pm format as well)

second - you probably don't want to do this at all, never never never rely on defaults.

third, even if you do this, it probably won't 'stick'
http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551282707163926958

any windows clients will have at least one nls thing set in the registry by default and hence, they will NOT see this from the init.ora



ops$tkyte%ORA11GR2> alter system set nls_date_format = 'DD:MM:YYYY HH24:MI:SS' scope=spfile;

System altered.


or edit your init.ora if you are using a pfile and set it.


and then restart



but - you do not want to do this, trust me. I am dead serious. do not rely on default nls_date settings.

ALWAYS use a format with to_date
ALWAYS use a format with to_char


Recycle bin, Log Sequence Number

Vimal, March 28, 2012 - 10:03 am UTC

Hi Tom,
1) I dropped a table and tried to get it back from recyclebin using Flashback table before drop. I got an error saying that the object is not in recyclebin. What may be the reason?

2) What is the location of Flashback_transaction_query view? Is it in undo segments?

3) Again where the pseudocolumns of the flashback version query resides?

4)In some queries both AS OF clause and VERSIONS BETWEEN SCN Minvalue and Maxvalue are used. How can it be possible? AS OF means one particular SCN. Again the other one is interval between 2 SCN's. Explain me Tom.

5)I dropped a table and tried to retrieve as of an old SCN. Why Oracle could't get the before image of the same from the Undo segments. It must be in undo probably. What could be the reason.

6)Again, for example, if a table is altered by adding a column(say empty column). How oracle includes that new column with the same table of old SCN?

7)What is Log Sequence Number? Is it like a serial number or something like that?


Thanks a lot Tom.
Regards,
Vimal.
Tom Kyte
March 28, 2012 - 10:50 am UTC

1) the reason is pretty clear: it is not there anymore.

things might be in a recycle bin for years.
things might not last in the recycle bin for seconds.


When you drop the table, the table stays right where it is - but its extents are marked "free". If the tablespace was full when you dropped the table - the tablespace would appear to now have free space equal to the size of the table. But the table would still be there. UNTIL:

something else wanted a new extent. An insert into an existing table could cause that. A create of a new table could cause that. Anything that causes a new extent to be allocated will wipe that table out of the recycle bin.

Or, your dba might have disabled the recycle bin, that is an option as well.


2) it is based on undo information, yes.

3) they do not reside anywhere, they are manufactured from undo as needed.

4) the as of and versions between are applied to separate tables. One of the tables will return rows "as of" a specific point in time, the other will return all of the row versions of certain rows between two points in time, and then you join them together.


for example:


ops$tkyte%ORA11GR2> /*
ops$tkyte%ORA11GR2> drop table emp;
ops$tkyte%ORA11GR2> drop table dept;
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table emp as select * from scott.emp;
ops$tkyte%ORA11GR2> create table dept as select * from scott.dept;
ops$tkyte%ORA11GR2> */
ops$tkyte%ORA11GR2> delete from emp;

14 rows deleted.

ops$tkyte%ORA11GR2> delete from dept;

4 rows deleted.

ops$tkyte%ORA11GR2> insert into emp select * from scott.emp;

14 rows created.

ops$tkyte%ORA11GR2> insert into dept select * from scott.dept;

4 rows created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> exec dbms_lock.sleep(3);

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> column start_time new_val START
ops$tkyte%ORA11GR2> column stop       new_val STOP
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select localtimestamp START_TIME from dual;

START_TIME
---------------------------------------------------------------------------
28-MAR-12 11.48.00.450850 AM

ops$tkyte%ORA11GR2> begin
  2          for i in 1 .. 10
  3          loop
  4                  update emp set sal = sal+1;
  5                  commit;
  6                  dbms_lock.sleep(1);
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> update dept set dname = lower(dname);

4 rows updated.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select localtimestamp STOP from dual;

STOP
---------------------------------------------------------------------------
28-MAR-12 11.48.10.515853 AM

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select dept.dname, emp.ename, emp.sal
  2    from dept as of timestamp to_timestamp('&start'),
  3         emp versions between timestamp to_timestamp('&start') and to_timestamp('&stop')
  4   where emp.ename = 'SCOTT'
  5     and dept.deptno = emp.deptno
  6  /
old   2:   from dept as of timestamp to_timestamp('&start'),
new   2:   from dept as of timestamp to_timestamp('28-MAR-12 11.48.00.450850 AM'),
old   3:        emp versions between timestamp to_timestamp('&start') and to_timestamp('&stop')
new   3:        emp versions between timestamp to_timestamp('28-MAR-12 11.48.00.450850 AM') and to_timestamp('28-MAR-12 11.48.10.515853 AM')

DNAME          ENAME             SAL
-------------- ---------- ----------
RESEARCH       SCOTT            3001
RESEARCH       SCOTT            3002
RESEARCH       SCOTT            3003
RESEARCH       SCOTT            3004
RESEARCH       SCOTT            3005
RESEARCH       SCOTT            3006
RESEARCH       SCOTT            3007
RESEARCH       SCOTT            3008
RESEARCH       SCOTT            3009
RESEARCH       SCOTT            3010

10 rows selected.

ops$tkyte%ORA11GR2> 


that just got the rows for SCOTT from the emp table between those two points in time and joined them to the single row in the dept table for that deptno "as of" the point in time the demo began (when dnames were in upper case)

6) I don't know what you mean.

7) yes, it is just a number assigned in a monotonically increasing fashion.

SCN & Crash Recovery

Vimal, April 19, 2012 - 1:06 am UTC

Hi Tom,
My questions:
1) I made a change to my table & not committed. Now, I shutdown Abort. Oracle rollsback & shutsdown. While startup, Oracle does not crash recovers the changes that are uncommitted. Why? Does this mean that we can get back only the committed transactions that are lost?
How can we get back that?

Kindly,
Vimal.


Tom Kyte
April 19, 2012 - 5:51 am UTC

1) if you shutdown abort, we do not rollback and shutdown. We just shutdown.

when we startup, we'll recover your transaction and put it back the way it was just right at the time of the abort - and then figure out "it did not commit" and roll it back then.

Yes, you are only going to get back committed work - anything that was in progress will be restored to the way it was before it started.


to get that back - you have to restart your transaction. remember now, the client that was IN THE MIDDLE of the transaction is "gone", there is nothing to finish the transaction - we don't know what to do with it to finish it - so we "undo it"

V$ view

Vimal, April 19, 2012 - 3:41 am UTC

Hi Tom,
I was reading that V$Views get information from instance memory and control file. If so the how come we are able to access v$views even at NOMOUNT stage which actually does not use control file?
Also how come it takes information from the instance memory. Where this instance memory get all those information from, since the memory is empty during the new database startup?

Kindly clarify me.

Thanks.
Tom Kyte
April 19, 2012 - 5:52 am UTC

because not all v$ views access files.



instance memory is not "empty" by the time you get a prompt, we have initialized everything we need by then.

make SCN format more friendly

Doug, June 05, 2012 - 10:27 pm UTC

Hello Tom,

I have a test database, I don't wanto have to rebuild it
just yet (in hindsite I sould have built it under a virtual box) any way the SCN is curently


SCN
--------------
56930696928

Being so large by default (with out col format )
in SQL*Plus that number is displayed as

SCN
----------
5.6931E+10

I realise normally you would never want to do this (but just to make my testing easier it would be handy to have it as a smaller number)
Is there a way to reset the SCN ?

Cheers

Regards

Doug


Tom Kyte
June 06, 2012 - 5:33 am UTC

You'd need a new database.

create a script:

exec dbms_output.put_line( dbms_flashback.get_system_change_number );


call it scn.sql and


ops$tkyte%ORA11GR2> exec dbms_output.put_line( dbms_flashback.get_system_change_number );
82867482

PL/SQL procedure successfully completed.




to select to_char(dbms_flashback.....) from dual;


backgrond processer

md abu fazal, June 18, 2012 - 9:50 am UTC

by which back ground processer we can read the data
Tom Kyte
June 18, 2012 - 9:53 am UTC

huh???

I have no idea what you mean

SCN equivalent for requests

Michal Pravda, November 12, 2012 - 7:42 am UTC

Good afternoon.

SCN changes with commits. Is there something (easily readable) which changes with requests?

I mean
block 1:
begin
  p1;
  p2;
end;
/


block 2:
begin
  p1;
end;
/

begin 
  p2;
end;
/


My question is:
Is there a way for p2 (with possible cooperation from p1) to distinguish whether it is run in block 1 or block 2?

This of course is simplified (I hope not oversimplified ) example of my true requirement. I've got about 100MB of legacy spaghetti code and this would be a rather simple way of ensuring that every error gets saved and is saved only once (callstack of 10 procedures each with
exception
  when others then
    save_exception(sqlerrm, 'procedure_name',...)
    raise;
end;

is not unheard of).

Tom Kyte
November 12, 2012 - 9:26 am UTC

see dbms_utility.format_call_stack

dbms_utility

Michal Pravda, November 13, 2012 - 2:33 am UTC

I've tried dbms_utility.format_call_stack and format_error_backtrace (I have used them for years) before I wrote my post, but they don't help with this.

Format call stack returns callstack at the time of calling. It shows through which method call the program have gone to the line it currently is. In my example it would return the same thing if called within p2. whether it was run in block 1 or 2.

In real situation it doesn't help because formatcallstack always shows current state (of course) and doesn't tell, that program was deeper and raised exception. And format_error_backtrace is "reset" on each level by that "when others then ... raise" statements. If those statements didn's reset the error backtrace stack then it would be useful. But they do.
Tom Kyte
November 14, 2012 - 8:13 pm UTC

well your question is very 'fuzzy', there isn't a block 1 and block 2 above, there are blocks 1, 2 and 3.

I don't know what you mean here at all, I'm not at all sure what you are really looking for.


there is not a block 1 and block 2 here, there are three.


what are you trying to do.

SCN equivalent for requests

Michal Pravda, November 15, 2012 - 5:07 am UTC

Very unfortunate choice of words on my side. I'll rephrase the question in one post and try to explain why do I ask for it in the second for clarity.

Question:
Let's say I have 2 scripts.
Script1:
begin
pkg.p1;
pkg.p2;
end;
/


Script 2:
begin
pkg.p1;
end;
/

begin
pkg.p2;
end;
/

Is there a way for procedure pkg.p2 to determine whether it is running in script 1 (in one block with pkg.p1) or in script 2 (in a separate block from p1)?


Tom Kyte
November 19, 2012 - 9:38 am UTC

if you are using sqlplus and you set up :


set appinfo on


then the script name itself will appear in v$session.module

ops$tkyte%ORA11GR2> create or replace package pkg
  2  as
  3          procedure p1;
  4          procedure p2;
  5  end;
  6  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body pkg
  2  as
  3          procedure p1
  4          is
  5          begin
  6                  dbms_output.put_line( 'p1 ran!' );
  7                  null;
  8          end;
  9  
 10          procedure p2
 11          is
 12          begin
 13                  for x in ( select client_info, action, module
 14                               from v$session
 15                                          where sid = (select sid
 16                                                         from v$mystat
 17                                            where rownum = 1 ) )
 18                  loop
 19                          dbms_output.put_line( x.client_info || ', ' || x.action || ', ' || x.module );
 20                  end loop;
 21          end;
 22  end;
 23  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> !echo "begin pkg.p1; pkg.p2; end;" > s1.sql

ops$tkyte%ORA11GR2> !echo / >> s1.sql

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> !echo "begin pkg.p2; end;" > s2.sql

ops$tkyte%ORA11GR2> !echo / >> s2.sql

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> set appinfo on
ops$tkyte%ORA11GR2> @s1
ops$tkyte%ORA11GR2> begin pkg.p1; pkg.p2; end;
  2  /
p1 ran!
, , 02@ s1.sql

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> @s2
ops$tkyte%ORA11GR2> begin pkg.p2; end;
  2  /
, , 02@ s2.sql

PL/SQL procedure successfully completed.



would that do it for you?

the reason

Michal Pravda, November 15, 2012 - 9:11 am UTC

The reason I ask this is that it would be the easiest and so far the only 100% complete way to solve my problem. I have plenty of legacy code whith plenty of exception handlers some of them handling particular exceptions, some of them general (when others). The handlers contain one or both of 2 different procedures with overlapping funcinality. E.g. the code may look like
Procedure p1
is
begin
...
exception
when user_exception then
handler1;
raise;
when another_exception then
handler1;
handler2;
raise;
when others then
handler2;
raise;
end;


or procedure p2
is
begin
...
exception
when others then
handler1;
handler2;
raise;
end;

or procedure px
is
begin
p1;
....
exception
when others then
handler2;
raise;
end;

Procedures call other procedures (p1 calls p2 which calls p3...) up to level ~10.

Common funkcinality of both handlers is to log into logtable output of dbms_utility.format_error_backtrace. Problem is how to save this info once and once only. Depending on how deep an exception was raised and what kind of exception it was I can end up with only handler1 handling it.
Or handler2.
Or handler1 followed by handler2.
Or handler1 followed by handler2 followed by handler1 followed by handler1 again followed by handler2.

Several following calls which lead to exception may happen in one session. There are hundreds of possible entry points making it difficult to reset some internal state (although this seems like the only way, however ugly).

Just now I am saving the stack multiple times for one exception(chain) with some resetting on known most used entry points. Of course optimal would be to save only once, only in first handler, but so far I havent' found how to determine in the handler that it is "the first" of this call.

If every call issued a commit then I could easily have a scn as an internal state and only when it changed I would save the stack. Since it does not I am looking for a "block run counter".

I dont' know whether my question and the reasoning behind it is less fuzzy now. However I suspect it won't get any clearer than that.

clarification

Michal Pravda, November 15, 2012 - 9:56 am UTC

I realised that for the concept there need not be 2 procedures. So here's shortest possible code illustrating the problem:

CREATE OR REPLACE PACKAGE save_stack IS
PROCEDURE test;
END;
/

CREATE OR REPLACE PACKAGE BODY save_stack IS
PROCEDURE handler IS
BEGIN
dbms_output.put_line(dbms_utility.format_error_backtrace);
END handler;

PROCEDURE test IS
BEGIN
--level 2
BEGIN
RAISE no_data_found;
EXCEPTION
WHEN OTHERS THEN
handler;
RAISE;
END;
--level 2 end
EXCEPTION
WHEN OTHERS THEN
handler;
RAISE;
END test;
END;
/

Test code (intentionally twice the same):

begin
save_stack.test;
end;
/

begin
save_stack.test;
end;
/

This implementation prints
ORA-06512: na "UI_APL.SAVE_STACK", line 10

ORA-06512: na "UI_APL.SAVE_STACK", line 14

ORA-06512: na "UI_APL.SAVE_STACK", line 10

ORA-06512: na "UI_APL.SAVE_STACK", line 14

I want only the line with "line 10" (the first line).

So desired output is:
ORA-06512: na "UI_APL.SAVE_STACK", line 10

ORA-06512: na "UI_APL.SAVE_STACK", line 10

You can alter the package in any way without touching the procedure test itself (it models the legacy code).
Tom Kyte
November 19, 2012 - 9:53 am UTC

why wouldn't you want to catch this "at the top", not at the bottom?

why is the error logging taking place all over the place instead of just at the top level, the thing that runs the code in the first place?

I don't know of any way to catch that you are at the bottom of the call stack (or rather - that you are NOT the bottom of the call stack)

inherited code

Michal Pravda, November 20, 2012 - 3:42 am UTC

Hello,

I would want to catch at the top. However, in present situation, at the top you don't have all the information because it's lost because of the intermediate RAISE commands.

I think that logging is all over the place because it was created by someone who didn't know dbms_utility.format_error_backtrace. Or maybe it wasn't available at the time of creation (entirely possible, it's an old project). I don't know right now how I would solve it prior 10G. I was too inexperienced to think about these things back then.

Thank you, so I am not missing something.
Tom Kyte
November 20, 2012 - 8:37 am UTC

if you want to log an error per transaction - we might be able to do that.

you could get dbms_transaction.local_transaction_id and compare it to a package global (state) variable. If different - then log error and save transaction id. If not, you can assume you've already logged the error for that transaction.

TIMESTAMP_TO_SCN performance.

A reader, October 01, 2013 - 8:20 pm UTC

I am loading a table that has 25 columns with a query that selects TIMESTAMP_TO_SCN(col1), TIMESTAMP_TO_SCN(col2), followed by the other 23 general columns.

The insert runs for 15 minutes for 8000 rows.

Is there something I can do to speed up the TIMESTAMP_TO_SCN? When I run the query without the 2 TIMESTAMP_TO_SCN it returns instantly.

Thanks.

Pradeep, October 16, 2013 - 2:45 am UTC

Hi how can i find the SCN of a query happened at a particular timestamp??
Tom Kyte
November 01, 2013 - 6:45 pm UTC

you would have to have audited it - using fine grained access control for example. only if you audit it and capture that information would we store that.

see DBMS_FGA, it will capture the SCN a given query is executed "as of"

Manipulate SCN

Dennis Schnell, December 12, 2013 - 2:14 pm UTC

Hi Tom,
I found this thread by searching the web for manipulating the SCN.
Is it possible to change/manipulate the SCN?
I would like to skip a range of numbers for example from 213.. to 1000.. without issuing a loop of transactions.

Thanks in advance
Dennis