Summary: Newbie question: how to change ownership of a table

Original question:

> What is the right/best way to change the ownership of an existing table or other object?
> I checked my manuals but did not find anything.

Thanks for responding to:

> From: rbaniste_at_cisco.com (Rick Banister)
> From: paladin_at_along.DIALix.oz.au (Alex Long)
> From: jenseng_at_cadvision.com
> From: lfeges_at_hookup.net (Louis Feges)
> From: Simon Holt <holt_at_ntrust.org.uk>

Summary:

   I received several good ideas. We will be migrating from Oracle6 to Oracle7 soon so I probably will export the tables from Oracle6 and import to Oracle7 and change the owner at that time.

Jeff Graves

Answers:

> From: rbaniste_at_cisco.com (Rick Banister)

There is no way to change ownership. You can copy a table's definition and contents by either of the following methods.

1. Export the table with the exp program under the original owner; then import the table with the imp program under the new owner (using the export dump file).
2. Do a CREATE TABLE 'xxx' AS SELECT * FROM olduser.tablename.

Either method will work, but remember to create the indexes. Export/Import takes care of that, if you let it (prompts you for export parameters in interactive mode).

> From: paladin_at_along.DIALix.oz.au (Alex Long)

A couple,of "quick and dirty" ways, may not be the most elegant, but ought to work.

1. Log in as new owner. Create table_name as select * from :owner.table_name; Tou can then drop the old one if you wish.
2. Export the table, and wher importing it, use the parameters to indicate the from_user and to_user. For this, you can either use the DBA login, or the new owner's.

   ---

> From: jenseng_at_cadvision.com

The right way/best way is to have the new owner create them (and drop them from the other place). However, if this is not possible. You can create a public synonym to point to the objects in question. For example, if a table EMP is owned by user BOB, but it really should be owned by BETTY because she needs to see what is in it (or update it). Do the following:

   login as BOB

           GRANT SELECT ON EMP TO PUBLIC;
           GRANT INSERT, UPDATE ON EMP TO BETTY;     /* or whatever priviledges are appropriate */
   login as SYSTEM
          CREATE PUBLIC SYNONYM EMP FOR BOB.EMP;

My syntax may not be quite right, but the idea is there.

>From: lfeges_at_hookup.net (Louis Feges)

I don't think there is a DIRECT way to change ownership. Here are two ways that work, assuming user A owns table X, and user B will become the new owner:

1. using SQL, connect as B and do a CREATE TABLE X as select * from A.x
2. using oracle's imp/exp utilities, export table X and import it back with the following additional parameters:

  fromuser=A,touser=B