I've been tasked with doing some tidying of our database. I'm not a DBA expert so here I am. (We don't have a DBA expert either).

We have a user Old_User and another user New_User.

Old_User owns a set of tables, Table_A and Table_B. New_User has synonyms t_a and t_b respectively.

Can anyone give me some advice on how I should go about moving the ownership of Table_A and Table_B to New_User.

There are tables owned by New_User which have foreign keys to the tables owned by Old_User. I'm not sure if that complicates anything? Ideally, all referential integrity and constraints should be the same.

The goal will be to delete the Old_User eventually.


You can't change the owner of a table.

You can create a new table that is owned by NEW_USER, copy the data from the old table to the new table, drop the foreign key constraints that reference the old table, and create new foreign key constraints that reference the new table. But that's realistically going to require a downtime window since the tables need to be static while this copy is going on.

There are various ways to create a new table that is a copy of the old table. Personally, I'd use your favorite PL/SQL IDE (i.e. SQL Developer, TOAD, etc.) to generate the DDL for Old_User.Table_A and then manually edit the DDL to create New_User.Table_A. You could also do an export & import with the FROMUSER and TOUSER parameters. Or you can use the DBMS_METADATA package to get the DDL

SELECT dbms_metadata.get_ddl( 'TABLE', 'TABLE_A', 'OLD_USER' ) 
  FROM dual

and edit that.

Once you've created the new table, you can copy the data over

INSERT INTO new_user.table_a( <<list of columns>> )
  SELECT <<list of columns>>
    FROM old_user.table_a

Then, you'll need to go through each child table dropping the old foreign key constraint and creating the new constraint, i.e.

ALTER TABLE child_of_a
 DROP CONSTRAINT fk_constraint_name;

ALTER TABLE child_of_a
  ADD CONSTRAINT fk_constraint_name FOREIGN KEY (name_of_column_in_child)
      REFERENCES new_user.table_a( name_of_column_in_parent )

If you have enough child tables, you could potentially write a script that dynamically generates that DDL.

Once that's done, you can drop the old table and let the applications start modifying the data again.

  • I have a question regarding the insert into one user to another user. I am using TOAD version 9. How can log in into both user simultaneously to fire the query you suggested? Can you help me with this? – ivorykoder Aug 8 '12 at 10:10

If you have a large number of tables and a good enough blackout window, it will be simpler to do this task using Data Pump with the REMAP_SCHEMA option.

Ref.: http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.