Re: exp file format / functionality blues

running imp with indexfile= will create a file containing *only* create table statements (as comment) and create index statements (logfile also contains insert statements). Restriction: "The INDEXFILE parameter can be used only with the FULL=Y, FROMUSER, TOUSER, or TABLES parameters."

regards

Klaus

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> schrieb im Newsbeitrag news:962297071.4119.1.pluto.d4ee154e_at_news.demon.nl...
>
> 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 Fri Jun 30 2000 - 00:00:00 CDT