In a data warehouse environment what is the best way to extract data for a specific period and populate to another database? I don't want the entire schema or full database, only a few tables with one month's data and respective table views and indexes.
Requires Free Membership to View
The Oracle export utility and Data Pump (if using 10g) both support the WHERE clause which can be applied to filter out rows of data. Suppose you have a table called ORDER_DETAILS and you only want those order details that are less than 30 days old. You can use the export utility as follows to export only those rows:
exp userid=system file=order_details.dmp tables=order_details where='detail_date between sysdate-30 and sysdate'When you run this export, the WHERE clause will be applied to the query that pulls records from the table. If more than one table is specified in the export utility, the WHERE clause is applied to all tables. So you may have to create different dump files for different tables depending on the WHERE clause you need for each table. Once you have the dump file(s), you can then import those records into your destination database.
This was first published in February 2007
Join the conversationComment
Share
Comments
Results
Contribute to the conversation