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
migrating schema form oracle to vertica
Moderator: NorbertKrupa
-
- Newbie
- Posts: 23
- Joined: Mon Feb 04, 2013 10:54 am
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: migrating schema form oracle to vertica
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:
You won't need the storage information for the table... Once you have the DDL statement you can run it in Vertica.
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:
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)
Switching back to Vertica, you will see that the rows were loaded:
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!
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
Code: Select all
dbadmin=> CREATE TABLE "TEST"."T1"
dbadmin-> ( "C1" NUMBER(5,0),
dbadmin(> "C2" VARCHAR2(100)
dbadmin(> );
CREATE TABLE
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;
Code: Select all
SQL> @c:\tmp\test.sql
1|Jim
2|Jane
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)
Code: Select all
dbadmin=> SELECT * FROM test.t1;
C1 | C2
----+------
1 | Jim
2 | Jane
(2 rows)
I hope this helps!
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- Newbie
- Posts: 1
- Joined: Mon Feb 25, 2013 3:38 pm
Re: migrating schema form oracle to vertica
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;
--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;