How do I change owners for an Oracle Table ?

No, Oracle does not support changing the owner of a table.

The owner of a table (or any other object) is recorded in the owner# column of the SYS.OBJ$ table.  Updates though of most SYS tables are strongly discouraged by Oracle, and are not supported.  If you have a test system that you don't mind breaking you could try updating the owner# column in one of the SYS.OBJ# records.  I expect that you would need to at least flush the shared pool after doing this, and you may need to shut Oracle down and restart it, since this information may also be kept in the x$... virtual tables that are loaded at instance startup.

Remember I'm not recommending that you try this (I never have) but it may work.

altquark,
  As Mark says, it's not supported.  You needn't export/import to move them, however.  You can CTASU and use an export with no data (ROWS=N) of the tables, indexes, grants, and constraints which could be imported into the new schema.

The sequence of the operation would be:

1) export the definitions
2) from the new schema CREATE TABLE ... NOLOGGING PARALLEL AS select * from PROD2DTA.<table>;
3) import the definitions (with ignore create errors)
4) Test
5) Drop original tables.

Good luck!

There are only three approaches in Oracle:
-Export/Import
-Create table .. AS Select ...
-Copy

Export/import is the only approach that will preserve all of the table's triggers, grants, indexes, constraints, and comments.  If you use create as select or copy, such table properties must be manually recreated, since these
methods merely transfer the data from one table to another. On the other hand, export/import is the most time-consuming of the three approaches.  Create as select and copy are generally faster, but keep in mind that these methods require that you have enough space to hold both tables.  When using create as select, make sure you have enough redolog and rollback space to cope with the amount of redo and rollback that the statement will generate.

Since your goal is to change the ownership of the table, Copy would be the best approach.

The combination "CTAS" (Create table ... as select...) plus import the DrSQL suggested may be the easiest way to do this.  If you are concerned about performance though, it may be faster (like 10 to 20 times faster) to use SQL*Loader with the direct path option instead of the "CTAS" or copy approach to move the data for your large tables.  This requires more programming effort on your part though.  Here are the steps:

1) export the definitions
2) write and run SQL*Plus or PL\SQL scripts to unload each table into ASCII files (be careful with your format masks for date and number columns or you may lose precision and/or time values!)
3). in the new schema run import with:
tables=Y
constraints=N
indexes=N
4) create SQL*Loader scripts for each table
5) run SQL*Loader for each table
6) run import again with
ignore = Y
constraints=Y
indexes=Y
grants=Y
7) Test
8) Drop original tables.
9) check for synonyms pointing to the old tables, and if found, drop them, and create replacements.

(You need this last check of synonyms regardless of which approach you use.)

In step 5 of my previous posting for SQL*Loader, I should have included "with direct_path = Y".  You can also specify "unrecoverable" if you use SQL*Loader's direct_path method to gain more speed.  This assumes though that you are prepared to do a backup right after the load.

***********UNDOCUMENTED AND PROBABLY UNSUPPORTED - BUT IT WORKS & takes less than 10 minutes! ****************

!!!!!DISCLAIMER:
If you are doing this on a production instance - do it at your own risk and after it is well tested. you will need to Check other dependencies etc i.e!!!!!!!!!!

Having said the above, the below works for me!!!!

All you need to do is drop all dependencies e/g grants, indexes, synonyms etc, change the owner as below and then recreate grants, synonyms, indexes, triggers etc.


SQL> connect system/manager@test_db

Assume you want to change owner of table BIG_TAB from A to B

SQL> select name, user# from user$ where name in ('A','B');

   Name        user#
  -------------------

    A          34
    B          102


SQL> update obj$ set owner# = 102 where name = 'BIG_TAB';

  1 row updated.

SQL> commit;



**********REBOOT THE INSTANCE***************



SQL> connect B/password@TEST_DB

SQL> desc BIG_TAB

SQL> select count(*) from BIG_TAB;

After this the previous user A will not be able to access this table unless it is granted by B to A;

GOOD LUCK!



sora

I did not notice that markgeer had already said something to the same effect as what I just posted


sora

it is probably worth noting that as part of a bug fix, Oracle had asked the oBJ$ to be updated.

In patchest #2 for 8.1.7.0 per bug 1901673

update obj$ set status=5 where type#=29 and owner#!=0;



sora

Sora is on the right track - can you also expand on this and explain whether theres a system table that can be linked back to help us with the indexes ?  You're on the track for big bonus points !

Sora

You have answered exactly what I was asking for - a method to change owners of tables across a schema.  We are going to thoroughly test this solution - and then will keep the board up to date with the results.

Thankyou for your time and patience on this - enjoy the bonus points !

Altquark

Just one correction to one of my posts - you need to connect as SYS (not SYSTEM) to be able to do this.

sora