Import Oracle Database
Solved
mongich
Posted messages
29
Status
Member
-
legeant16 -
legeant16 -
Hello,
I want to import an Oracle database from one machine to another, but the problem is that we are dealing with two different versions, namely 8 and 9i. When I try, I get this message:
"Character set conversion is not possible ..."
Please respond, and thank you very much.
I want to import an Oracle database from one machine to another, but the problem is that we are dealing with two different versions, namely 8 and 9i. When I try, I get this message:
"Character set conversion is not possible ..."
Please respond, and thank you very much.
22 answers
- 1
- 2
Next
What does "select * from nls_database_parameters" return on the two machines?
@++
Poster, post again and always :-)
@++
Poster, post again and always :-)
Hi mick57,
If you're on Linux or Unix, you can use the dbca tool in graphical mode. It's a "Wizard" that will help you create an empty database.
Quite simple and quick. You'll find it under $ORACLE_HOME/bin
Good luck.
Dave
If you're on Linux or Unix, you can use the dbca tool in graphical mode. It's a "Wizard" that will help you create an empty database.
Quite simple and quick. You'll find it under $ORACLE_HOME/bin
Good luck.
Dave
export you mean ???
import:
something (file, etc.) => oracle
export:
oracle => something...
@++
Are you torn between Linux and Windows?
Do you want to spend time or money?
import:
something (file, etc.) => oracle
export:
oracle => something...
@++
Are you torn between Linux and Windows?
Do you want to spend time or money?
Hello
I want to know what the syntax is for importing an Oracle database
and whether the entire structure of the database will be imported or not
Thank you
I want to know what the syntax is for importing an Oracle database
and whether the entire structure of the database will be imported or not
Thank you
Hello everyone,
I am importing from an Oracle 9i database to a 10g one, and the import starts correctly, with some tables being created properly. However, after a certain time, I get the following message:
IMP-00017: Failed to execute the following statement with ORACLE error 6550:
"BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_IMP_SKIP_INDEXES_OFF; END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: Line 1, column 12:
PLS-00302: Component 'DBMS_EXPORT_EXTENSION' must be declared
ORA-06550: Line 1, column 8:
PL/SQL: Statement ignored
Do you have any idea what the source of this error could be?
I am importing from an Oracle 9i database to a 10g one, and the import starts correctly, with some tables being created properly. However, after a certain time, I get the following message:
IMP-00017: Failed to execute the following statement with ORACLE error 6550:
"BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_IMP_SKIP_INDEXES_OFF; END;"
IMP-00003: ORACLE error 6550 encountered
ORA-06550: Line 1, column 12:
PLS-00302: Component 'DBMS_EXPORT_EXTENSION' must be declared
ORA-06550: Line 1, column 8:
PL/SQL: Statement ignored
Do you have any idea what the source of this error could be?
Importing an export from a base 8 into a base 9i is possible. The reverse is not (basically you can upgrade but not downgrade).
However, BATMAT is right; it's a problem related to the CHARACTER SET that is defined at the creation of the database and cannot be changed afterwards.
It is not possible to import a database with a "higher" character set than the database into which you want to import it. (Basically, if your original database has "à" or "é" while the one you are importing into does not recognize these characters: it's dead!).
So: recreate the 9i database.
However, BATMAT is right; it's a problem related to the CHARACTER SET that is defined at the creation of the database and cannot be changed afterwards.
It is not possible to import a database with a "higher" character set than the database into which you want to import it. (Basically, if your original database has "à" or "é" while the one you are importing into does not recognize these characters: it's dead!).
So: recreate the 9i database.
There might still be a solution: use tr to excess. I've never looked at the format of Oracle exports: if the format is simple and uncompressed, you could do something like
tr 'àâéèê' 'aaeee' < yourExport > tmp
mv tmp> yourExport
I'll check on Monday if it's possible and I'll try to let you know what it is... Unless someone announces that the Oracle export format is pure binary, compressed, encrypted, or whatever ;p
@++
Post, post again and again :-)
tr 'àâéèê' 'aaeee' < yourExport > tmp
mv tmp> yourExport
I'll check on Monday if it's possible and I'll try to let you know what it is... Unless someone announces that the Oracle export format is pure binary, compressed, encrypted, or whatever ;p
@++
Post, post again and again :-)
I just checked and the export leaves the data in plain text... So it must be possible to use tr.
To be tested then :)
@++
Post, post again and always :-)
To be tested then :)
@++
Post, post again and always :-)
Thank you,
it's true that when it comes to a higher character set, it's not possible to do the import,
so the solution is to reinstall 9i carefully, choosing the appropriate character set, and it was done successfully. Additionally, it should be mentioned in the import script that the FULL option must be set to YES.
Thank you all.
it's true that when it comes to a higher character set, it's not possible to do the import,
so the solution is to reinstall 9i carefully, choosing the appropriate character set, and it was done successfully. Additionally, it should be mentioned in the import script that the FULL option must be set to YES.
Thank you all.
Hello,
I am looking for how to import only the data from an Oracle database and I do not want to import the table structures.
Thank you
I am looking for how to import only the data from an Oracle database and I do not want to import the table structures.
Thank you
Pour importer/exporter une base de données Oracle dans Unix, vous pouvez utiliser les outils suivants :
1. **Exportation :**
Utilisez l'outil `exp` ou `expdp` (Data Pump Export) pour exporter les données de la base.
Exemple de commande avec `expdp` :
```bash
expdp username/password@dbname directory=dir_name dumpfile=export_file.dmp logfile=export_log.log
```
2. **Importation :**
Utilisez l'outil `imp` ou `impdp` (Data Pump Import) pour importer les données dans la base.
Exemple de commande avec `impdp` :
```bash
impdp username/password@dbname directory=dir_name dumpfile=export_file.dmp logfile=import_log.log
```
Assurez-vous que le répertoire spécifié (`dir_name`) a été créé dans Oracle et que l'utilisateur a les permissions nécessaires.
1. **Exportation :**
Utilisez l'outil `exp` ou `expdp` (Data Pump Export) pour exporter les données de la base.
Exemple de commande avec `expdp` :
```bash
expdp username/password@dbname directory=dir_name dumpfile=export_file.dmp logfile=export_log.log
```
2. **Importation :**
Utilisez l'outil `imp` ou `impdp` (Data Pump Import) pour importer les données dans la base.
Exemple de commande avec `impdp` :
```bash
impdp username/password@dbname directory=dir_name dumpfile=export_file.dmp logfile=import_log.log
```
Assurez-vous que le répertoire spécifié (`dir_name`) a été créé dans Oracle et que l'utilisateur a les permissions nécessaires.
Hello
to export under linux/unix
exp system/xxxx file=/var/dump/file.dump log /var/log/export_oracle.log FULL=Y ROWS=Y
But take a closer look at the options with -help
Additionally, to perform an export there is a preliminary operation to carry out:
the information here:
http://www.nuxora.com/2007/01/22/import-export-sous-oracle/
to export under linux/unix
exp system/xxxx file=/var/dump/file.dump log /var/log/export_oracle.log FULL=Y ROWS=Y
But take a closer look at the options with -help
Additionally, to perform an export there is a preliminary operation to carry out:
the information here:
http://www.nuxora.com/2007/01/22/import-export-sous-oracle/
Hello everyone
I'm currently doing an internship and I need to back up the Oracle database 9.2.0.1.0 on Red Hat Enterprise Linux 3, on a NAS disk, and I honestly admit that I'm really bad at Linux (for now). So if someone could help me, it would be really nice because I see the internship progressing but not my project. That's it, thanks to those who will lend me a hand.
cramouillo
I'm currently doing an internship and I need to back up the Oracle database 9.2.0.1.0 on Red Hat Enterprise Linux 3, on a NAS disk, and I honestly admit that I'm really bad at Linux (for now). So if someone could help me, it would be really nice because I see the internship progressing but not my project. That's it, thanks to those who will lend me a hand.
cramouillo
Please, every time I install Oracle 9i, OMS cannot start.
I have tried several times, and I have not succeeded. What should I do?
That is to say, how do I install Oracle 9i with OMS?
I have tried several times, and I have not succeeded. What should I do?
That is to say, how do I install Oracle 9i with OMS?
Hello,
I'm also looking to migrate from Oracle 8i to 9i.
No problem with the export.
However, how do I create an empty database on 9i to import into a clean database?
Thank you
Micka
I'm also looking to migrate from Oracle 8i to 9i.
No problem with the export.
However, how do I create an empty database on 9i to import into a clean database?
Thank you
Micka
Hi and thank you for your response davlar99.
Actually, I'm on Windows.
I managed to create my empty database. I created the tablespace and the rollback segments like the existing 8i database.
I also created the role and the user with which I am doing the export.
The import is going "not too badly," but there are errors with the statistics:
IMP-00017: The following instruction failed with Oracle error 6550:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '31'; SREC.MAXVAL := "
"'393939'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.N"
"UMARRAY(254422546068207000000000000000000000,297121534289157000000000000000"
"000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS"
".SET_COLUMN_STATS(NULL,''xxxx,'ICODE',NULL,NULL,NULL,,,00000"
"1111111,0,srec,4,0); END;"
IMP-00003: Oracle error 6550 encountered
ORA-06550: Line 1, column 367:
PLS-00103: Symbol "," found where one of the following symbols was expected:
( - + case mod new not null others <an identifier=""> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
pipe
The symbol "null" was substituted for "," to continue.
Do you have any idea?
Thanks</an>
Actually, I'm on Windows.
I managed to create my empty database. I created the tablespace and the rollback segments like the existing 8i database.
I also created the role and the user with which I am doing the export.
The import is going "not too badly," but there are errors with the statistics:
IMP-00017: The following instruction failed with Oracle error 6550:
"DECLARE SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '31'; SREC.MAXVAL := "
"'393939'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.N"
"UMARRAY(254422546068207000000000000000000000,297121534289157000000000000000"
"000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS"
".SET_COLUMN_STATS(NULL,''xxxx,'ICODE',NULL,NULL,NULL,,,00000"
"1111111,0,srec,4,0); END;"
IMP-00003: Oracle error 6550 encountered
ORA-06550: Line 1, column 367:
PLS-00103: Symbol "," found where one of the following symbols was expected:
( - + case mod new not null others <an identifier=""> avg
count current exists max min prior sql stddev sum variance
execute forall merge time timestamp interval date
pipe
The symbol "null" was substituted for "," to continue.
Do you have any idea?
Thanks</an>
Hello,
This is a known bug from Oracle since 2005.
Most often, it's because there is an apostrophe somewhere in the name of a column.
So there are either 2 possibilities: the NLS are not the same (DB to DB) or (in the registry or environment variable) or
it's either the apostrophe issue.
In the case of the apostrophe:
I took the time since to export (in 9i) with the option statistics=none
The option might also exist with the import command (never tried).
I know that the ANALYZE=N option exists but does it do the same thing?.
In the case of NLS:
Compare your registry parameters or those of your DBs in the v$NLS_PARAMETERS table.
Cheers!
This is a known bug from Oracle since 2005.
Most often, it's because there is an apostrophe somewhere in the name of a column.
So there are either 2 possibilities: the NLS are not the same (DB to DB) or (in the registry or environment variable) or
it's either the apostrophe issue.
In the case of the apostrophe:
I took the time since to export (in 9i) with the option statistics=none
The option might also exist with the import command (never tried).
I know that the ANALYZE=N option exists but does it do the same thing?.
In the case of NLS:
Compare your registry parameters or those of your DBs in the v$NLS_PARAMETERS table.
Cheers!
Bonjour, merci
Je veux l'instruction pour importer la base de données sous Oracle, j'ai la sauvegarde .BMP ? Merci.
Je veux l'instruction pour importer la base de données sous Oracle, j'ai la sauvegarde .BMP ? Merci.
- 1
- 2
Next