Solved

How do I change owners for an Oracle Table ?

Posted on 2002-03-21
12
1,597 Views
Last Modified: 2007-12-19
I have a large number of tables owned by an Oracle ID "PROD2DTA" - I wish to quickley change the owner to "PRODDTA".  Under MS SQL Server - its possible to do this using sp_changeobjectowner - is there any way to do this in Oracle ?

I do not wish to export/import the tables - since many have >10m rows.

For anyone that solves this outright - look for bonus points !
0
Comment
Question by:altquark
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6886327
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.
0
 
LVL 22

Expert Comment

by:DrSQL
ID: 6886384
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!
0
 

Expert Comment

by:mchauhan
ID: 6886447
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.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6886727
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.)
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6886745
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.
0
 
LVL 5

Expert Comment

by:sora
ID: 6887796
***********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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:sora
ID: 6887807
I did not notice that markgeer had already said something to the same effect as what I just posted


sora
0
 
LVL 5

Expert Comment

by:sora
ID: 6887813
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
0
 
LVL 1

Author Comment

by:altquark
ID: 6889227
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 !
0
 
LVL 5

Accepted Solution

by:
sora earned 200 total points
ID: 6890430
alright, I have taken this to the limit!

I have changed the owner for ALL objects from owner1 to owner2 and it works with no visible side effects, although I would not be willing to take responsibility should anything go wrong...

But it works alright!   and as follows:

I have an existing user (OPS$AQSOP) in one of my testbed copies of a production instance. I created a new user called NEW_USER and then transferred ownership of ALL OPS$AQSOP's objects to NEW_USER. This included the following:
  - Tables
  - Indexes
  - Constraints
  - Synonyms
  - Procedures, triggers, packages, functions etc

The grants made by OPS$AQSOP are still in effect but after the migration is done, these GRANTs are considered as having been 'granted' by NEW_USER who can revoke as he/she so desires

OK...here goes....

SQL> SELECT NAME,USER# FROM USER$ WHERE NAME IN ('OPS$AQSOP','NEW_USER');

NAME                               USER#
------------------------------ ---------
NEW_USER                              55
OPS$AQSOP                             53


REM  NOTE: the below takes care of tables, indexes, procedures, packages, functions etc
SQL> UPDATE obj$ set owner#=55 where owner#=53;

1604 rows updated.

REM Note: the below takes care of synonyms
SQL>
SQL> UPDATE syn$ set owner='NEW_USER' where owner='OPS$AQSOP';

149 rows updated.

REM Note: the below takes care of constraints
SQL>
SQL> UPDATE con$ set owner# = 55 where owner# = 53;

1432 rows updated.

SQL> commit;

Commit complete.

SQL> exit


************RESTART THE INSTANCE*****************

Then I checked samples of tables, indexes, constraints, grants etc and it all seems fine!!!!!

SQL> select owner from dba_tables
  2  where table_name='EXCHANGE_LIST';

OWNER
------------------------------
NEW_USER


SQL> select owner
  2  from dba_indexes
  3  where table_name='EXCHANGE_LIST';

OWNER
------------------------------
NEW_USER
NEW_USER
NEW_USER

SQL> SELECT OWNER FROM DBA_CONSTRAINTS WHERE TABLE_NAME='EXCHANGE_LIST';

OWNER
------------------------------
NEW_USER
NEW_USER
NEW_USER
NEW_USER
NEW_USER
NEW_USER

6 rows selected.




Good Luck!



sora
0
 
LVL 1

Author Comment

by:altquark
ID: 6890952
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 !
0
 
LVL 5

Expert Comment

by:sora
ID: 6891061
Altquark

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

sora
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now