Home » SQL & PL/SQL » SQL & PL/SQL » SCN Number (Oracle 10G)
icon4.gif  SCN Number [message #391738] Fri, 13 March 2009 05:14 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which transaction? Current one? Another concurrent one? Another one in the past? ...?

Regards
Michel
Re: SCN Number [message #391948 is a reply to message #391775] Sat, 14 March 2009 23:28 Go to previous messageGo to next message
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
Re: SCN Number [message #391949 is a reply to message #391738] Sun, 15 March 2009 00:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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.
Re: SCN Number [message #391952 is a reply to message #391948] Sun, 15 March 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SCN Number [message #392076 is a reply to message #391952] Mon, 16 March 2009 05:08 Go to previous message
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.
Previous Topic: Execute the sql script contents in plsql environment.
Next Topic: Drop user based on userid OR change the username
Goto Forum:
  


Current Time: Sat Dec 10 06:46:51 CST 2016

Total time taken to generate the page: 0.04111 seconds