Page 1 of 1

Move Data From Oracle to Vertica

Posted: Thu Jun 14, 2012 10:49 pm
by ishaqbaig
Dear Vertica Gurus,

I am newbie to vertica .
I would like to Move Data From Oracle to Vertica. Please suggest some tools / procedure to do so.
Also I would like to know is there any Database browser tool for vertica like Toad.



Re: Move Data From Oracle to Vertica

Posted: Fri Jun 15, 2012 2:45 am
by JimKnicely
Hi! Welcome to Vertica forums!

Check out this link for the best free tools for querying Vertica:


There is also a version of Toad you can use:


How many tables and how much data do you have to move into Vertica from Oracle? I moved data from a MySQL and SQL Server database by simply extracting the data from them into flat files and loaded them with the bulk copy command in vsql. It's really, really efficient.

Here's another post that may help you out:


Re: Move Data From Oracle to Vertica

Posted: Fri Jun 15, 2012 10:16 pm
by ishaqbaig

Thanks for the quick reply. The links were helpful.

Regarding the amount of data to be transferred. I do an incremental load of the facts which are currently in oracle. I could create flat file for the updated/inserted rows and then move them using the bulk copy command what you suggested. Please let me know more info of the bulk copy command or point me to the link.

I am evaluating vertica against Oracle using Business Objects as our query tool. Do you have more information regarding the combination of Business Objects and vertica.

Thanks again for your valuable help.


Business Objects Developer
Saudi Arabia

Re: Move Data From Oracle to Vertica

Posted: Tue Dec 18, 2012 11:24 pm
by stazer
Hello Ishaq,

Do you have any feedback to share on the BO-Vertica integration?

Thanks for your inpu.


Re: Move Data From Oracle to Vertica

Posted: Tue Mar 26, 2013 6:30 am
by fasterfaster
hi, we've recently moved from oracle to vertica and have BO XiR3 and R4 connected using Data Direct drivers and seems to work fine.

we do have problems with Web Intelligence not recognising the vertica supports database ranking, that icon in webi is disabled. It is active when connecting to Oracle

does anyone have any ideas what that is so?

Re: Move Data From Oracle to Vertica

Posted: Thu Mar 16, 2017 4:51 pm
by JimKnicely
Below are several options for copying data from Oracle to Vertica that I have used:

  • odbc_loader_package: This package contains a pair of User-Defined Load functions, ODBCSource() and ODBCLoader(), that can be used to load data directly from a remote database. These Load functions connect to a remote database and load data directly from that database into Vertica. Data is not converted into an intermediate file format; the data is fetched over the network via ODBC and each record is copied directly into Vertica as it is received.

    Get it here: ... er_package
  • SQL*Plus: Install Oracle SQL*Plus on a Vertica node. Use SQL*Plus on that node to select data from Oracle and pipe the output to a vsql COPY command

    sqlplus64 -s oracle_user@"(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle_host)(PORT=oracle_port)))(CONNECT_DATA=(SID=oracle_sid)(SRVR=DEDICATED)))"/oracle_user_password @extract.sql | vsql -Cc "copy table_name from stdin direct;"
  • ODB tool: odb is a platform independent, multi-threaded, ODBC command line tool you can use as a query driver, data loader/extractor and SQL interpreter.

Re: Move Data From Oracle to Vertica

Posted: Mon Jun 26, 2017 10:08 am
by Durso
Thanks for the advice, I'm another one moving over from Oracle too.