Move tables (online / offline) in Oracle (9i-12cR2)

I must thank my fellow DBA Franky Weber Faust for his publication in his blog.
 The goal here is to share the procedures for performing the move of tables in Oracle Database from version 9i to version 12cR2.

Here in this article I am using 12cR2 (12.2.0.1) to also be able to demonstrate the features of this version. So by taking the part of the Multitenant architecture, the procedures also work for non-CBD databases.

Let’s first configure our environment before performing the move …

Setting up the environment

It is connected to the Root container:

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Check out existing Pluggable Databases:

 SQL> select name, open_mode from v$pdbs;

NAME OPEN_MODE
-------------- ----------
PDB$SEED READ ONLY
PDB1 MOUNTED

Elapsed: 00:00:00.02

I create a new PDB called SOEPDB:

 SQL> create pluggable database soepdb admin user pdbadmin identified by oracle default tablespace soedatadatafile size 100M autoextend on parallel 2 storage (maxsize 5g);

Pluggable database created.

Elapsed: 00:00:08.18
I elevate my session to the newly created PDB:
SQL> alter session set container=soepdb;

Session altered.

Elapsed: 00:00:00.01

I open the PDB in question:

 SQL> alter pluggable database soepdb open;

Pluggable database altered.

Elapsed: 00:00:09.74
I create the user FRANKY already with the Role DBA:
SQL> grant dba to franky identified by oracle;

Grant succeeded.

Elapsed: 00:00:00.09

I create two tablespaces to perform the tests:

 SQL> create tablespace users;

Tablespace created.

Elapsed: 00:00:00.78

SQL> create tablespace users2;

Tablespace created.

Elapsed: 00:00:00.71

I connect to the PDB:

 SQL> conn franky/oracle@localhost:1521/soepdb.localdomain
Connected.

Session altered.

Elapsed: 00:00:00.00

Move Online from tables using DBMS_REDEFINITION package

The purpose here is to move the T_MOVE1 table to the USERS2 tablespace without generating unavailability for the application using the DBMS_REDEFINITION package. This procedure works from Oracle Database 10gR1, although the DBMS_REDEFINITION package exists from Oracle Database 9i.

I create the table in question in the USERS tablespace from the ALL_OBJECTS view:

SQL> create table t_move1 tablespace users as select * from all_objects;

Table created.

Elapsed: 00:00:05.55

I create an index for this table also in the USERS tablespace:

SQL> create index i_move1 on t_move1 (object_id) tablespace users;

Index created.

Elapsed: 00:00:00.16

I check the created segments and also the tablespace where they are stored:

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS
T_MOVE1 TABLE USERS

Elapsed: 00:00:00.20

Verify that the T_MOVE1 table of the owner FRANKY can be redefined:

SQL> exec dbms_redefinition.can_redef_table('FRANKY','T_MOVE1');
BEGIN dbms_redefinition.can_redef_table('FRANKY','T_MOVE1'); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "FRANKY"."T_MOVE1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 242
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5439
ORA-06512: at line 1
Elapsed: 00:00:00.04

I made a mistake in checking because this table does not have a primary key. We can only use this method for tables that have PK.

I create the PK constraint in the table:

SQL> alter table t_move1 add constraint t_move1_pk primary key (object_id);

Table altered.

Elapsed: 00:00:00.14

Again check if the table can be redefined:

 SQL> exec dbms_redefinition.can_redef_table('FRANKY','T_MOVE1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
I create the T_MOVE2 table in the USERS2 tablespace based on T_MOVE1, but without its data:I create the T_MOVE2 table in the USERS2 tablespace based on T_MOVE1, but without its data:
SQL> create table t_move2 tablespace users2 as select * from t_move1 where 1=0;

Table created.

Elapsed: 00:00:00.22
Start the reset process by specifying the owner, the source table, and the target table (which must be empty):
SQL> exec dbms_redefinition.start_redef_table('FRANKY','T_MOVE1','T_MOVE2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.05
I consult the data from both tables to see if there are the same number of records:

SQL> select count(*) from t_move1;

COUNT(*)
----------
68108

Elapsed: 00:00:00.10

SQL> select count(*) from t_move2;

COUNT(*)
----------
68108

Elapsed: 00:00:00.05
Given that both are the same I will use the anonymous block below to copy the dependencies of the source table to the target table:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
dbms_redefinition.copy_table_dependents(
'FRANKY',
'T_MOVE1',
'T_MOVE2',
copy_indexes => dbms_redefinition.cons_orig_params,
num_errors => num_errors
);
dbms_output.put_line(num_errors);
END;
/
DECLARE
*
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2074
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2074
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1608
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1487
ORA-06512: at "SYS.DBMS_REDEFINITION", line 408
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1480
ORA-06512: at "SYS.DBMS_REDEFINITION", line 2056
ORA-06512: at "SYS.DBMS_REDEFINITION", line 3115
ORA-06512: at "SYS.DBMS_REDEFINITION", line 5578
ORA-06512: at line 4
Elapsed: 00:00:09.52

An error was returned stating that the source NULL columns are already with NOT NULL on the target.

We can ignore these errors by reporting the ignore_errors parameter with the value TRUE in the anonymous block below:

SQL> DECLARE
num_errors PLS_INTEGER;
BEGIN
dbms_redefinition.copy_table_dependents(
'FRANKY',
'T_MOVE1',
'T_MOVE2',
copy_indexes => dbms_redefinition.cons_orig_params,
num_errors => num_errors,
ignore_errors => true
);
dbms_output.put_line(num_errors);
END;
/

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.55

Another option is to use this anonymous block (by oracle-base.com) informing in the parameter copy_constraints the value FALSE:

-- Copy table dependents
SET SERVEROUTPUT ON
DECLARE
l_num_errors PLS_INTEGER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'FRANKY',
orig_table => 'T_MOVE1',
int_table => 'T_MOVE2',
copy_indexes => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
copy_triggers => TRUE, -- Default
copy_constraints => FALSE, -- Non Default
copy_privileges => TRUE, -- Default
ignore_errors => FALSE, -- Default
num_errors => l_num_errors);
DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/

It is important to observe the other objects dependent on a table during the procedure, because in some versions of Oracle Database PL / SQL objects may be invalid, mainly triggers, because they are directly related to the tables.

After copying the dependencies I check the existing segments. Notice that we have the table T_MOVE1 and its index I_MOVE1 in the tablespace USERS and the table T_MOVE2 in the tablespace USERS2 and its index TMP $$ _ I_MOVE10 in the tablespace USERS:


 SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS
TMP$$_I_MOVE10 INDEX USERS
T_MOVE1 TABLE USERS
T_MOVE2 TABLE USERS2
I’m going to insert a new record in the T_MOVE1 table:

SQL> insert into t_move1 (OWNER,OBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,NAMESPACE) values ('FRANKY','ASDF',1,SYSDATE,SYSDATE,999);

1 row created.

Elapsed: 00:00:00.50

SQL> commit;

Commit complete.

Elapsed: 00:00:00.06
And now I compare the source table with the target table:

SQL> select count(*) from t_move1;

COUNT(*)
----------
68109

Elapsed: 00:00:00.01

SQL> select count(*) from t_move2;

COUNT(*)
----------
68108

Elapsed: 00:00:00.01

Note that we have a different amount of records, so we concluded that our application could continue working on the source table during this move process.

To match the tables we need to execute the synchronism. For this we use the procedure SYNC_INTERIM_TABLE passing again the owner, the source table and the target table:

 SQL> exec dbms_redefinition.sync_interim_table('FRANKY','T_MOVE1','T_MOVE2');


_redefinition.sync_interim_table('FRANKY','T_MOVE1','T_MOVE2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
I now refer to the T_MOVE2 table to see if it has been synchronized with T_MOVE1:

SQL> select count(*) from t_move2;

COUNT(*)
----------
68109

Elapsed: 00:00:00.01
Again I consult the segments created after this synchronization process:

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MLOG$_T_MOVE1 INDEX SOEDATA
MLOG$_T_MOVE1 TABLE SOEDATA
T_MOVE2 TABLE USERS2
I_MOVE1 INDEX USERS
TMP$$_I_MOVE10 INDEX USERS
T_MOVE1 TABLE USERS

6 rows selected.

Elapsed: 00:00:00.05

Note that two more segments have been created: the MLOG table $ _T_MOVE1 and the I_MLOG index $ _T_MOVE1. Actually here we have a Materialized View Log and the T_MOVE2 table is like our Materialized View.We conclude that this process is done through a Snapshot method.

I’ll insert another record to see if sync happens automatically:

SQL> insert into t_move1 (OWNER,OBJECT_NAME,OBJECT_ID,CREATED,LAST_DDL_TIME,NAMESPACE) values ('C##FRANKY','ASDF',9999998,SYSDATE,SYSDATE,999);

1 row created.

Elapsed: 00:00:00.07

SQL> commit;

Commit complete.

Elapsed: 00:00:00.03

When looking at the tables we can see that the synchronism did not occur:

SQL> select count(*) from t_move1;

COUNT(*)
----------
68110

Elapsed: 00:00:00.01

SQL> select count(*) from t_move2;

COUNT(*)
----------
68109

Elapsed: 00:00:00.01

You must do this manually:

SQL> exec dbms_redefinition.sync_interim_table('FRANKY','T_MOVE1','T_MOVE2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.29

Now the tables are synchronized:

 SQL> select count(*) from t_move2;

COUNT(*)
----------
68110

Elapsed: 00:00:00.01

I consult again the segments to verify that no other was created:


 SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MLOG$_T_MOVE1 INDEX SOEDATA
I_MOVE1 INDEX USERS
MLOG$_T_MOVE1 TABLE SOEDATA
TMP$$_I_MOVE10 INDEX USERS
T_MOVE1 TABLE USERS
T_MOVE2 TABLE USERS2

6 rows selected.

Elapsed: 00:00:00.09
Now to finish the process I execute the FINISH_REDEF_TABLE procedure. This procedure will sync final and remove the Materialized View Log:
SQL> exec dbms_redefinition.finish_redef_table('FRANKY','T_MOVE1','T_MOVE2');

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.04



I re-query the segments to verify that the MLOG has been removed and that the T_MOVE1 table is now in the USERS2 tablespace and T_MOVE2 is in the tablespace users:


 SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS
TMP$$_I_MOVE10 INDEX USERS
T_MOVE1 TABLE USERS2
T_MOVE2 TABLE USERS

Elapsed: 00:00:00.11




What really happened was a rename of the tables involved. The redefinition process does this transparently.


Now I remove the T_MOVE2 table:



 SQL> drop table T_MOVE2;

Table dropped.

Elapsed: 00:00:00.37SQL> drop table T_MOVE2;

Table dropped.

Elapsed: 00:00:00.37




 I consult the table index T_MOVE1 to confirm that its state is valid after the reset:


SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1';

INDEX_NAME TABLE_NAME STATUS
-------------------- -------------------- --------
I_MOVE1 T_MOVE1 VALID

Elapsed: 00:00:00.01




Again I consult the segments:



 SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
BIN$UbJLSmoxG+ngUxMPqMBjFg==$0 INDEX USERS
BIN$UbJLSmoyG+ngUxMPqMBjFg==$0 TABLE USERS
I_MOVE1 INDEX USERS
T_MOVE1 TABLE USERS2

Elapsed: 00:00:00.11




As I am with recyclebin enabled the segments remain with the prefix BIN $ ... so it is necessary to clean the recyclebin:


SQL> purge recyclebin;

Recyclebin purged.

Elapsed: 00:00:00.39




The move from table T_MOVE1 to tablespace USERS2 was completed successfully online.


Move from tables using MOVE (offline)


The purpose here is to move the T_MOVE1 table to the USERS tablespace using the MOVE clause of the ALTER TABLE command. This method generates unavailability for application and also invalidates table indexes. This procedure works from Oracle Database 9i.


I'll look at the segments and check in which tablespace they are stored:


SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS
T_MOVE1 TABLE USERS2

Elapsed: 00:00:00.05



I confirm that the index is valid:


SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1';

INDEX_NAME TABLE_NAME STATUS
-------------------- -------------------- --------
I_MOVE1 T_MOVE1 VALID

Elapsed: 00:00:00.01



Move the T_MOVE1 table to the USERS tablespace:


SQL> alter table t_move1 move tablespace users;

Table altered.

Elapsed: 00:00:00.24




I verify that the index has become unusable (unusable):


SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1';

INDEX_NAME TABLE_NAME STATUS
-------------------- -------------------- --------
I_MOVE1 T_MOVE1 UNUSABLE

Elapsed: 00:00:00.00

I check the segments and note that the index object exists, but not its segment:

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
T_MOVE1 TABLE USERS

Elapsed: 00:00:00.01

I also rebuilt the index in the USERS tablespace:

SQL> alter index i_move1 rebuild tablespace users;

Index altered.

Elapsed: 00:00:00.26

Now the index has become valid:

SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1';

INDEX_NAME TABLE_NAME STATUS
-------------------- -------------------- --------
I_MOVE1 T_MOVE1 VALID

Elapsed: 00:00:00.00
The index segment was created after its rebuild:
SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS
T_MOVE1 TABLE USERS

Elapsed: 00:00:00.01

Move from tables using MOVE ONLINE (online)

The purpose here is to move the T_MOVE1 table to the USERS2 tablespace using the MOVE ONLINE clause of the ALTER TABLE command. This method does not generate unavailability for application and does not invalidate the table indexes. This procedure works from Oracle Database 12cR2.

I'll look at the segments and check in which tablespace they are stored:

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS
T_MOVE1 TABLE USERS

Elapsed: 00:00:00.01

I move the T_MOVE1 table to the USERS2 tablespace using MOVE ONLINE:

SQL> alter table t_move1 move online tablespace users2;

Table altered.

Elapsed: 00:00:00.94

I check the state of the table index and note that it remains valid:

SQL> select index_name, table_name, status from user_indexes where table_name='T_MOVE1';

INDEX_NAME TABLE_NAME STATUS
-------------------- -------------------- --------
I_MOVE1 T_MOVE1 VALID

Elapsed: 00:00:00.00

Here as process bonuses I rebuild the index online in the tablespace USERS2:

SQL> alter index i_move1 rebuild online tablespace users2;

Index altered.

Elapsed: 00:00:00.20

I check the segments again:

SQL> select segment_name, segment_type, tablespace_name from user_segments;

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
I_MOVE1 INDEX USERS2
T_MOVE1 TABLE USERS2

Table and index moved to the USERS2 tablespace without generating unavailability for the application using the new MOVE ONLINE feature of Oracle Database 12cR2.

Moving tables in Oracle has other methods not covered in this article, but you can also move them using DataPump.

I hope this article is useful for many people. If you liked, subscribe to the blog as a contribution and still know every time you leave a new article.