How find all the data loaded on previous day? Issue: Data Loading into development environment from production database

My production database is 40gb but I do not want all of those 40gb of data into my dev env and so my question is 


How can I get all the data loaded into Oracle database yesterday?
Is there a way in Oracle to get all data loaded into db on previous day, manage all dependencies and generate another dev snapshot with one day minimal data?
Oracle Data Pump Network-Mode Import may be one of possible solutions. You can invoke Data Pump Import in network mode using database link (NETWORK_LINK parameter) and specifying which version of data you want to import with FLASHBACK_SCN or FLASHBACK_TIME parameters.

First, create a database link to you production database on your development database:

CREATE DATABASE LINK prod_db 
  CONNECT TO system
  IDENTIFIED BY "hackme"
  USING 'orcl';


Where orcl is the connect string you're using to connect to your production database. You can test if the link is working by querying some table in the production database:

SQL&gt; select count(*) from hr.employees@prod_db;

  COUNT(*)
----------
       107


See Database Administrator's Guide for more info about creating database links.

Then to import data into development database from production database, you would run Data Pump Import like this:

[oracle@oca ~]$ impdp sys/hackme \
SCHEMAS=hr,oe \
CONTENT=ALL \
NETWORK_LINK=prod_db \
FLASHBACK_TIME="TO_TIMESTAMP('13-05-2013 17:35:00', 'DD-MM-YYYY HH24:MI:SS')"


In this example, two schemas are imported, CONTENT specifies that both metadata and data should be imported (default), and FLASHBACK_TIME specifies the time of the snapshot you want to import.

Due to the restrictions of FLASHBACK_TIME:


  The FLASHBACK_TIME parameter pertains only to the Flashback Query
  capability of Oracle Database. It is not applicable to Flashback
  Database, Flashback Drop, or Flashback Data Archive.


You have to ensure that you have enough undo data on your source production database for the snapshot to be consistent, since Flashback Query depends entirely on undo.

Based on your requirements, you would need to guarantee undo retention to at least 24 hours to be able to get the previous versions of the data you're importing. You would also need to size you undo tablespace on the source database appropriately.

To guarantee the retention, connect to your source database and alter your current undo tablespace like this:

SQL&gt; alter tablespace undotbs1 retention guarantee;
Tablespace altered.


To specify the retention time (in seconds):

SQL&gt; alter system set undo_retention=86400;
System altered.

SQL&gt; show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management                      string      AUTO
undo_retention                       integer     86400
undo_tablespace                      string      UNDOTBS1


(86400 is 60 seconds * 60 minutes * 24 hours.)

Sizing the undo tablespace:

SQL&gt; alter database datafile '/oradata/ocaexam/undotbs01.dbf' resize 10g;


You can determine what's the best size for your undo tablespace using Undo Advisor in Enterprise Manager.

If you would like to automate your import process, you could use PL/SQL and Oracle Scheduler as described here.