Ora2pg.pl - some feedback
A few pointers if you're going to use ora2pg.pl to port your oracle schema
and data to postgres.
1) For non-perl savvy folks - The script does not do this all at once -
uncomment one TYPE option at a time. For example, say you want to export
your sequences, tables, and data (as in my case). First uncomment type =>
'SEQUENCE' and run ora2pg.sql. Save the output script as something else
other than output.sql. Then comment that back and uncomment type => 'TABLE'
and run your script again - again saving output.sql as something
else.....etc.
2) In order to export your data, your postgres db and tables should be
owned by the user name that runs ora2pg.pl. Otherwise, you get all kinds of
creative errors.
Good news is that I was able to get this script to work for me.
Question - is there a way to change the owner of a postgres database after
it's been created? I know I can alter table info, but is there something
to do this for the db?
--
Susan Lane
DPN, Incorporated
4631 Spring Mountain Road
Las Vegas, NV 89102
Email suel@dpn.com
Ph. (702) 873-3282
Fax (702) 873-3913
http://www.dpn.com
On Fri, 10 Aug 2001, Susan Lane wrote:
Question - is there a way to change the owner of a postgres database after
it's been created? I know I can alter table info, but is there something
to do this for the db?
Look at the pg_database system catalog. Specifically, the datdba column.
It should be the PostgreSQL UID of the user who owns it.
For example:
lx=# SELECT datname, datdba FROM pg_database
lx-# INNER JOIN pg_user ON (datdba=usesysid);
datname | datdba
--------------+--------
postgres | 507
twig2 | 501
template0 | 507
Regards,
Jw.
--
jlx@commandprompt.com by way of pgsql-general@commandprompt.com
On Fri, 10 Aug 2001 pgsql-general@commandprompt.com wrote:
For example:
lx=# SELECT datname, datdba FROM pg_database
lx-# INNER JOIN pg_user ON (datdba=usesysid);
datname | datdba
-------------+--------
postgres | 507
twig2 | 501
template0 | 507
Whoops, that was missing an important identifier to be meaningful. ;)
Trying again:
lx=# SELECT datname, datdba, usename FROM pg_database
lx-# INNER JOIN pg_user ON (datdba=usesysid);
datname | datdba | usename
--------------+--------+----------
postgres | 507 | postgres
twig2 | 501 | jd
template0 | 507 | postgres
...
So, look up the usename/usesysid in the pg_user table to find out what new
datdba to UPDATE your database (datname) to in pg_database. Hope that's
helpful.
Regards,
Jw.
--
jlx@commandprompt.com by way of pgsql-general@commandprompt.com
Yes, thanks! That helps!
----- Original Message -----
From: <pgsql-general@commandprompt.com>
To: Susan Lane <suel@dpn.com>
Cc: <pgsql-general@postgresql.org>
Sent: Friday, August 10, 2001 1:09 PM
Subject: Re: [GENERAL] Ora2pg.pl - some feedback
Show quoted text
On Fri, 10 Aug 2001 pgsql-general@commandprompt.com wrote:
For example:
lx=# SELECT datname, datdba FROM pg_database
lx-# INNER JOIN pg_user ON (datdba=usesysid);
datname | datdba
-------------+--------
postgres | 507
twig2 | 501
template0 | 507Whoops, that was missing an important identifier to be meaningful. ;)
Trying again:lx=# SELECT datname, datdba, usename FROM pg_database
lx-# INNER JOIN pg_user ON (datdba=usesysid);
datname | datdba | usename
--------------+--------+----------
postgres | 507 | postgres
twig2 | 501 | jd
template0 | 507 | postgres...
So, look up the usename/usesysid in the pg_user table to find out what new
datdba to UPDATE your database (datname) to in pg_database. Hope that's
helpful.Regards,
Jw.
--
jlx@commandprompt.com by way of pgsql-general@commandprompt.com