A question on import

[ClaudiaFilliponi]

I am importing an schema called bond from production to dev.

I use the following syntax:

imp system/password file=$PIPEFILE log=${IMPORT_LOG} fromuser=bond touser=bond;

The output from log shows no error:
. importing BOND's objects into BOND
. . importing table "ADDRESS" 2303 rows imported
. . importing table "ADDRESS_TRANSFER" 1457 rows imported
. . importing table "AL_RESULTS" 666 rows imported
. . importing table "AL_TESTZ" 5589 rows imported
. . importing table "AUDIT_MSG" 5 rows imported
. . importing table "AUDIT_MSG_TYPE" 1 rows imported
. . importing table "AUDIT_RATING" 27 rows imported
. . importing table "AUDIT_USER" 13 rows imported
. . importing table "BCK_BOND_PRICES_TO_DELETE" 5038 rows imported
. . importing table "BENCHMARK" 198 rows imported
. . importing table "BENCHMARK_TYPE" 18 rows imported
. . importing table "BOND" 30244 rows imported
. . importing table "BOND_PRICE" 17585683 rows imported
. . importing table "BOND_PRICES_TO_DELETE" 0 rows imported
. . importing table "BOND_TRADE" 4051188 rows imported
. . importing table "BOND_TRADE_EXPORT" 0 rows imported
. . importing table "BOND_TRADE_IMPORT" 218444 rows imported
. . importing table "USER_PASSWORD" 5535 rows imported
. . importing table "USER_SESSIONS" 767 rows imported
. . importing table "USER_TYPE" 8 rows imported
Import terminated successfully without warnings.

Imports finishes with no error. The developer is claiming that he sees some some columns in dev that are not in production when running a perl script. We are investigating this further.

My question is that I always assumed that an import of a schema (with the above syntax) will drop and recreate tables and rebuild indexes sequentially. In other words all the schema will be refreshed and all objects (tables, indexes, constraints, sps, triggers etc) will be recreated in the correct order by import. For example a table will be created and populated, then indexes on this table will be built etc. Is this correct?

Thanks,

Claudia

 

[lewisp]

As far as I know import does not drop a table then recreate it. By default if the table already exists it will stop with an error, unless you tell it to ignore create errors.

If the tables already exist data from the import will be appended.

If there are extra columns in your tables then those columns must exist in the export file.

 

[ClaudiaFilliponi]

Thanks.

Actually we do the following before import for this schema:

drop user bond cascade ;

So effectively all objects belonging to this schema are dropped prior to import. We then create this user and assign the appropriate profile. However, we found the proplem and is not related to import.

thanks

 

[SantaMufasa]

And was the problem a PICNIC problem? (Problem In Chair, Not In Computer)

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ClaudiaFilliponi]

The problem was that the report run in production did not finish properly and as such the figures were different. The one on dev was OK.

As a matter of interest what option in import will force all the objects in the schema to be dropped and recreated. Obviously using drop <user> cascade prior to import will get rid of the schema and all objects but the downside is that you will need to create the user and profile etc again. What is the easiest way of doing the same (forcing to drop all database objects for a schema) through imp?

Thanks

 

[SantaMufasa]

Sorry, Claudia, but what you speak of is a "feature deficiency" in Oracle's import utility.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ClaudiaFilliponi]

So Santa any table with existing data will be appended to? I was under impression that indexes will be rebuilt. Is that true?

Thanks

 

[SantaMufasa]

True on both accounts, Claudia. There are, however, a couple of considerations which should prevent inadvertent duplication of data:

1) The default for imp is "IGNORE=N", which directs the imp utility not to ignore the existence of a table you are attempting to import; it throws an error for that table...does not proceed with the import for that table, but proceeds to the next table.

2) Your tables should always have a Primary Key definition, which prevents importation of duplicate rows.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[ClaudiaFilliponi]

Your point

1) The default for imp is "IGNORE=N", which directs the imp utility not to ignore the existence of a table you are attempting to import; it throws an error for that table...does not proceed with the import for that table, but proceeds to the next table.

But if the table exists it will not create the table (throws error saying that table already exists) but will append records?

 

[SantaMufasa]

No, (and I should have clarified in my earlier post): If the table exists and you want the import rows to append to the existing row set, then you must override the default imp behaviour with "IGNORE=Y'. Sorry for the omission.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.