Learn Database Concepts

Enable Flash back on : Starting from Oracle 11g R2 we don't have to bounce the Database for these effects to get affected. If you are using pre 11G R2, you have to re bounce your system for these settings to get affected. [oracle@Linux1 ~]$ sqlplus /"As sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Mon May 4 10:25:05 2015 Copyright (c) 1982, 2009, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production PL/SQL Release 11.2.0.1.0 - Production CORE    11.2.0.1.0      Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production Lets check if Flash back is enabled or not : SQL...

How to restore the old data using flashback query My intention is , I want to get back past data of database after erroneously updated and committed. We know that committed data can never be flashed back. But with 10g new flashback feature we can get back past data even they are committed.  Before proceed ensure that, •The UNDO_RETENTION initialization parameter is set to a value so that you can back your data far in the past that you might want to query. •UNDO_MANAGEMENT is set to AUTO. •In your UNDO TABLESPACE you have enough space. With an example I will demonstrate the whole procedure. 1)I have created a table named test_flash_table with column name and salary. SQL> create table test_flash_table(name varchar2(10), salary number); Table created. SQL> insert into test_flash_table values('ABCD',10); 1 row created. SQL> commit; Commit complete. The table contains one row. 2)I erroneously updated column salary of Arju and commited data. SQL> update test_flash_table s...