Moving a table from one user/schema to another

I had a requirement to move a table from one user (i.e. schema) to another. As this is not possible with an ALTER, the usual approach is to do CREATE TABLE AS or an export-import. Depending on the volume of data, this may take a long time. I found this interesting approach on google and decided to try it

Can you move a table between schemas ?

Many thanks to the person that identified this approach.

My test was as follows:

As sysdba, I created the below users. The assumption is user JIM needs to be dropped but his table named JIMS_TABLE needs to be transferred to user BOB. User Mike is a user who has select on JIM.JIMS_TABLE. The grant on DBA_TABLES to JIM is only to facilitate the creation of a test table. It has no other relevance to the test.

create user jim identified by N0t_Jim#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to jim;
GRANT UNLIMITED TABLESPACE TO jim;
grant create table to jim;
grant select on dba_tables to jim;
 
 
create user bob identified by N0t_Bob#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to bob;
GRANT UNLIMITED TABLESPACE TO bob;
grant create table to bob;
grant select on dba_tables to bob;
 
 
create user mike identified by N0t_Mike#01#  TEMPORARY TABLESPACE TEMP DEFAULT TABLESPACE PRODTSPACE;
grant connect to mike;

As user Jim, create a table, grant select to user MIKE.

create table jim.jims_table
tablespace PRODTSPACE
as
select * from dba_tables
;
 
GRANT SELECT ON JIM.JIMS_TABLE TO MIKE;

The table now exists

col owner format a5;
col table_name format a10;
select
owner, table_name, tablespace_name, partitioned, row_movement
from dba_tables
where owner in ('JIM','BOB')
;
 
OWNER TABLE_NAME TABLESPACE_NAME                PAR ROW_MOVE
----- ---------- ------------------------------ --- --------
JIM   JIMS_TABLE PRODTSPACE                     NO  DISABLED

With the following grants:

set pages 99;
col grantee  format a10;
col owner format a10;
col table_name format a10;
col grantor  format a10;
col PRIVILEGE format a17;
 
SELECT GRANTEE, OWNER, TABLE_NAME, GRANTOR, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE  owner in ('JIM','BOB')
order by grantee
;
 
GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- -----------------
BOB        JIM        JIMS_TABLE JIM        ALTER
BOB        JIM        JIMS_TABLE JIM        DELETE
BOB        JIM        JIMS_TABLE JIM        INDEX
BOB        JIM        JIMS_TABLE JIM        INSERT
BOB        JIM        JIMS_TABLE JIM        SELECT
BOB        JIM        JIMS_TABLE JIM        UPDATE
BOB        JIM        JIMS_TABLE JIM        FLASHBACK
BOB        JIM        JIMS_TABLE JIM        REFERENCES
BOB        JIM        JIMS_TABLE JIM        READ
BOB        JIM        JIMS_TABLE JIM        ON COMMIT REFRESH
BOB        JIM        JIMS_TABLE JIM        QUERY REWRITE
BOB        JIM        JIMS_TABLE JIM        DEBUG
MIKE       JIM        JIMS_TABLE JIM        SELECT

In preparation to move the table, user JIM grants all on the table to the future new owner BOB.

GRANT ALL ON JIM.JIMS_TABLE TO BOB;

Next, JIM generates the DDL of the table

set long 9999999 pages 0 heading off pagesize 0;
SELECT dbms_metadata.get_ddl('TABLE','JIMS_TABLE') FROM dual;

Give the DDL to user BOB, change the table owner and add a partitioning clause (see the last line of DDL). In order to reduce the size of the DDL, I have deleted many of the columns.

  CREATE TABLE "BOB"."JIMS_TABLE"
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE,
        "TABLE_NAME" VARCHAR2(128) NOT NULL ENABLE,
        "TABLESPACE_NAME" VARCHAR2(30),
        .
        .
        .
        Columns deleted
        .
        .
        .
        "DATA_LINK_DML_ENABLED" VARCHAR2(3),
        "LOGICAL_REPLICATION" VARCHAR2(8)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PRODTSPACE"
  partition BY hash (OWNER) (partition p1);

We now have two tables

col owner format a5;
col table_name format a10;
select
owner, table_name, tablespace_name, partitioned, row_movement
from dba_tables
where owner in ('JIM','BOB')
;
 
OWNER TABLE_NAME TABLESPACE_NAME                PAR ROW_MOVE
----- ---------- ------------------------------ --- --------
JIM   JIMS_TABLE PRODTSPACE                     NO  DISABLED
BOB   JIMS_TABLE                                YES DISABLED

Now to swap the data from JIM’S table to the new BOB table

ALTER TABLE BOB.JIMS_TABLE exchange partition p1 WITH TABLE JIM.JIMS_TABLE;

The data is now in the new table

SELECT COUNT(*) FROM JIM.JIMS_TABLE;
SELECT COUNT(*) FROM BOB.JIMS_TABLE;
 
SQL> SELECT COUNT(*) FROM JIM.JIMS_TABLE;
 
  COUNT(*)
----------
         0
 
SQL> SELECT COUNT(*) FROM BOB.JIMS_TABLE;
 
  COUNT(*)
----------
      2228

The grants will have to be re-granted on the new table named BOB.JIMS_TABLE;

GRANTEE    OWNER      TABLE_NAME GRANTOR    PRIVILEGE
---------- ---------- ---------- ---------- -----------------
BOB        JIM        JIMS_TABLE JIM        ALTER
BOB        JIM        JIMS_TABLE JIM        DELETE
BOB        JIM        JIMS_TABLE JIM        INDEX
BOB        JIM        JIMS_TABLE JIM        INSERT
BOB        JIM        JIMS_TABLE JIM        SELECT
BOB        JIM        JIMS_TABLE JIM        UPDATE
BOB        JIM        JIMS_TABLE JIM        REFERENCES
BOB        JIM        JIMS_TABLE JIM        READ
BOB        JIM        JIMS_TABLE JIM        ON COMMIT REFRESH
BOB        JIM        JIMS_TABLE JIM        QUERY REWRITE
BOB        JIM        JIMS_TABLE JIM        DEBUG
BOB        JIM        JIMS_TABLE JIM        FLASHBACK
MIKE       JIM        JIMS_TABLE JIM        SELECT