Q
Problem solve Get help with specific problems with your technologies, process and projects.

Extracting data from a specific period

In a data warehouse environment what is the best way to extract data for a specific period and populate to another database?

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.
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 last published in February 2007

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close