OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SCN Number (Oracle 10G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SCN Number [message #391738]

Fri, 13 March 2009 05:14

ranjeet_badhe
Messages: 2
Registered: March 2009
Location: Chicago

Junior Member

I have enabled table row movement, to flashback a table using SCN number.What is the SQL command to view SCN number of the transaction?

[Updated on: Fri, 13 March 2009 06:04]

Report message to a moderator

Re: SCN Number [message #391775 is a reply to message #391738]

Fri, 13 March 2009 07:22

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Which transaction? Current one? Another concurrent one? Another one in the past? ...?

Regards
Michel

Report message to a moderator

Re: SCN Number [message #391948 is a reply to message #391775]

Sat, 14 March 2009 23:28

ranjeet_badhe
Messages: 2
Registered: March 2009
Location: Chicago

Junior Member

For example lets consider one scenario.I create one table emp with columns "name" and "salary" .I insert value into it and then update the table.

No I want to know the SCN number for these transactions so that I am able to FLASHBACK TABLE emp TO partcicular SCN number.

Could you please let me know the SQL query to extract the SCN number?
Thank you

Report message to a moderator

Re: SCN Number [message #391949 is a reply to message #391738]

Sun, 15 March 2009 00:16

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/

With computers in general & Oracle specifically, details matter!

>No I want to know the SCN number for these transactions
From what you have posted NO transaction has occurred.

A true & complete transaction only occurs after or as part of COMMIT.

Check "current" SCN.
COMMIT
Check "current" SCN.
Your transaction is between those 2 values which could differ by more than ONE; depending upon how many other concurrent users.

Report message to a moderator

Re: SCN Number [message #391952 is a reply to message #391948]

Sun, 15 March 2009 01:24

Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you are a DBA, search for and study FLASHBACK_TRANSACTION_QUERY:

SQL> desc FLASHBACK_TRANSACTION_QUERY
 Name                                
 ------------------------------------
 XID                                 
 START_SCN                           
 START_TIMESTAMP                     
 COMMIT_SCN                          
 COMMIT_TIMESTAMP                    
 LOGON_USER                          
 UNDO_CHANGE#                        
 OPERATION                           
 TABLE_NAME                          
 TABLE_OWNER                         
 ROW_ID                              
 UNDO_SQL

To get current scn you can use:

SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
                 3007668

1 row selected.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
    3007670

1 row selected.

Regards
Michel

Report message to a moderator

Re: SCN Number [message #392076 is a reply to message #391952]

Mon, 16 March 2009 05:08

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

In 10g, you get the ORA_ROWSCN function that gives you a "conservative upper bound" estimate on the last SCN at which a row was updated.

You need to specify the ROWDEPENDENCIES clause when you create a table for this to work.

Report message to a moderator

Previous Topic:	Execute the sql script contents in plsql environment.
Next Topic:	Drop user based on userid OR change the username

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 04 16:02:36 CDT 2025