edit dmp file oracle

Can I edit my Dump File? Backup and Recovery Tips

Copyright © Howard Rogers 2001 28/10/2001 of 5

Can I edit my Dump File? Absolutely not. The dump file produced by export is a binary file. It can most certainly be opened in a text editor (I am forever opening mine in WordPad to see what’s inside it). So I can do this, for example: "C:\PROGRAM FILES\WINDOWS NT\ACCESSORIES\WORDPAD.EXE" Z:EXPDAT.EMP …and notepad pops up displaying this sort of thing:

This is useful: it means you can see plainly the various ‘create’ and ‘insert into’ statements contained with a dump file. It gives you an idea of what import will do when run. But you’ll notice the long sequences of meaningless squares and assorted letters: this is WordPad’s valiant effort to display the binary parts of the dump file. If you seek to edit any part of this file, and then save the edited result, the result will be disaster:



Here, I’m changing the name of one of the tablespaces that is included within the dump file. It used to be called UNDOTBS, but I’m changing it to UNDOXYZ (so the number of characters hasn’t changed at all, which some people claim make a difference to whether or not this approach works): Save that changed file, and run import, and look what happens: C:\>IMP SYSTEM/MANAGER FILE=EXPDAT.DMP FULL=Y IGNORE=Y IMPORT: RELEASE 9.0.1.1.1 - PRODUCTION ON SUN OCT 28 10:26:06 2001 (C) COPYRIGHT 2001 ORACLE CORPORATION. ALL RIGHTS RESERVED. CONNECTED TO: ORACLE9I ENTERPRISE EDITION RELEASE 9.0.1.1.1 - PRODUCTION WITH THE PARTITIONING OPTION JSERVER RELEASE 9.0.1.1.1 - PRODUCTION IMP-00010: NOT A VALID EXPORT FILE, HEADER FAILED VERIFICATION IMP-00000: IMPORT TERMINATED UNSUCCESSFULLY Note that import gives up the minute the header of the file is seen to be gibberish. Exactly the same happens on Unix when you use vi, pico or any similar text editor to make changes to a dump file. I’ve successfully demonstrated exactly this problem in training classes on Solaris many times, for example. It doesn’t matter what part of the file you edit, nor whether you keep the character count the same: the mere fact of saving the dump file as a text file is enough to make the meaningless display of characters representing binary data actually get saved as a meaningless string of text characters, after which… well, bye-bye dump file, basically.

Alteration is here!



So editing the file is out. But why would you want to edit the thing in the first place –and are there any alternative ways of achieving those ends which are rather more successful? The usual reason people want to edit the dump file directly is that it contains statements like this one: CREATE TABLE "EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING INSERT INTO "EMP" ("EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO") VALUES (:1, :2, :3, :4, :5, :6, :7, :8)

…and we see the complete storage clause for the table there, parts of which we want to change. You might not want the EMP table to be imported back into the USERS tablespace, for example. Or perhaps you would like the INITIAL extent size to be changed. All these things can be accomplished, but –as we’ve seen- not by editing these statements directly in the dump file. There are a number of approaches you can try. If the setting you want changed is one that can be altered by issuing an ‘alter table’ command, the easiest approach is to change the setting, then perform the export, drop the original table, and finally run import. By altering the setting before doing the export, you ensure that the dump file actually contains all the right settings in the first place. That approach can deal with all of the storage clause, for example –except the INITIAL extent clause (because you can never alter that using an ‘alter table’ command). So if what you want to change is something that cannot be got right with an ‘alter table’ command (and I can only think of two –the INITIAL extent size, and the tablespace clause for versions earlier than 8i), this is the more appropriate approach: First, export the table as normal. The dump file has the wrong settings for INITIAL and/or tablespace, but no matter. Next, run import using these sorts of parameters: IMP SCOTT/TIGER TABLES=EMP ROWS=N IGNORE=Y INDEXES=N INDEXFILE=BLAH.TXT Run like this, import actually doesn’t do anything at all –except output the various ‘create table’ and ‘insert into’ statements it would normally have performed into a text file (in this case called BLAH.TXT). That’s a genuine text file, and so it can be edited in a text editor like vi or notepad. If you open up the text file, it will look like this: REM CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME"



REM VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, REM "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 REM FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY REM ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 REM STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE REM "USERS" ENABLE ; REM ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY REM ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ; REM ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "PK_EMP" ; REM ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ;

All these statements, you’ll notice, are REM’med out. But the REMs are not too hard to edit away. You’re then left with a bunch of text which can be edited in any way you like, after which it can then be run as a normal SQL script. In our case (wanting to fix up the INITIAL extent size, for example), we’d edit the file so it looked like this (I’ve underlined the change for the sake of clarity): CREATE TABLE "SCOTT"."EMP" ("EMPNO" NUMBER(4, 0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4, 0), "HIREDATE" DATE, "SAL" NUMBER(7, 2), "COMM" NUMBER(7, 2), "DEPTNO" NUMBER(2, 0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING ; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

STORAGE(INITIAL 100K FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" ENABLE ; ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "DEPT" ("DEPTNO") ENABLE NOVALIDATE ; ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "PK_EMP" ; ALTER TABLE "SCOTT"."EMP" ENABLE CONSTRAINT "FK_DEPTNO" ;

This gets saved as, let us say, createmp.sql. Now I perform the following actions: SQL> DROP TABLE EMP; SQL>@CREATEMP.SQL …and this ensures a new EMP table with all the right settings is created. Of course, it’s missing its data (because there are no ‘insert’ statements in the text file we created), so finally we do this: C:\>IMP SCOTT/TIGER TABLES=EMP IGNORE=Y ROWS=Y INDEXES=Y …and that gets the data loaded from the real dump file, and all indexes re-created. There are yet other techniques which can be used to fix particular problems up. To simply change the tablespace of a table, for example (assuming you don’t have version 8i, where such things are trivially easy to do within the database itself), you could simply drop the original table, re-create it manually in the correct tablespace, and then run import with



IGNORE=Y –import won’t be able to create an identically-named table, and so the inserts will take place into the table you created yourself in the correct tablespace. Another approach that is sometimes used is to drop the table, alter the User’s default tablespace to be the “correct” tablespace, and alter that same User’s quota on the original tablespace to be zero (ALTER USER FRED QUOTA ZERO ON USERS;) When import is now run, it will not be able to create the table in the tablespace directed by the dump file (because of the new zero quota), and so will proceed to create the table in the User’s new default tablespace. The point is, there are a variety of methods that can be used to fix the ‘create table’ statements up, and none of them require editing the dump file. In conclusion, I’ll say that over the years, enough people have claimed to have successfully edited the dump file to make me believe that under exactly the right circumstances, and with a dollop of good luck, they might genuinely be getting away with it. (Though I have to add that after questioning one person about exactly how he’d performed the editing, it turned out that he’d used a binary editor after all. It took three weeks to ring that “minor” detail out of him!). But the point I hope I’ve demonstrated here is that you can’t reliably get away with it –and there’s precisely zero reason why you’d need to take the risk anyway. Anything you might reasonably want to change about a table can be changed using the INDEXFILE method, or by using the ‘alter, export, drop, import’ approach.

edit dmp file oracle

Documents