How to move the ownership/copy a table from one user to another?

Question

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.

Answer 1

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.