Change ownership for objects(update the dictionary!!)
                                                 Last update (2008-06-12 12:25:36)
                                                                                                                   Date added (2008-02-22 13:23:08)

Summary
You can change ownership for a table (for userA to userB) with the traditional ways:

export/import
create as select
SQL*Plus copy command, etc

But if you want someting Quick and Dirty you can update the dictionary

This is unsupported from Oracle

How to
Create a "TEST" table owned by SYS
CREATE TABLE TEST AS SELECT * FROM dba_objects;
I want to change ownership from SYS to HELPDESK user for the table SYS.TEST, first connect as sys.
SELECT user_id, username FROM dba_users WHERE username IN ('HELPDESK', 'SYS');

   USER_ID USERNAME
---------- ------------------------------
       312 HELPDESK
         0 SYS
Update the dictionary
UPDATE obj$ SET owner# = 312 
WHERE owner# = 0
AND NAME = 'TEST'
AND TYPE# IN (2);

COMMIT;
Flush the shared pool
alter system flush shared_pool;
Now the object is in HELPDESK schema.
SELECT * FROM HELPDESK.TEST;
To create a procedure that changes and tables partitions, indexes, and triggers
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;
/
Tip: Procedures, Functions and Packages have to be moved individually

Other links
Data Dictionary Inconsistency
Press CTRL-C during drop of functional based index
Reviews
Categories
Oracle DBA-> (147)
  Alertlog (3)
  Archivelog (5)
  Auditing (1)
  Backup - Recovery (19)
  Database files (6)
  Database options (2)
  DB links (2)
  Dependencies (1)
  Dictionary (2)
  Exp-Imp Datapump (6)
  Jobs (2)
  Mview (2)
  Networking (3)
  Objects (7)
  Parameters (2)
  Redologs (6)
  Roles - Grants (2)
  Rollback - Undo (8)
  Segments (3)
  Sequences (2)
  Sessions (14)
  SGA (14)
  Tables (10)
  Tablespaces (10)
  Temp (4)
  Toad (5)
  Transactions (1)
  Upgrade (2)
  Users (3)
Oracle APPS DBA-> (66)
Exadata (1)
Performance Tuning-> (59)
Oracle Real Cases (24)
Oracle Errors (23)
Oracle SQL tricks (32)
Oracle RAC (3)
Oracle Security (8)
Filters
Search