How do I make imp use right charachter set? Add a comment| 0. If you are using oracle server XE it is not possible to change the charset of database server or any database. I'm also searching for an answer. Remove some entries from oracle DMP file. Character map broken during FTP transfer. Hot Network Questions.
I have below export command for exporting the schema and which works fine in command prompt.
The exp02_rm.par file has below information:
Now i want to exclude few tables i.e KPI_LOGS,ALERT_LOGS which i dont want to export.But really dont know how to do that.
1 Answer
I dont know how to excludes the table in the PAR file. But you can use TABLES option in the PAR file and include the name of the tables you can to export.
Not the answer you're looking for? Browse other questions tagged sqloracleexportpar or ask your own question.
I am trying to import a dmp file from one database to another. The problem is that some special danish characters are not imported right because some character map conversion is done during import.
This is the info when I start the import with imp (see the note about possible character conversion):
Each special character (typically Æ, Ø and Å) are using 2 charactes instead of 1, and the data are padded with spaces to fill up, so this causes an error that the data contains 31 caharacters for this field, and the field is defined as size 30.
The export that generated the dmp file was executed like this:
The import was executed like this:How do I make this work correctly?
What can be done to get this right? It seems like the imp ignores the charset used, as the import server uses AL32UTF8 (which is wrong).
3 Answers
You are importing data into a database using the AL32UTF8 characterset (this is the default). The importer must therefore convert the characters to fit that and some will become multi-byte characters as you have found. There are two ways to deal with this:
1) If you don't need Unicode in the new database recreate it in the same characterset as the old database. Run this code on the old database to get the characterset and use that
Comment On Table Oracle
Once the new and old database have the same characterset the import won't have to do the conversion.
2) If you can pre-create the tables, you could use the NLS_LENGTH_SEMANTICS parameter. If you set that to CHAR rather than the default of BYTE, a VARCHAR2(5) will be allocated enough space to store 5 characters in the database character set (potentially up to 20 bytes) rather than 5 bytes (which could allow just 1 character). Or instead you could modify the table creation DDL to add CHAR to every VARCHAR2 column declaration. e.g.
This way you can convert the data to Unicode and have a better characterset going forward, and so long as your application can support it this is perhaps the preferred method.
We ended up solving this by changing all the character fields to use CHAR instead of the default BYTE. This is basically option 2 proposed by @BrokenCrust in his answer.Instead of re-creating the tables, we just re-defined them using this SQL:
if you are using oracle server XE it is not possible to change the charset of database server or any database. I'm also searching for an answer.
