Re: Export and import as system - table ownership???

From: Antony Sampson <sampson_at_dtf.sa.gov.au>
Date: 1995/12/15
Message-ID: <cf7cb$f1f2d.3b8_at_murdoch.systems.sa.gov.au>#1/1

badri_at_cc.gatech.edu (badri) wrote:

>Hi:
 

> I want to export and import all objects from one database to
>another. The question is whether the ownership attribute will be
>preserved. If I have a user called user1 and if I import it into
>another database as system, will the objects now be owned by system?
 

>thanks a lot,
 

>-Badri
 

>p.s: If the answer is yes, how do I fix it? Also, what if I did not have
>a userid in the destination database that matches the ownership of
>the tables in the source database?

When doing a full database import or export the ownership is preserved.
If the account doesn't exist on the destination database it is created.

The real problem occurs when you import into a database where the user in question does exist and already has objects in their schema.

In this case the "create table" statement that results from the import will fall over - the objext will not be re-imported. If you turn the switch called "Ignore create errors" on, the create tables will still fail, but then the data rows will be inserted into the table that was already there.

Therefore, if you are importing a user which is already there in the destination database (because you wish to restore data values that have changed in some tables for instance) it is best to drop all the objects in the user's schema first.

A sql script that will do this is:  

	set head off
	set pages 1000
	set newpage 1
	spool drops.sql
 
	select 'drop table '||table_name||';' from user_tables
	where table_name like '&1';
 
	spool off;
	_at_drops

Make sure you have connected as the user whose objects you wish to drop, or else you may suddenly find you are dropping all of the objects of the username you are connected as.

Hope this helps, mail if it doesn't.

cheers,

---

Tony Sampson
DBA/software terrorist, South Australian Government Financing Authority sampson_at_dtf.sa.gov.au

                   The unnatural, that too is natural.
                                  Goethe

Received on Fri Dec 15 1995 - 00:00:00 CET