Tuesday, 8 May 2018

Flashback table past time in 11gr2

$ export ORACLE_SID=prod

~]$ sqlplus / as sysdba

SQL> startup

SQL> grant connect, resource to javed identified by javed;

SQL> conn javed/javed

SQL> create table emp (eno number(10));

SQL> insert into emp values(&eno);         (insert many rows)

SQL> commit;

SQL> select * from emp;

SQL> select to_char(sysdate, 'hh24:mi:ss dd-mon-yyyy') from dual;

           TO_CHAR(SYSDATE,'HH2
           18:28:12    01-may-2018



Flashback Query:-

SQL> select * from emp as of timestamp to_timestamp('18:28:12 01-may-
2018','hh24:mi:ss dd-mon-yyyy');



Flashback table past time:-

SQL> delete from emp;

SQL> select table_name,ROW_MOVEMENT from user_tables;

TABLE_NAME               ROW_MOVE
EMP                                   DISABLED

SQL> alter table emp enable row movement;

SQL> select table_name,ROW_MOVEMENT from user_tables;

TABLE_NAME            ROW_MOVE
EMP                                ENABLED

SQL> flashback table emp to timestamp to_timestamp('18:28:12 01-may-
2018','hh24:mi:ss dd-mon-yyyy');

Flashback complete.

SQL> select * from emp;

SQL> alter table emp disable row movement;

SQL> select table_name,ROW_MOVEMENT from user_tables;

TABLE_NAME                 ROW_MOVEMENT
EMP                                     DISABLED
SYS_TEMP_FBT                DISABLED

No comments:

Post a Comment

relink a single executable (binary file) using ADADMIN utility

In this post, I would like to write something useful for patching and fine-tuning. When times, we have to relink a particular binary file ...