Re: exp file format / functionality blues

Your post contains strong accusations. Do you really think if that was the general feeling, Oracle wouldn't have changed the functionality? Exp and imp exist because there is a need to be able to make a logical *backup* of (part of ) the database. This makes it necessary to ascertain the dump file will import in to the database, so ruling out any edits of the dmp file.
If you need an ascii dump of one or more tables, you can dump them using sqlplus (see http://osi.oracle.com/~tkyte for a tool using sqlplus) and load them using sqlloader.

As to your questions:
1 You can do that in either one of two ways a) run the imp command with show=y log=y logfile=<filename>, which will dump all create table commands to a file, or use Toad (www.toadsoft.com) to generate a file with create table commands. Edit that file and precreate all your tables.
b) make sure the receiving user has the correct new (default) tablespace, doesn't have unlimited tablespace privilege and has quota on the correct tablespace only.
So
revoke unlimited tablespace from <username>; alter user <username> quota unlimited on <new tablespace> quota 0 on <old tablespace> quota 0 on <other tablespaces> Then run imp

2
The ALTER TABLE command has been provided for structure changes. Generally speaking you shouldn't need to drop and create your tables. In exceptional cases (when you need to reduce column lengths) you can copy your data to a temporary table, truncate the old table, alter it, and insert your data back into the changed table.
The exp/imp will not work, as exp/imp has been designed to generate logical backups, which will fail if the structure has changed.

Personally I find it questionable design when Oracle would have enabled the Oracle community to hack around in a dump file. I consider people who are willing to do it, or actually doing it, as dangerous to any database.

Regards,

Sybrand Bakker, Oracle DBA

"Jan Kim" <kim_at_jupiter.mpiz-koeln.mpg.de> wrote in message news:8jfo30$1o2g$1_at_gwdu67.gwdg.de...
> Dear Oraclers,
>
> after being responsible for administering and developing an Oracle
> based database, I find myself getting the blues whenever I think of the
> ``exp'' and ``imp'' programs. Perhaps, I use these programs
> inadequately, or I overlook obvious things. At this point, however, my
> impression is that I've run into a field which is somewhat less than
> optimally supported by Oracle. At any rate, I'd like your opinion. I
> have Oracle 8.0.5 running under Compaq Tru64 OSF/1 4.0e (the platform
> formerly known as DEC Alpha).
>
> The central problem for me is that ``exp'' produces non-text, "binary"
> output. This makes it effectively impossible to solve problems by
> simply editing the *.dmp files created by ``exp''.
>
> The main problems which I cannot seem to solve are:
>
> * I have two installations of my database, one for development &
> messing up things, the other for production. Both use different
> users and tablespaces. It seems impossible to import a dump of
> the production database into the development database, because
> ``imp'' insists to create the tables in the tablespaces they were
> exported from (apparently, this is in the CREATE TABLE statements
> embedded in the *.dmp file).
>
> * Sometimes, I want to change the database structure. One
> convenient way to do this would be to run ``exp'', then drop all
> tables, then create the new tables, and finally reload the data
> (not the schema) from the *.dmp file. However, this procedure
> results in lots of errors, which sometimes can be worked around,
> sometimes not (in a recent case, ``imp'' caught some internal
> exception and exited "due to multiple internal errors"; the last
> record which it tried to import was obviously garbled, presumably
> because the program somehow got out of frame while reading the
> binary data representations).
>
> So, has anyone here come across these troubles too? Are there other
> ways to do the things I'd like to do?
>
> Personally, I consider it a questionable design that ``exp'' generates
> files that cannot be processed with a standard text editor.
>
> Greetinx & thanx in advance, Jan
> --
> +- Jan T. Kim -------------------------------------------------------+
> | email: kim_at_mpiz-koeln.mpg.de |
> | WWW: http://www.mpiz-koeln.mpg.de/~kim/ |
> *-----=< hierarchical systems are for files, not for humans >=-----*
Received on Thu Jun 29 2000 - 00:00:00 CDT