Export Oracle 8i database with Oracle 10g

crock9
crock9 used Ask the Experts™
on
I'm trying to export my Oracle 8i (8.1.7.0.0) database using Oracle 10g (10.2.0.4.0) 32-bit.  When I execute the EXP command on the 10g server, I receive the following error:

EXP-00056: ORACLE error 6550 encountered
ORA-06550: line 1, column 41:
PLS-00302: component 'SET_NLS_NUMERIC_CHAR' must be declared
ORA-06550: line 1, column 15:
PL/SQL: Statement ignored
EXP-00000: Export terminated unsuccessfully

My Oracle 8i database is running on Windows 2000 Server (32-bit), and Oracle 10g database is running on Windows Server 2008 (32-bit).  Any suggestions on where/how to set the SET_NLS_NUMERIC_CHAR component?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
So, based on the EE comment from the link you provided, in order to export from my 8i database, I need to use 8i export, right?  Then if I want to import the exported data into my 10g database, I need to use 10g to process the import, right?

Do you or anyone else know of a workaround that will allow me to export from 8i using 10g?  Perhaps the SET_NLS_NUMERIC_CHAR component can be declared somewhere in 10g?
Most Valuable Expert 2011
Top Expert 2012

Commented:
sorry, no, it's straight from the oracle documentation you use the lowest version for export and and target version for import.

for some non-export/import options

you could try database links  from the 10g db to the 8i

create table your_table_on_10g as select * from your_table_on_8i@dblink_to_8i

of course, that only does tables and you have to do them one at a time (but could script it)

you would still need to do all other objects, indexes, packages, constraints, types, synonyms, sequences, triggers, etc


you could use some reverse-engineering tools to generate schema scripts then run those.
you could then migrate the data with db links  (insert into 10gtable select * from 8itable@dblink)


Toad could do all of it for you.  There are probably other tools but that's the one I'm most familiar with

if you have good source code control, run all of the create scripts in your 10g then use dblinks to copy the data
Most Valuable Expert 2011
Top Expert 2012
Commented:
The Oracle doc for the above info (and my original post in the other question ajexpert linked too)


http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#sthref2852

Author

Commented:
Thanks sdstuber and ajexpert!  That Oracle documentation was exactly what I was looking for.

Commented:
Kudos to Sean!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial