CODEWITHSUNDEEP
oracle-databasepostgresqloracle10ggreenplum
user2148036
user2148036

Reputation: 11

Transfer data from an ORACLE View to greenplum DB table

I have an Oracle view containing very large amount of data in it and I want to migrate this data in a table in Greenplum database. Is there any way I can write any query in Postgresql to fetch that Oracle view's data?

If not possible by query in Postgresql, kindly suggest me some way to access Oracle view from Linux server, so that I can create data file from that Oracle view to my Linux server and load that file via gpfdist to a Greenplum table.

NOTE: an Oracle view is from third party, I only have an access to view that data (I have all the connection info) I can access that view via SQL Developer NOTE: Exporting data from SQL Developer to my local machine is not feasible here as the data is very large

Thanks, Sunny

Upvotes: 0

Views: 1300

Answers (1)

bma
bma

Reputation: 9796

The last time I used Greenplum (3 years ago) I don't think there were any untrusted languages like plperlu, so fetching directly from Oracle from within Greenplum might not be possible. If the data has a primary key, are you able to fetch in batches, compress it, then ship it to Greenplum?

Do you have a Greenplum support contract? If so, you could also try them if you haven't already: https://sso.emc.com/sso/login.htm

I recall that gpfdist can be configured to fetch from remote servers with a bit of fiddling, so if you are able to copy out the Oracle data to disk, you could fetch it using gpfdist without any intermediary steps.

Upvotes: 2

Related Questions