Oracle Dump File: can it be edited? 1

[cfFran]

Has anyone ever tried to edit the DMP file produced by the Oracle EXP utility?

I noticed that the dump file contains the name of the tablespace of the original set of tables. Oracle will IMP the tables into the same tablespace name on the new server. If I want to IMP into another server which has a different tablespace name, I thought I might be able to edit the "TABLESPACE <name here>" clause in the dump file. Wrong! The error message says that the "character set marker is unknown". What's that all about?

Any advice, guys? Maybe there is an easier way to IMP and change the tablespace name at the same time?

 

[SantaMufasa]

Fran,

First, (Oracle and) I recommend that you NEVER attempt to edit an Oracle dmp file because only part of the dmp file is ASCII and the other part is Binary (you could call it "Half-ASCII" if you choose).

What I recommend doing if you wish to change tablespace designation is to produce an ASCII text file of your dmp file's Data-Definition Language by doing the following:

1) Do a command-line invocation of the Oracle "imp" utility and on that command line, specify as one of the parameters, "...indexfile=<some file name>.sql...".

2) Edit the resulting "<some file name>.sql" file, which now contains editable text commands of all of the CREATE INDEX and CREATE TABLE statements for all of the tables in the dmp file. (The CREATE TABLE statements are all REMarked out, so you will want to un-remark out all the "REM " indicators.) You can modify all of the "TABLESPACE..." designators for both the TABLE and INDEX definitions. (One other modification is to remove or move and tailor the "connect <username>/<password>" command that needs to be at the top of the file.

3) Run your edited script as a plain, old SQL script, which creates empty copies of the tables and indexes in the tablespaces that you specify.

4) Run the original dmp file through the "imp" utility with the imp parameter, "...ignore=Y...", which tells the import utility to ignore the fact that the tables already exist and to simply import the rows of data into the now-pre-existing tables.

Now, concerning the error you encountered: "character set marker is unknown", usually occurs when you are attempting to import a dmp file into an older version of Oracle than the version that created the dmp file. If that is the case, I/we can advise you how to deal with that problem, as well. (Let us know if that is the case.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[cfFran]

Mufasa,

I realized today that I never responded to you. Sorry about that. I tried your approach and it works great. We are able to move our tables into the correct tablespace now.

You were a great help.

Thanks,
Fran