Re: exp file format / functionality blues

"Jan Kim" <kim_at_jupiter.mpiz-koeln.mpg.de> wrote in message news:8jg9re$1ovf$1_at_gwdu67.gwdg.de...
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:
 [snip]
> > 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.
>
> Ok -- but this will then involve running ``imp'' against a pre-created
> database, if I understand correctly? This would generate the problems
> I was complaining about.
>

That was why I stated 'Edit the resulting file'

> > 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.
>
> I think I tried this and it doesn't work. The problem is that the dump
> file contains explicit specifications of the target tablespace (i.e.
> statements of the form CREATE TABLE (...) TABLESPACE "OLD_TBLSPC").
> Thus, if the receiving user doesn't have privilege / quotas on the
> old tablespace any longer, the import fails.

Did you follow my advice to the letter including make sure the default tablespace of that user is the new tablespace. If it still didn't work, that's very strange, because it worked for me many times.

>
> > 2
> > The ALTER TABLE command has been provided for structure changes.
 Generally
> > speaking you shouldn't need to drop and create your tables.
>
> It is correct that there rarely is any technical necessity to do this.
> Nonetheless, it's sometimes the most sensible thing to do. For
> development purposes, it's much more logical to maintain a file
> containing the DDL statements for creating the database structure
> than to maintain a file containting DDL statements for creating
> some obsolete database structure plus an increasing tail of
> ALTER TABLE statements for transforming that old structure into
> the current one.
>
> > 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.
>
> I strongly disagree. A DBA is a person who knows what she/he is
> doing,

Sorry to disagree with that. You can have an OCP certificate and still don't know what you are doing. One of my major functions at my current employer is to clean the mess of my colleague DBAs made and left behind at customers. Needless to say that is very frustrating.

and the database software should support him/her in getting
> that done. While it may well be that exp / imp are the wrong tools for
> doing what I want to do, but there is no reason to assert that
> modifying a database structure is dangerous to any database.

I didn't say that. I said (I will rephrase) 'An export is a logical unity, you shouldn't edit it, because this will create significant problems'

 At times,
> it's definitely more dangerous not to do so.
>
> Greetinx, 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 >=-----*

Regards,

Sybrand Bakker, Oracle DBA Received on Fri Jun 30 2000 - 00:00:00 CDT