Change table ownership and properties in less than a minute

This Oracle procedure cuts the time to move a large table and its properties to less than a minute.

Warning: This is an unsupported feature of Oracle and should be used with extreme caution!

Here is how to change the ownership of a large table -- with its partitions, indexes, and triggers -- and recreate the synonyms of the table in less than a minute. If you do this in one of the traditional ways -- export/import or sqlldr or SQL*Plus copy command or insert /*+ APPEND */ select -- it will take hours to move the table to a different user. So I thought let's hack the data dictionary table to make it simple. With this script, I was able to move a very large table in less than a minute. It will work on version 8i and higher.

--Program Name: dbms_moveobj.sql as SYS
--Created By:  Vijaya R. Reddy Dumpa. 
--Purpose:  To move a large table and its partitions, indexes, and triggers 
--from one user to another user less than a minute.
--Parameters:  Table name or object name, from user and to user.
--Note: Procedures, Functions and Packages have to be moved individually
--depending on what we can actually move.
--Usage: execute sys.dbms_moveobj('EMP','TEST','RR');

Create or Replace Procedure 
dbms_moveobj(mt_name varchar2, from_user varchar2, to_user varchar2)
is

cursor cur_index_trig_name_from_tab
is
select index_name
from dba_indexes
where table_name = mt_name
UNION
select trigger_name
from dba_triggers
where table_name = mt_name;

rec_index_trig_name_from_tab cur_index_trig_name_from_tab%ROWTYPE;


cursor cur_synonym_name_from_tab
is
select owner,synonym_name,table_owner,table_name
 from dba_synonyms
where table_name = mt_name;

rec_synonym_name_from_tab cur_synonym_name_from_tab%ROWTYPE;


from_uid number;
to_uid number;
fu_name varchar2(30);
tu_name varchar2(30);
sql_str1 varchar2(1000);
sql_str2 varchar2(1000);

begin

select user_id, username
 into from_uid, fu_name
from dba_users
where username = from_user;

select user_id, username 
 into to_uid, tu_name
from dba_users
where username = to_user;

update obj$
 set owner# = to_uid
where owner# = from_uid
and name = mt_name
and type# in (2,4,6,7,8,9,11,19);
commit;


For rec_index_trig_name_from_tab in cur_index_trig_name_from_tab
Loop
update obj$
 set owner# = to_uid
where owner# = from_uid
and name = rec_index_trig_name_from_tab.index_name;
commit;
End Loop;

For rec_synonym_name_from_tab in cur_synonym_name_from_tab
Loop
sql_str1 := 'drop synonym '||rec_synonym_name_from_tab.owner||'.'||rec_synonym_name_from_tab.synonym_name;
execute immediate sql_str1;
sql_str2 := 'create synonym '||rec_synonym_name_from_tab.owner||'.'||rec_synonym_name_from_tab.synonym_name||' 
  for '||to_user||'.'||rec_synonym_name_from_tab.table_name;
execute immediate sql_str2;
End Loop;

--execute immediate 'alter system flush shared_pool';

Exception
  WHEN OTHERS THEN
  RAISE;

end;

Note: Run the above procedure as SYS user. Here is a bonus: Recreating the synonyms for the given table after moving it to a different user, as follows:
Create or Replace Procedure 
Recreate_syn(mt_name varchar2, mt_user varchar2)
is

cursor cur_synonym_name_from_tab
is
select owner,synonym_name,table_owner,table_name
 from dba_synonyms
where table_name = mt_name;

rec_synonym_name_from_tab cur_synonym_name_from_tab%ROWTYPE;


sql_str1 varchar2(1000);
sql_str2 varchar2(1000);

begin

For rec_synonym_name_from_tab in cur_synonym_name_from_tab
Loop
sql_str1 := 'drop synonym '||rec_synonym_name_from_tab.owner||'.'||rec_synonym_name_from_tab.synonym_name;
execute immediate sql_str1;
sql_str2 := 'create synonym '||rec_synonym_name_from_tab.owner||'.'||rec_synonym_name_from_tab.synonym_name||' 
  for '||mt_user||'.'||rec_synonym_name_from_tab.table_name;
execute immediate sql_str2;
End Loop;

Exception
  WHEN OTHERS THEN
  RAISE;

end;

Reader Feedback

Arup N. writes: I would like to point out that this tip advises to the user to update the data dictionary directly, which is a very dangerous approach. While this may appear to work, the change of data dictionary by Oracle is NOT SUPPORTED by Oracle Support and moment this is discovered, the database will be desupported, hanging in limbo! No one will want to do that to a production database.

The dictionary of Oracle is not a publicly known resource. While the advised method may work for this objective, it may potentially change some other parts of the data dictionary with irreversible damage. How does the author know that the only change to updating the obj$ table is the change of ownership, nothing else? And worst of all he (or she) has not even bothered to place the usual warning about the changing the data dictionary.

This is not a tip; this is a brute force approach to handle things thatshould be handled in a certain way. Consider a mechanic advising you to push a car down a slope and suddenly change gears to start it. Well, it may start the engine; but it may very well break your transmission or your gears. Worst, perhaps it did and you didn't even know till you shift to a lower gear to drive uphill! Would you call that a professional tip?

David K. writes: I feel the tip to rename objects in Oracle by running the package DBMS_MOVEOBJ is something that should have a stronger warning at the beginning of the tip. If you use this package and you have problems with your instance Oracle will not support you, so why would you do this? Also, the author calls his package "DBMS_", a naming convention for valid Oracle supported packages. If someone compiles this under SYS, another DBA might think this is an Oracle supported process. This I feel is the biggest problem -- I feel that the author should call the procedure UNSUPPORTED_MOVEOBJ to make sure everyone knows this is really an unsupported feature.

Bill M. writes: I am extremely dissappointed with this tip. Please post a warning along with it.

Iudith M. writes: The previous readers are right, such an operation may have unexpected results upon the data dictionary. But I think Oracle should supply a supported procedure for performing such database tasks as 1) changing object ownership without having to drop and recreate the object and 2) moving an object from one tablespace to another.

For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

This was first published in April 2003

Dig deeper on Oracle database backup and recovery

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close