migrating schema form oracle to vertica

Moderator: NorbertKrupa

Post Reply
malargopal
Newbie
Newbie
Posts: 23
Joined: Mon Feb 04, 2013 10:54 am

migrating schema form oracle to vertica

Post by malargopal » Tue Feb 19, 2013 12:13 pm

Hi ,

Can anybody help me out to do the following thing .

1.How to migrate a schema or tables from oracle to vertica.

thanks,
Malar

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: migrating schema form oracle to vertica

Post by JimKnicely » Fri Feb 22, 2013 4:51 pm

Hi,

Here is one way of copying a table from Oracle to Vertica...

Use the dbms_metadata.get_ddl function in Oracle to get the create table statement. For instance to get the DDL for a table named T1 in the TEST schema, you can run this:

Code: Select all

SQL> SELECT dbms_metadata.get_ddl('TABLE', 'T1', 'TEST') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','T1','TEST')
---------------------------------------------------------------------


  CREATE TABLE "TEST"."T1"
   (    "C1" NUMBER(*,0),
        "C2" VARCHAR2(100)
   ) SE
You won't need the storage information for the table... Once you have the DDL statement you can run it in Vertica.

Code: Select all

dbadmin=> CREATE TABLE "TEST"."T1"
dbadmin->  (    "C1" NUMBER(5,0),
dbadmin(>       "C2" VARCHAR2(100)
dbadmin(>  );
CREATE TABLE
Then you can grab the table data from Oracle using SQL*Plus.

For instance, you could create a script file named test.sql containing the following commands:
  • SET ECHO OFF
    SET HEADING OFF
    SET COLSEP "|"
    SPOOL C:\tmp\test.txt;
    SELECT dbms_metadata.get_ddl('TABLE', 'T1', 'TEST') FROM dual;
    SET FEEDBACK OFF
    SELECT c1, c2 FROM test.t1;
    SPOOL OFF;
In SQL*Plus you can run the script like this:

Code: Select all

SQL> @c:\tmp\test.sql

         1|Jim
         2|Jane
Now that you have a data file, you can load it into Vertica using the COPY command in vsql (note, my example has the vsql Vertica client installed locally on the same server running Oracle)

Code: Select all

C:\tmp>C:\vertica\bin\vsql -h verticatest1 -U dbadmin -w XXXXXXX -c "COPY test.t1 (c1, c2) FROM LOCAL 'C:\tmp\test.txt';"
 Rows Loaded
-------------
           2
(1 row)
Switching back to Vertica, you will see that the rows were loaded:

Code: Select all

dbadmin=> SELECT * FROM test.t1;
 C1 |  C2
----+------
  1 | Jim
  2 | Jane
(2 rows)
The example above is pretty tedious. But I wanted to show you an example of the steps. You can automate a lot of it.

I hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

send2mohanraj
Newbie
Newbie
Posts: 1
Joined: Mon Feb 25, 2013 3:38 pm

Re: migrating schema form oracle to vertica

Post by send2mohanraj » Tue Mar 12, 2013 4:36 am

The below sql will help export the data from oracle tables.

--use this to set the line size in sqlplus
select sum( decode(data_type,'DATE',20,data_length )+ 1) from dba_tab_columns where table_name='&table_name';
--set the following in sqlplus
set linesize <output from above>
set pagesize 0
set heading off

--spool using delimiter
select
(case when column_id=1 then 'select ' end ) ||
(case when data_type ='DATE' then 'to_char(' end) || column_name || (case when data_type ='DATE' then ',''dd:mm:yyyy hh24:mi:ss'') ' end) ||
(case when column_id=(select max(column_id) from dba_tab_columns where table_name=x.table_name and owner=x.owner) then ' from ' || x.owner || '.' || x.table_name || ';'
else '|| ''~'' || ' end )
from dba_tab_columns x where table_name='&table_name' order by column_id;


--spool using fixed width
select (case
when column_id = 1 then
'select '
end) ||
(case
when data_type = 'DATE' then
'lpad(nvl(to_char('
when data_type = 'NUMBER' then
'to_char(nvl('
when data_type like '%CHAR%' then
'rpad(nvl('
end)
|| column_name ||
(case
when data_type = 'DATE' then
',''dd:mm:yyyy hh24:mi:ss''),'' '' ),19,'' '')'
when data_type = 'NUMBER' then
',0),''' || RPAD('0', DATA_PRECISION-DATA_SCALE,9 ) || (case when data_scale>0 then '.'|| RPAD('0', DATA_SCALE,9 ) end) || ''')'
when data_type like '%CHAR%' then
','' ''),' || data_length || ','' '')'
end)
|| (case
when column_id = (select max(column_id)
from dba_tab_columns
where table_name = x.table_name
and owner = x.owner) then
' from ' || x.owner || '.' || x.table_name || ';'
else
'||'
end) sql, x.*
from dba_tab_columns x
where table_name = '&table_name'
order by column_id;

Post Reply

Return to “Vertica Migration”