Tip

Change table ownership and properties in 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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.