Re: exp file format / functionality blues

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote:

> Your post contains strong accusations. Do you really think if that was the
> general feeling, Oracle wouldn't have changed the functionality?

I apologize if I offended anyone, it was not my intention to accuse Oracle or anybody else. I just look for this functionality. It may well be just me.

> 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.

I tried this, but it doesn't seem to work with tables containing BLOBs.

> 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.

> 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.

> 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, 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. 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  >=-----*