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

oracleoracle-11g

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.

Best Answer

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.

Related Solutions

Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

As long as you don't add data between the last refresh before the column add and after the column add, you should be fine. If you must allow for this then the easiest thing would be to do a complete refresh at the end of your plan. You shouldn't need to re-create the materialized view log.

Setup

--Create master table.
drop table t1;
create table t1 (id number(10) primary key, datetime date);
insert into t1 values (1, sysdate-1);
create materialized view log on t1 with primary key;

--Create materialized view table.
drop materialized view t2;
drop table t2;
create table t2 as (select * from t1);

--Create materialized view.
create materialized view t2 on prebuilt table with reduced precision
refresh fast on demand
as select id, datetime from t1;

--Show an empty materialized view.
select * from t2;

--Add data to t1.
insert into t1 values (2, sysdate);
commit;

--Refresh
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t2;

Planned Column Add

--Add Column.
alter table t1 add n varchar2(1);
alter table t2 add n varchar2(1);

--Re-create the materialized view.
drop materialized view t2 preserve table;
--***T1 updates between last refresh and the following statement will be lost.
create materialized view t2 on prebuilt table with reduced precision 
   refresh fast on demand
   as select id, datetime, n from t1;

--Add data.
insert into t1 values (3,sysdate+1,'a');
commit;

--Refresh.
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t1;
select * from t2;

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Best Answer

Related Solutions

Way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases

Sql-server – How to connect multiple users/schemas in Oracle 11g

Related Question