3 Replies Latest reply: Oct 10, 2012 5:16 AM by Mkirtley-Oracle RSS

    Migration Procss from Postgesql to Oracle 10g

    944300
      hi
      any one can suggest me
      how can i Migrate from Postgresql to Oracle 10g
      is there any tool for this free witch i can i chek
      i am getting Problem to convert function

      Migration Process from Postgesql to Oracle 10g
      -------------
      CREATE OR REPLACE FUNCTION getgraphdatanew()
      RETURNS SETOF sessiongraphdata AS
      $BODY$
      declare
      v_row RECORD; --session%ROWTYPE;
      v_row1 sessiongraphdata%ROWTYPE;
      v_row2 RECORD;
      v_cursor refcursor;
      v_lastupd timestamp without time zone;

      begin
           --if not exists(select sesno from sessiongraphdata) then
                truncate table sessiongraphdata; this would probably re-initialise the table and speed up
           --end if;
           --v_lastupd:=now();
           --first copy the sessions to the graphdata table
           --NOTE: only those sessions are copied which are not already in the graphdata

           --here may be a cursor can be used to avoid any updates between these two statements
           ---LOCK TABLE session IN SHARE UPDATE EXCLUSIVE MODE;
           delete from sessiongraphdata;
           insert into sessiongraphdata (sesno,actno,subsno,nasno,bytepulse,timepulse,start,lastupd,bytesin,
                     bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid)
                select sesno,actno,subsno,nasno,bytepulse,timepulse,start,lastupd,bytesin,
                     bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid
                from session where state !='W' ;

           delete from session where state='C' and sesno in (select sesno from sessiongraphdata where state='C');
           ---update session set lastbytesin=0, lastbytesout=0;

           insert into sessionhistory (sesno,actno,subsno, nasno,bytepulse,timepulse,start,lastupd,bytesin,
                bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid)
           select sesno,actno,subsno, nasno,bytepulse,timepulse,start,lastupd,bytesin,
                bytesout,state,ipaddr,macaddr,closedt,closereason,lastbytesin,lastbytesout,internalid,cpno,snatip,qosno,userid
                from sessiongraphdata where state='C';

           open v_cursor for select * from sessiongraphdata;
           fetch v_cursor into v_row2;
           while found
           loop
                v_row1.actno=v_row2.actno;
                v_row1.lastbytesin=v_row2.lastbytesin;
                v_row1.lastbytesout=v_row2.lastbytesout;
                v_row1.lastupd=v_row2.lastupd;
                v_row1.sesno=v_row2.sesno;
                v_row1.subsno=v_row2.subsno;
                return next v_row1;
                fetch v_cursor into v_row2;
           end loop;
           return;
      end;

      $BODY$
      LANGUAGE plpgsql VOLATILE
        • 1. Re: Migration Procss from Postgesql to Oracle 10g
          Mkirtley-Oracle
          Hi,
          There is the SQL*Developer migration workbench but his currently does not support the migration from Postgres to Oracle.
          I think your only option is to find a third party tool that may be able to help.
          There are some links to third party partners and support from the the following -

          Oracle Database Migration Technology

          http://www.oracle.com/technetwork/database/migration/index-084442.html

          under the heading -

          Finding More Help: Oracle Migration Partners and Support

          Regards,
          Mike
          • 2. Re: Migration Procss from Postgesql to Oracle 10g
            944300
            Thank you so much for you response, I went through the link. There are lot of tool available, however I did not find any tool which support PostgreSQL to Oracle. Can somebody advise me on that?
            • 3. Re: Migration Procss from Postgesql to Oracle 10g
              Mkirtley-Oracle
              Hi,
              If you can't find any tools that support a Postgres migration then all I can suggest is that you try one of the contacts under the heading -

              Finding More Help: Oracle Migration Partners and Support

              on the migration site -

              http://www.oracle.com/technetwork/database/migration/index-084442.html

              I made an internet search using the term "postgres migration to oracle tools" and found references to some tools that may be able to help. It looked like some were free and others were paid for but you could try this and see if any could help you.

              Regards,
              Mike