How to Flashback Deleted Rows Using Flashback Query in Oracle

• Using Flashback Query with AS-OF clause oracle restore the past data without performing Media recovery.
• Flashback feature will use Undo_data from undo_retention to restore the transactions from the undo tables-pace.

 Note :- First enable archivelog mode in your database and then configure FRA.

>> Here i had done a Small Scenario to Understand Flashback-Query Easily <<

•  Check the counts of the emp1 table.

15:25:32 SQL> select count(*) from emp1;

64 rows selected.


                                 >> NOW DELETE SOME RECORDS <<


15:25:44 SQL> delete from emp1 where comm='250';

24 rows deleted.


15:26:05 SQL> select count(*) from emp1;

  COUNT(*)
----------
        40

15:26:15 SQL> commit;

Commit complete.


>> Here we will retrive Lost Rows Using As-of clause with Flashback Query <<


15:30:41 SQL> select * from sam.emp1 as of timestamp to_timestamp ('11-01-2017 15:25:43', 'DD-MM-YYYY HH24:MI:SS') where comm='250';

     EMPNO      ENAME         JOB              MGR        HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ------------------------------------------------
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      9826        TURNER10   ENGR10       3365         28-APR-87          9000        250         10
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      4578        TURNER7     ENGR7         3021         25-APR-87          1000        250          7
      4578        TURNER7    ENGR7          3021         25-APR-87          1000        250          7
      4578        TURNER7    ENGR7          3021         25-APR-87          1000        250          7
      4578        TURNER7    ENGR7          3021         25-APR-87          1000        250          7
      4578        TURNER7    ENGR7          3021         25-APR-87          1000        250          7

24 rows selected.


>> Now we will restore Lost rows using insert <<


15:44:27 SQL> INSERT INTO SAM.EMP1 (SELECT * FROM SAM.EMP1 AS OF TIMESTAMP TO_TIMESTAMP ('11-01-2017 15:25:43','DD-MM-YYYY HH24:MI:SS') where comm='250');

24 rows created.
             #################################################################################
             Same Scenario here But we are Restoring rows of the Hire-date Column because rows are                                 deleted in this table using date.
#################################################################################

15:52:18 SQL> select count(*) from emp1;

  COUNT(*)
----------
       184


15:52:22 SQL> delete from emp1 where hiredate='24-APR-87';

32 rows deleted.


15:54:27 SQL> SELECT COUNT(*) FROM EMP1;

  COUNT(*)
----------
       152


16:09:02 SQL> insert into sam.emp1 (select * from sam.emp1 as of timestamp to_timestamp ('11-01-2017 15:52:18', 'DD-MM-YYYY HH24:MI:SS') where HIREDATE='24-APR-87');

32 rows created.



16:11:35 SQL> select count(*) from emp1;

  COUNT(*)
----------
       184


Happy Learning...

How to check Index Creation or Index Rebuild Date in Oracle

Hi Guys,

Today I will show you how simple is to check the Index creation date.

Index Creation Date or Index Rebuild date Both are same because when we rebuild the indexes oracle will drop the existing index and recreate freshly..


Steps :-

• We can see here Last Index Creation date is 18-Dec-16

SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='OTORDERS';

TABLE_NAME     INDEX_NAME        LAST_ANALYZED
----------------------------------------------------------------------
OTORDERS       ORMEDN_IND        18-DEC-16
OTORDERS       OREPRC_IDX         18-DEC-16
OTORDERS       PK_ORDERS          18-DEC-16

•  Now i will analyze the first index (ORMEDN_IND) and check the last_analyzed date.

SQL> analyze index ormedn_ind validate structure;

Index analyzed.

•  We can see Index Last_Analyzed date has not changed even after Analyzing this index.

SQL> select table_name,index_name,last_analyzed from dba_indexes where table_name='OTORDERS';

TABLE_NAME     INDEX_NAME        LAST_ANALYZED
-------------- ----------------- --------------------------------------
OTORDERS       ORMEDN_IND        18-DEC-16
OTORDERS       OREPRC_IDX        18-DEC-16
OTORDERS       PK_ORDERS         18-DEC-16

•  Now i will rebuild the index and execute the same query.

SQL> Alter index ORMEDN_IND rebuild online;

• Now date has changed....

SQL> Select table_name,index_name,last_analyzed from dba_indexes where table_name='OTORDERS';

TABLE_NAME     INDEX_NAME        LAST_ANALYZED
-----------------------------------------------------------------------
OTORDERS       ORMEDN_IND        04-JAN-17
OTORDERS       OREPRC_IDX        18-DEC-16
OTORDERS       PK_ORDERS         18-DEC-16

•  You can also check index creation date by using this query also...

SQL> select last_ddl_time from user_objects where Object_name='ORMEDN_IND';

LAST_DDL_TIME
--------------------------
04-JAN-17

• You can observe here in Last_Analyzed column the date has changed, it means oracle will          change the date of an Index when you rebuild it...

Happy Learning...

Exporting Tablespace with Partitioned Table in Oracle

Hi Guys,

I got this task to take export of a Tablespace which is having 8 partitions in my production database and Import on Test Server.

Steps :-


D:\iecexp>expdp dumpfile=tspart1 directory=iecexp tablespaces=tspart1 log=tspart1.log

Export: Release 11.2.0.3.0 - Production on Sat Jan 7 11:14:41 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "log=tspart1.log" Location: Command Line, Replaced with: "logfile=tspart1.log"
Legacy Mode has set reuse_dumpfiles=true parameter.
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01":  system/******** dumpfile=tspart1 directory=iecexp tablespaces=tspart1 logfile=tspart1.log reuse_dumpfiles=true
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.085 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2016"          432.8 MB 3074761 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2017"          333.0 MB 2352610 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2015"          148.5 MB 1100876 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2014"          7.037 MB   61045 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2013"          311.2 KB    2386 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2018"          65.96 KB     271 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2020"          27.09 KB       4 rows
. . exported "HMIS"."OTOCURNC_PART":"OTC_P2019"              0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
  D:\IECEXP\TSPART1.DMP
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at 11:16:35


=================================
NOW IMPORT THIS PARTITION TABLE
=================================

> Create Tablespace with same name at target machine.

> Create same user with the same privileges as source.


E:\impbab>impdp dumpfile=TSPART1.DMP directory=impbab remap_tablespace=tspart1:tspart1

Import: Release 11.2.0.3.0 - Production on Sat Jan 7 11:34:48 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=TSPART1.DMP directory=impbab remap_tablespace=tspart1:tspart1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2016"          432.8 MB   3074761 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2017"          333.0 MB   2352610 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2015"          148.5 MB   1100876 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2014"          7.037 MB     61045 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2013"          311.2 KB      2386 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2018"          65.96 KB       271 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2020"          27.09 KB         4 rows
. . imported "HMIS"."OTOCURNC_PART":"OTC_P2019"              0 KB            0 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 11:40:40



>> NOTE:- you can observe here when we imported tablespace, 8 partitions are automatically imported with their rows.



Happy Learning...